SQL Server Metadata Succinctly®
CHAPTER 7
Security
The data held by an organization is an important business asset, and it is the job of a SQL administrator to ensure this data is secure from hackers and other unauthorized users. In this chapter, we will focus on using the views and features to determine any areas where the SQL databases might be at risk.
Attack surface
The attack surface represents all the areas of a system that an unauthorized user can use to gain access to the system. As an administrator of a SQL server, it is necessary to defend all potential areas, since the hacker only needs to use one to perform nefarious deeds.
Demo databases
SQL Server provides two sample databases (AdventureWorks and WorldWideImporters). In this script, we will check to see if these sample databases are still installed on a server. It is not a good idea to leave unmonitored sample databases in a production server environment.
Note: Prior SQL versions had sample databases called Northwind and Pubs.
Code Listing 54: Script to check for sample databases
-------------------------------------------------------- -- Script: Sample_Databases.sql -- Are demo/sample databases included on the server? --------------------------------------------------------- SELECT name as [DatabaseName], 'This is a demo database, consider removing it' as Msg FROM sys.databases WHERE [name] in ('AdventureWorks', 'WorldWideImporters', 'Pubs', 'Northwind' ) |
A database administrator (DBA) should always be aware of what databases are installed on a production server. The following script can be used to identify new databases added within the past two weeks. A development database that gets installed on a production probably lacks security and could give an attacker a way to get to the production server.
Code Listing 55: Recently installed databases
--------------------------------------------------------- -- Script: Recently_Installed.sql -- Any databases recently installed? --------------------------------------------------------- SELECT name as [DatabaseName],create_date FROM sys.databases WHERE create_date >= dateadd(WEEK,-2,getDate()) |
If the list of databases is small, you could also write a script to identify any databases outside of the expected database list.
Guest user
Every SQL database has a guest user, and although it has minimum privileges, it still represents an attack surface. You cannot remove the guest user, but you can prevent it from being used to access a database. The following script identifies whether the guest account can connect to the current database.
Code Listing 56: Guest Login accounts
--------------------------------------------- -- Script: Guest_Logins.sql -- Suggest removing GUEST login if enabled --------------------------------------------- SELECT DISTINCT 'Consider disabling the GUEST account in '+db_Name() as Msg FROM sys.database_principals dp INNER JOIN sys.server_permissions sp ON dp.principal_id = sp.grantee_principal_id WHERE name = 'guest' AND permission_name = 'CONNECT' |
If you’ve identified that the guest user is still in the database, you can run the following SQL script to prevent the account from accessing the database.
REVOKE CONNECT FROM guest
SQL logins
In SQL Server, a login is an ID that allows you to connect to the server itself. This is separate from a user (which is an ID within a database). The sys.syslogins view contains all the logins on the server. Table 32 lists the key columns in the view.
Table 32: SQL logins view
Field | Description |
|---|---|
SID | Security identifier. |
createdate | Date login was added to the system. |
updatedate | Date login was last updated. |
name | Login name of the user. |
dbname | Name of the default database when user connects. |
hasaccess | Does account have access to the server? |
isntname | 1 = Windows user or group, 0 = SQL Server login |
isntgroup | 1 = A Windows group |
isntuser | 1 = A Windows user |
sysadmin | 1 = Member of sys admin role (can perform any server activity) |
securityadmin | 1 = Member of the security admin role (used to manage logins and properties) |
serveradmin | 1 = Member of server admin role (can change server-wide configuration and shut down the server) |
setupadmin | 1 = Member of setup admin role (add and remove remote servers) |
processadmin | 1 = Member of process admin role (can end processes running SQL Server) |
diskadmin | 1 = Member of disk admin role (manages disk files) |
dbcreator | 1 = Member of dbcreator (create, alter, drop, restore databases) |
bulkadmin | 1 = Member of bulk admin (can run the BULK INSERT statement) |
loginname | Login name for this account. |
Note: All SQL logins belong to the public role as well.
Windows users (isntuser) and Windows group (isntgroup) have their credentials managed by Active Directory. However, SQL logins (isntname=0) are managed by SQL. The sql_logins view is a list of just the SQL logins from the list of login accounts. We can use some SQL code to perform security checks on these logins. The sql_logins view adds fields for policy check, expiration check, and the password hash. Although we can’t extract the password from the encrypted hash, we can make use of it to do some password checking.
Password policy
In general, the password policy and expiration date should be checked on all SQL logins. The password policy SQL Server uses is generally the same policy that Windows uses, including:
- Cannot contain username.
- Is at least eight characters long.
- Contains at least three of (upper case, lower case, digits, and special characters).
Code Listing 57 checks that the password policy and password expiration policy are set for SQL logins.
Code Listing 57: Check password and expiration
-------------------------------------------------- -- Script: CheckPasswordPolicy.sql -- Identify accounts not using password policies -------------------------------------------------- SELECT name, CASE WHEN is_policy_checked=0 and is_expiration_checked=0 THEN 'Password policy and expiration dates are not checked' WHEN is_policy_checked=0 and is_expiration_checked=1 THEN 'Password policy is not checked' WHEN is_policy_checked=1 and is_expiration_checked=0 THEN 'Password expiration is not checked' END AS Msg FROM sys.sql_logins WHERE is_disabled=0 AND (is_policy_checked=0 or is_expiration_checked=0) ORDER BY name |
Duplicate passwords
In some environments, users might have multiple accounts, and use the same password for each login. This can create a situation where if an attacker can compromise a single account, they could gain access to multiple logins, potentially ones with more permissions and rights. Code Listing 58 provides a simple script to identify accounts that have duplicate passwords.
Code Listing 58: Duplicate password check
--------------------------------------------- -- Script: Duplicated_passwords.sql -- Accounts using the same password --------------------------------------------- SELECT [name] as Account, 'have a duplicate passwords' as Msg FROM sys.sql_logins WHERE password_hash in (SELECT password_hash FROM sys.sql_logins GROUP BY[password_hash] HAVING count(*)> 1 ) |
In this code, we are grouping by the password hash field simply to check for any time a hash occurs more than once. We won’t know the actual password, but we will know that multiple accounts are using the same one.
Blank passwords
If a password is blank, or the password is the same as the account name, it can easily be hacked. This simple script allows you to identify these risky accounts.
Code Listing 59: Blank passwords
--------------------------------------------- -- Script: Blank_Passwords.sql -- Blank or passwords same as account name --------------------------------------------- SELECT name as [AccoutName], CASE WHEN PWDCOMPARE('', password_hash) = 1 THEN 'This account has an empty password' WHEN PWDCOMPARE(name, password_hash)= 1 THEN 'This accounts password is the same as the name' END as Msg FROM sys.sql_logins WHERE(PWDCOMPARE('', password_hash) = 1 or PWDCOMPARE(name, password_hash) = 1) |
Note that if password policy is enforced, this condition should never occur.
Common passwords
While you cannot expose account passwords using SQL Server, you can check a password against a text string. By creating a table of common passwords, you can compare accounts against this list and identify those accounts using simple passwords.
Code Listing 60: Common passwords
--------------------------------------------- -- Script: Common_Passwords.sql -- Commonly used, simple passwords --------------------------------------------- CREATE TABLE #passwords ( PasswordString varchar(32) ) INSERT INTO #passwords VALUES ('password'),('123456'),('qwerty'), ('Admin'), ('password1'), ('abc123') SELECT name as [Account], p.PasswordString+' is not a secure password' as Msg FROM sys.sql_logins l JOIN #passwords p on (1=1) WHERE PWDCOMPARE(p.passwordString,l.Password_hash)=1 DROP TABLE #passwords |
Note that this script will show the account and the bad password. You can change the msg string if you want to find the account, but not expose the actual password used.
Tip: There are many sites that have common password lists available for download, such as this one.
If password policy is enforced, your common password list should be adjusted to meet the password complexity rules (eight digits, upper, lower, and digit), but still common. For example, Abcd1234 and Password1 meet the complexity rules, but are simple passwords.
Recent accounts
If a hacker gains access to your server, one thing they might do is create their own account in case the hacked account gets detected. This script reports any recently added or modified accounts, which possibly could be a sign of suspicious activity.
Code Listing 61: Recent accounts adds/updates
------------------------------------------------------ -- Script: Recent_Accounts.sql -- Look for any recent accounts added or modified ------------------------------------------------------ SELECT name AS 'Account Name', 'Login created recently' as Msg FROM master.sys.server_principals WHERE type LIKE 's' and datediff(d,create_date,getdate())< 14 UNION SELECT name, 'Login modified recently' as Msg FROM master.sys.server_principals WHERE type LIKE 's' and datediff(d,modify_date,getdate())< 14 |
Auditing logins
The SQL Server Security menu at the server level allows you to specify authentication (either Windows or SQL and Windows) and the login auditing to use.
Figure 6: SQL Server security
The Windows authentication login is considered more secure because of its reliance on Active Directory and is recommended for that reason. In addition, your server should ideally audit all login attempts, but at a minimum, it should log failed logins. The following listing checks for the server authentication mode and the login level. This listing uses the SERVERPROPERTY function to determine the authentication mode.
Code Listing 62: Check authentication mode
------------------------------------------------------ -- Script: AuthenticationMode.sql -- Check authentication mode ------------------------------------------------------ SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 0 THEN 'SQL and Windows Authentication' ELSE 'Windows Authentication only ' END as AuthMode |
This listing uses the system stored procedure to read the audit level from the registry and returns both the audit level and a description.
Code Listing 63: Check login auditing level
--------------------------------------------- -- Script: Audit_Level.sql -- Check on audit level of server --------------------------------------------- DECLARE @auditLevel INT; EXEC MASTER.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 'Software\Microsoft\MSSQLServer\MSSQLServer', 'AuditLevel', @AuditLevel OUTPUT; SELECT @auditLevel as AuditLevel, CASE @auditLevel WHEN 1 THEN 'No login auditing' WHEN 2 THEN 'Failed Logins only' WHEN 3 THEN 'Successful logins only' ELSE 'All Logins' END as AuditRules |
SA account
The system administrator or SQL administrator (SA) account is a well-known and powerful account on a SQL server. If it is enabled, it must be protected with a strong password. The following listing checks the SA password against empty or sa, or any other weak passwords.
Code Listing 64: Check for weak SA password
-------------------------------------------------------- -- Script: Weak_SA_Password.sql -- Report weak SA passwords --------------------------------------------------------- CREATE TABLE #passwords ( PasswordString varchar(32) ) INSERT INTO #passwords VALUES ('password'),('123456'),('Qwerty'), ('Admin'), ('Password1'), ('abc123'),(''),('sa') SELECT 'ERROR: '+p.PasswordString+' is not a secure password on the sa account' as Msg FROM sys.sql_logins l JOIN #passwords p on (1=1) WHERE PWDCOMPARE(p.passwordString,l.Password_hash)=1 and [name]='sa' DROP TABLE #passwords |
You should add your own common password lists, particularly if you know passwords commonly used with your organization. This is a back door you do not want to leave unlocked.
Users
The sys.database_principals view shows the user with the current database. You can use this table to identify SQL logins and Windows logins that have access to the current database. Code Listing 65 lists various users in the database, their type, and what database roles they have.
Code Listing 65: Database users
--------------------------------------------------------- -- Script: Database_Users.sql -- Users in the current database --------------------------------------------------------- SELECT p.[name],p.type_desc,p.create_date,p.modify_date,rl.name as RoleName FROM sys.database_principals p LEFT JOIN sys.database_role_members rm ON rm.member_principal_id=p.principal_id LEFT JOIN sys.database_principals rl ON rl.principal_id=rm.role_principal_id WHERE p.type in ('S','U') |
The Guest, sys, and INFORMATION_SCHEMA principals will appear in each database, although they are not accounts that can log in, and typically have limited rights. You should monitor any newly added users, and particularly those with write access to the database.
Public role in database
Every user in the database has access to the public role. If you grant the public role UPDATE, DELETE, and INSERT rights, or give the role rights to all tables and system stored procedures, you could be putting your server at risk of any user who manages to connect to it. Code Listing 66 lists some items to which the public role most likely should not have access.
Code Listing 66: Check public role
-------------------------------------------------------- -- Script: What_CanPublicRole_do.sql -- Can the public role see all items -------------------------------------------------------- SELECT p.permission_name,OBJECT_NAME(major_id) as AllowedView FROM sys.database_permissions p JOIN sys.database_principals pr on pr.principal_id=p.grantee_principal_id WHERE pr.name = 'Public' and state='G' and class<>0 AND object_name(major_id) like 'All[_]%' and p.permission_name='SELECT' UNION SELECT p.permission_name,OBJECT_NAME(major_id) as AllowedView FROM sys.database_permissions p JOIN sys.database_principals pr on pr.principal_id=p.grantee_principal_id WHERE pr.name = 'Public' and state='G' and class<>0 AND object_name(major_id) like 'SP[_]%' and p.permission_name='EXECUTE' UNION SELECT p.permission_name,'' FROM sys.database_permissions p JOIN sys.database_principals pr on pr.principal_id=p.grantee_principal_id WHERE pr.name = 'Public' and state='G' and class<>0 AND p.permission_name in ('UPDATE','DELETE','INSERT') ORDER BY p.permission_name,AllowedView |
You can tweak this query to check the public role’s access, but in general, the public role should be very limited, particularly on a production server.
What is on my server?
Ideally, SQL Server and its supporting tools are the only major applications that are running on your SQL Server hardware. However, it is possible that other programs are installed, and these could represent a security risk to the server.
Unneeded applications
While the Microsoft Office suite is a common product, I would not expect it to be available on my SQL server box. Code Listing 67 uses the new enumerate_filesystem function to look for Microsoft Office or Visual Studio. You can supplement the code to include your own list of applications that should not be installed on a production server.
Code Listing 67: Check for unneeded applications
--------------------------------------------------------- -- Script: Check_for_Applications.sql -- See if any unexpected applications are installed --------------------------------------------------------- IF SERVERPROPERTY('ProductMajorVersion') >= '14' BEGIN SELECT file_or_directory_name,creation_time from sys.dm_os_enumerate_filesystem('C:\Program Files\','*.*') x WHERE x.is_directory=1 AND (file_or_directory_name LIKE 'Microsoft Office%' or file_or_directory_name LIKE 'Microsoft Visual Studio%') END ELSE SELECT 'Requires SQL 2017 or higher' as Msg |
Newly added files
Code Listing 68 will check the Windows folders for any new .dll or .exe files added to the server.
Code Listing 68: Check for new .dll or .exe files
--------------------------------------------------------- -- Script: Check_ForNewFiles.sql -- Check if any DLL or EXE were added to server recently --------------------------------------------------------- DECLARE @numDays INT = -14 IF SERVERPROPERTY('ProductMajorVersion') >= '14' BEGIN SELECT file_or_directory_name,creation_time from sys.dm_os_enumerate_filesystem('c:\windows\system32\','*.dll') WHERE creation_time>=dateadd(DAY,@numDays,getDate()) UNION SELECT file_or_directory_name,creation_time from sys.dm_os_enumerate_filesystem('c:\windows\system32\','*.exe') WHERE creation_time>=dateadd(DAY,@numDays,getDate()) ORDER BY creation_time DESC END ELSE SELECT 'Requires SQL 2017 or higher' as Msg |
Summary
By reducing the potential surface area that a hacker can attack, you can improve the security of the company’s data. You should also monitor user accounts—a powerful account with a simple password can be dangerous in the hands of an attacker. And finally, there are certain stored procedures that are very powerful, but should be carefully protected. Imagine the damage a hacker could do via xp_cmdshell and access to the operating system the server is running on.
These scripts should give you a starting point to monitor the security of your server and to take steps to keep the data protected from prying eyes and keyboards.
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.