Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sonnysingh
Starting Member
42 Posts |
 Posted - 2007-04-05 : 02:37:08
|
Hi FolksI am running this following commandexec master..xp_cmdshell 'copy E:\backups\*.bak \\bakserver\backups\' giving error: Access DeniedI am member of sysadmin and also have executing rights on xp_cmdshell sp. SQL Server user is same as login to the windows on this source SQL Server. Destination SQL Server have same configuration. When I run this same copy command on command line it's work fine.FYI... I have windows authentication mode on my SQL Server PC and when i run this copy command from QA it's run fine too.What I am missing??? plesae guide me through steps that what I need to check or what are things need to configure?Thanks in advance as always... it is matter of urgency guys...sonnySqlIndia |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-05 : 02:54:43
|
| Do you have file access to the two UNC paths?Peter LarssonHelsingborg, Sweden |
 |
|
|
sonnysingh
Starting Member
42 Posts |
Posted - 2007-04-05 : 04:06:12
|
| Thanks Pesoseems to me yes cos like I said that copy the file from source server to the destination server working fine from command line but not from QA using 'EXEC master..xp_cmdshell..."By the way what is UNC path??Thanks..SqlIndia |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-04-05 : 06:09:50
|
| UNC...universal naming convention....\\servername\drirectorysharenamealternative method used to map to a drive...rather than just use I:\, J:\, etcRemember E: needs to be local from the view point of the server?...and not from your own client PC. Try some other command like DIR or ECHO, to see if it's a xp_cmdshell access problem, or access rights once xp_cmdshell is active and running. |
|
|
|
sonnysingh
Starting Member
42 Posts |
Posted - 2007-04-05 : 12:17:00
|
| Thanks AndrewI did tried the these suggestions and it's worl fine locally but not for destination server on the network. could anyone guide on steps I need to make sure to work this?Thanks,sonnySqlIndia |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-05 : 12:54:32
|
from BOLquote:
Remarksxp_cmdshell operates synchronously. Control is not returned until the command shell command completes.When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure. When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
It uses the SQL Server startup account to run the command shell. By default, SQL Server is startup using the SYSTEM ACCOUNT. System account can only access the local folders and not network folders. This explain why it will works for local folder but not network folder.What you need to do is to create a user a/c with admin rights and use this account to startup your sql server.
KH |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
xp_cmdshell running problem