February 4, 2008 at 11:14 am
I've been tasked with gathering all of our SQL Servers in our company and finding out just what we have.
One of my other tasks was to setup a specific account for our SQL servers to run under (domain account).
Hopefully, a few easy questions here.
I was reading that it was a good idea to setup all the SQL Servers to run under a specific Domain Account, for extra added security. Does the same hold true for the SQL Server agent?
Is there any instance where we would want separate accounts for the two?
And lastly, any guidelines for a naming convention? I would imagine, you might not want it to obvious of a name?
Appreciate it.
JW
February 4, 2008 at 1:57 pm
Probably the most secure thing you could do is have everything run as LocalSystem (not a domain account).
Using a domain account, having one account per server and per service would be more secure since you could tailer each account to only have very specific permissions required by that SQL server or the SQL Agent. You could also have all different passwords changing on different intervals making it really hard to break into more than one server.
On the other hand, that is a lot to manage and I like to try to use a single account for the SQL Service on as may servers as possible. Try to keep the permissions on this account reasonable (not a domain admin). The SQLAgent should also be an account with the minimum permissions necessary. Remember that the accounts that you use will somewhat give their tokens to users with enough permissions on the SQL Server, so if your SQL Agent service account is a domain admin, and you have a user that can create a SQL Job, the user can create a job to execute a command line that gives their login domain admin permissions and then they can do whatever they want.
SQL 2005 disables a lot by default (xp_CmdShell for instance), so keeping these disabled will also help a lot. Try to use accounts that only have enough permissions to do what they need to. If you have one server or process that needs extensive permissions, use a specific login for these actions (either by proxy or by the service account).
February 4, 2008 at 2:21 pm
Michael Earl (2/4/2008)
Probably the most secure thing you could do is have everything run as LocalSystem (not a domain account).Using a domain account, having one account per server and per service would be more secure since you could tailer each account to only have very specific permissions required by that SQL server or the SQL Agent. You could also have all different passwords changing on different intervals making it really hard to break into more than one server.
On the other hand, that is a lot to manage and I like to try to use a single account for the SQL Service on as may servers as possible. Try to keep the permissions on this account reasonable (not a domain admin). The SQLAgent should also be an account with the minimum permissions necessary. Remember that the accounts that you use will somewhat give their tokens to users with enough permissions on the SQL Server, so if your SQL Agent service account is a domain admin, and you have a user that can create a SQL Job, the user can create a job to execute a command line that gives their login domain admin permissions and then they can do whatever they want.
SQL 2005 disables a lot by default (xp_CmdShell for instance), so keeping these disabled will also help a lot. Try to use accounts that only have enough permissions to do what they need to. If you have one server or process that needs extensive permissions, use a specific login for these actions (either by proxy or by the service account).
Appreciate the help.
I was going to create just a domain account (or two, depending on if I need one for the SQL agent), but not make them a domain admin; I would only make the account a local admin on the box.
I just wasn't sure if we needed one account for both SQL server and the SQL agent.
Any particular naming convention? Or just whatever...
Thanks!
Jaso
February 4, 2008 at 9:32 pm
I did some research into this a while back.
When SQL (2005) is installed, it automatically creates some windows groups (SQLServer2005*).
These groups have various permissions for different things.
So, create a domain account (say 'sqlservices') with just normal privileges (member of domain users for example). Then on each SQL server, go into the SQL Server Configuration Manager, and within there you can adjust the sql server and the sql agent login accounts to use that domain account.
What this does in the background (and you should confirm this by looking in the Local Users and Groups on the machine) is it will add that domain account to the necessary local SQLServer2005* groups. This gives that user the necessary permissions to start SQL and do all of those things.
What I have found, however, is it doesn't give the user Logon As A Service rights, so you may need to do that manually.
Adjusting the identity account within the normal windows Services control panel isn't sufficient, as that tool doesn't add the user to the necessary groups. Or you could add the user manually to the groups and then do it via Services.. whatever is easiest for you. I can't recall off hand which groups the SQL Configuration Manager adds the user too but you'll see that once you've done one.
Hope this helps. It has been my experience that the sql services user does NOT need, and shouldn't have, any administrative permissions at all. Running it under LocalSystem is not recommended.
There's a number of documents about this on the web.
cheers
Dave
February 5, 2008 at 10:39 am
floyd99 (2/4/2008)
I did some research into this a while back.When SQL (2005) is installed, it automatically creates some windows groups (SQLServer2005*).
These groups have various permissions for different things.
So, create a domain account (say 'sqlservices') with just normal privileges (member of domain users for example). Then on each SQL server, go into the SQL Server Configuration Manager, and within there you can adjust the sql server and the sql agent login accounts to use that domain account.
What this does in the background (and you should confirm this by looking in the Local Users and Groups on the machine) is it will add that domain account to the necessary local SQLServer2005* groups. This gives that user the necessary permissions to start SQL and do all of those things.
What I have found, however, is it doesn't give the user Logon As A Service rights, so you may need to do that manually.
Adjusting the identity account within the normal windows Services control panel isn't sufficient, as that tool doesn't add the user to the necessary groups. Or you could add the user manually to the groups and then do it via Services.. whatever is easiest for you. I can't recall off hand which groups the SQL Configuration Manager adds the user too but you'll see that once you've done one.
Hope this helps. It has been my experience that the sql services user does NOT need, and shouldn't have, any administrative permissions at all. Running it under LocalSystem is not recommended.
There's a number of documents about this on the web.
cheers
Dave
Hi Dave. Appreciate the follow up.
Thought I would chime in here quickly to show some results from my testing I did this morning.
I created a generic domain account, only part of the Domain users group.
I opened up the "Sql Server Configuration" utility and made some changes. Basically, I changed the "Log on As" for the "SQL Server" as well as the "SQL Server Agent".
After that, i checked the groups on the machine and the configuration utility dropped the account into two specific groups:
The one for the SQL Server:
SQLServer2005MSSQLUser
For the SQL Server Agent:
SQLServer2005SQLAgentUser
Just thought i'd post my findings.
Thanks.
Jason
February 5, 2008 at 3:23 pm
Hi Jason,
Yep thats the ones. Did you then restart SQL and SQL Agent ? And is everything working as it should ?
One thing I forgot to mention - make that account a sysadmin within sql too.. ! but i'm sure you would have thought of that 😉
as i said, just be aware of the Log On As A Service... if the services fail to restart, check event log and if it says the user doesn't have that permission, just give it to the user manually with the local security policy on the server.
cheers
dave
February 6, 2008 at 3:32 am
I would advise against running SQL Server as local system. This means SQL Server has local admin rights on the box it is running on. If someone can hack into SQL then they can create their own local administrator account. With that, they can often get into your domain...
It is far better to use either a dedicated local or a domain account to run SQL Server. There can be advantages in running Agent under a different account to SQL. Agent does not need update access to your database files, so using different accounts can help protect your system.
It is worth looking at http://www.codeplex.com/SQLServerFineBuild. This is something I published that helps you get a 1-click install and configuration of SQL Server 2005. The FineBuild Reference doc has a lot of useful information about security and other issues.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 6, 2008 at 11:57 am
I have one production ID that has these specific rights to install and run the MSSQLServer and SQLAgent services:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
Increase Quotas = SeIncreaseQuotaPrivilege
Perform Volume Maintenance Tasks
Adjust Memory Quotes for a process
February 6, 2008 at 12:37 pm
just to add ...
if you are planning to use reporting server, keep in mind it only supports accounts with a maximum character length of 20 characters!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply