Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

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.
0
mirde
Asked:
mirde
  • 6
  • 5
  • 4
2 Solutions
 
AmmarRCommented:
change the  SQL services from NT AUTHORITY\NETWORK SERVICES  to local system
0
 
HumpdyCommented:
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
 
mirdeAuthor Commented:
Should I change all the services to go from Local System?

SQL Server
SQL Server Agent
SQL Server Integration, Reporting, services?
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
AmmarRCommented:
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
 
AmmarRCommented:
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
 
AmmarRCommented:
nice article you might want to read  about SQL Server 2005 Service Accounts

http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/
0
 
HumpdyCommented:
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
 
mirdeAuthor Commented:
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
 
mirdeAuthor Commented:
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
 
HumpdyCommented:
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
 
AmmarRCommented:
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
 
mirdeAuthor Commented:
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
 
HumpdyCommented:
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
 
AmmarRCommented:
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
 
mirdeAuthor Commented:
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.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now