May 9, 2008 at 12:35 pm
I'm running the below queries:
EXEC master..xp_cmdshell 'dir F:\Test'
It works fine by returning the directory & files in this location.
When I execute:
EXEC master..xp_cmdshell 'dir \\server-name\Test'
It gives error message that no access to that file system.
"server-name" is the name of the universal system name where the SQL Server is located. Also, I have the permission to this location.
Thanks.
May 9, 2008 at 1:24 pm
I don't think xp_cmdshell runs under your user rights, I think it runs under the rights of the SQL Server service. So, referencing a location using drive letter will work, but if the SQL Server service account has no network rights accessing a location using sharing and UNC path may not work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 9, 2008 at 1:29 pm
Thanks, Jack. But, how to find the service account name that is running the SQL Server instance?
May 9, 2008 at 1:34 pm
THe SQL Server Service Manager which runs in the system tray or in the services applet. Most likely it is running under local system which has no network rights or Network System which has limited network rights.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 9, 2008 at 1:37 pm
Thanks Again. But, if that account is a "SA" & not a windows trusted connection how will I assign the SQL Server login id with network rights on them.
If I'm a domain user my user id can be added through security tab. How to add for a SQL Server login id?
Thanks.
May 9, 2008 at 1:44 pm
The SQL Server Service itself does not run as 'SA', SA is a SQL Server user NOT a windows user. The SQL Server Serivce is a Windows Service, like Automatic Updates, which has an account that is runs under. By default I think SQL Server 2000 installs using the Local System account, but can be assigned a domain login as well, if your SQL Server needs network access or in SQL 7 and 2000 you want to use SQL Mail. You can change the SQL Server Account in the Services applet in Control Panel.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 9, 2008 at 1:52 pm
The issue I have is when I use this xp_cmdshell command from SQL Agent job it is failing when using UNC name. But, when I use the direct path (f:\test) it is working fine.
But, in production I want the UNC to be placed rather than direct path. So how would I resolve this.
May 9, 2008 at 2:51 pm
Verify the startup properties of your SQL Agent account.
Start-Run-services.msc
Scroll down to SQLSERVERAGENT. Look at the "Log On As" column. This user needs to have rights on not only have rights on the file system (security tab), but the share (sharing/permissions tab) as well.
Kyle
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply