July 27, 2012 at 1:05 pm
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?
July 28, 2012 at 3:36 am
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.
July 29, 2012 at 4:50 am
Under which account is the sql server service running ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 29, 2012 at 2:31 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2012 at 9:52 am
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.
July 30, 2012 at 10:03 am
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
July 30, 2012 at 10:20 am
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.
August 7, 2012 at 4:00 am
Check out your share level permissions if you are using this to specify your backup location as the most restrictive rights will apply.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply