Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-11-08
15
748 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
This article runs through the process of deploying a single EXE application selectively to a group of user.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…

789 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