This month I am providing a 12-point checklist of scripts to review your 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!
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.
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?
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.
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.