SQL Server Database Security checklist

　　

SQL Server is a sensitive repository for organizations, and managers need to ensure that only authorized users can access this sensitive information. However, it is not easy to make SQL Server configuration secure without generating an error, and as a DBA we have to perform a series of additional steps to harden our SQL Server deployment Security configuration. This article lists a Microsoft SQL Server database Security best Practices Checklist to help DBAs better protect the database from internal and external attacks.

Certification

SQL Server supports two modes of authentication: Windows Authentication and Mixed Mode authentication. Based on SQL Server security best practices, we recommend that you select Windows Authentication for your SQL Server deployment unless the legacy application requires mixed-mode authentication for backward-compatible access.

Windows authentication is more secure than hybrid authentication mode, and after this mode is enabled, Windows authentication credentials (that is, Kerberos or Windows NT LAN Manager "NTLM" authentication credentials) are allowed to log on to SQL Server. Windows logon uses many cryptographic information to authenticate SQL Server, and passwords are not passed across the network during authentication. In addition, the Active Directory under the Kerberos protocol provides an additional level of security. As a result, authentication is more reliable, and the use of role-based Active Directory groups can reduce control access management. Compared to Windows Authentication mode, mixed Mode authentication supports Windows account and SQL Server dedicated account login to SQL Server. SQL login passwords are passed through the network for authentication, which is less secure than Windows.

Ensure sysadmin account security

If you leave without modification, the "SySAdmin" (SA) account is vulnerable. Potential SQL Server attackers are aware of this, and if they control this powerful user, database attacks are easier. To prevent attacks using the "SA" account, you can rename the "SA" account to another account name. We can do this by expanding login in the Object Explorer, right-clicking on the "SA" account and selecting "Rename" in the menu. Alternatively, we can also execute the following T-SQL script to rename the "SA" account:

Use [master] go-ALTER LOGIN SA with NAME = [] Go

Alternatively, you can disable the "SA" account for the instance of SQL Server.

Set up complex passwords for SA and SQL Server dedicated login accounts

When using hybrid authentication mode, be sure to set a complex password for the "SA" account and the SQL Server dedicated login account used on the other SQL Server. First, select the "Force password expiration" and "Enforce password policy" options for the "SA" account and all other SQL login accounts. These two entries ensure that all other SQL Server dedicated logins follow the login policy of the underlying operating system. In addition, the "must_change" option is enabled for all newly set SQL login accounts. This option ensures that the login must change the password after the first login.

"SySAdmin" fixed server role and "CONTROL Server" permissions Eligibility

Be cautious about choosing the sysadmin fixed server role because the role can do whatever it wants on SQL Server. Also, do not explicitly grant the "CONTROL SERVER" permission to Windows logins, Windows group logins, and SQL Server logins because the logins for this permission have full administrator rights over the entire SQL Server deployment. By default, the sysadmin fixed server role explicitly owns this permission.

SQL Server Management

To avoid using "SA", or any other SQL login account that has been granted Control server permission, or a member of the sysadmin fixed server role to administer an instance of SQL Server. Instead, set up a dedicated Windows login account for DBAs to assign "SySAdmin" permissions to these accounts for administrative purposes. To assign permissions to users, you can use built-in fixed server roles or database roles, or you can create your own custom server roles and database roles to meet your finer-grained permission controls.

Disable Guest user access

By default, the guest user exists under each user and system database, which is a potential security risk in a secure, closed environment because it allows database-agnostic users to log on to access the database. Due to this potential risk, we need to disable the guest user in all user and system databases (except msdb). This ensures that members of the public server role cannot access the user database on the instance of SQL Server unless the user is explicitly authorized to access the databases.

Restricting authorization to public roles

Because of the potential security risks, we can use the following extended stored procedure to cancel access to public roles.

Also, do not explicitly assign permissions to user public roles and access to system stored procedures. To list the stored procedures that are available for a public role, you can perform 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 area

When you configure SQL Server, you should install only the necessary features and disable the unneeded features by using the perimeter interface of the SQL Server system after installation. You can also use policy-based management to create a system policy to implement fine-grained configuration settings for one or more SQL Server systems.

Hardening SQL Server ports

Another SQL Server security best practice is to use SQL Server Configuration Manager to modify the default port when you install SQL Server. Also, use a dedicated TCP port instead of a dynamic port. Also, be sure to avoid common TCP ports (such as 1433 and 1434), and do not use these ports for client requests and interactions, because these ports are too well known to be an attack target.

Disabling the SQL Server Browser service

Make sure that the SQL Server Browser service only runs on a single SQL Server that has multiple instances of SQL Server running on it. The SQL Server Browser service displays SQL Server information in a network environment, which can be a potential security threat in a secure, closed environment.

SQL Server service Account

We should create a dedicated low-privilege domain account to run the SQL Server service. Also, periodically check the SQL Server service account members to make sure that they are not members of any domain user group or local user group, because that would give those users unnecessary permissions.

Securing SQL Server error logs and registry keys

Use NTFS permissions to ensure that SQL Server error logs and registry keys are secure because they can present a large amount of information about SQL Server instances and installations.

Share from multi-backup reprint please specify the source

SQL Server Database Security checklist