SQL Server Security Best Practices
One of the primary responsibilities of Database Administrator is to Secure all SQL Server which they manage. SQL Server Security in itself is a very vast topic hence this article outlines some of the SQL Server Best Practices which Database Administrators can follow to Secure SQL Servers in their environment.
Regularly Audit SQL Server Logins
Database administrators should enable login auditing feature of SQL Server on all instances which they manage. Once this feature is enabled SQL Server will write all the failed and successful login information in SQL Server Error Logs. It is always a best practice to auditing failed SQL Server logins. Read the following article which explains “How to Auditing SQL Server Logins”.
Limited Privileges to SQL Server Service Account
Database administrator should always avoid running SQL Server Services under the context of Local Administrator, Local System or a Domain Administrator Account. All SQL Server Services should be configured to run under a minimal privileged Local Domain Account. SQL Server Service account should always be given Full Control Permissions on Data, Logs and Backup Directory to perform read and write activities. In case if you wish to change the default backup directory in SQL Server after installation then read the following article which explains “How to change the default database backup folder location in SQL Server”.
Always Setup Complex Passwords for SQL Server SA Account & SQL Server Logins
If you have configured SQL Server Instance to run under Mixed Mode Authentication then as a best practice one should always set a complex password for a System Administrator (SA) account. As a best practice one should change the SA password at regular intervals. Always use a windows account which has System Administrator to perform day-to-day maintenance activities on SQL Server. If there is more than one DBA in a team then it is recommended to create a Windows Domain Group and add all DBAs to that group and then give the windows domain group System Administrator Privileges on SQL Server. Read the following article which explains “How to identify currently used SQL Server authentication mode”.
Enforce Password Policies and Password Expiration for SQL Server Logins
It is always recommend to Enforce Password Policies and Password Expiration for SQL Server Logins when you use Mixed Mode Authentication. This will allow you to set complex password for a SQL Server Login. Read the following article which explains “How to enforce password policies and password expiration for SQL Server Logins”.
Avoid Using SQL Server Authentication and Promote the Usage of Windows Authentication
As a best practice, to connect to a SQL Server instance one should always use Windows Authentication. The simple reason being when you use windows authentication the user passwords are authenticated by the active directory for windows logins thereby leveraging password policies set by your organization.
Periodic review of Windows and SQL Server Logins
As a best practice database administrators should perform a periodic review of all Windows and SQL Server Logins on every instance of SQL Server which they manage. This way you can remove logins which are no more relevant on the server. DBAs should very clear document all the logins along with each respective privilege within the Disaster Recovery document.
Encrypt SQL Server Database Backups
One of the primary responsibilities of a Database Administrator is to make sure all the databases are backed up regularly and the backups are restored on Disaster Recovery environments to make sure they are usable when they are needed the most. However, at the same time you need to make sure the database backups are encrypted to avoid the misuse. Learn more about Transparent Data Encryption Feature of SQL Server to know how to encrypt the database backups in SQL Server.
Secure Database Backup Folder from Unauthorized Access
Database backups are very critical and its DBAs responsibility to safeguard them from unauthorized user access. As a best practice access to database backup folders should be restricted and only those who really need access should be granted access. Unauthorized access to backup folders can be very dangerous as users can delete critical backup files, copy the backups to unauthorized locations etc.
Running SQL Server in Different Port other than the Default Port
By default SQL Server uses the TCP/IP Port 1433 for the Database Engine. As a best practice DBAs should change the TCP/IP Port on which SQL Server Database Engine will be listening. These changes must always be done using SQL Server Configuration Manager.
Disable SQL Server Browser Service
As a Security Best Practice database administrators should Disable SQL Server Browser Service when running the default instance of SQL Server. If there are any named instances of SQL Server then one must explicitly specify the port number within the connection strings to connect to the named instance. SQL Server Browser Service uses by default UDP Port 1434 for connectivity.
Hide an Instance of SQL Server
Database administrators can also hide an instance of SQL Server. This can be easily done by using SQL Server Configuration Manager. The SQL Server Browser Service basically enumerates instances of the Database Engine installed on the computer thereby enabling applications to browser for a server. Read the following article which explains “How to Hide an Instance of SQL Server”.
Encrypting Connections to SQL Server
Database administrators can enable Secure Sockets Layer (SSL) to encrypt the data which is transmitted across a network between the client application and the instance of SQL Server. SSL encryption increases the security of data which is transferred across the network between an application and SQL Server. However, one need to understand that enabling encryption will slow down the performance a bit. Hence it is always recommended to perform all testing before implementing in a production environment. Read the following article which explains “How to encrypt connections in SQL Server”.
Install Latest Service Packs and Hot fixes
As a best practice Database Administrators should always patch the SQL Servers which they manage with the latest service packs and hot fixes. Read the following article which has a complete list of all the Service Packs, Hot fixes and Cumulative Updates released by Microsoft for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Check the above links to see which patches are missing on your SQL Server Instances.
Dedicated SQL Server Connection
Dedicated SQL Server Connection is one very important feature which DBA’s must consider enabling it across all servers which they manage. This feature can help you troubleshoot issue rather than just restarting SQL Services during emergencies. Read the following article which explains “How to Use Dedicated Administrator Connection in SQL Server”.
Disable All the Unused SQL Server Features
As a best practice database administrators should always disable unused features of SQL Server such as OLE AUTOMATION, XP_CMDSHELL and OPENROWSET etc to reduce surface area attacks. These features can be easily be disabled or enabled using SQL Server Configuration Manager if you are using SQL Server 2005. If you are using a higher version of SQL Server then these features can be easily managed using Policy Based Management Feature which was initially introduced in SQL Server 2008.
SQL Server Security in itself is a vast topic and in this article we have discussed few of the settings which can be easily implemented to improve the overall security of the SQL Servers which you manage day-to-day in your work.