EFS can be used to encrypt SQL Server 2005 data files and folders. EFS is supported on Windows 2000 and later operating systems with New Technology File Systems (NTFS) formatted drives. EFS uses a combination of symmetric and asymmetric methods to provide transparent SQL Server 2005 data encryption. On Windows 2003 Server and newer operating systems, EFS by default creates a random File Encryption Key, which is a 256-bit AES key to perform data encryption.The File Encryption Key is then itself encrypted with the user’s public key and stored within the encrypted file or folder.
To encrypt SQL Server 2005 data files and folders using EFS, follow these steps:
1. Stop the SQL Server service.
2. Log out and log in using the SQL Server service account credentials.
3. Right-click on the file or folder to be encrypted and select Properties | General Tab | Advanced.
4. Within the Advanced attributes window, select Encrypt contents to secure data.
5. Within the Advanced attributes window, press OK.
6. Within the Properties tab, press OK.
7. If you are encrypting a folder containing subfolders, you will be presented with another window asking if you would like to encrypt them as well. Press OK.
8. EFS encrypted files and folder names should now appear in green within any Windows file explorer window.
9. Restart the SQL Server services.
If errors are generated, you may have encrypted the SQL Server data files using an account that is not linked to the SQL Server service account.You can decrypt the data folders by reversing the steps above and trying again. When encrypting individual database files, EFS first creates a plain text copy of the file to be encrypted, encrypts the target file, and then deletes the temporary file.This temporary file is not securely deleted and can be recovered using common data recovery tools. To prevent local file disclosure, you should use a secure data deletion tool to overwrite the areas of disk containing the temporary file. Alternatively, you can simply encrypt the parent folder that contains the database files to ensure any temporary files are also encrypted.
EFS encryption is beneficial if the database media is stolen or misplaced. When transferring EFS encrypted files over the network, Windows first decrypts the file and then transfers the plain text equivalent. Some administrators perform manual backups of database files prior to implementing changes on the database server. If this backup involves copying data files from one server to another, you will effectively be storing an unencrypted copy of your database on the destination server.
Encryption File System Contains Inherit Flaws
On Windows Server 2003, EFS uses a strong 256-bit AES key to encrypt data. Under most circumstances, this would be an effective method of encryption; however, this AES key is protected by the user’s public key, which is based on the user’s Windows login password. This ultimately reduces EFS protection to the strength of the user’s Windows password. There are publicly available tools that can successfully decrypt EFS encrypted data by exploiting this flaw. Because of this, EFS should not be used to encrypt sensitive database data.
Working with EFS Encrypted Data
EFS encryption is managed by the operating system, and seamlessly provides file and folder encryption to SQL Server 2005. All SQL Server functions and operations remain unchanged when using this encryption method. Because EFS is handled outside of SQL Server 2005, encryption keys must be backed up separately in addition to your database backups.