Checklist for Securing MS SQL Server

Following are the best practices:

  1. Secure the Windows Server where SQL Server Operates

    Secure the Windows server because attackers’ intention is to gain access to the OS and copy the data files to their own server, where they can break passwords and encryption.

  2. Install Only the Required SQL Database Components

  3. Limit the Permissions of Service Accounts According to the Principle of Least Privilege

    • Each SQL Server service is configured to run under a specified Windows or Active Directory account - Plan the account that needs to be permitted to run specific services based on the principle of least privilege. Also, plan the states that each account should have and the minimum permissions and system rights it needs to start the functioning.

    • Active Directory managed service account - This is usually the best option. Since you cannot use managed service accounts to log on to a server, they are more secure than domain user accounts. You do not need to manually manage password resets for service accounts, as you must for regular domain user accounts.

    • Domain user account - This is most common type of account used to run services. This account type is quite secure in a domain environment because it doesn’t have administrator privileges.

    • Local user account - This is a good choice for non-domain environments.

    • Local system account - These accounts are highly privileged so, avoid using them to run services.

    • Network service account - This type of account has fewer privileges than a system account, but it does enable a service to have access to network resources, so avoid using it whenever possible.

    • Virtual service account - A virtual service account is like an AD managed service account, but it is a type of local account that you can use to manage services without a domain. It is an instance of the built-in Network Service account with its own unique identifier. Virtual service accounts are good to use for SQL services.

  4. Turn Off the SQL Server Browser Service

  5. Use Groups and Roles to Simplify Management of Effective Permissions

    The effective permissions for a given account on a specific resource result from:

    • Explicit permissions granted directly to the account on the resource.

    • Permissions inherited from membership in a role or group.

    • Permissions inherited from a parent resource.

  6. Follow the Principle of Least Privilege when Assigning SQL Server Roles

    • Sysadmin — Perform any activity on the SQL server

    • Serveradmin — Configure SQL server settings and shut down the server

    • Securityadmin — Manage logins, including their properties, passwords and permissions

    • Processadmin — Terminate processes on the SQL Server instance

    • Setupadmin — Add or remove linked servers and manage replication

    • Bulkadmin — Execute the BULK INSERT statement

    • Diskadmin — Manage disk files

    • Dbcreator — Create, alter or drop any database

    • Public — Every user is a member of this role. It does not have any permissions except to objects that are configured as public.

  7. Use Strong Passwords for Database Administrators

    Strong passwords are a must for all database administrator accounts to make them resistant to brute-force attacks. At a minimum, these passwords must contain at least 10 characters, including uppercase and lowercase letters, numbers, and specific symbols; however, passphrases are the best choice. These password best practices offer additional proven techniques for managing DB admin passwords properly.

  8. Use Appropriate Authentication Options

    • SQL Server Authentication mode

    • Windows Authentication mode

    • SQL Server and Windows Authentication mode

  9. Monitor Activity on Your SQL Server

    • Concurrency issues - If multiple users attempt access the same data, some requests are blocked until others complete, which can result in deadlock, in which two operations are blocking one another.

    • Deviations from your baseline - Record regular workloads metrics to establish a baseline so you know what they look like when the SQL database is operating normally. Then watch for deviations from that baseline. If you experience a significant change from your baseline without a clear cause, begin a security investigation as soon as possible.

  10. Audit Access and Changes to SQL Server and Your Databases

    Always ensure to audit the failed logins to SQL Server. Once you have enabled login auditing in SQL Server, the failed and successful login information is written to the SQL Server error logs. Ensure to monitor regularly for suspicious activities.

  11. Protect against SQL Injection Attacks

    The best way to protect against these attacks is to parameterize each query sent to the database. Use properly configured stored procedures; they are safer than direct dynamic SQL. Never pass string values in the front-end application and ensure that all queries to the database are sanitized before being executed against the database.

  12. Use Encryption Wisely

    Encrypting data helps keep it secure even if unauthorized users gain access to it. There are several encryption features in SQL Server you can use to protect your data:

    • Transparent data encryption (TDE)

    • Always encrypted- MS-SQL server 2016 or later this will provide the best security.

    • Column level encryption