August 10, 2004 at 10:41 pm
Hi All,
I have a domain user used to start up all SQL services on our SQL servers. Can anyone enlighten me on what the minimum permissions are that I can give to this user to do this specific task, both at the Server layer and the SQL layer. Ideally I dont want this user added to SQL server with Server administrator role.
Cheers,
Terry
August 12, 2004 at 8:33 am
Best practice says that the sql server service account should be a local administrator on the server. That should give the login all the permissions it needs. I don't believe that login will need sys admin rights to the server. I don't think the service account actually has to be granted access to SQL Server by creating a login for it.
Do a search in BOL for Setting up Windows Service accounts. It should help shed some light on it.
John
August 12, 2004 at 8:45 am
From a security perspective this philosophy has changed greatly. Even those of us who used to recommend running as a full-blown administrator do not any longer. Too many issues you can't resolve. Best practice says to run with the minimum rights necessary (basically carrying the Principle of Least Privilege over to the service account). I'll take my lumps jumping through hoops with monitoring, etc. but it's better than someone owning SQL Server and adding themselves to the local Administrators group using xp_cmdshell (one example).
And yes, the service account will need a login to SQL Server and will need to be a member of the sysadmin role.
In Books Online: Installing SQL Server >> Overview of Installing SQL Server >> Setting up Windows Services accounts.
K. Brian Kelley
@kbriankelley
August 12, 2004 at 8:55 am
Check out this KB article. It tells you what "rights" the service account should have and how to manually configure it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;283811
I have personally just set up a regular user account and added the SQL Server service and/or agent account via Enterprise Manager. This way, SQL Server gives the normal Windows user account all of the rights that it needs (with the exception of filesystem permissions if you have locked those down).
Hope that this helps .....
August 30, 2004 at 10:12 pm
Hi All,
Thank you all for your responses. Its been an interesting ride....and the ride hasn't stopped. Basically we created a Domain account then added this account to start all the servers sql services. The policy was set for this account not to have interactive login. For each server the account was added to the local administrators. For each SQL server security list, the account was also added and give sysadmin rights. We also removed BuiltIn Admins.
Then all the fun started.
What we started getting intermittently was SSPI errors. A bit of digging revealed that because we were using the Domain account the services were not registering in Active Directory. So I ended up learning lots about the underlying security mechanisms of SQL Server. Now that I've learnt it all...Im still no wiser.
Basically I have followed all the steps suggested in an article on that topic from this site(http://qa.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp
)....plus following the 'Security And Delegation' document from books online. I have still not managed to register the SQL service using our domain account. When I run the setspn -a command, it tells me that the object has been updated, but a setspn -l shows that the service has not been added.
Whats more confusing is I have a particular box with NTLM disabled, Named pipes disabled, yet Windows authentication seems to work fine. So if kerberos relys on the service being registered in AD and the TCP/IP protocol enabled...then Im totally stumped how authentication is occuring.
I have MSSQL 2000 Standard Edition installed with SP 3a on a Windows 2000 server with SP 4.
Nothings ever straight forward!
Regards,
Terry
September 3, 2004 at 1:02 pm
I took a different approach and here is how I set up my service account.
service account is a domain user account.
the login is added to the USER group on the server side.
Add domain\user account to c:\program file with read \exec permission
add domain\user account to all SQL server folder with full permission on program, data, log and backup directory.
Add login to SQL Server and give Sys Admin priveledge.
Change SQL Server Agent to run under that service account.
Change SQL Server to run under that service account.
As part of the user group this login can not be login using terminal service program. If you had to login using this service account you would have to go to the consol.
As a security measure, we set sa password, use mixed mode for security and remove Builtin\Administrator.
So far I have not seen any problem on any of my servers.
mom
September 5, 2004 at 5:37 pm
Essentially we have done the same thing. If you had changed your services to run under the domain account via EM, then apparently all the correct permissions are applied to relevant folders and registry values. I'd be interested to know whether your server is authenticating Windows accounts using kerberos or NTLM.
As stated in my previous posting, since the domain account does not have enough privledges in AD, there is no MSSQL service registered. Even though logic tells me that I should be using kerberos, I havent been able to prove it.
Thanks,
Terry
October 21, 2004 at 4:27 pm
I had to got the MS support on this and basically you can use setspn to register the account
From BOL:
Adding an SPN to SQL Server
To add an SPN on an instance of SQL Server named "myserver.microsoft.com", for an instance listening on port 1433, using service account MYDOMAIN\sqlsvc, run the following at a command prompt:
setspn -A MSSQLSvc/myserver.microsoft.com:1433 sqlsvc
You cannot use the Netbios name. You must use the fully qualified DNS name. You cannot specify the domain qualifier for the service account. You must use only the account name.
To change and use the LocalSystem account, enter the following code at a command prompt to delete the previously registered SPN :
setspn -D MSSQLSvc/myserver.microsoft.com:1433 sqlsvc
I could not verify this had taken place but using ADSI(in the Win2K Support tools) looking at the Domain NC/DC Domain/CN USERS/Username used to start SQL service instance. I was able to view Service Principle names and see that the above command took effect.
Sean Wyatt
seanwyatt.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply