A Simple SQL Server Security Checklist

This month I am providing a 12-point checklist of scripts to check your SQL server’s current security posture. Use these scripts to get an idea of where you are, so that you can make plans for the places you may want to go. The paradigms of least privilege and defense in depth are important to remember as you go through this exercise!

Download our SQL Server Security Check Community Tool!

April 2024 Update –> This month we released our first SQL Server community tool – our sp_checksecurity SQL Server Security check procedure. Read more about it on the page describing the tool. Definitely download this checklist, but also grab that script and perform a security assessment on your SQL Servers checking over 30 different checks with tips for each.

Please remember that you should always follow company policies and procedures before making any changes. We also recommend working with experts to make sure that changes go smoothly. Feel free to read about our SQL Server consulting services here if you want additional help, a SQL Server health check, or some 1:1 mentoring time with our team running through the why for each.

1 – Who has access to the server?

SELECT * FROM sys.server_principals
ORDER BY TYPE

Take inventory of what logins exist by category. Make sure to take a look at the categories that one may gloss over such as server roles and certificates. Knowing of non-standard logins will help ensure they are not overlooked in any security changes that need to be made.

2 – What roles are logins members of?

SELECT A.name AS Role, B.name AS Principal
FROM master.sys.server_role_members AB
INNER JOIN
master.sys.server_principals A 
ON A.principal_id = AB.role_principal_id AND A.type = 'R'
INNER JOIN
master.sys.server_principals B 
ON B.principal_id = AB.member_principal_id

Take note of which logins are a part of which groups, with special care to look at sysadmins and securityadmins. Remember the principle of least privilege, only grant the permission necessary to complete the job function.

3 – Have any non-role-based permissions been changed?

SELECT  A.name
,       A.type_desc
,       B.permission_name
,       B.state_desc
,       B.class_desc
FROM    sys.server_principals A
LEFT JOIN
       sys.server_permissions B
ON      B.grantee_principal_id = A.principal_id
ORDER BY A.name

This is excruciating work, especially if you have many logins. However, taking a look at what permissions have been granted to individual users outside of roles, checking to make sure the default roles do not have any unexpected exceptions, and verifying everything that is supposed to be default is the way you expect to be is important in establishing a baseline for security going forward.

4 – Are there any database users?

EXEC sp_MSforeachdb @command1 ='
      SELECT  ''?''
      ,   DP1.name AS DatabaseRoleName
      ,   DP2.name AS DatabaseUserName
      FROM sys.database_role_members AS DRM
      RIGHT OUTER JOIN sys.database_principals AS DP1
          ON DRM.role_principal_id = DP1.principal_id  
      LEFT OUTER JOIN sys.database_principals AS DP2  
          ON DRM.member_principal_id = DP2.principal_id  
      WHERE 
          DP1.type = ''R'' AND 
          DP2.name IS NOT Null
      ORDER BY DP1.name;'

We know about sp_MSforeachdb, feel free to replace the code here with your preferred tool! There are some expected users in each database, you are looking to take stock of any exceptions to this. Similarly to the Server permissions, you can run a check on each database for changed permissions.

5 – Have any database permissions been changed?

EXEC sp_MSforeachdb @command1 ='
SELECT  A.name
,       A.type_desc
,       B.permission_name
,       B.state_desc
,       B.class_desc
FROM    sys.database_principals A
LEFT JOIN
sys.database_permissions B
ON B.grantee_principal_id = A.principal_id
ORDER BY A.name''

I would recommend sending the results to a file and reviewing this in excel, this is not a fast process. However, these changes could be lurking especially if you have inherited an environment.

6 – How is SQL Server being secured right now?

There are a few things to check here. First, are there any encrypted databases?

SELECT A.name AS 'Database Name'
,	B.name AS 'Cert Name'
,	C.encryptor_type AS 'Type'
,	CASE
    			WHEN C.encryption_state = 3 THEN 'Encrypted'
    			WHEN C.encryption_state = 2 THEN 'In Progress'
    			ELSE 'Not Encrypted'
END AS State
,	C.encryption_state
,	C.percent_complete
, 	C.key_algorithm
, 	C.key_length
, 	C.* FROM sys.dm_database_encryption_keys C
RIGHT JOIN sys.databases A ON A.database_id = C.database_id
LEFT JOIN sys.certificates B ON C.encryptor_thumbprint=B.thumbprint

Second, is traffic being encrypted?

SELECT * FROM sys.dm_exec_connections

Third, are backups being encrypted?

SELECT 
 	A.database_name
,	key_algorithm
,	encryptor_thumbprint
,	encryptor_type
,    	is_encrypted
,	type
,	AB.physical_device_name
	FROM msdb.dbo.backupset A
INNER JOIN 
msdb.dbo.backupmediaset C ON A.media_set_id = C.media_set_id
INNER JOIN 
msdb.dbo.backupmediafamily AB on AB.media_set_id=A.media_set_id
ORDER BY A.backup_start_date  DESC

Fourth, are there any row-level security policies set?

SELECT * FROM sys.security_policies

