External Procedures (Problem to do with impersonation & contexts, I think)
Posted on 2001-06-14
- 2 machines, NT Wkst Client, NT Serv Server (Both NT 4.0 SP 6.a High Encryption)
- MS-SQL Server 7 on Server
- Both machines in the same domain
- Logged in as myself, namely a user in the domain with normal user rights
(I have also made my user part of the local Admins on both machines)
What I want to do:
- There is a field in a DB (varchar 255) with a file path in it.
- I want to check the consistency of this file path, by determining the existence of this file.
What I have done:
- Created an external stored procedure (xp_FileExists) using the Platform SDK function FindFirstFile.
- I have tested the DLL using a normal Console Client - I'm assuming there is no error in my code
(I'm one of those optimistic programmers)
- This function works beautifully for files stored locally on the actual server.
- However, if I want determine the existence of a file using a path such as "//PC/share/test.txt"
(This PC is a 3rd PC, i.e. neither the client nor the server) the file cannot be found.
- I added srv_impersonate_client without a concomitant srv_revert_to_self in the code.
- The following now happened when trying to locate the file "//PC/share/test.txt":
- When running xp_FileExists from Query Analyzer running on the client, the file cannot be found.
- When running xp_FileExists from Query Analyzer on the server, the file is found.
- If I keep Query Analyzer open on the server, the client will now work.
Although this has now solved my problem, it's infeasible as the real system will be located in the field.
What I think is wrong:
- NT security contexts of which I have very little understanding.
- Apparently all external procs are run under the SQL Server context.
I'm not too sure what this context entails and if it is possible to modify it?
- Perhaps impersonation could get around this problem.
The (juicy parts of the) Source Code:
SRVRETCODE xp_FileExists (SRV_PROC* pSrvProc)
// ... all sorts of parameter checking
srv_impersonate_client ( pSrvProc );
hFind = FindFirstFile("////PC//share//test.txt", &FindFileData)
if (hFind == INVALID_HANDLE_VALUE) // ... then we can't find the file
What I would like:
- Any applicable solution
- An explanation about srv_impersonate_client (i.e. an example to see it in action and what you can do with it)