October 31, 2003 at 8:33 am
I am in the process of securing our SQL Server. I have been following the article
http://qa.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp but just wanted to check something...
The Startup account is currently set to the Network Administrator login. If I change it to a login with hardly any network priveleges, will it still work ok? As in is the Startup account merely an account to get Sql server going and nothing else? Does it need any permissions? Does SQL Server Agent have the same behaviour?
So do I then change that login so it is not actually allowed to logon to the physical server machine?
I am scared to change it as I worry if I do I will lock myself out. It has taken me all afternoon to get the courage to delete BUILTIN\Administrators!!!
sorry if this is a really easy question - I am just trying to be super-cautious.
October 31, 2003 at 8:46 am
quote:
The Startup account is currently set to the Network Administrator login. If I change it to a login with hardly any network priveleges, will it still work ok? As in is the Startup account merely an account to get Sql server going and nothing else? Does it need any permissions? Does SQL Server Agent have the same behaviour?So do I then change that login so it is not actually allowed to logon to the physical server machine?
From BOL.
"Setting up Windows Services Accounts
On the Microsoft® Windows NT® and Microsoft Windows® 2000 operating systems, Microsoft SQL Server™ and SQL Server Agent are started and run as Windows services. These services appear in the list of installed services in the Services dialog box, available using Windows Control Panel. The table shows each service name and the term used to refer to the default and named instances of SQL Server, as displayed in the Services dialog box.
Service
Name Term for default instance Term for named instance
Microsoft SQL Server SQL Server MSSQLSERVER MSSQL$InstanceName
Microsoft SQL Server Agent SQL Server Agent SQLSERVERAGENT SQLAgent$InstanceName
For Microsoft SQL Server™ and SQL Server Agent to run as services in Windows, they must be assigned a Windows user account. Typically, both SQL Server and SQL Server Agent are assigned the same user account, either the local system or domain user account. However, you can customize the settings for each service during the installation process. For more information about how to customize account information for each service, see Services Accounts.
The Microsoft Search service (full-text search) must always run under the local system account.
Note Microsoft Windows 98 does not support Windows services; instead, SQL Server simulates the SQL Server and SQL Server Agent services. It is not required that you create user accounts for these simulated services.
Using the Local System Account
The local system account does not require a password, does not have network access rights in Windows NT 4.0 and Windows 2000, and restricts your SQL Server installation from interacting with other servers.
Using a Domain User Account
A domain user account uses Windows Authentication, that is, the same user name and password used to connect to the operating system is also used to connect to SQL Server. A domain user account is typically used because many server-to-server activities can be performed only with a domain user account, for example:
Remote procedure calls.
Replication.
Backing up to network drives.
Heterogeneous joins that involve remote data sources.
SQL Server Agent mail features and SQL Mail. This restriction applies if using Microsoft Exchange. Most other mail systems also require clients (the SQL Server and SQL Server Agent services) to be run on accounts with network access.
Note Several servers running SQL Server can share the same user account. When setting up replication, it is recommended that a Publisher and all its Subscribers share the same service account for the SQL Server service.
Requirements for Domain User Account
All domain user accounts must have permission to:
Access and change the SQL Server directory (\Program Files\Microsoft SQL Server\Mssql).
Access and change the .mdf, .ndf, and .ldf database files.
Log on as a service.
Read and write registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
-or- for any named instance: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server.
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer.
-or- for any named instance: HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename.
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib.
In addition, a domain user account must be able to read and write corresponding registry keys for these services: SQLAgent$InstanceName, MSSearch, and MSDTC.
This table shows additional permissions required for certain functionality.
Service Permission Functionality
SQL Server Network write privileges Write to a mail slot using xp_sendmail.
SQL Server Act as part of operating system and replace a process level token Run xp_cmdshell for a user other than a SQL Server administrator.
SQL Server Agent Member of the Administrators local group Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator.
Use the autorestart feature.
Use run-when-idle jobs.
SQL Server Member of local Power Users or local Administrators group Add and delete SQL Server objects in the Windows 2000 Active Directory.
Changing User Accounts
To change the password or other properties of any SQL Server–related service after installing SQL Server, use SQL Server Enterprise Manager. If your Windows password expires and you change it, be sure to also revise the SQL Server services settings in Windows. For more information, see Changing Passwords and User Accounts."
quote:
I am scared to change it as I worry if I do I will lock myself out. It has taken me all afternoon to get the courage to delete BUILTIN\Administrators!!!
Before remove it, make sure SQL Server security is configured in SQL Serve and Windlws authentication (Mixed Mode) and do have correct 'sa' password.
You need add 'NT Authority\System' to SQL Server login with 'sysadmin' privilege, otherwise, Full-Text search may not work properly.
November 3, 2003 at 4:05 am
Sorry for being dense, but does the sql server startup account have to have Administrator rights?
November 3, 2003 at 6:32 am
To the OS, no. Does it require sysadmin rights within SQL Server? Yes.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply