Solved

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

Posted on 2001-06-14
12
647 Views
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)

0
Comment
Question by:BBSmile
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 3

Expert Comment

by:mgmanoj
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.
0
 
LVL 18

Expert Comment

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

Author Comment

by:BBSmile
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.
0
 

Author Comment

by:BBSmile
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.
0
 
LVL 2

Expert Comment

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

Author Comment

by:BBSmile
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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:BBSmile
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.  :-)
0
 
LVL 18

Assisted Solution

by:nigelrivett
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.
0
 
LVL 3

Accepted Solution

by:
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.

0
 

Author Comment

by:BBSmile
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)
0
 

Author Comment

by:BBSmile
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)
0
 

Expert Comment

by:CleanupPing
ID: 9282217
BBSmile:
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
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

10 Experts available now in Live!

Get 1:1 Help Now