January 30, 2008 at 12:25 pm
Hi,
I am trying to do a back up of the log files and it fails stating that the location is not reachable
When i checked the path there is a folder name which has spaces in between them, Is this the issue and how do I get around it
EXEC master..xp_cmdshell Dir "\\ServerName\ShareName\Folder 1\"
The name is "Folder 1" and not Folder1. The network admin confirmed that the SQLServerAgent did have read\write access to this folder.
Thanks
Vinu Verma
January 30, 2008 at 2:15 pm
It looks like you're missing a few single quotes, but those may have gotten dropped by the forum software.
1) Is the job owned by sa or another member of the sysadmin fixed server role? If not, it's running as the SQL Server Agent Proxy account. By default, this isn't set.
2) xp_cmdshell, when executed by sa or a member of the sysadmin fixed server role, runs under the context of the SQL Server service account. If this is different from the SQL Server Agent service account, you're checking permissions on the wrong account.
3) Verify that not only does the service account have rights to the folder, but to the share as well. An easy way to test is to log on as that service account and try to browse to the share location manually. But NTFS and share permissions are evaluated separately and the server will go with whichever set is most restrictive (gives the least access). That means if at the file system level the account has access but it doesn't through the share (or vice versa), you don't get access through that share.
K. Brian Kelley
@kbriankelley
January 30, 2008 at 5:15 pm
K. Brian Kelley (1/30/2008)
It looks like you're missing a few single quotes, but those may have gotten dropped by the forum software.1) Is the job owned by sa or another member of the sysadmin fixed server role? If not, it's running as the SQL Server Agent Proxy account. By default, this isn't set.
The job is run under an sa account
2) xp_cmdshell, when executed by sa or a member of the sysadmin fixed server role, runs under the context of the SQL Server service account. If this is different from the SQL Server Agent service account, you're checking permissions on the wrong account.
I used the RUNAS command to open the SSMS using the SQL Server Agent to use the xp_cmdshell command
3) Verify that not only does the service account have rights to the folder, but to the share as well. An easy way to test is to log on as that service account and try to browse to the share location manually. But NTFS and share permissions are evaluated separately and the server will go with whichever set is most restrictive (gives the least access). That means if at the file system level the account has access but it doesn't through the share (or vice versa), you don't get access through that share.
I will try this out..
Thanks
Vinoj
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply