SQL Server is one of the world’s leading data platforms. It is being broadly used hosting millions of databases. These databases store data. These data are each organization’s most valuable asset. It is with this data that organizations run their everyday operations and processes. This fact makes it a necessity to efficiently secure your SQL Server instances, in order to protect your databases and consequently your data. This article suggests a list with the top 10 security considerations based on which you can efficiently secure your SQL Server instances.
Physical environment
Securing the physical environment of your database server is crucial. Imagine having your SQL Server instance hardened to the maximum security level but leaving the physical location of the database server with weak security. This would truly be a contradiction. As such, you need to limit the physical access to your physical database server. To achieve this, you must establish the proper procedures to be followed along with adequate controls in order only authorized personnel to have physical access to the servers.
Operating system
Securing the Operating System onto which SQL Server is installed is also important. If the Operating System has not been secured, a potential attacker could for example, access your SQL Server instance’s data and log files thus gaining access to your data. To secure the Operating System you can follow the below guidelines:
Keep it up to date with the latest patches and service packs (after you verify that they won’t affect the operation of the database server in any way by properly testing them).
Follow the least-privilege principle for service accounts (you can easily do this by following Microsoft’s recommendations – see MSDN article: Configure Windows Service Accounts and Permissions).
Follow the least-privilege account for any other accounts as well.
Restrict access to SQL Server’s physical files by setting the proper folder and file security permissions.
-
Network
All data within an organization travel through the network. You have your database servers, application servers, clients, Storage Area Network (SAN) and the list goes on. The network must be secured in order to restrict access to resources from unauthorized sources, as well as not allow data to flow to unauthorized destinations. In order to achieve this level of security, you need to properly configure the firewalls. For example, put a firewall between the database server and the Internet. When it comes to SQL Server, you need to follow Microsoft’s recommendations in order to properly configure the Windows firewall for SQL Server. These recommendations can be found in this MSDN article.
-
Application
When it comes to database security, it’s not only about securing your SQL Server instances. It also has to do with securing the application which connects to the SQL Server instance. The reason is that data flows take place between your SQL Server instance and your application, so you need to make sure that all three entities participating to this communication have been secured. Typically, there are three entity types participating to such data flow:
Database Server and Instance
Clients (i.e. Application server or direct client connections)
Network connection
To secure your application, you can perform the below:
Do not expose user passwords in code or in external files (i.e. file with connection strings) that are used by the application. Use encrypted connection strings instead.
Prefer using Windows Authentication for application service accounts that connect to your SQL Server instance instead of Mixed Mode (username/password). With Windows Authentication, the client application does not send any passwords to SQL Server thus making the process more secure.
Establish an encrypted connection to your SQL Server instance. With an encrypted connection, even if a potential attacker intercepts the network traffic between your client and the SQL server instance, he or she will not be able to read the data because it will not be just clear text but rather encrypted data packets. SQL Server 2016 introduced the Always Encrypted feature which provides you with an additional encryption option when it comes to data traffic between clients and SQL Server instances.
-
SQL Server instance: Surface Area
Now that all “external” factors have been discussed, let’s focus on the top factors for securing your SQL Server instance and its hosted databases. The first thing to check is your SQL Server instance’s surface area. Surface area configuration in SQL Server allows stopping or disabling components and features that are not used. By disabling these features, you limit the surface of SQL Server which is subject to potential attacks. Note that if you need to enable one or more of these features, you also need to take all the necessary security precautions.
You can see the available surface area configuration options by executing the following T-SQL script:
1234567891011sp_configure 'show advanced options',1;GORECONFIGURE;GOsp_configure;GOsp_configure 'show advanced options',0;GORECONFIGURE;Below you can see a screenshot that illustrates the (partial) output of the above script on a SQL Server 2016 instance. Note that in SQL Server 2016 there are available 76 surface area configuration options through sp_configure (when ‘show advanced options’ is set to 1).
Major surface area options that should be disabled, unless there is a justified need to enable them, are:
If for any reason you need to enable any of the above surface area configuration options then you must make sure that you follow the recommended security precautions prior to enabling them.
For more information please refer to the following MSDN article.
-
SQL Server instance: Server-Level and Database Permissions
You need to be very careful with server-level permissions as they grant access on the instance-level. The available fixed server roles in SQL Server 2016 are:
sysadmin
serveradmin
securityadmin
processadmin
setupadmin
bulkadmin
diskadmin
dbcreator
public
Make sure that you include only the necessary logins to memberships on the above roles. For example, never assign the SysAdmin role to BUILTIN\Administrators windows group. Machine administrators must not have access to the SQL Server instance. Now, if you want to grant access to SQL Server to a person that he or she is also a machine administrator, you can do this individually for the specific login and not the BUILTIN\Administrators group.
The following MSDN article describes what exactly each one of the above server roles allows. Please take into consideration this article whenever you assign a server-role to a SQL Server user.
In addition to server roles, you should also be careful when you provide access to SQL Server databases. For example, grant “db_owner” access only when needed because among other, a db_owner can drop the database. You can review the fixed-database roles for SQL Server on the following MSDN article.
-
SQL Server instance: Authentication and Authorization
When installing SQL Server, one of the settings you can set is the Authentication Mode. You can select Windows Authentication Mode or SQL Server and Windows Authentication Mode combined (also known as “Mixed Mode”). Windows Authentication Mode is more secure because with Windows Authentication, SQL Server validates the user’s credentials using the Windows principal token in the Operating System. This makes the whole process more secure because the authentication is made using the Kerberos security protocol.
If, for any reason, you do not want to set your SQL Server instance’s authentication mode to “Windows Authentication Mode” only, then at least try for the majority (if not all) of the logins you create to use Windows Authentication.
SQL Server gives you the option to change the authentication mode at any moment via your SQL Server instance’s properties (from SSMS, right-click on the instance, select “Properties” and then click on the “Security” tab).
Note: Even though you can change the Server authentication mode at any time, you need to take consideration the current logins you have on your instance and check if they could be affected prior to making any changes to the Server authentication mode.
Other security considerations that fall under the authorization category are:
Public database role
Guest user permissions
Orphaned users
Public Database Role
Every database user in SQL Server is automatically assigned the ‘Public’ database role. However, beyond its default behavior, this role must not be given any additional permission on any database in order to avoid allowing access to unauthorized users.Guest User Permissions
The Guest user is a special SQL Server database user which exists in order to permit access to a database for logins that are not mapped to a specific database user. However, because any login can use a database via this user, it is recommended to disable this user, after of course you verify that it is not required for a specific database. If for any reason you do not want to disable the Guest user, you can consider revoking (if applicable) the CONNECT permission. Read this Microsoft Support article for more information.Orphaned Users
Orphaned users are database users that are not assigned to any login. Unless the involved databases are Contained Databases, these users create a risk because potential attackers might get access to them and inherit their permissions on the database. The recommended practice is after verifying that these users are not used in any way, to first disable them (REVOKE CONNECT) and eventually remove them. More info on orphaned users can be found on the following MSDN article. -
SQL Server instance: Password Policies
As mentioned in Point 7 (Authentication and Authorization), when Windows Authentication Mode is used, then password policy enforcement (i.e. complexity validation for strong passwords), support for account lockout and password expiration are some of security features that are applied by default for the logins that use this mode.
If you want to apply the same complexity and expiration policies used in Windows for SQL logins (that use a username and password) as well, then you need to make sure that Password Policy and Password Expiration options are set for these logins as well.
Note: For logins which are used as service accounts, it is not recommended to set their passwords to expire as such thing could possibly disrupt the connection of the respective applications to the databases if the password expired. Instead of setting these passwords to expire, you can choose a very strong password in the beginning, do not share it with anyone, and still if you want to change the password, you can do it in a more controlled manner in coordination with the application owners.
More information about the SQL Server Password Policy can be found on this MSDN article.
-
SQL Server instance: Patching
It is essential that you keep your SQL Server instances up to date by installing the latest service pack and critical cumulative patches. However, prior to applying any patch or service pack, you need to verify that it will not create any issues for your current SQL Server setup. To this end, always test service packs and patches on a Test environment first and proceed to Production only after you verify that everything works properly. You can visit Microsoft’s Update Center for Microsoft SQL Server in order to find information about the latest service packs and updates for all the available SQL Server versions.
-
SQL Server instance: Encryption
If you have databases that store sensitive data, SQL Server provides a number of encryption options that you can use.
The encryption options currently available are:
Additionally you can encrypt the file system/disk with EFS and BitLocker.
- Certificate Management in SQL Server 2019 - May 31, 2019
- SQL Server consolidation – Hosting multiple databases on a single SQL Server instance - December 2, 2016
- How to create and manage T-SQL code snippets - October 28, 2016
This article suggested the top 10 security considerations for your SQL Server installation. Sure, there are many other additional options and features that can be tuned in order to contribute towards more secure SQL Server instances, but this article aimed at providing the most significant security factors based on years of experience of working with SQL Server and proven security best practices.
Securing your SQL Server instances should be an ongoing process. You should regularly check your SQL Server instances and databases and keep them up to date and healthy in order to provide a secure, non-disruptive database service to the data applications they support.
View all posts by Artemakis Artemiou