April 1, 2012 at 1:21 pm
Hello,
I am trying to do a backup onto a disk. When I try doing that I get the following error.
System.Data.SqlClient.SqlError: Cannot open backup device 'D:\AdventureWorks.bak'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)
I would like to know how do I configure my Sql Server service account to have access to that drive?
How do I look at the permissions the sql server service account ..for example which drives it has access currently ..etc..?
April 1, 2012 at 2:57 pm
The best answer I could find was using a script of some sort to list files and directories that a windows user account has access to. This is one example I found: http://technet.microsoft.com/en-us/sysinternals/bb664922.
Often only specific windows administrator accounts have access to the root directory of any given drive (d:\ in your case), and in my experience it has been more manageable to create a directory like 'backups' on the d: (or whatever) drive and give the sql account access to that. In my case I like to script T-SQL backups to run in the agent, so I grant read / write access to the 'd:\backups' directory to the windows account that runs the SQL agent service. However, I guess it is possible to give the SQL service or SQL agent account access to the root drive. I've been finding the username of the SQL or SQL agent account from looking at the properties SQL and / or SQL agent services in control panel.
Alternatively I've seen some folks on the web mention giving the account running the sql or agent service local system access but I've always tried to avoid giving SQL service accounts more access than necessary.
You can grant the SQL or agent account access to the directory by right clicking on the directory and selecting security tab under properties menu, it varies a bit from windows versions in my experience, here's a nice windows server 2008 page http://technet.microsoft.com/en-us/library/bb727008.aspx. Alternatively you can use CACLS at the command line (http://technet.microsoft.com/en-us/library/bb490872.aspx
April 1, 2012 at 5:11 pm
THANK YOU VERY MUCH. That was very informative
April 1, 2012 at 6:26 pm
you're welcome and good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply