Solved

SQL Server 2008 permission issues on Windows Server 2008 R2, help please.

Posted on 2010-11-08
15
743 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:mirde
  • 6
  • 5
  • 4
15 Comments
 
LVL 15

Expert Comment

by:AmmarR
ID: 34085210
change the  SQL services from NT AUTHORITY\NETWORK SERVICES  to local system
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085221
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.
0
 

Author Comment

by:mirde
ID: 34085240
Should I change all the services to go from Local System?

SQL Server
SQL Server Agent
SQL Server Integration, Reporting, services?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34085275
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
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34085293
for the job you will need a domain account for you sql agent service i mean..

by the way check this discussion

http://www.experts-exchange.com/Software/Office_Productivity/Productivity_Applications/Q_26557612.html
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34085326
nice article you might want to read  about SQL Server 2005 Service Accounts

http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085347
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.
0
 

Author Comment

by:mirde
ID: 34085403
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
0
 

Author Comment

by:mirde
ID: 34085439
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.
0
 
LVL 10

Accepted Solution

by:
Humpdy earned 250 total points
ID: 34085446
sql browser should be turned on if you're interacting with other systems/databases.
turn off full text if you're not using it.
and I would certainly create a domain a/c specifically for sql to run these service accounts.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34085478
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
0
 

Author Comment

by:mirde
ID: 34085614
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?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34085657
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.
0
 
LVL 15

Assisted Solution

by:AmmarR
AmmarR earned 250 total points
ID: 34086183
The NetworkService account is a predefined local account used by the service control manager. This account is not recognized by the security subsystem, so you cannot specify its name in a call to the LookupAccountName function. It has minimum privileges on the local computer and acts as the computer on the network.

http://msdn.microsoft.com/en-us/library/ms684272(VS.85).aspx

while a domain account is a domain account and depends on the access this account have.
-----

Dear Humpdy.

i would agree about using a domain account if as you said you need to interact outside your local box, but now in the case of mirde, he mentioned that this sql only uses local system, so why to give sql access to things it doesn't need.

yeah i would agree to give SSIS a domain account or sql agent if he requires that, but why sql server service.

0
 

Author Closing Comment

by:mirde
ID: 34087552
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.
0

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now