What are SQL Server Security Best Practices?
SQL Server is Microsoft’s popular relational database. It comes with a rich set of features, including capabilities you can use to secure your information. There is a wide range of SQL Server security best practices you can implement to protect your database and maintain compliance as needed. This article explains six notable best practices, including a few tips to help you get started.
In this article, you will learn about the following SQL Server security best practices:
- Run Multiple SQL Server Security Audits
- Have a Strong Password Policy
- Limit Service Accounts Permissions
- Use Appropriate Authentication Options
- Use a Strong Database Backup Strategy
- Use SQL Monitoring Tools
1. Run Multiple SQL Server Security Audits
To prevent potential attacks and help support forensic analysis efforts, you should perform regular audits of server security, permissions, and logins. These audits do not only contribute to improving your security posture, but are also often required by regulations like the General Data Protection Regulation (GDPR) and the Health Insurance Portability and Accountability Act (HIPAA).
Ensure you keep track of the information most relevant for auditors. For example, you can choose to monitor common compliance criteria, as well as user logins and login auditing. You can also monitor C2 auditing, server configuration, schema changes, etc.
The key is to perform audits on a regular basis, to learn about issues that need to be remediated and act on them. Auditing is, of course, only valuable if you invest resources to remediate security weaknesses found.
Learn more in our detailed guide to SQL Server Audit functionality
2. Have a Strong Password Policy
Database admin accounts are required to have strong passwords that can hold out against brute-force attacks. Here are several guidelines you can follow:
- Use a minimum of ten characters—including uppercase and lowercase letters, special characters, and numbers. Passphrases can also generate strong passwords, when consisting of a minimum of fifteen characters and including both numbers and letters.
- Avoid passwords that are easy to guess—attackers perform dictionary-based brute force attacks, and can easily crack passwords that include birthdates, names, or common words and phrases.
Change your passwords periodically—enforce this and review admin passwords to ensure passwords are regularly rotated. - Use a password management tool—preferably one that comes with a strong master key for storing multiple passwords.
3. Limit Service Accounts Permissions
SQL Server services are each configured to run under a predefined Active Directory (AD) or Windows account. When specifying privileges for each account, you should use the principle of least privilege to ensure that each account works with the minimum system rights and permission it needs.
Ideally, you should assign a separate account for each service. The account should be a member of a security group. This configuration can prevent issues if an account for one service is damaged or compromised, ensuring that other services continue normal operations. You can set this up in the Server Configuration Manager.
Here are the common types of accounts you can configure for your SQL Server services:
- AD-managed service account—an account that cannot log on to a server, and is therefore considered more secure than domain-user accounts. Additionally, there is no need to manually manage password resets for accounts.
- Domain user account—an account that runs services. It does not come with administrator privileges, and is therefore considered secure in a domain environment.
- Local user account—ideal for non-domain environments.
- Local system account—a highly privileged account which should not be used to run services.
- Network service account—comes with fewer privileges than system accounts, but it still enables a service to gain access to network resources.
Related content: Read our guide to SQL Server roles
4. Use Appropriate Authentication Options
Here are several user authentication options you can use in SQL Server:
- SQL Server Authentication mode—allows only AD or Windows users to connect to your SQL Server. If you use this mode, it is recommended to disable the system administrator (sa) account.
- Windows Authentication mode—allows only AD or Windows users to connect to your SQL Server. In this case, SQL Server does not directly authenticate users. Instead, it permits access according to an access token that was previously issued to the logged in user.
- SQL Server and Windows Authentication mode—also called mixed authentication, this mode allows SQL and Windows logins, like sa accounts, to gain access to SQL Server.
When managing SQL Server authentication, it is preferable to use Windows Authentication, which can take advantage of features like AD accounts, groups, and password policies.
5. Use a Strong Database Backup Strategy
Backup copies are needed for recovery purposes during failure incidents. Here are two approaches to SQL Server backup:
- Full backup—this process backs up the entire database.
- Differential backup—once a full backup is completed, SQL Server maintains a map of extents (an extent is a group of eight pages in SQL Server database storage) eligible for back up, and then backs up only the extents that changed. Note that SQL Server does not clear the map of changed extents after performing a differential backup. Rather, the map is cleared only after a full backup.
Differential backups are faster and occupy less disk space than full backups. For large databases, prefer using differential backups continuously and full backups periodically. It is okay to do only full backups of small databases periodically.
6. Use SQL Monitoring Tools
SQL monitoring tools can help scan the processes of your database application as well as monitor all changes that occur to database server settings. These tools can promptly detect session anomalies and errors, as well as SQL statements that grant access to sensitive data. Keep in mind that since monitoring tools are integrated into the ecosystem, the tools themselves can represent a security risk. Ensure that they comply with your security and compliance requirements.Microsoft SQL Server Security With Satori
Satori, The DataSecOps platform, allows companies to enforce security policies from a single location, across SQL Server, as well as their other databases, data warehouses and data lakes. Such security policies can be data masking, data localization, row-level security and more.
Learn more: