One of my long time friends reached out to me last week regarding securing his SQL Server instance. He has a SQL Server 2005 instance hosted on a server in which many users are part of Local Administrators Group. In the recent past, he was finding it difficult to manage the SQL Server instance, since all those Local Users had sysadmin privileges on it. Since my friend was an Accidental DBA, he wanted me to suggest an option so that the sysadmin privileges on that system is only available to the sa login. Instead of sending the document over email, I thought of posting it here.
In most of the non-production instances, the SQL Server service is configured to start using the Local System account. This account gets access to the SQL Server instance through the BUILTIN\Administrators group. Whoever is part of the Administrators group on the Server is part of this group. In SQL Server 2000 & SQL Server 2005, by default the BUILTIN\Administrators group is added to the sysadmin server role on the SQL Server instance. To restrict access to the Local System Administrators on the SQL Server Instance, the following action needs to be performed.
- The service account of SQL Server/Agent service needs to be granted requisite permissions on the SQL Server instance.
- If the SQL Server/Agent service is starting using Local System account, it needs to be changed to start using a Domain\Windows account.
- The sysadmin privileges for the BUILTIN\Administrators group needs to be revoked
Create a New Login in SQL Server for the Service Account
A new login needs to be created in SQL Server for the service account. This account needs to have the privileges as explained in this article. Since I know that the server where this will implemented is not so critical, it is safe to add the SQL Server service account to the Administrators group on the Server. The same account can be used to start the SQL Server Agent service or a new login with sysadmin privileges on the instance can also be created.
This login can also be created by executing the below command.
CREATE LOGIN [DOMAIN\AccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
EXEC master..sp_addsrvrolemember @loginame = 'Domain\AccountName', @rolename = 'sysadmin'
Change the Service Account for the SQL Server service
Once the login for the Service Account has been created on the SQL Server instance, we can go ahead and change the SQL Server service to start with the new account.
Open SQL Server Configuration Manager and change the SQL Server & Agent service account to a Domain\Windows account as in this screenshot.
Similarly change the SQL Server Agent to start with the new account. After making the changes, the SQL Server and Agent services needs to be restarted.
Revoking sysadmin privileges for BUILTIN\Administrators login
Before proceeding further, make sure that the sa login is enabled and you know the sa password. In SSMS open the Properties of BUILTIN\Administrators login and uncheck the sysadmin server role.
Or execute the below script in a new query window.
@loginame = N'BUILTIN\Administrators',
@rolename = N'sysadmin'
Enabling sa access
Since the requirement is to enable sa login to access the instance, SQL Server must be configured for SQL Server and Windows Authentication mode authentication. This can be configured in the SQL Server Properties screen.
After making this change, the SQL Server service needs to be restarted for the changes to take effect. Now the SQL Server instance is configured to accept SQL Server authenticated logins.
From now on none of the system administrators on the server would be able to logon to SQL Server and sa is the only login who will have access to the instance.