Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2010-11-08
15
Medium Priority
?
760 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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 1000 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 1000 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

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I’m willing to make a bet that your organization stores sensitive data in your Windows File Servers; files and folders that you really don’t want making it into the wrong hands.
In this article, we will discuss how you can secure Active Directory using free tools, and how you can choose a safe and secure Active Directory security auditing tool.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

579 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