I’ve been involved in a lot of IT security audits, many times due to legislative requirements. A few good examples of these are PCI, SOX, HIPAA, GLBA audits. Each of these US legislative acts have a set of defined security standards & configurations that organizations have to adhere to.  Audits for these systems happen both internally and externally to ensure the company is compliant to these standards and any remediation’s are acted upon.

Being a DBA, you’re usually involved in these IT audits. The scope for these audits should be very well defined per your internal IT security policies. As a database administrator, you are responsible for the secure management of the company’s data, vis-à-vis compliance data. The systems in scope for these databases are usually ones (but not limited to) that contain the following data:

1. Payment Card Data or database systems used for Payment processing (Online payment web systems, retail POS etc)
2. Financial Data (like Bank, Insurance, Stocks etc)
3. Health Information (like medical, patient records etc)
4. Personally Identifiable Information (PII) data (like client/member data including DOB, Tax Number, SSN etc)

While each of these compliance regulations are very-well documented (especially PCI DSS), you must follow certain best practices or standards for any DBMS system, irrespective of the requirements. In today’s world, data leaks are becoming increasing prevalent and within the DB world, it is very essential to adhere to strict & enforceable data security practices.

All that said, coming to SQL Server security good practices, I’ve listed what I feel are the bare-minimum security checks that need to be performed. You can add stuff to this, but in my view these are essential and non-negotiable settings that you must check as a DBA. I’ll probably share some scripts & code to monitor these setting centrally in an automated way, in another post. This post is to list down and share these settings so that you are aware of the various things to consider when looking at SQL Server Security Hardening & Audits.

CHECKLIST: Security Audit of a SQL Server Database Instance

Instance Level:-
1. Server Role Membership
2. Server Permissions e.g. CONTROL SERVER, ALTER ANY DATABASE
3. Public Role Permissions (harden this as much as possible on user database, leaving aside system databases)
4. BUILTIN Admins present as a login?
5. Default Port changed?
6. Orphaned AD Users & Groups must be removed
7. [NT AUTHORITY\ANONYMOUS LOGON] must be removed.

Database Level:-
1. Database Role Membership (Elevated)
2. Database Role Membership (Standard)
3. Database Permissions e.g. DELETE, ALTER, SCHEMA
4. Guest User Disabled?
5. DBO User ownership (you can read my previous post on DBO ownership HERE (point #2) to understand why this is important)
6. No Orphaned Users should exist

Windows OS Level:-
1. Local Administrators Group Members
2. Local Security Policy Privileges (following this MSDN  doc to grant only those privileges required to run SQL Services to the SQL service accounts)
3. Guest User enabled?
4. Windows Firewall configured to allow connections via a specific Port Only (I hate systems setup to allow all incoming connections to sqlservr.exe. Duh!)
5. SQL Browser Service Disabled?

Configuration:-
1. XP_CMDSHELL Disabled?
2. Password Complexity & Lockout for SQL Logins (SQL logins inherit the account & password policies of the server on which the instance is running. Work with your IT admins to implement a stronger policy using GPO if required. Otherwise make sure CHECK_POLICY setting is turned ON for all SQL logins [including SA])
3. SA password rotated frequently (if you can disable the SA account, even better)
4. Cross database ownership chaining Disabled?

Patch Management
1. Are all the security bulletin patches for known vulnerabilities applied on your SQL Server instance. (Refer to https://technet.microsoft.com/en-us/security/bulletins.aspx and filter on your specific version & patch level to get the list of known security patches available).
2. Are all known Windows security patches applied (Depending on your company, this might be handled by another team, but it’s still a DB Server, so better be prepared if someone else isn’t doing their job properly 😉 )

Compliance:-
1. Access Control mechanisms in place for Sensitive/PII Data?
2. Access Control mechanisms in place for Regulatory Data?
3. SQL Server Audit enabled to track permissions/role/users changes to compliance data?

Penetration Tests
1. SA Login checks. Is anyone trying to login by brute-force using the SA account?
2. Main App login. Is anyone trying to login by brute-force using the application login (especially if it’s a SQL login)

I was almost about to add managed service accounts (MSA/GMSA) to this list, but considering SQL 2016 just released a week ago and most of you are probably running SQL 2012/2014 with either Clustering/AlwaysON, I’m not going to make it a must-do yet. (Damn you Microsoft, you promised GMSA support for SQL 2014 but didn’t follow through 😦 )

That’s all for now. I’ll continue this in another post with some sample code to help you through all these items.

Until then, stay safe and may the force be with you.

-TheSQLDude (Sudarshan)