Key Insights into Ensuring SQL Server Security with These Practices

by Sep 20, 2023

There are several examples where data theft has halted businesses or resulted in bad press that will leave a tarnished image. For instance, in 2017, Equifax disclosed that the personal information of over 145 million people was exposed, including social security numbers and credit card numbers. 2018 Facebook suffered at least two separate data privacy incidents, affecting over 90 million user accounts. There have been many more horror stories from banks, financial institutions, healthcare providers, and retail organizations recently, and they get even scarier as we move into this digital-only world. As organizations start looking at security as first-class citizens and work on it proactively, these incidents will still keep happening, and we will always be playing the catch-up game.

In this paper, we cover some of the standard security practices that I propose to my customers daily and explain how one needs to start implementing security measures within their deployment of SQL Server. If you are a DBA, these are like checklists you don’t want to miss when working with a database engine like SQL Server. We will cover details around the logins and authentication area.

Audit Logins

Keep the setting “Both failed and successful logins” for login auditing. By default, these settings are not to be tampered with. Sometimes inexperienced DBAs have turned this off. 

The idea is to ensure no Denial-of-Service (DOS) attacks are made on the system, and we are tracking the connections coming into the server. From SQL Server 2008, it is also possible to use the new Audit feature to log connection attempts. If you plan to use this as a best practice, you should use a secured location for audit data files.

Authentication and Disable SA

Try to use Windows Authentication as much as possible. The usage of mixed-mode authentication is recommended for legacy applications and non-Windows users. We recommend this for SQL Server Administration instead of the default SYSADMIN SA account. Try to keep the SA account private from multiple users. We can change the authentication type on the Server Properties > Security > Server Authentication.

Make sure to disable the SA login inside SQL Server. Rename or disable this account and use another to administer your SQL Server instance. The following command can help you DISABLE / ENABLE the SA user account.

  1. /* Disable SA Login */
  2. ALTER LOGIN [sa] DISABLE
  3. GO
  4. /* Enable SA Login */
  5. ALTER LOGIN [sa] ENABLE
  6. GO

As we move to the following recommendation if you have to use mixed-mode authentication, enable the password expiration and password complexity values for all the logins created on the server for all the authentication types.

If multiple users administer a given SQL Server instance, then tracking password changes for SQL Server logins is recommended. Enabling auditing on password changes for SQL Server logins allows you to investigate when and by whom the passwords of specific logins have changed. Consider creating a server audit for the action ‘LOGIN_CHANGE_PASSWORD_GROUP’.

View Database Permissions

This is server-level permission which is often less appreciated. If given, the login can see all metadata for all databases, irrespective of whether the login owns the database. By controlling this permission, we can restrict the data a login can see in the SYS. DATABASES, SYS.SYSDATABASES views and SP_HELPDB calls. To limit visibility to database metadata, deny a log-in the VIEW DATABASE permission.

  1. /* REVOKE VIEW DEFINITION PERMISSION */
  2. USE master
  3. GO
  4. REVOKE VIEW ANY DEFINITION TO “NAME”

After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns. By default, the public role is granted this permission; don’t use DENY on the “public” role. Otherwise, no one will be able to list database objects.

Sysadmins Have SuperPowers

From SQL Server 2008 onwards, the Windows built-in administrators group is not part of the SQL Server SYSADMIN fixed server role. This is a great “secure by default” strategy and best practice, and I am glad this was done. During the installation, we are requested to explicitly mention/grant the logins with SYSADMIN privileges. Be very cautious in giving SYSADMIN privileges to logins. The script below lets you discover who the Sysadmins are on your server today. 

  1. SELECT
  2. prin2.name AS [Name],
  3. prin2.is_disabled,
  4. prin2.type_desc AS [Login Type],
  5. prin2.create_date
  6. FROM sys.server_principals prin
  7. INNER JOIN sys.server_role_members mem
  8. ON prin.principal_id = mem.role_principal_id
  9. INNER JOIN sys.server_principals prin2
  10. ON prin2.principal_id = mem.member_principal_id
  11. WHERE prin.type = ‘R’ and prin.name = N’sysadmin’

As the name suggests, Sysadmins have all the permissions to do anything inside SQL Server. If you have to give permission, then provision the logins in the SYSADMIN fixed server role directly; don’t use a common SQL Authentication with the SYSADMIN rights on the server – this helps while auditing. 

Giving SYSADMIN privileges to the lowest number of accounts is highly recommended to maximize security. And do not assign any elevated rights to a user if that user does not need it.

How to Protect from Sysadmins

As we can see, the Sysadmins are really powerful, and when using SYSADMIN privileges, note that no permission check is done. Use this cautiously and give access to only limited and, if possible, the least number of users.

