External Procedures (Problem to do with impersonation & contexts, I think)

Posted on 2001-06-14
Last Modified: 2012-05-04
Hi there

My Setup:
- 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)
   WIN32_FIND_DATA FindFileData;
   HANDLE hFind;

   // ... 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)

Question by:BBSmile
  • 6
  • 2
  • 2
  • +2

Expert Comment

ID: 6192532
your impersonate account is not having permission to access your extended stored procedure. Grant the permission to that account specificall defining the procedure name & execute permission with grant statement.
LVL 18

Expert Comment

ID: 6192589
why not just use a xp_cmdshell 'dir ...' command to check for the existance of the file?

Author Comment

ID: 6192683
mgmanoj - thanks for responding so promptly.

I forgot to mention that I always log in as 'sa'.
I've now tried creating and logging in as another user.
I've also tried NT authentication.
No Luck!

Perhaps I do not fully understand what you are proposing?

If you have a similar extended proc working on your side,
Perhaps you can tell me exactly what you did. I'll try it on my side then.

Author Comment

ID: 6192714
mgmanoj - thanks for responding so promptly.

I forgot to mention that I always log in as 'sa'.
I've now tried creating and logging in as another user.
I've also tried NT authentication.
No Luck!

Perhaps I do not fully understand what you are proposing?

If you have a similar extended proc working on your side,
Perhaps you can tell me exactly what you did. I'll try it on my side then.

Expert Comment

ID: 6192730
I think the UNC format should be "\\machinename\shared folder name" instead of "//"

Author Comment

ID: 6192766
Oooops - seems I'm a bit eager with that refresh button.

nigel - Thanks for the input - I'm thinking about your option. I would rather go the external procedure route, as I feel that calling a shell is a last ditch solution. True - the results will be identical, but it's personal design choice.

I also plan to use the DLL for more advanced file functions that I rather wouldn't do from a shell.
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.


Author Comment

ID: 6192826
[Nigel] ? just tried it out.

EXEC master..xp_cmdshell "dir *.*?  works like a charm, BUT!

EXEC master..xp_cmdshell "dir //PC/share/test.txt"
Returns ?Access is denied.?
I have granted EXEC permission to this user.
What am I doing wrong here?

If I open a prompt; dir //PC/share/test.txt works from both the client and the server. I think if we solve either of these approaches, both will be sorted!

[Cqoca] ? If you?ve been staring at this one as long as I  have ?\? and ?/? look the same.  :-)
LVL 18

Assisted Solution

nigelrivett earned 150 total points
ID: 6192872
if you are sa you will be using the sql server service security context.
If you are not part of sysadmin group you will default to the sql server agent service account.

Accepted Solution

mgmanoj earned 150 total points
ID: 6192906
I think the nt login you are using on client is not having permission to access the store procedure & that is not required also. check your security criteria you are not forcing the login of client as user on that & you are allowing the login & password of impersonate_client only to work.


Author Comment

ID: 6193025
Like a good little house elf, I've earned my sleep!

This is what I did:
Nigel thanks for the last comment (vis. the different contexts)
I made MSSQL + Agent services run under my account Domain\BBSmile vs. the system account.
I took the impersonation out (I would still like to see an example using impersonation as I don't understand it properly).
Then everything worked (my external proc + xp_cmdshell)!

I'm not excessively happy with the idea of using my own account - is there any way to get around this?
(I'd love to continue this after a few hours of sleep - bye for now)

Author Comment

ID: 6195024
- Please refer to the section: "What I would like:" in my problem statement.
Part 1, namely providing me with an acceptable solution has been done - thnkx to Nigel and Manoj.
Part 2 is outstanding - no one has given me an explanation of impersonation with a worked example.
- Both your answers steered me towards working code, I'll give both of you some points
(300 to the person who also explains impersonation, 75 to the other)

Expert Comment

ID: 9282217
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using Aggregate Functions to Count 3 34
Need to update TableA to TableB 6 34
SQL Server Question 5 26
Can someone plz fix this..getting an error 3 18
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now