One of the key responsibilities of a database administrator is to make sure all the SQL Server instances they manage are secure. SQL Server security in itself is a very broad topic, and this article is just an introduction to my eight best practices you can follow to secure the SQL Servers you manage.
The importance of encrypted database backups
Database backups are always critical for an organization. If the backup files are not encrypted, they can easily be copied and restored to any other SQL Server, resulting in data theft and a security breach. A database administrator can avoid this unsavory scenario by creating database backups using the built-in MEDIAPASSWORD feature. The sample script below can be used to create encrypted database backups in SQL Server:
BACKUP DATABASE AdventureWorks
More on SQL Server security
Straighten out your SQL Server security goals
See various ways to audit SQL Server security
Secure the database backup folder by removing unwanted users
A database administrator should make sure that access to the database backup folder is restricted and permission should only be granted to users who really need it. Unauthorized access can result in the database backup folder being vulnerable to backup files being copied to remote servers. Unauthorized access can also result in accidental deletion of critical backup files, thereby breaking the database restore sequence. Hence, it is very critical as a database administrator to make sure only the right people have access to the database backup folder.
Use Windows Authentication instead of SQL Server Authentication Mode
As a SQL Server security best practice, one should use Windows Authentication to connect to SQL Server. Windows Authentication Mode in SQL Server can leverage the organization-wide Active Directory, account, group and password policies, thereby making access more secure. If you have to use SQL Server Authentication Mode to connect to SQL Server, it is recommended not to use a system administrator (SA) account. Even if you are using SQL Server Authentication, you can still take advantage of Windows password policies when setting passwords for user accounts.
Make the system administrator account's password complicated
If you are using mixed-mode authentication in SQL Server, always set a complex password for the SA account. As a best practice, always avoid using the SA account to connect Web applications to SQL Server. One should always avoid using the system administrator account to perform day-to-day maintenance activities. For day-to-day stuff, use Windows accounts with appropriate permissions. As a best practice, don't forget to change SA account passwords once every few days.
As a SQL Server security best practice, you should always audit failed logins to SQL Server. Once you have enabled login auditing in SQL Server, the failed and successful login information will be written to the SQL Server error logs, which can be monitored regularly to look into suspicious activities from time to time.
Turn off the SQL Server browser service
As another SQL Server security best practice, a database admin should turn off the SQL Server browser service when running a default instance of SQL Server. Even if you are running a named instance, you can explicitly define the port and mention the port within the application connection strings to connect to a named instance of SQL Server.
Disable unused features in SQL Server
Disable features such as XP_CMDSHELL, OLE AUTOMATION, OPENROWSET and OPENDATASET when not in use to reduce surface area attacks. In SQL Server 2005 you can enable or disable these features using SQL Server Configuration Manager. If you are using SQL Server 2008 or higher versions you can enable or disable the above-mentioned features using the policy-based management feature.
Decrease privileges for the SQL Server service account
A final best practice is that a database administrator should always run SQL Server services in a minimally privileged local domain account. Avoid running SQL Server services under the contexts of local system, local administrator or domain administrator accounts. However, make sure that the SQL Server service account has "full control" permissions on data, log and backup directories for read and write activities.
This was first published in January 2013