January 14, 2009 at 12:06 pm
We recently upgraded from Windows 2003 32-bit to Windows 64-bit (SQL Server 2005 SP2). I am able to map the drive, but when I run master..xp_cmdshell 'dir \\servername\drive$\filename ' -- I receive Access is denied.
I did check the file properties and ensured that the account running sql server service has full control. The account is a domain account.
Can anyone direct me on how to solve this?
Thank you in advance!
January 14, 2009 at 12:17 pm
schep021 (1/14/2009)
We recently upgraded from Windows 2003 32-bit to Windows 64-bit (SQL Server 2005 SP2). I am able to map the drive, but when I run master..xp_cmdshell 'dir \\ ' -- I receive Access is denied.I did check the file properties and ensured that the account running sql server service has full control. The account is a domain account.
Can anyone direct me on how to solve this?
Thank you in advance!
I have an update. I changed the account that runs sql server service to domain admin just to see if it was a permissions issue with account. I needed to restart the service in order for the changes to take affect. But, that did fix it. I cannot leave this account as domain admin though. Does anyone know what permissions are needed in order to solve this?
January 14, 2009 at 12:23 pm
I believe that in order to do the dir command on a folder you only need to have Read permissions. I could be wrong but that is my recollection;
January 14, 2009 at 12:26 pm
Mike (1/14/2009)
I believe that in order to do the dir command on a folder you only need to have Read permissions. I could be wrong but that is my recollection;
Thanks Mike. The account does have full control on the directory and the file itself.
January 14, 2009 at 12:33 pm
It may be worth looking at the Effective Permissions, just to be sure that whatever permissions are on that folder has not somehow being overwritten by a previous directory. You can see those by going to the files
Properties-->Security-->Advanced-->Effective Permissions
Also it may be worth asking if the file is on a server that is within a cluster. There have been a few times I have come across issue where on one node the effective permissions are different for a user than on the second node (or third, etc..). I am personally not very Active Directory savvy but it seems like there are some strange things in there every now and then that is worth looking at.
January 14, 2009 at 12:33 pm
Did you just check the file properties or did you check the share properties as well. Depending on how the share was setup you could be having issues with that as well.
Also, you might want to check out the SQL Proxy account as that may play into this as well.
-Luke.
January 15, 2009 at 1:14 pm
Thank you for the suggestions Luke and Mike. I did check effective permissions - the account that sql server service is using does have full control of both that directory and that file specifically.
We do not have any proxy accounts set up.
The drive where the file exists is on the SAN.
I don't think I made it very clear that I am trying to access a file on another server. So, from serverA I am performing a restore that is using a bak file from serverB. I get access denied when I run the following on serverA:
exec master..xp_cmdshell 'dir \\serverB\drive$\'
January 16, 2009 at 12:20 pm
Another note:
I was trying different permission settings. I was able to obtain access by adding the account to the backup operators group on serverB. Adding account to Users, Power Users, or SQLServer2005MSSQLUser$serverB$MSSQLSERVER did not work.
So, it sounds like there are some server permission issues. I will report back it I find more. Feel free to comment or make suggestions! 🙂
Have a great weekend!
Jen
January 16, 2009 at 12:25 pm
It looks like you are going in through an admin share. c$/d$ etc... depending on how your security is setup I've seen security apps where it will look like it saved the security changes you made, but it just reverts them back at the next refresh interval etc... that's why when you added your user account to the Domian Admins group and the Backup operators group everything worked, because you then had enough resources to access that share via the share permissions and the underlying file permissions.
Have you tried using a different share? creating a test one that explicitly gives permissions to the SQL Server account?
-Luke.
January 16, 2009 at 12:28 pm
Luke L (1/16/2009)
It looks like you are going in through an admin share. c$/d$ etc... depending on how your security is setup I've seen security apps where it will look like it saved the security changes you made, but it just reverts them back at the next refresh interval etc... that's why when you added your user account to the Domian Admins group and the Backup operators group everything worked, because you then had enough resources to access that share via the share permissions and the underlying file permissions.Have you tried using a different share? creating a test one that explicitly gives permissions to the SQL Server account?
-Luke.
Luke, thank you for taking the time to reply. I will work with the server admin folks to check this out. I'll get back to you. Thanks! 🙂
January 23, 2009 at 9:37 am
schep021 (1/16/2009)
Luke L (1/16/2009)
It looks like you are going in through an admin share. c$/d$ etc... depending on how your security is setup I've seen security apps where it will look like it saved the security changes you made, but it just reverts them back at the next refresh interval etc... that's why when you added your user account to the Domian Admins group and the Backup operators group everything worked, because you then had enough resources to access that share via the share permissions and the underlying file permissions.Have you tried using a different share? creating a test one that explicitly gives permissions to the SQL Server account?
-Luke.
Luke, thank you for taking the time to reply. I will work with the server admin folks to check this out. I'll get back to you. Thanks! 🙂
I had the server admins create a shared folder for me and that worked like a charm! We are now debating if it would be better to have a shared folder, or add the login as backup operator... Any suggestions or things to watch out for?
January 23, 2009 at 9:53 am
Principle of least privlege says that you create a shared and grant only those rights it needs. IE. give it rights to read/write to that share instead of giving it R/W rights to the entire server/volume etc...
Backup Operator is rather generic and is for a domain or server level setting...
IMHO, If you only need to grant it rights to that share then that's what you should do as it is the most specific(granular) security setting which gives you the appropriate rights to do what you want/need to do, without giving you more.
-Luke.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply