Netwrix recognized in Gartner's 2020 Market Guide for File Analysis Software
SQL Server is designed to be a secure database platform, but using the default settings leaves security gaps in the system. Moreover, SQL Server has many security features you should configure individually to improve security. Here are the top SQL Server security best practices you should follow.
It is important to harden the Windows Server operating system before installing SQL Server on it. Otherwise, attackers who cannot defeat your SQL Server security measures can simply go around them: They will gain access to the OS and copy the data files to their own server, where they can breaking passwords and encryption at their leisure. To learn what to do, review these Windows Server hardening best practices.
You should limit the installation to just the components needed for your database to perform its tasks. This approach reduces your attack surface area by eliminating components that could have security vulnerabilities. It also minimizes resource utilization by the database and simplifies administration by eliminating services and components needed to be managed.
Each SQL Server service is configured to run under a specified Windows or Active Directory account. You should plan which accounts should be permitted to run which services based on the principle of least privilege, which states that each account should have the minimum permissions and system rights it needs to function.
It’s a best practice to assign each service a separate account that is a member of a relevant security group. That way, even if the account for one service is compromised or damaged, other services will still operate normally. You can configure these permissions in Server Configuration Manager.
Here are the types of accounts you can use for SQL Server services:
The SQL Server Browser service eliminates the need to assign port numbers to the instances. It enables SQL admins and authorized users to discover database instances over the network. However, this also makes it possible for attackers to gain knowledge of the available SQL Server resources. Therefore, when running a default instance of SQL Server, you should either turn off the SQL Server Browser service or configure another port for it to use to communicate.
The effective permissions for a given account on a specific resource result from:
In order to more easily understand and manage effective permissions, the best practice is to create containers, such as groups or roles, and assign those containers permissions to access resources. Then make accounts members of those groups of roles. That way, it’s easier to understand the effective permissions for each account. Moreover, simply by putting a user in the right groups or roles, you can assign the correct permissions to new hires, modify permissions as a user’s role changes and remove a user’s permissions when they leave the organization.
Server roles provide an easy way to delegate administrative privileges, but you must assign these roles carefully. Here are all the default SQL server roles and the permissions they have:
It is very important that you follow the principle of least privilege when assigning roles to users. For example, if a user only needs permissions to shut down the server and end processes, they should be assigned the serveradmin and processadmin roles; assigning them the sysadmin role would a big violation of principle of least privilege.
If no default server role matches your security requirements, you should create a custom role that does. You can do that using either TransactSQL or the Management console.
Strong passwords are a must for all database administrator accounts to make them resistant to brute-force attacks. At a minimum, require these passwords to contain at least 10 characters, including uppercase and lowercase letters, numbers and specific symbols; however, passphrases are the best choice. These password best practices offer additional proven techniques for managing DB admin passwords properly.
Both white-hat and malicious hackers are constantly discovering vulnerabilities and exploits in SQL Server. Microsoft releases several types of updates to fix them:
The simplest way to secure SQL Server is to keep it up to date. The easiest way to achieve this is to enable automatic updates from Microsoft. Larger organizations or those with strong change processes should apply updates only after testing them in test environments.
SQL server offers several options for authenticating users:
Best practices recommend using Windows Authentication to connect to SQL Server because it can leverage the Active Directory account, group and password policies. If you have to use SQL Server Authentication Mode to connect to SQL Server, do not use an sa account; instead, disable that account because it is the first account attackers will try to compromise in a brute-force attack .
In a Windowsbased environment, administrators can enable policies for Windows users that control things such as password complexity and expiration. SQL Server can enforce similar options for SQL Server logins.
When you create a SQL Server login, you can specify the following options:
If a login will not be used for a long period of time, such as a month or longer, you should disable it and then reenable it later if needed. In these situations, it is better to disable the login rather than deleting it from the system. However, you should review your logins periodically and delete any that were disabled more than a year ago.
You must ensure that your database is backed up properly so the data can be recovered if a failure occurs. There are two types of backups: full backups and incremental backups. A full backup, as the name suggests, backs up the full database. After a full backup has been completed, SQL Server maintains a map of extents that can be backed up, and it backs up only those extents that have changed; this is called a differential backup. SQL Server does not clear the map of modified extents after a differential backup; it clears it only after a full backup.
Differential backups are much faster and occupy less disk space than full backups, so they are very useful for medium and large databases after the initial full backup; however, full backups should still be taken periodically. For small databases, the best practice is to simply use full backups every time.
For very large databases, you can consider using a file and filegroup backup strategy, which backs up only certain files or filegroups. This strategy can reduce the time required to perform backups. It can also speed recovery times, because if a single file is lost, you need to restore only that file or the filegroup that contains that file, instead of the whole database. However, be aware that managing filegroup backups can be complex.
Effective monitoring is critical to detecting, diagnosing and resolving problems. For example, you might identify longrunning queries that could turn out to be malicious. In particular, be sure to watch for the following:
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 should be monitored regularly for suspicious activities.
Also be sure to monitor access, changes and deletions to database objects that contain restricted data. You should also track changes to SQL Server configurations and permissions, so you can block attacks and remediate mistakes before you suffer significant damage. This tracking can be done using SQL traces or third-party software like Netwrix Auditor for SQL Server.
In a SQL injection attack, hackers enter SQL commands into a form field of an application front end. The application then passes that code to the database engine, which executes it; for example, it might create logins, delete data or change permissions.
The best way to protect against these attacks is to parameterize every query sent to the database. You should use properly configured stored procedures; they are much safer than direct dynamic SQL. Never pass string values in the front-end application, and be sure that all queries to the database are sanitized before being executed against the database.
Encrypting data helps keep it secure even if unauthorized users gain access to it. There are several encryption features in SQL Server you can use to protect your data:
