Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Spyder
SQLTeam Author
75 Posts |
 Posted - 2002-02-07 : 17:35:20
|
Much of this has been gleaned from the most excellent MS SQL Server 2000 Security whitepaper by Richard Waymire and Ben Thomas, which is available at [url]http://www.microsoft.com/sql/techinfo/administration/2000/2000SecurityWP.doc[/url].Below, I've summarized some of the key points relating to SQL Server service accounts and added in some of my own observations. One point I find interesting is even though the account running MSSQLSERVER does not have to belong to any SQL Server roles, the service account running the SQLSERVERAGENT must be a member of the System Administrator role. Anyhow, I hope you find this useful!* Creating a service account for running the MSSQLSERVER service:1. Can be either a domain user, a local user, or the local system account a. the MSSQLSERVER service account must be run under a domain user account to take advantage of network features like replication, backup and restore to/from network drives, heterogeneous queries, etc. b. if using local system account, the permissions requirements detailed below will already be set up2. Needs "Log On As A Service" permission3. Needs Full Control of the SQL Server directory a. The default SQL Server directory is: \Program Files\Microsoft SQL Server\MSSQL4. Needs Full Control of all directories containing SQL Server database files (.mdf, .ldf, and .ndf files)5. Needs Full Control of the following registry keys: a. HKLM\Software\Microsoft\MSSQLServerOR HKLM\Software\Microsoft\InstanceName b. HKLM\System\CurrentControlSet\Services\MSSQLServerOR HKLM\System\CurrentControlSet\Services\MSSQL$InstanceName6. Does NOT need to belong to any SQL Server server-roles* Creating a service account for running the SQLSERVERAGENT service:1. Can be either a domain user, a local user, or the local system account a. The SQLSERVERAGENT service account must be run under a domain user account to take advantage of SQLAgent features like SQLMail2. *MUST* belong to the SQL Server "System Administrators" role a. If SQL Server is running in "SQL Server and Windows" (mixed) login mode, you may use the local system account for SQLSERVERAGENT provided that you also specify a SQL Server account belonging to the System Administrator role to use for SQLSERVERAGENT's SQL Server connection (see the Connection tab of the SQL Server Agent properties dialog box) b. If SQL Server is running in "Windows Only" login mode, you cannot use the local system account for SQLSERVERAGENT because the local system account does not belong to the System Administrator role* Changing the SQL Server Login Mode1. Under normal situations, the login mode should be changed using the Security tab of the Server Properties dialog (right-click on the server name in Enterprise Manager) a. however, if you accidentally 'lock yourself out' of SQL Server, you can change the login mode back to mixed mode by setting the following registry key to a value of 0: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\LoginMode |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-07 : 18:32:27
|
| To add to this,The account should be the same that starts both services. It should typically be an administrator on the Server.In addition to "Log on as a Service" It also needs the following rights:Increase QuotasAct as part of the OSReplace a process level token.If the account is specified during installation, these rights are automagically given to the account. Same if it is changed in Enterprise manager (Not through CP->Services though)-Chad |
 |
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-08 : 09:33:53
|
| I agree that having these services run under the same account with local administrator rights does simplify things. The whitepaper also makes reference that having the account that the SQLSERVERAGENT service runs under a local administrator gives you additional flexibility (such as the ability to configure the use of CmdExec and ActiveScript jobs for non-system administrators, auto-restart, and run-when-idle jobs).HOWEVER, I think the point is worth noting that there is no actual requirement that these services run with local administrator privileges and it is possible to have them run under different user accounts. In environments that want to screw down security and implement a 'least privileges' approach, this might be worth considering (of course, at the expense of less flexibility). |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-08 : 09:41:34
|
quote:
(of course, at the expense of less flexibility)
Which is, of course, the way we must do things with Microsoft products now! (Bill says so!) |
|
|
|
|
|
|
|
|
Creating service accounts for SQL Server services