SQL Server is a repository of critical data, attracting potential threats from malicious individuals seeking financial gains. As a SQL Server DBA, it is essential to assess the security vulnerabilities of your SQL Server and perform an access audit to ensure the safety of your data
SQL Server Vulnerability Scan
I recommend you start with the built-in SQL Server vulnerability assessment tool to identify and review potential vulnerabilities. While not all recommendations need to be implemented, it is important to consider each and document them in your disaster recovery plans’ “Accepted Risk” section. You can create a custom report using the provided scripts and run it across your environments to expedite the process. A helpful resource is the Microsoft Books walkthrough, which offers guidance through the tool.
Here is an example of one of the scripts provided in the Vulnerability assessment, modified and used on a Central Management Server:
Along with the General checks of the above vulnerability assessment, I would recommend spending the time needed to go through each of the below sections in your environment:
Logins
Verify the necessity of each login’s access to the server and review their explicit and role-based permissions. Follow the script below to identify all logins:
-- Windows Logins
SELECT *
FROM sys.server_principals
WHERE type IN ('U','G', 'E', 'X')
AND name NOT LIKE '%NT%'
GO
-- SQL Logins
SELECT *
FROM sys.server_principals
WHERE type = 'S'
AND name NOT LIKE '%#%'
AND name NOT LIKE 'sa'
GO
Note: Windows integrated accounts are easier to manage and more secure. If possible, migrate users from SQL Authenticated accounts to Windows Authenticated accounts.
Explicitly granted permissions to Logins:
To find permissions explicitly granted to each login, execute the following scripts:
-- Server Logins with explicitly granted permissions
SELECT DISTINCT pr.principal_id
, pr.name
, pr.type_desc
, pe.state_desc
, pe.permission_name
, so.name
, so.type_desc
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.all_objects so
ON pe.major_id = so.object_id
WHERE pr.type IN ('U','G', 'E', 'X', 'S')
AND pr.name NOT LIKE '%NT%'
AND pr.name NOT LIKE '%##%'
AND permission_name not like '%Connect%'
-- Database Logins with explicitly granted permissions
EXEC sp_MSforeachdb 'USE ?
SELECT DISTINCT db_name() as [Database name]
, pr.principal_id
, pr.name
, pr.type_desc
, pr.authentication_type_desc
, pe.state_desc
, pe.permission_name
, so.name
, so.type_desc
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.all_objects so
ON pe.major_id = so.object_id
WHERE pr.type IN (''U'',''G'', ''E'', ''X'', ''S'')
AND pr.name NOT LIKE ''%NT%''
AND pr.name NOT LIKE ''%##%''
AND permission_name not like ''%Connect%'''
--Edited on 8/24/2023 to add WHAT object are referenced in the explicit permissions
Review the outputs of these scripts. If any unexpected permissions are returned, such as ‘CONTROL SERVER,’ address them promptly. Ideally, explicit permissions should be primarily in the ‘DENY’ realm, with roles being the preferred method of managing and assigning permissions.
Roles
Evaluate user-created roles within your SQL Server and determine if they can be replaced with built-in roles. Built-in roles, such as “db_datareader,” offer the advantage of fixed permissions that cannot be altered. Execute the following script to check for user-created roles:
--Server Principals
SELECT *
FROM sys.server_principals
WHERE type = 'R'
AND is_fixed_role = 0
AND name NOT IN ('public')
GO
--Database Principals
EXEC sp_MSforeachdb 'USE ?
SELECT db_name() as [Database name]
, name
, type
, create_date
FROM sys.database_principals
WHERE TYPE = ''R''
AND is_fixed_role = 0
AND name NOT IN (''public'');'
Note: It is normal to have a few non-fixed roles in system databases and feature-related databases like Reporting Services.
Role Memberships
To identify the roles each user is associated with, execute the following scripts:
--Find Server-Level Role Memberships SELECT DP1.name AS ServerRoleName , isnull(DP2.name, 'No members') AS ServerUserName , 'EXEC sp_dropsrvrolemember ['+DP2.name+'], ['+DP1.name+'];' AS [Role Removal Script - Be EXTREMELY careful] FROM sys.server_role_members AS DRM RIGHT OUTER JOIN sys.server_principals DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.server_principals DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' AND DP2.name NOT LIKE '%NT SER%' AND DP2.name <>'sa' ORDER BY DP1.name; -- Find Database-Level Role Memberships EXEC sp_MSforeachdb 'USE ? SELECT ''?'' AS [Database name] , DP1.name AS DatabaseRoleName , isnull(DP2.name, ''No members'') AS ServerUserName , ''USE ''+ ''?'' + ''; EXEC sp_droprolemember [''+DP2.name+''], [''+DP1.name+'']; '' AS [Role Removal Script - Be EXTREMELY careful] FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = ''R'' AND DP2.name NOT LIKE ''%##%'' AND DP2.name NOT LIKE ''%dbo%'' AND DP2.name NOT LIKE ''%dc_%'' AND DP2.name NOT LIKE ''%MS_%'' AND DP2.name NOT LIKE ''%Role%'' ORDER BY DP1.name; ' -- Edited on 8/31/2023 to edit correction script.
User-created and Public Role Permissions
There are a variety of ways to see what permissions a role has; however, this is my favorite since it puts it very nicely into a format that is easily read and understood, especially when comparing roles or looking to add or remove specific permissions. The following script, created by John Eisbrener, offers a convenient format for easy reading and comparison:
/********************************************************************
* *
* Author: John Eisbrener *
* Script Purpose: Script out Database Role Definition *
* Notes: Please report any bugs to http://www.dbaeyes.com/ *
* *
* Update: 2014-03-03 - Adjusted output to accommodate Role *
* definitions that are longer than 8000 chars *
* Update: 2013-09-03 - Added user output per Joe Spivey's comment *
* - Modified formatting for oddly named objects *
* - Included support for Grants on DMVs *
********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'
-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf
SELECT @roleDesc = @roleDesc +
CASE dp.state
WHEN 'D' THEN 'DENY '
WHEN 'G' THEN 'GRANT '
WHEN 'R' THEN 'REVOKE '
WHEN 'W' THEN 'GRANT '
END +
dp.permission_name + ' ' +
CASE dp.class
WHEN 0 THEN ''
WHEN 1 THEN --table or column subset on the table
CASE WHEN dp.major_id < 0 THEN
+ 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
ELSE
+ 'ON [' +
(SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
+ -- optionally concatenate column names
CASE WHEN MAX(dp.minor_id) > 0
THEN '] ([' + REPLACE(
(SELECT name + '], ['
FROM sys.columns
WHERE object_id = dp.major_id
AND column_id IN (SELECT minor_id
FROM sys.database_permissions
WHERE major_id = dp.major_id
AND USER_NAME(grantee_principal_id) IN (@roleName)
)
FOR XML PATH('')
) --replace final square bracket pair
+ '])', ', []', '')
ELSE ']'
END + ' '
END
WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
END COLLATE SQL_Latin1_General_CP1_CI_AS
+ 'TO [' + @roleName + ']' +
CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM sys.database_permissions dp
WHERE USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class
SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf
-- Display users within Role. Code stubbed by Joe Spivey
SELECT @roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM sys.database_principals users
INNER JOIN sys.database_role_members link
ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles
ON roles.principal_id = link.role_principal_id
WHERE roles.name = @roleName
-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000
WHILE LEN(@roleDesc) > 8000
BEGIN
-- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))
PRINT LEFT(@roleDesc, @printCur)
SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END
-- Output new permissions
PRINT @roleDesc + 'GO'
The output looks like this:
Check for Public Role Permissions
To ensure that no permissions have been added to the PUBLIC role, execute the following scripts:
-- Run this for Server-Level changes
SELECT permission_name AS [Permission],
CASE
WHEN perms.class = 101 THEN 'LOGIN'
ELSE class_desc
END AS [Permission Class],
CASE
WHEN perms.class = 100 THEN @@SERVERNAME -- SERVER
WHEN perms.class = 101 THEN prins.NAME -- SERVER_PRINCIPAL
WHEN perms.class = 105 THEN ep.NAME -- ENDPOINT
END AS [Object]
FROM sys.server_permissions AS perms
LEFT JOIN sys.endpoints AS ep
ON ep.endpoint_id = perms.major_id
LEFT JOIN sys.server_principals AS prins
ON prins.principal_id = perms.major_id
WHERE grantee_principal_id = Suser_id('public')
AND NOT (
(permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL LOCAL MACHINE') OR
(permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL Named Pipes') OR
(permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL Default TCP') OR
(permission_name = 'CONNECT' AND perms.class = 105 AND ep.NAME = 'TSQL Default VIA') OR
(permission_name = 'VIEW ANY DATABASE' AND perms.class = 100)
)
-- Run this for Database-Level changes
DECLARE @DB_Name VARCHAR(256),
@SQL NVARCHAR(4000);
DECLARE public_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.databases
WHERE database_id > 4 AND state = 0
AND [name] not in (
SELECT adc.database_name
FROM sys.availability_replicas AS ar
JOIN sys.availability_databases_cluster adc ON adc.group_id = ar.group_id
WHERE ar.secondary_role_allow_connections = 0
AND ar.replica_server_name = @@SERVERNAME
AND sys.fn_hadr_is_primary_replica(adc.database_name) = 0
)
OPEN public_cursor
FETCH NEXT FROM public_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ' + QUOTENAME(@DB_Name) + '; ' +
'SELECT
db_name() as [DatabaseName]
, ''The [public] role has been granted the permission ['' + perms.permission_name + ''] on the object [''
+ CASE
WHEN perms.class = 0 THEN db_name()
WHEN perms.class = 3 THEN schema_name(major_id)
WHEN perms.class = 4 THEN printarget.NAME
WHEN perms.class = 5 THEN asm.NAME
WHEN perms.class = 6 THEN type_name(major_id)
WHEN perms.class = 10 THEN xmlsc.NAME
WHEN perms.class = 15 THEN msgt.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 16 THEN svcc.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 17 THEN svcs.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 18 THEN rsb.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 19 THEN rts.NAME COLLATE DATABASE_DEFAULT
WHEN perms.class = 23 THEN ftc.NAME
WHEN perms.class = 24 THEN sym.NAME
WHEN perms.class = 25 THEN crt.NAME
WHEN perms.class = 26 THEN asym.NAME
END + ''].''
, ''Because these permissions are available to anyone who can connect to your instance, they should be revoked and granted to users, groups, or roles other than public.''
FROM sys.database_permissions AS perms
LEFT JOIN sys.database_principals AS prin ON perms.grantee_principal_id = prin.principal_id
LEFT JOIN sys.assemblies AS asm ON perms.major_id = asm.assembly_id
LEFT JOIN sys.xml_schema_collections AS xmlsc ON perms.major_id = xmlsc.xml_collection_id
LEFT JOIN sys.service_message_types AS msgt ON perms.major_id = msgt.message_type_id
LEFT JOIN sys.service_contracts AS svcc ON perms.major_id = svcc.service_contract_id
LEFT JOIN sys.services AS svcs ON perms.major_id = svcs.service_id
LEFT JOIN sys.remote_service_bindings AS rsb ON perms.major_id = rsb.remote_service_binding_id
LEFT JOIN sys.routes AS rts ON perms.major_id = rts.route_id
LEFT JOIN sys.database_principals AS printarget ON perms.major_id = printarget.principal_id
LEFT JOIN sys.symmetric_keys AS sym ON perms.major_id = sym.symmetric_key_id
LEFT JOIN sys.asymmetric_keys AS asym ON perms.major_id = asym.asymmetric_key_id
LEFT JOIN sys.certificates AS crt ON perms.major_id = crt.certificate_id
LEFT JOIN sys.fulltext_catalogs AS ftc ON perms.major_id = ftc.fulltext_catalog_id
WHERE perms.grantee_principal_id = DATABASE_PRINCIPAL_ID(''public'')
AND class != 1 -- Object or Columns (class = 1) are handled by VA1054 and have different remediation syntax
AND [state] IN (''G'',''W'')
AND NOT (
perms.class = 0
AND prin.NAME = ''public''
AND perms.major_id = 0
AND perms.minor_id = 0
AND permission_name IN (
''VIEW ANY COLUMN ENCRYPTION KEY DEFINITION''
,''VIEW ANY COLUMN MASTER KEY DEFINITION''
)
)'
EXEC sp_executesql @SQL
/* iterate the cursor to the next database name */
FETCH NEXT FROM public_cursor INTO @DB_Name
END
CLOSE public_cursor;
DEALLOCATE public_cursor;
It is recommended to use custom roles rather than modifying the Public role, which is provided by Microsoft. If any of the above scripts show additional permissions in the Public role, it should be reviewed and remediated immediately.
Domain Groups
In most cases, SQL environments involve domain groups, offering both advantages and potential risks. Domain groups simplify permission assignment, allowing users to be added or removed through Active Directory. However, it is crucial to verify the members of domain groups with access to SQL Server. Use the following command prompt script to determine the members of a domain group:
If you continue to use SQL Authenticated accounts, it is essential to ensure that the account passwords are not easily guessable. I adapted the script found here to check for commonly used passwords among the top 100,000 PWNED passwords available on the internet (– source: https://www.ncsc.gov.uk/static-assets/documents/PwnedPasswordsTop100k.txt). This script helps identify weak passwords and emphasizes the importance of enforcing password policies for SQL Authenticated accounts.
Security is of utmost importance, and it is vital to invest time in reviewing access, adjusting permissions, and safeguarding your data. This post and these scripts aim to facilitate the process of vulnerability assessment and access audit, making it more manageable and effective.