SQL Server Security: A Practical Guide
What is Data Security?
Microsoft SQL Server provides several built in features that enable security, including encrypted communication over SSL/TLS, the Windows Data Protection API (DPAPI) used to encrypt data at rest, authentication and authorization. It is up to each database administrator to configure these features, or use additional security measures as needed, to address the security and compliance requirements of their data and applications.
SQL Server security is based on several hierarchical entities:
- Server—representing the entire SQL Server instance.
- Database—each server can have multiple databases. A database is a collection of securable objects.
- Securable object—data stored in a database, which requires associated permissions.
- Principal—a person, group, or process that needs to access data. Privileges granted to principals are managed by the SQL Server security framework.
In this article, you will learn:
- SQL Server Security Threats
- SQL Server Security Configuration
- SQL Server Security Best Practices
- Run Routine Security Audits
- Have a Strong Password Policy
- Reducing the Attack Surface
- Use Encryption
- Use a SQL Monitoring Tool
- SQL Server Security in the Cloud
- Azure SQL Server Security
- AWS SQL Server Security
- Google Cloud SQL Server Security
- SQL Server Security with Satori
SQL Server Security Threats
Here are three common security threats that affect SQL Server databases:
- SQL server authentication—SQL Server login can be vulnerable to connection string injection attacks. When a connection string is constructed at run time, unless the string is checked for valid keyword pairs, an attacker can add extra characters that can perform unauthorized actions on the server. A better option is to use Windows authentication, which is more secure.
- Privilege escalation—SQL Server databases typically contain sensitive data, and there is always a risk that users will assume privileges of more trusted roles, gaining unauthorized access to data and database functions. To mitigate this, prefer to run users as least-privileged accounts, and block the ability to execute code from administrative or owner accounts. If you need to grant extra permissions, use certificates to sign stored procedures or use impersonation, only for the duration of the task.
- SQL injection—SQL Server, like other databases, is vulnerable to attacks in which malicious users inject commands into query strings. These commands can damage or corrupt the database or be used to exfiltrate sensitive data. To prevent SQL injection, ensure you sanitize all database inputs to ensure they do not contain characters that can be used to execute code.
- Verbose errors—in many cases, SQL Server databases return error messages that include internal information, which can help attackers identify a vulnerability or plan an attack. Ensure that all procedural code uses error handling, to prevent default error messages from reaching the user.
- Denial of service attacks—production SQL Server databases may be subject to application-layer denial of service (DoS) attacks, in which attackers flood the database with fake queries, slowing performance for legitimate users, and eventually resulting in downtime. If you run SQL Server in the cloud, you can leverage DDoS protection services, which can capture and divert malicious traffic away from your database.
- Lack of security expertise and awareness—many database administrators are not sufficiently aware or trained on security issues, and may not know how to use the security capabilities of SQL Server. Any team that works on sensitive databases must have regular security training, and should work together with security experts to ensure SQL Server is securely configured.
SQL Server Security Configuration
Microsoft SQL Server provides several layers of security: conditional access, auditing, and encryption.
- Role-based access—SQL Server lets you control access to data at three levels, the entire database instance, a database, and a table. Learn more about built-in and custom security roles in our detailed guide to SQL Server Roles.
- Data masking—you can automatically mask data, by replacing it with realistic fake data, to securely copy production databases and provide data for testing, training, and similar use cases. Learn more in our detailed guide to SQL Server data masking.
- Row-Level Security (RLS) and Column-Level Security (CLS)—you can define conditional access to specific rows or columns in a database table. This is a powerful tool for protecting sensitive data, or data subject to compliance requirements. Learn more in our detailed guide to SQL Server Row Level Security and SQL Server Column Level Security (coming soon).
Microsoft provides SQL Server Audit, a tool built into SQL Server, which reads transaction logs and records information about data and object changes in your database. You can use it to create server-level audits and database-level audits, with several auditing levels.
Learn more in our detailed guide to SQL Server Audit
- Encrypted communications—connections to SQL Server can be protected using SSL/TLS. SQL Server transfers data using the Tabular Data Stream (TDS) protocol, which is unencrypted by default. Administrators can provide a certificate and enable SSL/TLS, either by enabling “Force Encryption” in database configuration, or by specifying the “Encrypt” option in a specific application connection chain.
- Windows Data Protection API (DPAPI)—this is an encryption capability built into Windows—the encryption algorithm used depends on the version of DPAPI present on the Windows machine. At the SQL Server instance level, the security objects it can use are logins, server roles and credentials. At the database level, the security objects are users, certificates, functions, schemes, and encryption keys.
- SQL Server Service Key—is the basic encryption key used to encrypt data in SQL Server, also protected by DPAPI. This service key is created by SQL Server during the first startup. You cannot create it, but you can backup and restore the key on the same instance or other instances. You can also reset the Service Key on an existing SQL Service instance.
- Master Key—this is an additional, optional key in each SQL Server database, which can be protected by the service key or by a secure password provided by the user. You can backup the master key and restore it on the same or another database. Regenerating the service key will also regenerate all other keys, such as symmetric or asymmetric master encryption keys.
- Transparent data encryption (TDE)—a feature provided by Enterprise, Developer, and Data Center editions of SQL Server. Lets you encrypt the entire database and its log file with a provided key. TDE is transparent for the user and applications accessing the SQL Server database. It lets you choose from several encryption algorithms including AES 256 bit and Triple DES.
Using a Firewall with SQL Server
Beyond these built-in security layers, it is strongly recommended to protect a SQL Server database with a firewall. When setting up a firewall, you need to ensure the essential ports SQL Server needs to communicate are open—these are TCP 1433 (database engine), 2383 (analysis services), 4022 (service broker), and UDP 1343 (browser access).
SQL Server Security Best Practices
Run Routine Security AuditsTo help prevent potential attacks and assist post-breach forensic analysis, routine audits of server security, login, and permissions are a necessity. This is no longer a mere investment in security; HIPAA, GDPR, and other regulations now require these audits by law.Your first step in an audit is defining what you wish to monitor. This should include server configuration, schema changes, SQL server auditing, and other common compliance criteria. You should also check C2 auditing (a form of logging specified by the US Department of Defense), SQL trace, change data capture (CDC), and extended events, as well as DML, DDL, logon triggers, user logins, and login auditing.A high priority should be identifying and responding to modified or missing database objects, repeated server login failures, and permissions or configuration changes. All of these could indicate a security breach.Routine audits also contribute to maintaining a healthy database and network. Running each query may not only highlight a security threat; it may indicate an error in the SQL execution plan. Seeking out and resolving the underlying reason may prevent future issues.
Have a Strong Password PolicyAll database administrator accounts require strong passwords to defend against brute force attacks. Use at least 10 characters, including numbers, upper and lower case letters, and special characters. Preferably, enforce passwords of 15 characters or more.Ensure that administrators select passwords that are not easy to guess, and educate them against using the same password on multiple systems. Ensure that administrator passwords are changed regularly. It is a good practice to use a password management tool shared by the database administration team, with a strong master key.
Deploy and Test SQL Server UpdatesOperating system service packs and upgrades, as well as SQL Server updates, contain important security enhancements. Test these updates with your database application, and then apply them as soon as possible.
Use a FirewallFirewalls are also a very effective security control. A firewall restricts network traffic according to a set of predefined rules, and can be configured to enforce your data security policy. By putting your database behind a firewall, you create another layer of security preventing malicious traffic from reaching your server. Firewalls can also help prevent malicious outbound traffic, such as command and control (C&C) communication or data exfiltration.
Use EncryptionEncryption cannot solve access control problems. However, if an attacker does manage to bypass access controls, encrypting data at rest using features like TDE can limit data loss and render the data worthless to attackers. In addition, set up secure channels using SSL/TLS and leverage the Windows Data Protection API to ensure encryption of all data in transit.
Avoid Installing Non-Essential SoftwareInstalling more applications and components on the machine running your SQL Server instance gives attackers additional entry points. In addition, you spend more time updating and managing this software, and if you fail to update it, unpatched software results in dangerous vulnerabilities.Even within SQL Server itself, it is recommended that you only install the components and features required for a specific task. This had an additional benefit of reducing resource requirements and simplifying administration.
Use a SQL Monitoring ToolUse a SQL monitoring tool to maintain your network, protect it from attacks, and ensure optimal performance. The tool should be capable of scanning database application processes and detecting server settings changes. It should be able to identify sessions, errors, and individual SQL statements that imply access to sensitive data.Remember that the monitoring tool is itself a potential security hazard, due to its contact with the system and its access to sensitive data. Ensure that your SQL monitoring tool itself is protected by stringent security controls.
Use a Data Access ControllerData Access Controllers provide visibility into data access and apply data access policies. Services such as Satori inspect every transaction and database response and build an audit of data access activity, an inventory of the sensitive data locations, a policy engine to define granular data access policies and a workflow mechanism to approve or deny access to datasets.
SQL Server Security in the Cloud
Many organizations run SQL Server as a managed service in the cloud. Let’s see how you can secure SQL Server databases on the leading cloud providers—Azure, AWS, and Google Cloud.
Azure Defender for SQL
Microsoft Azure provides a dedicated service called Azure Defender for SQL, which can be used with local SQL Server, Amazon Web Services (AWS), Google Cloud Platform (GCP) multi-cloud deployments, and Azure virtual machines.
Azure Defender for SQL continuously monitors for known SQL Server vulnerabilities and threats. It has two key capabilities:
- Vulnerability Assessment—a scanning service used to find, track, and repair potential database vulnerabilities. The assessment scan provides an overview of the security posture of the SQL Server instance, detailed information about vulnerabilities discovered including remediation instructions.
- Advanced Threat Protection—a detection service that continuously monitors SQL Server for threats like SQL injection, brute force attacks, and abuse of privileges. The service provides security alerts in Azure Security Center, giving you detailed information about suspicious activity, guidance on how to mitigate threats, and the option to perform in-depth investigation in Azure Sentinel.
AWS SQL Server Security
Amazon RDS is a managed relational database service that supports several database engines, including SQL Server. RDS provides a set of features to securely store and access your data, including:
- Network level isolation using Amazon Virtual Private Cloud (VPC)—you use security groups to control the IP addresses or Amazon EC2 instances that can connect to your database. A built-in firewall blocks access to the database through specified rules.
- Access management via AWS Identity and Access Management (IAM) policies—let you assign permissions to determine who can manage your RDS resources. You can use SQL Server security features to control who can log into your database, as if it were on a local network. You can also map database users to IAM roles for federated access.
- Encryption for data in transit using SSL/TLS connections. You can use Amazon Key Management Service (KMS) to encrypt production databases and backups.
Google Cloud SQL Server Security
Google Cloud SQL lets you run SQL Server as a managed service. Cloud SQL provides the following security features:
- Identity and Access Management (IAM)—Cloud SQL provides a set of predefined roles designed to control database access. You can also create your own custom roles.
- Encryption for internal communication and data at rest—Google Cloud encrypts data by default before transmitting it on internal Google networks, and while data is at rest in database tables, backups, or temporary files.
- Encryption for external communication—connections to Google Cloud SQL from external sources can be secured using Cloud SQL Proxy or SSL/TLS.