Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2001-06-14
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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.

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 300 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 300 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

704 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