Fifth, are there any audits running?

SELECT * from sys.server_audits

Dive into each individually, find out if the keys and certificates are backed up in a safe location, if the passwords to unlock them are saved, and if the security policies are comprehensive enough. Use this data for your next security meeting.

7 – Are there any linked servers, HADR replicas, or external data providers?

--linked servers
EXEC sp_linkedservers
-- HADR Check
SELECT * FROM sys.dm_hadr_database_replica_states
--Cluster Check
SELECT * FROM sys.dm_hadr_cluster_members
-- Mirroring Check
SELECT A.name as [Database Name], * FROM sys.database_mirroring B
RIGHT JOIN sys.databases A on A.database_id = b.Database_ID
-- Replication check 
SELECT name, is_published, is_subscribed, is_merge_published, is_distributor
FROM sys.databases
-- External Data sources Check
SELECT * from sys.external_data_sources

Each of these increases the surface area of the SQL server, you will need to dive into each HADR node and linked server to get a full view.

8 – Are there any SQL configurations to consider?

Select name
,	case
	when value_in_use = 0 then 'DISABLED'
	else 'ENABLED'
	end as State
,	description 
from sys.configurations
where name in ('xp_cmdshell')

There are many configurations you could check, but the primary to be sure isn’t enabled without your knowledge and considerations is xp_cmdshell. This configuration allows for extended stored procedures, which reduces the barrier between the SQL user and the underlying host. Unless you have a very good reason, this should be disabled!

9 – Is SQL Server patched?

SELECT @@Version

Use https://sqlserverbuilds.blogspot.com/ to identify the latest or N-1 patch for your SQL version. Is your SQL version at end of life? SQL Server 2012 just passed extended support, and others are exiting support soon. Use this chart at your next upgrade meeting: https://docs.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16#lifecycle-dates , it may be time to consider an upgrade!

P.S. Make sure your operating system is patched regularly as well!

10 – Are queries and stored procedures parameterized to avoid injection attacks, and error handling to reduce information gathering efforts?

Build a table:

CREATE TABLE Patient (
    LoginID tinyint,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
	SSN nvarchar(11),
	CardNumber nvarchar(19)
);

INSERT INTO Patient (loginid, lastname, firstname, address, city, ssn, cardnumber)
VALUES	(1,'Arbiter', 'Agatha', '111 Apple Ave.', 'Atlanta', '111-11-1111', '1111-1111-1111-1111'),
		(2, 'Bob', 'Billy', '222 Bayshore Blvd.', 'Boice', '222-22-2222', '2222-2222-2222-2222'),
		(3, 'Choice', 'Charley', '333 Castaway Ct.', 'Chesterfield', '333-33-3333', '3333-3333-3333-3333'),
		(4, 'Dangerfield', 'David', '4444 Denvue Drive', 'Denver', '444-44-4444', '4444-4444-4444-4444'),
		(5, 'Engleton', 'Edbert', '5555 Esquire Rd. E', 'Easton', '555-55-5555', '5555-5555-5555-5555')

The expected input is when a user logs in with their correct id, and sees their own data:

SELECT * 
FROM Patient 
WHERE loginid = '1' -- user input

But what happens if someone inputs something unexpected and malicious?

SELECT * 
FROM Patient
where loginid = '' or 1=1  -- user input

However, with a parameterized query, the good behavior is protected, but the malicious behavior is not:

declare @Loginid tinyint
	set @Loginid = '' or 1=1-- --user input

SELECT * 
FROM Patient
where loginid = @Loginid --parameterized input

	set @Loginid = ‘3’ -- --user input

SELECT * 
FROM Patient
where loginid = @Loginid --parameterized input

Verbose errors are when SQL server returns errors that are then returned to the user. A malicious user can use this information to learn more about the environment and then further adapt their strategy to attack. Adding error handling helps protect the company from this:

declare @Loginid tinyint
	BEGIN TRY
		set @Loginid = ''' or 1=1--' --user input
	END TRY
	BEGIN CATCH
		Print 'Please use only your user ID'
	END CATCH

SELECT * 
FROM Patient
where loginid = @Loginid --parameterized input

(This is for demonstrative purposes, more robust solutions should be used!)

11 – Is SQL isolated from the internet?

Do your connection strings have plaintext queries, login information, server names or ports? There are many sites dedicated to helping you secure your SQL connection strings. If you can see the QUERY, and/ or LOGIN information in your connection string, so can malicious actors.

12 – Is your system prepared for a breach or ransomware?

Zero trust assumes that breach will eventually occur. To be secure one much assume that eventually someone can find a way in, so what do you do next when that happens? Having a backup strategy for data loss or ransomware is important, as is testing your restoration solution. Offsite backups and disaster recovery sites should be considered as part of every disaster recovery plan as well. Fortunately, the ease at which a new site can be spun up in the cloud means you have a lower barrier to entry should a disaster occur.

Conclusion

This checklist should give you and your team a general overview of the current security posture of your SQL server(s). There is always more to dig into, and we always recommend working with an expert to ensure things are set appropriately, and that nothing is missed.