Solved

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

Posted on 2001-06-14
12
658 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
[X]
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
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

617 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