identity problem using xp_cmdshell to copy file over network 
Author Message
 identity problem using xp_cmdshell to copy file over network
I've written a stored procedure that uses xp_cmdshell to copy a file. It
works fine on the SQL Server (copying the file to the local machine), but
fails with a 'permission denied' error when I set the destination to a
network drive. How can I get around this? What identity do I need to use and
how do I set it?

Thanks,

Dennis



Tue, 03 Dec 2002 03:00:00 GMT
 identity problem using xp_cmdshell to copy file over network

 Dennis,

 > I've written a stored procedure that uses xp_cmdshell to copy a file. It

Quote:
> works fine on the SQL Server (copying the file to the local machine), but
> fails with a 'permission denied' error when I set the destination to a
> network drive. How can I get around this? What identity do I need to use and
> how do I set it?

Q.  Why can't I get at a network file when I run a program with xp_cmdshell
from SQL Server?

A.  The reason is that the MSSQLSERVER service is running under a separate set
of NT credentials.  It doesn't matter who YOU are logged on as (after all SQL
runs quite happily when no-one is logged on to the console doesn't it).  
Therefore your logon account and any mapped drives are irrelevant.  It is SQL
Server running the program (e.g. bcp) not you.

If you're logged on as "sa" or are still running SQL 4.x then xp_cmdshell runs
with the NT account that the mssqlserver service is configured to run under.  
The default set of NT credentials used by MSSQLSERVER is the Localsystem
account.  You can check what userid that MSSQLSERVER is running under by
looking at control panel/services highlighting MSSQLSERVER and choosing the
start-up option.  If no username is present then you are using the localsystem
account - this account has no access to shares on the network as it isn't an
authenticated network account.  

If you're not logged on as "sa" then another account may be used :-

   With 6.5 and below it is used if the option (in SQL setup)  'xp_cmdshell -
simulates Client' is checked.  

   With SQL 7.0 the other account is always used for non "sa" users.  

This account is SQLExecutiveCmdExec for SQL 6.x and SQLAgentCmdExec for SQL
7.0.  These accounts are only in the local user group by default.

So, if you want a program running under xp_cmdshell to access a network
resource your choices are :-

1.  Change the account the MSSQLSERVER service runs under to a user account
with the relevant network rights.

2.  For non-sa users give the "xxCmdExec" account the relevant permissions

or

3.  To get localsystem to work - amend the following registry value on the
TARGET server and add the sharename you want to access - the share does not
then authenticate who is coming in and so a Localsystem account will work.  The
server service on the target server must be re-started before the change takes
effect.  Note that this effectively removes security on that share, so you need
to be careful about what is in the share.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\Nu
llSessionShares

Whichever method you use, you MUST use a UNC name to reference the resources
required and not a drive letter.

e.g.  xp_cmdshell 'dir \\server01\share'

===

v1.01 1999.11.03
Applies to SQL Server versions  : All
FAQ Categories                  : Troubleshooting, Connectivity
Related FAQ articles            : n/a
Related Microsoft Kb articles   : n/a
Other related information       : n/a
Authors                         : Neil Pike
 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq



Tue, 03 Dec 2002 03:00:00 GMT
 identity problem using xp_cmdshell to copy file over network
Thanks Neil for this very thorough and helpful response.

Dennis



Wed, 11 Dec 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Problem invoking VB6 EXE file using xp_cmdshell

2. Problem copying data from ASCII file to SQL 6.5 using BCP

3. Problem with importing flat file using COPY

4. Identity check lost when using wizard to copy tables

5. Help using batch file, cursor and xp_cmdshell

6. Delete a file using xp_cmdshell

7. Output Text File Line Breaks Using master..xp_cmdshell

8. Run *bat files using master..xp_cmdshell conmmand

9. using xp_cmdshell to launch VB or .bat file

10. Run *bat files using master..xp_cmdshell conmmand

11. How to move a file without using xp_cmdshell?

12. How to create a directory or file in a SP without using xp_cmdshell


 
Powered by phpBB® Forum Software