SQL Server 2008 permission issues on Windows Server 2008 R2, help please.
Hello,
I recently installed a new server on our VM Network, SQL Server 2008 SP1 on Windows Server 2008 R2 Enterprise (both 64bit).
I am having some strange issues though, I setup the SQL services to run under NT AUTHORITY\NETWORK SERVICES during installation, everything installed just fine, but now I am running into issues such as...
When I try to back-up a database, and save it to the Backup directory on that same server, I get a permission denied message:
"Operating System Error 5 ( Access is Denied)"
Any idea how to fix this? I tried to move some databases over, doing a simple OS level copy/paste from the remote SQL, and I get access denied messages.
I am locally logged on to the SQL box as a Domain Admin, I can create new files on the NTFS location, but trying to copy stuff from remote location or using Server Manager Studio to back-up fails.
Any ideas?
Thanks.
I recently installed a new server on our VM Network, SQL Server 2008 SP1 on Windows Server 2008 R2 Enterprise (both 64bit).
I am having some strange issues though, I setup the SQL services to run under NT AUTHORITY\NETWORK SERVICES during installation, everything installed just fine, but now I am running into issues such as...
When I try to back-up a database, and save it to the Backup directory on that same server, I get a permission denied message:
"Operating System Error 5 ( Access is Denied)"
Any idea how to fix this? I tried to move some databases over, doing a simple OS level copy/paste from the remote SQL, and I get access denied messages.
I am locally logged on to the SQL box as a Domain Admin, I can create new files on the NTFS location, but trying to copy stuff from remote location or using Server Manager Studio to back-up fails.
Any ideas?
Thanks.
AmmarR
change the SQL services from NT AUTHORITY\NETWORK SERVICES to local system
you need to ensure that the account that is running the SQL Server Service, has permissions to the required folder that you're backing up.
ASKER
Should I change all the services to go from Local System?
SQL Server
SQL Server Agent
SQL Server Integration, Reporting, services?
SQL Server
SQL Server Agent
SQL Server Integration, Reporting, services?
it depends
if your server is just local and uses local settings you can just use local system for all
if you need specific resource that requires a domain account, you will need to use a domain account.
e.g if you want to create a job that uses a domain resource you will need a domain account
if you want to creat an ssis package that will send email you will need to use a domain account for your integration services
but for a normal server local system is perfect
if your server is just local and uses local settings you can just use local system for all
if you need specific resource that requires a domain account, you will need to use a domain account.
e.g if you want to create a job that uses a domain resource you will need a domain account
if you want to creat an ssis package that will send email you will need to use a domain account for your integration services
but for a normal server local system is perfect
for the job you will need a domain account for you sql agent service i mean..
by the way check this discussion
https://www.experts-exchange.com/questions/26557612/SQL-Server-2008-Express-See-only-C-drive-for-backups-ans-restores.html
by the way check this discussion
https://www.experts-exchange.com/questions/26557612/SQL-Server-2008-Express-See-only-C-drive-for-backups-ans-restores.html
nice article you might want to read about SQL Server 2005 Service Accounts
http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/
http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/
I would personally never let the services run using local system. Have a google on it and security to learn more, but it's not recommended.
Unless it's just on your local machine for dev or test purposes.
Unless it's just on your local machine for dev or test purposes.
ASKER
This is for a production SQL Server, mostly used internally by our applications, not exposed to the web.
I want to be able to deploy SSIS/SSAS packages to it remotely (from say, another DEV SQL server on the network), and possibly have the SQL Server send emails through our exchange server (also on the same network).
Currently I switched to "LocalSystem" account for all my SQL Services, and backing up works just fine on the D:\ and E:\ drives.
After the changes, SQL Server started back up fine, I am just a bit skeptical changing these post-installation, do not want to run into issues down the road when I bring over all my databases.
Capture.JPG
I want to be able to deploy SSIS/SSAS packages to it remotely (from say, another DEV SQL server on the network), and possibly have the SQL Server send emails through our exchange server (also on the same network).
Currently I switched to "LocalSystem" account for all my SQL Services, and backing up works just fine on the D:\ and E:\ drives.
After the changes, SQL Server started back up fine, I am just a bit skeptical changing these post-installation, do not want to run into issues down the road when I bring over all my databases.
Capture.JPG
ASKER
Looking more into this, our SQL2005 box, which is the one we are migrating from is set to run as "domain\service account, which has access to most of our network stuff.
Probably a good idea to just replicate that setup.
Probably a good idea to just replicate that setup.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dear mirde:
you can easily change service accounts any time in the future if you fall into issues
if your server is as you mentioned its just internal and you will uses SSAS you can have your sql server and SSAS on local system
i suggest you keep it now as local system and depends on your requirements in the future you can change it accordingly.
because its really not standard...
read this article http://sql-articles.com/articles/general/sql-server-service-accounts
and in the other i posted and you can google about it as Humpdy: said
you can easily change service accounts any time in the future if you fall into issues
if your server is as you mentioned its just internal and you will uses SSAS you can have your sql server and SSAS on local system
i suggest you keep it now as local system and depends on your requirements in the future you can change it accordingly.
because its really not standard...
read this article http://sql-articles.com/articles/general/sql-server-service-accounts
and in the other i posted and you can google about it as Humpdy: said
ASKER
I can see now that the accounts the service run on does change depending on the setup, so I assume this won't hurt my current setup "changing" these.
Currently I have it setup with:
SSIS > Domain Account
SQL Full Text - DISABLED
SQL Server - Domain Account
SSAS - Domain Account
SSRS - NT Authority \ NetworkService
SQL Server Browser - NT Authority \ NetworkService
SQL Server Agent - Domain Account
What is the different between networkService and a Domain Account?
Currently I have it setup with:
SSIS > Domain Account
SQL Full Text - DISABLED
SQL Server - Domain Account
SSAS - Domain Account
SSRS - NT Authority \ NetworkService
SQL Server Browser - NT Authority \ NetworkService
SQL Server Agent - Domain Account
What is the different between networkService and a Domain Account?
network service won't have access to anything outside of the box like a domain a/c will.
Which will in turn mean you'll have less ability for any interaction outside the box with a non-domain a/c.
Which will in turn mean you'll have less ability for any interaction outside the box with a non-domain a/c.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This has answered my questions, and solved my issue where the SQL Server was not able to access the disk to back-up databases to.