Presentation Slides and Enabling Database File Encryption

The PowerPoint slideshow of the presentation I held at this year's NT Conference is ready for download. The presentation is in Slovene, but I'm willing to translate it to English (or German or Latin – if you give me some time ;) – leave a comment in this blog if you're interested.

The presentation provides an overview of cryptographic capabilities of SQL Server 2005, focusing on the Database Engine. I also provide a brief (and simplified) general overview of cryptography as a way to improve the security of data exchange.

The demonstration of how digital signatures can be used to allow cross-database access without resorting to cross-database ownership chaining is a simplified version of the demonstration provided by Raul Garcia in his blog, and is also available for download. I've included an additional script for you to play with.

If you've decided on improving SQL Server security using encryption then perhaps this is the first thing to consider: encrypting database files. Microsoft Windows (versions 2000 and later) support file encryption on NTFS drives – this is a certain way of preventing access to database files on illegally acquired devices (e.g. stolen notebooks and/or disks).

How to encrypt database files?

If the database has not been created yet then follow these steps:

1. Make sure SQL Server 2005 Service is running in an appropriate Local User or Domain User account;
2. Log on to the server using the service account. This is vital as the service account needs access to encrypted files;
3. Access the properties of an existing folder or of a newly created one and check the »Encrypt contents to secure data« under »Advanced Attributes« (see illustration bellow). It is recommended that you encrypt the folder rather than individual files – encryption is automatically applied to new files as they are added to the encrypted folder.

Encrypting files and folders in Windows NTFS.

If the database already exists then do this:

1. Make sure SQL Server 2005 Service is running in an appropriate Local User or Domain User account;
2. Detach the database (see Books Online for details);
3. Log on to the server using the service account. This is vital as the service account needs access to encrypted files;
4. Move the database files to the encrypted folder(s);
5. Still logged on as the service user re-attach the database (see Books Online for details). Encrypted database files can only be attached by the user who encrypted them.

After completing the steps above the files are encrypted, and any user with sufficient permissions on the SQL Server instance (e.g. ALTER DATABASE) can create encrypted database files provided the correct folder is specified as the file path.

Simple, isn't it – yet so rarely used.

ML