Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This article provides information about best practices and guidelines that help establish security for SQL Server. For a comprehensive review of SQL Server security features, see Securing SQL Server.
For specific product security best practices, see Azure SQL Database and SQL Managed Instance and SQL Server on Azure VMs.
A layered security methodology provides a defense-in-depth solution by using multiple security capabilities targeted at different security scopes. The security features made available in SQL Server 2016, and improved in subsequent releases, help counter security threats and provide well-secured database applications.
Azure complies with several industry regulations and standards that can enable you to build a compliant solution with SQL Server running in a virtual machine. For information about regulatory compliance with Azure, see Azure Trust Center.
Organizations often need to protect data at the column level as data regarding customers, employees, trade secrets, product data, healthcare, financial, and other sensitive data is often stored in SQL Server databases. Sensitive columns often include identification/social security numbers, mobile phone numbers, first name, family name, financial account identification, and any other data that could be deemed personal data.
The methods and features mentioned in this section raise the level of protection at the column level with minimal overhead, and without requiring extensive changes to application code.
Use Always Encrypted to encrypt data at rest and over the wire. Encrypted data is only decrypted by client libraries at the application client level. Use randomized encryption over deterministic where possible. Always Encrypted with secure enclaves can improve performance for comparison operations such as BETWEEN, IN, LIKE, DISTINCT, Joins, and more for randomized encryption scenarios.
Use Dynamic Data Masking (DDM) to obfuscate data at the column level when Always Encrypted isn't an available option. Dynamic Data Masking (DDM) isn't compatible with Always Encrypted. Use Always Encrypted over dynamic data masking whenever possible.
You can also GRANT permissions at the column level to a table, view, or table-valued function. Consider the following:
- Only SELECT
, REFERENCES
, and UPDATE
permissions can be granted on a column.
- A table-level DENY
doesn't take precedence over a column-level GRANT
.
Row-Level Security (RLS) enables the ability to use user execution context in order to control access to rows in a database table. RLS ensures that users can only see the record that pertains to them. This gives your application 'record level' security without having to make significant changes to your application.
The business logic is encapsulated within table-valued functions controlled by a security policy that toggles the RLS functionality on and off. The security policy also controls the FILTER
and BLOCK
predicates that are bound to the tables RLS operates against. Use Row-Level Security (RLS) to limit the records that are returned to the user making the call. Use SESSION_CONTEXT (T-SQL) for users who connect to the database through a middle-tier application where application users share the same SQL Server user account. For optimal performance and manageability, follow the Row-Level Security best practices.
Tip
Use Row-Level Security (RLS) together with either Always Encrypted or Dynamic Data Masking (DDM) to maximize the security posture of your organization.
Transparent Data Encryption (TDE) protects the data at the file level by providing encryption-at-rest to the database files. Transparent Data Encryption (TDE) ensures that database files, backup files, and tempdb
files can't be attached and read without proper certificates decrypting database files. Without Transparent Data Encryption (TDE), it's possible for an attacker to take the physical media (drives or backup tapes) and restore or attach the database to read the contents. Transparent Data Encryption (TDE) is supported to work with all other security capabilities in SQL Server. Transparent Data Encryption (TDE) provides real-time I/O encryption and decryption of the data and log files. TDE encryption uses a database encryption key (DEK) is stored in the user database. The database encryption key can also be protected using a certificate, which is protected by the database master key of the master
database.
Use TDE to protect data at rest, backups, and tempdb
.
To audit SQL Server, create an audit policy at either the server or database level. Server policies apply to all existing and newly created databases on the server. For simplicity, enable server-level auditing and allow the database-level auditing to inherit the server-level property for all databases.
Audit tables and columns with sensitive data that have security measures applied to them. If a table or column is important enough to need protection by a security capability, then it should be considered important enough to audit. It's especially important to audit and regularly review tables that contain sensitive information but where it's not possible to apply desired security measures due to some kind of application or architectural limitation.
SQL Server supports two authentication modes, Windows authentication mode and 'SQL Server and Windows Authentication mode' (mixed mode).
Logins are separate from database users. First, map logins or Windows groups to database users or roles separately. Next, grant permissions to users, server roles, and/or database roles to access database objects.
SQL Server supports the following types of logins:
master
database.master
database) that hosts the database. SQL Server supports contained database users for both Windows and SQL Server authentication.The following recommendations and best practices help secure your identities and authentication methods:
Use least-privilege role-based security strategies to improve security management.
In Azure, use least-privilege security by using role-based access (RBAC) controls
Choose Active Directory over SQL Server authentication whenever possible, and especially choose Active Directory over storing the security at the application or database level.
Use multifactor authentication for accounts that have machine-level access, including accounts that use RDP to log into the machine. This helps guard against credential theft or leaks, as single-factor password-based authentication is a weaker form of authentication with credentials at risk of being compromised or mistakenly given away.
Require strong and complex passwords that can't be easily guessed, and aren't used for any other accounts or purposes. Regularly update passwords and enforce Active Directory policies.
Group-Managed Service Accounts (gMSA) provide automatic password management, simplified service principal name (SPN) management and delegate the management to other administrators.
Minimize the rights granted to the AD account of the DBA; Consider a separation of duties that limit access to the virtual machine, the ability to log into the operating system, the ability to modify error and auditing logs, and the ability to install applications and/or features.
Consider removing DBA accounts from the sysadmin role and granting CONTROL SERVER to DBA accounts rather than making them a member of the sysadmin role. The system admin role doesn't respect DENY
while CONTROL SERVER does.
Keeping historical records of data changes over time can be beneficial to address accidental changes to the data. It can also be useful for application-change auditing and can recover data elements when a bad actor introduces data changes that weren't authorized.
The following configuration and assessment tools address surface-area security, identify data security opportunities, and provide a best practice assessment of the security of your SQL Server environment at the instance level.
It helps to know what are some common threats that risk SQL Server:
--
.To minimize the risk of a SQL injection, consider the following items:
EXECUTE
, EXEC
, or sp_executesql
.;
: Query delimiter'
: Character data string delimiter--
: Single-line comment delimiter./* ... */
: Comment delimiters.xp_
: Catalog-extended stored procedures, such as xp_cmdshell
.
xp_cmdshell
on any SQL Server environment. Use SQLCLR instead, or look for other alternatives due to the risks xp_cmdshell
can introduce.To minimize the risk of a side-channel attack, consider the following:
Consider the following common infrastructure threats:
Since you don't want attackers to easily guess account names, or passwords, the following steps help reduce the risk of passwords being discovered:
Create a SQL account with a unique name that has sysadmin membership. You can do this from the portal by enabling SQL Authentication during provisioning.
Tip
If you don't enable SQL Authentication during provisioning, you must manually change the authentication mode to SQL Server and Windows Authentication Mode. For more information, see Change server authentication mode.
If you must use the SA login, enable the login after provisioning and assign a new strong password.
Consider the following to minimize ransomware risks:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
You learn about common cyberthreats like ransomware and what kinds of attack patterns an organization must be prepared for.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
Securing SQL Server - SQL Server
Use these articles to create and implement an effective security plan in SQL Server. Learn about the platform, authentication, objects, and applications.
Security documentation for SQL Server & Azure SQL Database - SQL Server
A reference of security and protection-related content for SQL Server and Azure SQL Database.
Vulnerability assessment for SQL Server - SQL Server
Use the vulnerability assessment scanner to discover, track, and remediate potential database vulnerabilities in SQL Server.