Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Permissions on Service Account Expand / Collapse
Author
Message
Posted Friday, July 27, 2012 1:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 529, Visits: 1,567
I was under the impression that when you specify the SQL Service Account during install of SQL Server, ALL rights needed for the service account were granted to the account specified as the service account. I thought it was not necessary to add addional rights, such as adding the sevice account to local admin group, But here's what I just ran into...

I insalled SQL 2008 R2-64 bit on a VM. I created a share on the VM and copied a database backup into it. When connencted to the instance via SSMS as "sa" and trying to restore the backup, the restore backup wizard was unable to see any files in the share. When types the name of the backup file, I got an error saying that the database engine could not see the file doe to it not existing or having sufficient rights. Then I logged on to the server via RDP as Administrator (which was added to SQL as a sysadmin)and had the same result, even though Administrator see the the BAK file in the file system.

Next I moved the BAK file to the folder designated as the backup folder during the SQL install. The remote SSMS session (as sa) still could not access the BAK, but the local SSMS session could and was able to restore the DB.

So is it often nessisary to give the SQL service account additional after assigning itas the service account during install or in SQL Configuration Manager?
Post #1336724
Posted Saturday, July 28, 2012 3:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 3, 2014 5:22 AM
Points: 92, Visits: 321
I think there are instances when SQL Server service account will require access to something that it may not be directly under its purview.

Therefore most DBAs keep life simple by making the service account a member of local administrator's group.



Post #1336896
Posted Sunday, July 29, 2012 4:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Under which account is the sql server service running ?
Post #1336983
Posted Sunday, July 29, 2012 2:31 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 4,388, Visits: 9,508
The installation grants all of the rights necessary for that service account to run SQL Server.

That does not mean that service account has full administrator access to the server, or - in fact - access to all of the folders in the system.

As of 2008 on Windows Server 2008, it gets even tighter and your service account doesn't even have access to SQL Server. The service is run under the service account - and a special service SID is created - and that service SID is granted access.

So - yes, if your service account needs to do anything else then you need to grant the additional privileges.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1337029
Posted Monday, July 30, 2012 9:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 529, Visits: 1,567
Thanks. Altough you would think that when logging in remotely as "sa" you should see files in the folder designated as the "Backup" location during installation.

In the interests of testing the "minimum rights needed to do the job" philisophy, I will give the "SQLService" account read only rights to the network share I initially copied the BAK to and see if sa can see files there when connected remotely.

Post #1337355
Posted Monday, July 30, 2012 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
the hiccup there, as you identified, is when files exist on the network, and not the local disk.

The system account is one of those special accounts that never logs into the domain, so it doesn't get access to any network shares.

If the instance ever needs anything except local disk access, I usually create a new user like SQLAdminMachineName in ActiveDirectory, and give it the network shares it needs access to, and use that as the startup acocunt for the service instead of the system account.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1337363
Posted Monday, July 30, 2012 10:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 529, Visits: 1,567
In this case, the network share is on the same server as the SQL instance. I use that share to copy items (like BAK files from other SQL instances) to this server. At work we put the service accounts in the local admin group because the application vendor says to, but in case I get a future job at a place that likes to lock down security, but in my self-training I like to figure out the minimum rights needed.

Post #1337383
Posted Tuesday, August 7, 2012 4:00 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:19 AM
Points: 728, Visits: 771
Check out your share level permissions if you are using this to specify your backup location as the most restrictive rights will apply.
Post #1341115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse