The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for the ‘General Thoughts’ Category

Checklist for SQL Server Database Audit & Security Hardening

Posted by Sudarshan Narasimhan on June 8, 2016


TheAuditIsComing
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)

Posted in Auditing, General Thoughts, Security | Tagged: , , , , , , | Leave a Comment »

Did you know–SQL 2000 isn’t supported on Windows 2008+ ?

Posted by Sudarshan Narasimhan on September 1, 2011


I’m not sure how many of you are aware of this but I happened to talk to quite a few DBA’s who were hell-bent on installing SQL Server 2000 on Windows 2008 (don’t ask me why). But for those of you who are planning to do this, DON’T, because it isn’t supported.

SQL Server 2000 and below are not supported on Windows Vista/Windows Server 2008 and above (which includes Windows 7/Windows Server 2008 R2). Here is the official blog post by the SQL Releases team on this,

http://blogs.msdn.com/b/sqlreleaseservices/archive/2009/10/23/sql-server-on-windows-7-and-windows-server-2008-r2.aspx

Stay out of trouble and upgrade Smile.

Posted in General Thoughts | Tagged: , , , , , | 1 Comment »

Trust No One and always ask “WHY”!

Posted by Sudarshan Narasimhan on August 13, 2011


Another good post by Paul Randal. It’s worth a read and I agree with his thoughts on Forums filled with “so-called” experts. The WWW can be a dangerous place for those looking for quick solutions. Don’t be misled into implementing something you will regret later or end up being someone else’s worst nightmare. Always ask yourself “WHY” instead of just focusing on the “WHAT TO DO” when looking for suggestions/solutions. This is a crucial part of logical troubleshooting which most people ignore. So do yourself a favour and ask “WHY” when someone asks you do to make this/that change. If they can’t explain “why”, well rest assured it is not 100% guaranteed to solve your issue. Even if it did, you still don’t know “why” it did.

Like the warrior in 300 says “Remember us, Remember WHY we died


Do yourself a favor… Trust No One
http://www.sqlskills.com/BLOGS/PAUL/post/Do-yourself-a-favor-Trust-No-One.aspx

Posted in General Thoughts | Tagged: , , , , | Leave a Comment »