Organizations need to protect their data, and I have seen Management asking if there is a way to protect data even from DBAs. The first question I ask is, “What rights have you given them?” The standard answer I get is, “They are DBAs, and hence we have given them the administrative rights of Sysadmins.” If you look at the examples and analogy I gave before, this is undoubtedly dangerous, right? So the immediate question is, what should be the rights of a DBA?

These are tough questions, but with every release of SQL Server, there have been enhancements that we don’t want to miss. Let me build the permissions for you, leading to a feature introduced in SQL Server 2014 and later versions.

Let us first start creating our super user SQLAuthority.

  1. USE [master]
  2. GO
  3. CREATE LOGIN SQLAuthority WITH PASSWORD=N’pass@word1’,
  4. DEFAULT_DATABASE=[master],
  5. CHECK_EXPIRATION=ON, CHECK_POLICY=ON
  6. GO

The idea now is NOT to give SYSADMIN rights but to give rights that enable the DBA to do their day-to-day activities without any problem. We are going to give this user CONTROL SERVER rights.

  1. GRANT CONTROL SERVER TO SQLAuthority;
  2. GO

Let us query DMV’s permissions to find out the effective permission SQLAuthority has after this GRANT. We will be using the following command:

  1. SELECT entity_name, permission_name 
  2. FROM sys.fn_my_permissions(NULL, NULL);

This gives us explicitly 34 specific server-level permissions. This still isn’t that restrictive.

If SQLAuthority tries to elevate himself to SYSADMIN rights using the following command:

  1. LTER SERVER ROLE sysadmin
  2. ADD MEMBER SQLAuthority

He will be presented with an error stating no permissions.

Msg 15151, Level 16, State 1, Line 11

Cannot alter the server role ‘sysadmin’ because it does not exist or you do not have permission.

But from the list of permissions granted, we can find that IMPERSONATE ANY LOGIN is available for this user. If that is the case, then SQLAuthority can impersonate ANY user in the system and gain access to the server. Typically, he would do it this way:

  1. EXECUTE AS LOGIN = ‘sa’;
  2. ALTER SERVER ROLE sysadmin
  3. ADD MEMBER SQLAuthority
  4. REVERT;

That is it; SQLAuthority has gained SYSADMIN rights into the system, a problem before SQL Server 2012. With SQL Server 2012, there was an exciting addition to DENY impersonation rights to a user, and even if they have CONTROL SERVER rights, SQL Server will honor the DENY permission. In our example, we can explicitly deny Impersonation to SQLAuthority:

  1. DENY IMPERSONATE ON LOGIN::sa TO [SQLAuthority]

This is great, and now SQLAuthority cannot impersonate the SA account. But do you see the problem here? We have yet to solve the problem. We have explicitly denied impersonation to this account. Still, other users inside the system can have SYSADMIN rights, and our CONTROL SERVER rights will allow SQLAuthority to impersonate one of them. It is humanly impossible to DENY such rights to every user with SYSADMIN rights for SQLAuthority.

SQL Server comes with permission called IMPERSONATE ANY LOGIN rights. This is powerful for the scenario we questioned. After giving the CONTROL SERVER rights, we can issue the following command in SQL Server:

  1. DENY IMPERSONATE ANY LOGIN TO SQLAuthority
  2. GO

After this command, the effective permission for SQLAuthority is the same as CONTROL SERVER, but he has been explicitly denied IMPERSONATION rights. This is so powerful because, as the management wants, we can give close to SYSADMIN rights to a user to perform their daily duties yet not provide them with the ability to elevate themselves easily.

If we run the command for effective permissions, we can see the list now has one fewer row at 33. The DMF for this is:

  1. SELECT entity_name, permission_name
  2. FROM sys.fn_my_permissions(NULL, NULL);

Also, the other permissions added in SQL Server are CONNECT ANY DATABASE and SELECT ALL USER SECURABLES.

  1. use [master]
  2. GO
  3. GRANT CONNECT ANY DATABASE TO [SQLAuthority]
  4. GO
  5. GRANT SELECT ALL USER SECURABLES TO [SQLAuthority]
  6. GO

Think of these rights when you want to give permission to an Auditor who visits your organization for compliance and wants access to the Server while they can only select and not update anything. These rights are powerful for those scenarios.

Idera provides robust solutions for SQL Server, Azure SQL Database, and Amazon RDS for SQL Server:

Idera SQL Compliance Manager is a robust solution for SQL Server, Azure SQL Database, and Amazon RDS for SQL Server. That protects your data by monitoring activity and changes with powerful alerting and tamper-proof audit tools.

Additional Resource:

To learn more about the balance between security practices and SQL database performance, please take a moment to read our 7-page whitepaper, “The Trade-off Between Database Security and Database Performance.”