Microsoft Sql Server Database Security Checklist
To stay on top of security you need to regularly review your server configurations. It's helpful to build a checklist to be used as part of an internal security audit review. Below is just such a checklist, specifically tailored to audit a SQL 2008 Server running on Windows Server 2008. Most of what's in this checklist will work for other versions of SQL and Windows, but certain paths and SQL queries may vary.
Refer to SQL Server Security Best Practices for more information on securing your SQL server.
SQL AUDIT CHECKLIST
This SQL audit checklist should be filled out on a regular interval (example: quarterly) for each of your SQL servers. SQL queries and Windows commands are included for convenience. You can also provide screen captures where appropriate. Each audit checklist should be saved with a custom file name, something like: servername-year-month-day-audit-checklist
SQL Server Audited:
Audited By:
Audit Date:
1. Default Database "Data" location.
2. Default Database "Log" location.
3. List all databases along with brief description.
SQL Query:
EXEC sp_databases
4. Backup schedule and type (daily/full) as well as backup server name.
5. Confirm "Login auditing" is configured for "Failed logins only" in the Security page of the SQL Server properties.
6. If this is a production SQL server, confirm there are no test databases (all test databases should be hosted on a test server).
7. Confirm "sample" SQL databases (AdventureWorks, etc.) do not exist on production SQL server.
8. List all "SQL Authentication" users along with purpose:
SQL Query:
SELECT * from sys.sql_logins
9. List all "Windows Authentication" users along with purpose.
SQL Query:
USE master
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE type_desc IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')
10. Confirm SQL Server Security is set to "Windows Authentication". If configured for both Windows and SQL authentication, explain why this is necessary.
11. If SQL Logins are used, confirm "Enforce password policy" and "Enforce password expiration" are enabled on each login. List exceptions below with reason.For "service" SQL logins that are not subject to password expiration (used for backend SQL authentication by applications), a routine password change procedure should be implemented.
12. Make sure SA account is disabled. If not disabled, make sure it is renamed and has a strong password. Change password at each audit. Or provide reason for keeping SA account.
13. List members of "Local Administrators" group along with purpose.
SQL Query:
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'
SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END
14. List db_owner database role members along with purpose.
SQL Query:
EXEC sp_helprolemember db_owner
15. List db_securityadmin database role members along with purpose.
SQL Query:
EXEC sp_helprolemember db_securityadmin
16. List db_accessadmin database role members along with purpose.
SQL Query:
EXEC sp_helprolemember db_accessadmin
17. List sysadmin server role members along with purpose.
SQL Query:
EXEC sp_helpsrvrolemember sysadmin
18. List serveradmin server role members along with purpose.
SQL Query:
EXEC sp_helpsrvrolemember serveradmin
19. List securityadmin server role members along with purpose.
SQL Query:
EXEC sp_helpsrvrolemember securityadmin
20. If custom application roles have been created, list those roles and membership, along with purpose.
SQL Query:
EXEC sp_helprotect application rolename
21. List permissions for the "C:\Program Files\Microsoft SQL Server" directory.
Windows Command:
icacls "C:\Program Files\Microsoft SQL Server"
22. List permissions for the "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn" directory.
Windows Command:
icacls "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn"
23. List permissions for the "C:\Program Files\Microsoft SQL Server\80\Tools\Binn" directory.
Windows Command:
icacls "C:\Program Files\Microsoft SQL Server\80\Tools\Binn"
24. List permissions for the "C:\Program Files\Microsoft SQL Server\80\Com" directory.
Windows Command:
icacls "C:\Program Files\Microsoft SQL Server\80\COM"
/ photo by Daniel Kulinski
Microsoft Sql Server Database Security Checklist
Source: https://ravingroo.com/337/dba-sql-audit-checklist-internal-security-review/