In this post, I continue my checklist series that will eventually become a new book. The focus of this checklist is SQL Server security, a very important topic that is often neglected because many DBAs are spending most of their time striving just to keep their SQL Server instances up and running.
When I was putting together this checklist, I found it difficult to balance the amount of detail to include in the list. For example, if I covered virtually every security related issue, the checklist would become very long. Because of this, I have tried to focus on the bigger issues, but at the same time reminding you of the details you need to keep in mind as a DBA when evaluating the security of your SQL Server instances.
Another issue I ran into when creating the list was how to format it so that it works well for most people. I found this next to impossible to do, but because the checklist is in a spreadsheet format, you should be able to add rows and columns as necessary to complete the checklist for your unique environment. Feel free to customize the checklist however you want so that it better meets your needs.
| The Basics | Your Response |
| Server Authentication Mode: | |
| Login Auditing Mode: | |
| Server Proxy Account Enabled: | |
| Common Criteria Compliance Enabled: | |
| C2 Audit Tracing Enabled: | |
| Cross Database Ownership Chaining Enabled: | |
| Are Only Required Network Libraries Installed: | |
| Are Only Required Network Protocols Installed: | |
| Protocol Encryption Enabled: | |
| Have Named Instances Been Assigned Static TCP Ports: | |
| Have All Required SQL Server Security Patches Been Installed: | |
| Are Test Databases, Such as AdventureWorks, Installed on the Production Instance: | |
| Has the Sample Code That Comes With SQL Server Been Installed on the Production Instance: | |
| Are There Any Unused Databases on the Production Instance: | |
| Have Only the Required SQL Server Services Been Installed on The Instance: | |
| Have All Unnecessary Installed SQL Server Services Been Disabled: | |
| Is this SQL Server Instance Accessible Directly From the Internet: | |
| OS Security | |
| Have All Unnecessary OS Services on the Instance Been Turned Off: | |
| Is the OS Firewall Turned On & Properly Configured to Only Allow Necessary Access: | |
| Is Anti-Virus/Spyware Turned On: | |
| Is the Web Browser Used Where the Instance is Running Used to Browse the Web: | |
| Is OS Password Complexity, Expiration & Enforcement Enabled: | |
| Have All Required OS Patches Been Installed: | |
| Does the Physical or Virtual Server Running the SQL Server Instance Have Any Shares: | |
| Surface Area Configuration | |
| In SQL Server 2005, Has the Surface Area Configuration Tool Been Used to Review Existing Settings: | |
| In SQL Server 2008, Has the Surface Area Configuration Facet Been Used to Review Existing Settings: | |
| Is xp_cmdshell Enabled? If so, Is It Required to be On: | |
| If xp_cmdshell is Enabled, Who Has Permission to Use It: | |
| Has the CLR Been Enabled? If So, Is It Being Used: | |
| Service Accounts | |
| Are Different Service Accounts Used for Each Separate SQL Server Instance: | |
| Does Each SQL Server Service (Within a Single Instance) Use a Different Service Account: | |
| List All Service Accounts Used for SQL Server Services: | |
| Does Any Service Account Have Local Admin or Domain Admin Rights: | |
| List Everyone Who Knows Service Account Passwords: | |
| Are Service Accounts Configured for Minimum Required Rights & Permissions: | |
| Are Service Accounts Passwords Documented, Where is the Documentation & Who Can Access It: | |
| Do Service Accounts Employ Complex Passwords: | |
| Is the SQL Server Configuration Manager Only Used to Alter Service Account Information: | |
| SA | |
| Has the SA Account Been Renamed to Obfuscate It: | |
| Is the SA Account Used by DBAs to Logon to SQL Server: | |
| Does the SA Account Have a Complex Password: | |
| Is the SA Account Password Documented, Where is the Documentation & Who Can Access It: | |
| Sysadmin Group | |
| List all Members of the Sysadmin Group: | |
| Have Members of Sysadmin Group Been Vetted: | |
| Do DBAs with Sysadmin Rights Use Separate Account for Non-DBA Tasks: | |
| Does Anyone Who Needs Instance Access a Sysadmin, or are Fixed Server Roles Used to Minimize Risk: | |
| If Using SQL Server 2000/2005, Has the BUILTIN/Administrators Group Been Removed for Non-Clusters: | |
| List Members of Fixed SQL Server Roles (Per Instance) | |
| Sysadmin: | |
| Serveradmin: | |
| Securityadmin: | |
| Setupadmin: | |
| Diskadmin: | |
| Dbcreator: | |
| Bulkadmin: | |
| Processadmin: | |
| Have Members of Server Roles Been Vetted: | |
| List Members of Fixed Database Roles (Per Database) | |
| DB_Accessadmin: | |
| DB_Backupoperator: | |
| DB_Datareader: | |
| DB_Datawriter: | |
| DB_DDLadmin: | |
| DB_Denydatareader: | |
| DB_Denydatawriter: | |
| DB_Owner: | |
| DB_Securityadmin: | |
| Has the Public Role Been Assigned Any Object Permissions: | |
| Have Members of Fixed Database Roles Been Vetted: | |
| Global Groups and Custom Roles | |
| Are Domain Global Groups Used to Manage Database User Accounts: | |
| List All Domain Global Groups Used to Manage Database User Accounts: | |
| Have All Domain Global Groups Been Documented, Where is the Documentation & Who Can Access It: | |
| When Was Last Time Domain Global Group Members Been Vetted: | |
| Are Custom Database Roles Used to Manage Database User Accounts: | |
| List All Custom Database Roles Per Production Database: | |
| Have All Custom Database Roles Been Documented, Where is the Documentation & Who Can Access It: | |
| When Was Last Time Custom Role Members Been Vetted: | |
| Application Roles | |
| List Any Application Roles Used, Along with Their Applications: | |
| Have Applications Roles & Passwords Been Documented, Where is It & Who Can Access It: | |
| List Everyone Who Has Knowledge of Application Role Passwords, and Why: | |
| Are Application Role Passwords Complex: | |
| SQL Server Login Accounts | |
| Have All Existing Logins Been Vetted? | |
| Have All Unused Logins Been Deleted? | |
| Database User Accounts | |
| Have All Orphan Database User Accounts Been Deleted: | |
| Has the Guest User been Disabled by Revoking its CONNECT Permission for All User Databases: | |
| Object Permissions | |
| Has the Rule of "Grant Users the Least Permissions They Need to Perform Their Job" Been Enforced: | |
| When was the Last Time Object Permissions Have Been Vetted: | |
| Are Any Users Allowed to Directly Access Tables (Not Using SPs or Views): | |
| Are Object Permissions Normally Assigned at the Global Group or Role Level: | |
| Are Object Permissions Ever Assigned to Individual Database User Accounts: | |
| SQL Server Agent Proxy Accounts | |
| List Accounts and Describe How Used: | |
| Cryptographic Providers | |
| List All, and Describe How Used: | |
| Encryption | |
| What, if Any Encryption is Being Used for Each Database: | |
| How is Encryption Being Implemented: | |
| Is Encryption Documented, Where is It & Who Can Access It: | |
| Is an Extensible Key Management Module Being Used, Describe It: | |
| Encryption Recoverability | |
| Are Encryption Keys/Certificates Backed Up: | |
| Where are Encryption Keys/Certificate Backups Kept: | |
| Who has Access to Encryption Key/Certificates: | |
| Endpoint Security | |
| Have Non-Default Endpoints Been Created: | |
| Do Non-Default Endpoints Have Correct CONNECT Permissions: | |
| Backup Security | |
| Are Physical Database Backups Stored Securely? | |
| If Backups are Stored on Disk, Are Disk Permissions Used to Limit Access: | |
| Are Database Backups Encrypted: | |
| Is the Backup Encryption Process Documented, Where is It & Who Has Access to It: | |
| Are Backup Passwords Being Used: | |
| Are Backup Passwords Properly Documented & Who Has Access to Them: | |
| Security Recoverability | |
| Have Server Login IDs Been Scripted: | |
| Have Database Users Been Scripted: | |
| Have Object Permissions Been Scripted: | |
| Has Security Recoverability Been Documented, Where is It & Who Can Access It: | |
| SQL Server 2008 Transparent Data Security | |
| Has TDS Been Implemented on this Instance: | |
| List All Databases Using TDS: | |
| Has TDS Been Documented, Where is the Documentation & Who Can Access It: | |
| SQL Server 2008 Audit | |
| Has SQL Server 2008 Auditing Been Implemented: | |
| Has the Trade-Off Between Auditing Needs & Performance Been Evaluated: | |
| How is SQL Server 2008 Auditing Data Monitored: | |
| Has SQL Server 2008 Auditing Been Documented, Where is It & Who Can Access It? | |
| Other | |
| List Any Applications That Use SA for Database Access: | |
| Do You Every Give Consultants, or Other Non-Employees, SA or Sysadmin Access, Even for Short Times: | |
| Do DBAs Always Lock or Log Off SQL Server When Not at Workstation: | |
| Are Servers Physically Secured: | |
| Have You Run the SQL Server Best Practices Analyzer Against the Instance: | |
| Has Your Instance Been Scanned by a Security Scanner to Identify Potential Security Holes: |
I would like your input on my SQL Server Security Checklist. For example, I would like your input on:
- What is missing from the list? What other aspects of SQL Server security should be included? Keep in mind that I can’t include every possible variation.
- What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.
- Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate? Should I change some of the categories, or move some of the items to different categories?
Please add your comments below, and as I get feedback, I will update the checklist. Thanks!
