SQL Server database security check list

SQL Server is a sensitive information library for organizations. Managers must ensure that only authorized users can access this sensitive information. However, it is not easy for SQL Server to be configured securely without generating errors. As a DBA, We have to perform a series of additional steps to enhance the security configuration of SQL Server deployment. This article lists a Microsoft SQL Server database security best practices checklist, which helps DBAs better protect databases and avoid internal and external attacks.AuthenticationSQL Server supports two authentication modes: Windows Authentication and Hybrid Authentication. Based on SQL Server security best practices, we recommend that you select Windows authentication for your SQL Server deployment, unless legacy application systems require Hybrid Authentication for backward compatible access. Windows authentication is safer than Hybrid Authentication. After this mode is enabled, Windows Authentication creden。 (that is, Kerberos or Windows nt lan Manager [NTLM] authentication creden。) Allow logon to SQL Server. Windows login uses a lot of encrypted information to authenticate SQL Server, and the password will not be transferred across networks during authentication. In addition, the Active Directory under Kerberos provides additional security levels. Therefore, authentication is more reliable, and role-based Active Directory groups can be used to reduce access control management. Compared with Windows Authentication mode, Hybrid Authentication supports logging on to SQL Server with Windows accounts and SQL Server dedicated accounts. The SQL login password is passed through the network for authentication, which is not as secure as Windows login.Ensure sySAdmin account SecurityIf you exit without modifying the settings, the "sySAdmin" (SA) account is vulnerable. Potential SQL Server attackers are aware of this. If they control this powerful user, database attacks are easier. To prevent attacks by using the "SA" account, you can rename the "SA" account to another account name. Follow these steps: Expand "Log on" in "Object Resource Manager", right-click the "SA" account, and select "RENAME" in the menu ". Alternatively, run the following T-SQL script to rename the "SA" account: USE [master] go alter login sa with name = [] GO, you can also disable the "SA" Account of the SQL Server instance.Set a complex password for the SA and SQL Server logon accountsWhen using the Hybrid Authentication mode, make sure to set a complex password for the "SA" account and the SQL Server dedicated Logon account used on other SQL servers. First, select "force password expiration" and "enhance password policy" for the "SA" account and all other SQL logon accounts. These two items ensure that all other SQL Server dedicated logon accounts follow the logon Policy of the underlying operating system. In addition, enable the "MUST_CHANGE" option for all newly set SQL logon accounts. This option ensures that the login must change the password after the first logon."SySAdmin" fixed SERVER role and "control server" permission qualificationsExercise caution when selecting the sySAdmin fixed Server role, because this role can do whatever you want on SQL Server. In addition, do not explicitly grant the "control server" permission to Windows logon, Windows Group logon, and SQL Server logon, because such logon has full administrator permissions for the entire SQL Server deployment. By default, the sySAdmin fixed server role clearly grants this permission.SQL Server ManagementAvoid using "SA", or any other SQL Logon account that has been granted the "control server" permission, or managing SQL Server instances by members under the sySAdmin fixed SERVER role. Instead, you need to set up dedicated Windows logon accounts for DBAs and assign the "sySAdmin" permission to these accounts for management purposes. To assign permissions to users, you can use built-in fixed server roles or database roles, or create your own custom server roles and database roles to achieve more refined permission control.Disable guest user accessBy default, guest users exist in each user and System database. It is a potential security risk in a secure and closed environment because it allows users unrelated to the database to log on to the database. Due to this potential risk, we need to disable guest users in all users and System databases (except msdb. In this way, members of the public Server role cannot access the user databases on the SQL Server instance, unless the user is explicitly authorized to access these databases.Restrict public role authorizationDue to potential security risks, we can use the following extended stored procedure to cancel the access permissions of public roles. In addition, do not explicitly assign permissions to public roles and access to system stored procedures. To list the stored procedures available for a public role, run the following query: SELECT o. [name] AS [SPName], u. [name] AS [Role] FROM [master] .. [sysobjects] o inner join [master] .. [sysprotects] p ON o. [id] = p. [id] inner join [master] .. [sysusers] u on p. uid = U. uid and p. [uid] = 0 AND o. [xtype] IN ('x', 'P ')Reduce SQL Server Surface AreaOnly necessary features should be installed When configuring SQL Server. After installation, use the SQL Server System's peripheral interface to disable unwanted features. You can also use the policy-based management function to create system policies for one or more SQL Server systems to implement fine-grained configuration settings.Strengthen the SQL Server portAnother SQL Server security best practice is to use the SQL Server Configuration Manager to modify the default port for SQL Server installation. In addition, special TCP ports should be used to replace dynamic ports. In addition, make sure to avoid common TCP ports (such as 1433 and 1434) and do not use these ports for client requests and interaction, because these ports are too well-known and easy to become targets.Disable the SQL Server Browser ServiceMake sure that the SQL Server Browser Service only runs on a single SQL Server running on multiple SQL Server instances. The SQL Server Browser Service displays SQL Server information in the network environment, which may be a potential security threat in a secure and closed environment.SQL Server service accountWe should create a dedicated low-Permission domain account to run the SQL Server service. In addition, you should regularly check the SQL Server service account members to ensure that they are not members of any domain user group or local user group, because they will have unnecessary permissions. For more information about the permissions required by each SQL Server service account, see "Configure Windows service account and permissions.Ensure SQL Server Error Log and registration key securityUse NTFS permissions to ensure SQL Server error logs and registration key security because they can display a large amount of information about SQL Server instances and installations.