- Related Products
- ADManager Plus
- ADAudit Plus
- ADSelfService Plus
- Exchange Reporter Plus
- AD360
- Log360
Click here to expand
Data is a critical asset of every organization, and poorly-secured databases are often the reason for security breaches. SQL Server is designed to be a secure database platform, however, using the default settings leaves security gaps in the system. SQL Server has many security features you should configure individually to improve security. This page details SQL server security best practices and essential security considerations for protecting your databases from malicious attacks.
Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'
Enabling Ad Hoc Distributed Queries allows users to query data and execute statements on external data sources. This feature can be used to access remotely and exploit vulnerabilities on remote SQL Server instances and to run unsafe visual basic for application functions.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE;
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Ensure 'CLR Enabled' Server Configuration Option is set to '0'
The clr enabled option specifies whether user assemblies can be run by SQL Server. Enabling use of CLR assemblies widens the attack surface of SQL Server and puts it at risk from both inadvertent and malicious assemblies.
This functionality should be disabled if 'clr strict security' option is set to 0. Note that this option is only available since SQL Server 2017. If clr strict security is set to 1 this recommendation is not applicable. By default, clr strict security is enabled and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. Though not recommended, the clr strict security option can be disabled for backward compatibility. To check the status of 'clr strict security' option, run the following T-SQL command:
SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr strict
security';Run the following T-SQL command:
EXECUTE sp_configure 'clr enabled', 0; RECONFIGURE;
Ensure 'Cross DB Ownership Chaining' Server Configuration Option is set to '0'
This option allows a member of the db_owner role in a database to gain access to objects owned by a login in any other database, causing an unnecessary information disclosure. Cross-database ownership chaining should only be enabled for the specific databases requiring it, instead of enabling it at the instance level for all databases by using the ALTER DATABASESET DB_CHAINING ON command. This database option may not be changed on the master, model, or tempdb system databases.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'cross db ownership chaining', 0; RECONFIGURE;
GO
Ensure 'Database Mail XPs' Server Configuration Option is set to '0'
The Database Mail XPs option controls the ability to generate and transmit email messages from SQL Server.
This configuration should be set to '0'. Disabling the Database Mail XPs option reduces the SQL Server surface, eliminates a DOS attack vector and channel to exfiltrate data from the database server to a remote host.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'Database Mail XPs', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Ensure 'Ole Automation Procedures' Server Configuration Option is set to '0'
The OLE Automation Procedures option controls whether OLE Automation objects can be instantiated within Transact-SQL batches. These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server. Enabling this option will increase the attack surface of SQL Server and allow users to execute functions in the security context of SQL Server.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Ensure 'Remote Access' Server Configuration Option is set to '0'
The 'Remote Access' option controls the execution of local stored procedures on remote servers or remote stored procedures on local server. This functionality can be abused to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'remote access', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Note: Restart the SQL Server service.
Ensure 'Remote Admin Connections' Server Configuration Option is set to '0'
The remote admin connections option controls whether a client application on a remote computer can use the Dedicated Administrator Connection (DAC).The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection.
In a cluster scenario, the administrator may not actually be logged on to the same node that is currently hosting the SQL Server instance and thus is considered "remote". Therefore, this setting should usually be enabled (1) for SQL Server failover clusters; otherwise, it should be disabled (0).
Run the following T-SQL command:
EXECUTE sp_configure 'remote admin connections', 0; RECONFIGURE;
GO
Ensure 'Scan For Startup Procs' Server Configuration Option is set to '0'
The scan for startup procedures option, if enabled, causes SQL Server to scan for and automatically run all stored procedures that are set to execute upon service startup. Setting Scan for Startup Procedures to 0 will prevent certain audit traces and other commonly used monitoring stored procedures from re-starting on start up. Additionally, replication requires this setting to be enabled (1) and will automatically change this setting if needed.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'scan for startup procs', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Note: Restart the SQL Server service.
Ensure 'Trustworthy' Database Property is set to 'Off'
The TRUSTWORTHY database option allows database objects to access objects in other databases under certain circumstances. Provides protection from malicious CLR assemblies or extended procedures.
This configuration should be set to '0' except for msdb database which requires this to be 'ON'.
Run the following T-SQL command for the databases where this property is turned on:
ALTER DATABASE [<database_name>] SET TRUSTWORTHY OFF;
Ensure 'SQL Mail XPs' Server Configuration Option is set to '0'
SQL Mail provides a mechanism to send, receive, delete, and process e-mail messages using SQL Server in 2008 R2 or Before.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'SQL Mail XPs', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Using default port(1433) makes the server vulnerable to the attacks directed to the default port.
Enabling Ad Hoc Distributed Queries allows users to query data and execute statements on external data sources. This feature can be used to access remotely and exploit vulnerabilities on remote SQL Server instances and to run unsafe Visual Basic for Application functions.
Any port available in the server.
The port can be anything but the default 1433.
Using GUI,
Note: The connection settings of any application that uses port number to communicate with SQL server needs to be reconfigured while changing the port of SQL server.
Ensure 'Hide Instance' option is set to 'Yes' for Production SQL Server instances
Non-clustered SQL Server instances within production environments should be designated as hidden to prevent advertisements by the SQL Server Browser service. However, clustered instances may break if this option is selected. If you hide a clustered named instance, the cluster service may not be able to connect to the SQL Server.
This configuration should be set to '1'.
Using GUI,
Alternatively run the following T-SQL command:
EXEC master.sys.xp_instance_regwrite @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib', @value_name = N'HideInstance', @type = N'REG_DWORD', @value = 1;
Ensure the 'sa' Login Account is set to 'Disabled'
The sa account is a widely known and often widely used SQL Server account with sysadmin privileges. This is the original login created during installation and always has the principal_id=1 and sid=0x01. Enforcing this control reduces the probability of an attacker executing brute force attacks against a well-known principal.
It is not a good security practice to code applications or scripts to use the sa account. However, if this has been done, disabling the sa account will prevent scripts and applications from authenticating to the database server and executing required tasks or functions.
Run the following T-SQL command:
USE [master]
GO
DECLARE @tsql nvarchar(max) SET @tsql = 'ALTER LOGIN ' + SUSER_NAME(0x01) + ' DISABLE' EXEC (@tsql)
GO
Note: The applications which use sa login to authenticate SQL Server connection need to be reconfigured with different user while altering the sa login.
Ensure the 'sa' Login Account has been renamed
It is easier to launch password-guessing and brute-force attacks against the sa login if the name is known.
Any set of characters that are allowed by Microsoft SQL login name restrictions
The sa Login should be renamed.
Run the following T-SQL command:
ALTER LOGIN sa WITH NAME = <different_user>;
Note: The applications which use sa login to authenticate SQL Server connection need to be reconfigured with different user while altering the sa login.
Ensure 'xp_cmdshell' Server Configuration Option is set to '0'
The xp_cmdshell option controls whether the xp_cmdshell extended stored procedure can be used by an authenticated SQL Server user to execute operating-system command shell commands and return results as rows within the SQL client.The xp_cmdshell procedure is commonly used by attackers to read or write data to/from the underlying Operating System of a database server.
This configuration should be set to '0'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'xp_cmdshell', 0; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Ensure 'AUTO_CLOSE' is set to 'OFF' on contained databases
AUTO_CLOSE determines if a given database is closed or not after a connection terminates. If enabled, subsequent connections to the given database will require the database to be reopened and relevant procedure caches to be rebuilt.
This configuration should be set to 'OFF'.
Run the following T-SQL command for databases where this configuration is 'OFF':
ALTER DATABASE <database_name> SET AUTO_CLOSE OFF;
Ensure no login exists with the name 'sa
The sa login (e.g. principal) is a widely known and often widely used SQL Server account. Therefore, there should not be a login called sa even when the original sa login (principal_id = 1) has been renamed.
Login names can be of any set of characters allowed by Microsoft SQL Login name guidelines.
No Logins should be named as 'sa'.
Run the following T-SQL command for logins where name is 'sa':
USE [master]
GO
ALTER LOGIN [sa] WITH NAME = <different_name>;
GO
Note: The applications which use the altered logins to authenticate SQL Server connection need to be reconfigured another user with equivalent privileges.
Ensure 'clr strict security' Server Configuration Option is set to '1'
The clr strict security option specifies whether the engine applies the PERMISSION_SET on the assemblies in SQL Server 2017 and 2019.
This configuration should be set to '1'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'clr strict security', 1; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Ensure 'Server Authentication' Property is set to 'Windows Authentication Mode'
Windows provides a more robust authentication mechanism than SQL Server authentication.
This configuration should be set to 'Windows Authentication Mode'.
Using GUI,
Alternatively run the following T-SQL command:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO
Note: Restart the SQL Server service.
Ensure CONNECT permissions on the 'guest' user is Revoked within all SQL Server databases excluding the master, msdb and tempdb
A login assumes the identity of the guest user when a login has access to SQL Server but does not have access to a database through its own account and the database has a guest user account. Revoking the CONNECT permission for the guest user will ensure that a login is not able to access database information without explicit access to do so.
The guest users might have or might not have CONNECT permissions.
CONNECT permission for guest users must be revoked in all databases except for master, msdb and tempdb.
Run the following T-SQL command for the databases with guest connect permission on:
USE <database_name>;
GO
REVOKE CONNECT FROM guest CASCADE;
Ensure 'Orphaned Users' are Dropped From SQL Server Databases
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance and is referred to as orphaned and should be removed. Orphan users should be removed to avoid potential misuse of those broken users in any way.
A Database might have or might not have any orphaned users
No orphaned users must be present in a database server.
Run the following T-SQL command for all the orphaned users:
USE <database_name>;
GO
DROP USER <username>;
Note: The orphaned users can be troubleshooted if possible. Refer Microsoft learn for further details.
Ensure SQL Authentication is not used in contained databases
Contained databases do not enforce password complexity rules for SQL Authenticated users. The absence of an enforced password policy may increase the likelihood of a weak credential being established in a contained database.
This configuration should be set to 'Windows Authentication Mode'.
Leverage Windows Authenticated users in contained databases. Refer Microsoft learn for further details.
If required use the following T-SQL command to drop logins:
USE <db_name>
GO
DROP USER <user_name>;
Note: Applications that use dropped logins to authenticate the SQL server need to be reconfigured with different logins.
Ensure only the default permissions specified by Microsoft are granted to the public server role
The 'public' is a special fixed server role containing all logins. Unlike other fixed server roles, permissions can be changed for the public role. In keeping with the principle of least privileges, the public server role should not be used to grant permissions at the server scope as these would be inherited by all users. Every SQL Server login belongs to the public role and cannot be removed from this role. Therefore, any permissions granted to this role will be available to all logins unless they have been explicitly denied to specific logins or user-defined server roles. When the extraneous permissions are revoked from the public server role, access may be lost unless the permissions are granted to the explicit logins or to user-defined server roles containing the logins which require the access.
Any number of permissions might be given to public role.
No extraneous permission must be given to public role and should be removed if given and delegated to user defined role if needed.
Add the extraneous permissions found in the results to the specific logins to user-defined server roles which require the access.
Run the following T-SQL command for the permissions found:
USE [master]
GO
REVOKE <permission_name> FROM public;
GO
Note: For public role, 'View any database' and 'Connect' are permissible.
Ensure Windows BUILTIN groups are not SQL Logins
The BUILTIN groups (Administrators, Everyone, Authenticated Users, Guests, etc.) generally contain very extensive memberships which would not meet the best practice of ensuring only the necessary users have been granted access to a SQL Server instance. These groups should not be used for any level of access into a SQL Server Database Engine instance.
Any group may it be BUILTIN or user defined, they can be SQL Logins.
The Windows BUILTIN groups must be removed from SQL Logins. Note that before dropping the BUILTIN group logins, ensure that alternative AD Groups or Windows logins have been added with equivalent permissions. Otherwise, the SQL Server instance may become totally inaccessible.
Using GUI,
USE [master]
GO
DROP LOGIN [<name>]
GO
Ensure Windows Local groups are not SQL Logins
Local Windows groups should not be used as logins for SQL Server instances. Allowing local Windows groups as SQL Logins provides a loophole whereby anyone with OS level administrator rights (and no SQL Server rights) could add users to the local Windows groups and give themselves or others access to the SQL Server instance.
Any windows group can be SQL Login.
The Windows Local groups must be removed from SQL Logins. Note that before dropping the Local group logins, ensure that alternative AD Groups or Windows logins have been added with equivalent permissions. Otherwise, the SQL Server instance may become totally inaccessible.
Using GUI,
USE [master]
GO
DROP LOGIN [<name>]
GO
Ensure the public role in the msdb database is not granted access to SQL Agent proxies
Granting access to SQL Agent proxies for the public role would allow all users to utilize the proxy which may have high privileges. This would likely break the principle of least privileges.
The public role might have access to any number of proxies.
Revoke any agent proxy access to public role. Before revoking the public role from the proxy, ensure that alternative logins or appropriate user-defined database roles have been added with equivalent permissions. Otherwise, SQL Agent job steps dependent upon this access will fail.
Using GUI,
USE [msdb]
GO
EXEC dbo.sp_revoke_login_from_proxy @name = N'public', @proxy_name = N'<proxyname>';
GO
Ensure 'CHECK_EXPIRATION' option is set to 'ON' for all SQL Authenticated Logins Within the Sysadmin Role
Applies the same password expiration policy used in Windows to passwords used inside SQL Server if turned on. Else the passwords in use might be weak.
This option should be set to 'ON'. This is a mitigating recommendation for systems which cannot follow the recommendation to use only Windows Authenticated logins.
Run the following T-SQL command for the login names where check expiration is set to 'OFF':
ALTER LOGIN [<login_name>] WITH CHECK_EXPIRATION = ON;
Ensure 'CHECK_POLICY' Option is set to 'ON' for All SQL Authenticated Logins
Applies the same password complexity policy used in Windows to passwords used inside SQL Server if turned on. Else the passwords in use might be weak.
This option should be set to 'ON'. The setting is only enforced when the password is changed. This setting does not force existing weak passwords to be changed. Thus existing passwords need to be changed manually.
Run the following T-SQL command for the login names where check policy is set to 'OFF':
ALTER LOGIN [<login_name>] WITH CHECK_POLICY = ON;
Ensure 'Maximum number of error log files' is set to greater than or equal to '12'
SQL Server error log files must be protected from loss. The log files must be backed up before they are overwritten. Retaining more error logs helps prevent loss from frequent recycling before backups can occur.
All positive numerical values
This option should be set to greater than or equal to 12.
Using GUI,
Alternatively run the following T-SQL command replacing <NumberGreaterThanOrEqualTo12>:
EXEC master.sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, <NumberGreaterThanOrEqualTo12>;
Ensure 'Default Trace Enabled' Server Configuration Option is set to '1'
The default trace provides audit logging of database activity including account creations, privilege elevation and execution of DBCC commands.
This configuration should be set to '1'.
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; EXECUTE sp_configure 'default trace enabled', 1; RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;
Ensure 'Login Auditing' is set to 'failed logins'
This setting will record failed authentication attempts for SQL Server logins to the SQL Server Errorlog. Capturing failed logins provides key information that can be used to detect or confirm password guessing attacks. Capturing successful login attempts can be used to confirm server access during forensic investigations, however, using this audit level setting to also capture successful logins creates excessive noise in the SQL Server Errorlog which can hamper a DBA trying to troubleshoot problems.
This configuration should be set to 'failure'.
Using GUI,
Alternatively run the following T-SQL command:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2
Note: Restart SQL Server service.
Ensure 'SQL Server Audit' is set to capture both 'failed' and 'successful logins'
SQL Server Audit is capable of capturing both failed and successful logins and writing them to one of three places: the application event log, the security event log, or the file system. By utilizing Audit instead of the traditional setting under the security tab to capture successful logins, we reduce the noise in the ERRORLOG.
Any number of Server Audits might be present in a Server with Audit Action Type of AUDIT_CHANGE_GROUP, FAILED_LOGIN_GROUP and SUCCESSFUL_LOGIN_GROUP.
There should be atleast one Server Audit specification must be created/present with following audit names:
Using GUI,
Alternatively run the following T-SQL command replacing <Enter audit name here> and <Enter audit spec name here>:
USE master
GO
CREATE SERVER AUDIT <Enter audit name here> TO APPLICATION_LOG;
GO
CREATE SERVER AUDIT SPECIFICATION <Enter audit spec name here> FOR SERVER AUDIT <Enter audit name here> ADD (FAILED_LOGIN_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP), ADD (FAILED_DATABASE_AUTHENTICATION_GROUP) WITH (STATE = ON);
GO
ALTER SERVER AUDIT <Enter audit name here> WITH (STATE = ON);
GO
Ensure 'CLR Assembly Permission Set' is set to 'SAFE_ACCESS' for All CLR Assemblies
Setting CLR Assembly Permission Sets to SAFE_ACCESS will prevent assemblies from accessing external system resources such as files, the network, environment variables, or the registry. Assemblies with EXTERNAL_ACCESS or UNSAFE permission sets can be used to access sensitive areas of the operating system, steal and/or transmit data and alter the state and other protection measures of the underlying Windows Operating System.
All CLR Assemblies should have the permission set to 'SAFE_ACCESS' except for those which are Microsoft-created (is_user_defined = 0) are excluded from this check as they are required for overall system functionality. The remediation measure should first be tested within a test environment prior to production to ensure the assembly still functions as designed with SAFE permission setting.
Run the following T-SQL command:
USE <database_name>;
GO
ALTER ASSEMBLY <assembly_name> WITH PERMISSION_SET = SAFE;
Ensure 'Symmetric Key Encryption algorithm' is set to 'AES_128' or higher in non-system databases
As per the Microsoft Best Practices, only the SQL Server AES algorithm options, AES_128, AES_192, and AES_256, should be used for a symmetric key encryption algorithm. The following algorithms (as referred to by SQL Server) are considered weak or deprecated and should no longer be used in SQL Server: DES, DESX, RC2, RC4, RC4_128.
All Symmetric keys in database must use 'AES_128' or higher as encryption algorithm.
Refer Microsoft learn for learning about Altering symmetric key.
If required, use following T-SQL command to drop symmetric keys:
USE <database_name>
GO
DROP SYMMETRIC KEY <key_name>;
Ensure Asymmetric Key Size is set to 'greater than or equal to 2048' in non-system databases
Microsoft Best Practices recommend to use at least a 2048-bit encryption algorithm for asymmetric keys. The RSA_2048 encryption algorithm for asymmetric keys in SQL Server is the highest bitlevel provided and therefore the most secure available choice.
Asymmetric key size should be set to greater than or equal to 2048 bits.
Refer Microsoft learn for learning about Altering asymmetric key.
If required, use following T-SQL command to drop asymmetric keys:
USE <database_name>
GO
DROP ASYMMETRIC KEY <key_name>;
Copyright © 2020, ZOHO Corp. All Rights Reserved.
Download PDF