Working out a series of security measures for SQL Server databases is an important task for every webmaster running websites on a Windows server. Searching on the Internet you can find numerous tips and guides, and this tutorial focuses on the most effective ones which work. Some of the tips require technical skills.
Improve Operating System Level Security
The security of the server environment and the operating system plays an important role in the security of SQL Server. Therefore, you should:
- Apply the latest service packs and install all critical updates for bug fixes and security enhancements.
- Use a firewall for SQL Server. As a firewall works like a separator between a trusted network and an untrustful one, it helps keep bad traffic away. You can use Windows Firewall or apply separate firewall rules to SQL Database. Configuring a firewall requires a relatively high level of skill, so you’d better seek help from an experienced user if you do not have enough knowledge about firewall.
- Limit the surface area of SQL Server by running the needed services only with the least privileges, which reduces the paths that can be used for attackers to intrude your system. To configure the services included in SQL Server, follow this tutorial.
Deal with Accounts and Passwords Carefully
If a database is a house full of data, user accounts and the passwords are the doors and keys to the house. The following suggestions should be useful for securing the house.
Disable the system administrator account. An SA account is created automatically in SQL Server setup, thus it is known to the world and always being the target of attackers. If a hacker enters your SQL Server with SA privileges, he/she can do whatever he/she wants. To secure the most important door of your house, you’d better create a new account and assign the administrator privileges to it, then disable the SA account and rename it.
Reduce the number of administrators and limit their privileges. Minimize the number of user accounts in the sysadmin server role and the db_owner database role, and give permissions only to those who need to manage the relevant tasks. Assign the ownership of different databases to more than one admin account so that some databases can survive even if one administrator account is compromised.
Do not use guest user accounts. Guest user accounts are convenient for allowing others to contribute to your site, but they can be vulnerabilities that help experienced hackers gain access to your SQL Server.
Harden the password system. The importance of strong passwords cannot be emphasized too much, especially for administrator accounts. So you have to enforce complicated passwords for every user account that has access to SQL Server. Usually, a strong password needs to be long enough and should include numbers, uppercase letters, lowercase letters and special characters.
Use Windows Authentication Mode
SQL Server 2012/2014 comes with two authentication modes for establishing access to databases: Windows Authentication mode and a mixed mode of Windows Authentication and SQL Server Authentication.
Windows Authentication mode is the default authentication mode that is enabled during SQL Server setup and can not be turned off. It is more secure than SQL Server Authentication. With Windows Authentication, user credentials are confirmed by Windows and then trusted by SQL Server. The database administrator can apply strong password enforcement, account lockout and password expiration. The access to SQL Server can be managed at the domain level so that no additional SQL Server account is needed.
SQL Server Authentication manages logins inside the SQL Server, not based on Windows. For each login, a strong password is required. Compared with Windows Authentication, it has some weaknesses in security such as fewer password policies, the lack of Kerberos security protocol, and possible password leaking when an application establishes an automatic connection. However, SQL Server Authentication has to be enabled when you need to use applications requiring the support for other operating systems besides Windows.
Change the TCP Port Number for SQL Server
By default, SQL Server listens on TCP port 1433, which is widely acknowledged and has been targeted by attackers. Therefore, it is suggested to configure SQL Server to another port number that can be less easily guessed. In addition, if the named instances for Database Engine are listening on dynamic ports, you’d better assign a specific port for them so that you can better limit ports in your firewall.
Utilizing SQL Server Configuration Manager is an easy way for changing the TCP port number. Follow the steps below to convert to a new static port.
- After opening SQL Server Configuration Manager, expand SQL Server Network Configuration and open the protocols for the SQL Server instance that needs configuration. For ours, the option is Protocols for MSSQL SERVER.
- Double click on TCP/IP. In the new dialog box of TCP/IP Properties, click on the IP Addresses tab. Now you can see a long list starting with IP 1 and ending with IPAll.
- Delete the values for TCP Dynamic Ports and TCP Port for all IPs except for IPAll. Then change the TCP port number of IPAll to whichever you would like to use. Of course, you can also enter the same port number for each IP. Leave the value for TCP Dynamic Ports blank. Click Apply to apply the changes.
- In SQL Server Configuration Manager, click on SQL Server Services, find the SQL Server instance you have configured before, and then right click to Restart it.
Encrypt Valuable and Sensitive Data
If your database contains sensitive data like credit card information, you need to consider using an encryption method to secure it. SQL Server allows you to use one or several mechanisms listed below for encryption.
Symmetric Keys. A symmetric key is a key used for both encryption and decryption. It is less secure compared with other mechanisms, but due to their high performance, symmetric keys are recommended for encrypting the sensitive data that is used routinely.
Asymmetric Keys. An asymmetric key consists of two keys: one private key and one public key. You can encrypt data with either key and decrypt it with the other key. Asymmetric keys are more secure than symmetric keys, but they could consume more server resources.
Certificates. Certificates can be used to secure SQL Server connections and encrypt data. You can use SQL Server to generate certificates or purchase one from a third-party certificate authority.
Transparent Data Encryption (TDE). TDE performs a database level encryption. It encrypts the whole database before data is written to the disk and decrypts data when it is read. The whole process is handled by the database engine, and you do not need to do anything except for turning TDE on.
Actually, to work out a good encryption plan for SQL Server databases, you will also need to acquire much knowledge about many other terms including Database Master Key, Server Master Key and Extensive Key Management module. If you are interested in the terms and would like to perform encryptions by yourself, read this tutorial.
Audit All Logins – Both the Failed and Successful Ones
SQL Server auditing tracks and logs events so that you can monitor the activities on your server. After turning on audit, you are allowed to make a choice among logging successful logins, logging failed logins and logging both failed and successful logins.
For the sake of security, we suggest you to audit both failed and successful logins. Auditing login failures helps you identify malicious login attempts and take actions to prevent potential attacks. Auditing successful logins allows you to monitor the activities of your sensitive data. This also lets you find undiscovered security issues caused by unconscious leaking of an administrator’s login credentials.
To enable auditing for all logins is easy. Follow the steps below to configure SQL Server.
- Open SQL Server Management Studio and right click on the SQL Server that you want to configure. Then select Properties.
- On the Server Properties page, open the Security page. Find the Login auditing section and check Both failed and successful logins. Then click on OK at the bottom of the page.
- Restart the SQL Server to activate the change.