Data Governance with SQL Server

  • Article

I thought it would be good to write up my talk at the IT Manager TechDays last week, as I get a fair number of questions and the information is not always easy to find. First let me explain what I mean by Governance: It is the policies, best practices, enforcement and training needed to address the compliance requirements of a business as part of its risk management strategy.

This then leads down to a set of things you decide you need to do to be compliant. You then have a list of features in products like SQL Server which might be useful in assisting in these processes. My top features of interest in SQL Server 2008 would be:

  • Policy Based Management. This allows a DBA to test and possibly ensure that a given set of servers is in compliance with a given set of criteria e.g. compatibility level, password strength and so on. This is especially powerful when the policies are run by automated scheduled (SQL Agent jobs running PowerShell) scripts which pump the result into a SQL database which can then be consumed by Reporting Services reports. The Enterprise Policy Management Framework on CodePlex is an example of how to do this. BTW Policies can also be fired at earlier version of SQL Server (provided you have at least one installation of SQL Server 2008)
  • Transparent Database Encryption (TDE) protects the database at rest. This prevents it being copied to another location without the corresponding certificate. This doesn’t affect access to it or require any changes to applications accessing when it is in its normal location. Note this is not a replacement to, but rather compliments the encryption of cells introduced in SQL Server 2005.
  • Extensible Key management simply allows you to use your third party key management software in SQL Server, so you only have one set of keys and one program to manage them.
  • SQL Audit might not stop your data being leaked but it will show you the who , how and when without needing to be a DBA. In fact not needing to be a DBA is important as an auditor needs to be able to see what the DBA is doing. Auditing everything would create a lot of noise and might overly affect performance so the Audit UI allows fine grain control of this. Finally the audit log might need to be secure so SQL Audit can write directly to the security log.

Of course securing and auditing SQL Server itself is only part of the story:

  • The operating system needs to be up to date and patched
  • SQL Server does not itself encrypt network traffic so you might need to
  • Securing your backup using third party encryption or TDE is important

However how does that all that fit into the regulations and compliance requirements that exist such as European Data Protection, ISO 27001, Payment Card Industry? A key point is that Microsoft itself has to be compliant with all these standards – it holds customer data, it has mechanisms like XBOX live and BT Vision that have to process credit card payments. Moreover this all runs on SQL Server, so where possible Microsoft IT can simply share its approach to compliance, where this might be difficult for the Microsoft consultants working on clients’ projects to do.

Your first port of call should be the whitepaper reaching compliance.  This includes sections on addressing vulnerability, defining risk management models, and managing security configurations.  There are also links to hands on labs to put help you with the features I have described above.

There is also Microsoft’s Data Governance Portal, which includes a number of solution accelerators such as one specifically for Payment Card Industry compliance, which I mention because I get asked about it so often!

Hopefully this will give you all the resources you need to meet your compliance needs, and give you the ammunition to debunk a few myths that may exist in your organisation leaving you to get on with the relatively painless task of configuring SQL Server for your compliance needs. If you have questions about compliance please feel free to contact me – I will try my best to get you the answer you need.

My parting shot is that compliance is a process and not a product.