SqlBits - notes from grok on using NTFS file encryption to secure SQL Server databases on laptop computers

This is a summary of the grok talk I gave at the SqlBits day at Microsoft UK in Reading on 6th October 2007. Thanks for all the delegates who finished lunch early to make the grok talks and I hope it proved useful.

The target audience for this grok talk was those developers using SQL Server (2000 or 2005) on their laptops who might want to secure those databases. This issue is becoming more important as horror stories of lost laptops containing sensitive customer information now seem to appear every other week.

Admittedly if you use Windows Vista Ultimate / Windows Vista Enterprise and you can make use of BitLocker to encrypt the entire hard drive the job is already done for you.  This is especially true if you have a built in TPM module and a fingerprint reader to accompany it.

If you have any other flavour of Windows Vista (including, unbelievably, Vista Business) or Windows XP, we don't have BitLocker and we have a security hole.  If your laptop is stolen, the drive can be removed placed in an external caddy, and straight away the MDF and LDF can be copied to another SQL Server. NTFS standard file security is implemented by the operating system booting from that drive, so read only access is easy and once an MDF/LDF is attached to another database, user rights within the original SQL Server don't apply.

One solution is to make use of NTFS file encryption.

Ideally don't start with an existing SQL Server installation, as you want to install SQL Server as a known user, not as the default 'Local System' or 'Network service'.
  1. Create a new user (say, Sql2005Developer) with a complex password
  2. Install SQL Server as a named instance, running as this new user
  3. (optional) During install you can specify a separate data directory, say C:\encrypted\Sql2005Dev
  4. Once everything is installed - login as the new user, and use Windows Explorer to encrypt your data directory (make a cup of tea if you have any decent sized DBs in this directory - it takes time to encrypt/decrypt large files)
The reason for using a named user is to give you the ability to easily encrypt/decrypt directories by logging in as that user.

Extra 1 - Don't forget the password for SQL Server user (or see Extra 2)

If you forget the SQL Server user password, and you reset it from the Administrator account the encryption key is reset and all the currently encrypted files will become unreadable.  So make a backup with ...

Extra 2 - command line encryption tool CIPHER (see cipher /help for command line options for this tool)

 cipher /e /a /f <directory-name>    ENCRYPTS directory, including all files
 cipher /d /a /f <directory-name>    DECRYPTS directory, including all files

Demo of cipher
  1. Create the following directories, c:\temp\encrypted and c:\temp\not-encrypted
  2. At a command line use cipher to encrypt directory (if viewed in Windows Explorer the folder name should be a different colour to indicate it is encrypted)
  3. Now create a new text file in 'encrypted', copy to 'not-encrypted' - it is still encrypted, encryption moved with file
  4. Create a new text file in not-encrypted (this should not appear in a different colour as it is not encrypted)
  5. At a command line open a new command line running under the administrator user, runas /user:administrator cmd, and try to edit the encrypted text files (can't read either, encryption followed file)
  6. If you need to backup the encryption keys use, cipher /r:<cert-filename> <directory-name> to backup the keys to the two key files. These can then used to restore encrypted files, see additional information on the cipher tool on microsoft.com.
Extra 3 - removing the new SQL Server named user from fast user switching user list

If you do have a named user for SQL Server, this appears on the user list in Windows XP (only appears if fast user switching is enabled, and not for machines on a domain).
  1. In regedit, locate SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList\ in the HKEY_LOCAL_MACHINE branch.
  2. Right click on the UserList and select New, DWORD value
  3. Rename New Value #1 to the new SQL Server named user account
The name will now disappear from the user list. This prevents the easy method of logging in as the SQL Server user to perform encryption operations via Windows Explorer. However, you can always choose to use the runas command to login as the SQL Server named user, opening a new command window in which you can run the cipher tool.

Print | posted on Wednesday, October 10, 2007 8:32 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)