Yesterday, I attended a great presentation on Security in SQL Server 2005 presented by Canadian IT Pro Advisor Damir Bersinic. He covered four main areas, Windows Server Security, SQL Server Logins and Connections, Database Schemas and Encryption and finally DDL Triggers.
SQL Server is secure by default out of the box. This means you have to turn things on in order to use them. This is easily accommodated by using the Surface Area Configuration Manager. It will allow you to enable any of the services you installed on the server and establish remote connectivity since only local connections are only supported out of the box.
For a complete discussion on SQL security, go to the Microsoft web site: http://www.microsoft.com/sql/technologies/security/securityfeatures_1.mspx. Today I'll discuss security features you can use right away.
DDL Triggers and Schemas are simpler to implement than Encryption only because encryption requires proper management policies in place to use properly. With those in place however, you can readily encrypt your sensitive data with only a minor performance hit.
DDL triggers allow you to police and manage how database objects are created and altered. This means that something like dropping a table can be rolled back as part of a DDL trigger. In addition, the attempted activity can be logged with user information and the statement information used for the table drop. If you know how to write a trigger, you know how to use this feature.
Schemas are the owners of objects and users get mapped to schemas. Why is this a big deal? First of all, you don’t want your users to become DBO for every database object. Secondly, applications are often written with user names embedded in the app. If Mary owns a database object and the app uses her name to qualify the object, what happens when Mary leaves? You have to re-write your app and then redistribute it. With Schemas, Mary can leave the company since the schema never goes away, only Mary's association with it. This is simple, effective and easy to manage.
Encryption can get complicated quickly since there's support for symmetric and asymmetric keys and certificates. Functions are used for both the encryption and decryption process. If you are going to use encryption, make sure you understand what has to be in place for any object or process that touches encrypted data. This includes stored procedures that will have to be rewritten, and more importantly handling information for reports, Analysis Services or Integration Services.
If your company stores sensitive information like credit card numbers, there's no reason not to use these security features. Sarbanes Oxley will force companies to meet compliance requirements and while there is a big workload in meeting those needs, the database technology is available to make it happen.
I enjoyed your post and insights. I also welcome you to this forum and I'm looking forward to dialoguing with you here.
Damir did another great job last night at the WWITPRO user group meeting held at RIM park in Waterloo, Ontario (see http://www.wwitpro.com/DesktopDefault.aspx for more about WWITPRO; see my review of the event - http://bringingclosure.blogspot.com/2006/02/segue-to-sql.html).
There weren't many SQL Server 2005 production deployments represented at the meeting of about 25 IT Pros (only one actually, Dave Totzke, President and Founder of CTTDNUG - http://www.cttdnug.org/DesktopDefault.aspx - a .NET developer group meeting monthly in Kitchener), but I think Damir probably stimulated the vast majority into at least testing the product.
Damir will be back again next month to present (or is that tease) the user group about Microsoft Vista (March 27th 7:00-9:00 pm at RIM Park). See you then, Damir.