The SQL Dude!

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

Posts Tagged ‘Auditing’

Checklist for SQL Server Database Audit & Security Hardening

Posted by Sudarshan Narasimhan on June 8, 2016

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
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?

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

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 »

Auditing T-SQL Script to find out who made changes to data in a table

Posted by Sudarshan Narasimhan on June 1, 2012

Having worked with multiple customers and different application where at times the data in the table has gotten changed and we need to find out when/where/who made the changes, I used the below T-SQL code to setup a trigger on the table and track the changes into an auditing table. There are obviously other ways to do this like SQL Profiler, XEvents etc., but I find using T-SQL more simpler and lesser overhead. So, sharing the script for others to use.

-- Note: This trigger tracks Updates and Deletes happening on a table. 
-- Please delete this trigger once the source has been identified and corrective actions have been taken.

-- 1. Creating the audit table to store information on Update/Delete
AuditID [int] IDENTITY(1,1) NOT NULL,
Timestamp datetime not null CONSTRAINT AuditTable_Timestamp DEFAULT (getdate()),
OperationType char(1),
OperationDate datetime DEFAULT (GetDate()), 
PrimaryKeyValue varchar(1000), 
OldColValue varchar(200), 
NewColValue varchar(200), 
UserName varchar(128),
AppName varchar(128),
ClientName varchar(128)

--2. Creating  the audit trigger
-- Replace PrimaryKeyValue with the PK Column Name
-- Replace NewColValue with the column name in the IF BLOCK
-- Replace OldColValue with the column name in the final SELECT statement
-- Replace TBLNAME with the name of your table which you want to track the changes for.

Create trigger TBLNAME_Audit on TBLNAME for update, delete
declare @OperationType char(1),
@OperationDate datetime,
@NewColValue varchar(200),
@OldColValue varchar(200),
@UserName varchar(128),
@AppName varchar(128),
@ClientName varchar(128)

select @UserName = system_user
select @OperationDate = CURRENT_TIMESTAMP
select @ClientName = HOST_NAME()
select @AppName = APP_NAME()

if exists (select * from deleted)
      if exists (select * from inserted)
            select @OperationType = 'U'
            select @NewColValue = NewColValue from inserted
            select @OperationType = 'D'
            select @NewColValue = null
Insert AuditTable (OperationType, OperationDate, PrimaryKeyValue, OldColValue, NewColValue, UserName, AppName, ClientName)
select @OperationType, @OperationDate, PrimaryKeyValue, OldColValue, @NewColValue, @UserName, @AppName, @ClientName
from deleted

--3. Query the audit table once the values in the base table has changed
select * from AuditTable

Posted in Auditing, Security, T-SQL | Tagged: , , , , , , | 5 Comments »