Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Implementing Encrypting File System (EFS) with SQL Server

By Brian Kelley, (first published: 2002/12/09)

Implementing Encrypting File System (EFS) with SQL Server

In his first article on SQL Server security, Chris Kempster briefly introduced Encrypting File System (EFS), a new feature to Windows 2000. EFS provides a mechanism for encrypting files completely transparent to higher level applications such as SQL Server. EFS should be of prime interest to any DBA looking to protect sensitive data files within SQL Server. After all, if an attacker can copy the unencrypted data files from a SQL Server, the attacker can then attach these files to his or her own SQL Server with very little issue. With EFS you can encrypt all of your data files in such a manner that only SQL Server has access to them, stopping any such nonsense.

In fact, copying data files from one server to another and then using sp_attach_db to attach the database to the new SQL Server is one method of data migration along with backup/restore, the copy database wizard, and generating SQL scripts and bcp files. Since copying and reattaching can be done so easily, the biggest concern is someone using this mechanism to steal data. EFS provides a relatively fail-safe solution to this issue since:

  • The files are encrypted.
  • Without the key to decrypt the files, copying fails.
  • By default, only the user account who encrypted the files and certified data recovery agents can decrypt.

In this article I'll provide a high-level overview of how EFS works, what to worry about, how you can implement it in your environment, and how to troubleshoot issues with EFS and SQL Server. One of the things I won't do is go into great detail on how EFS works since an expert has already done so. I offer you links to a 2-part series by Mark Russinovich, co-author of Inside Microsoft Windows 2000, on the internals of EFS (the articles are published by Windows and .NET Magazine).

The Basics - What to Worry About

There are a few of things you'll want to keep in mind before implementing EFS in your environment. First, you'll want to cover your data recovery process. Second, you'll want to configure EFS using the user account your SQL Server service runs under. Third, you'll want to make sure you cover your data recovery process. Fourth, there is a performance hit for all that encryption, though it's estimated to be fairly small. It is there, though. Fifth and finally, you'll want to make absolutely sure you cover your data recovery process.

No Data Recovery Process? Proceed at Your Own Risk.

Yes, I did mention the data recovery process as three of the five things you'll want to keep in mind before implementing EFS. Proceeding without a detailed data recovery process is like driving down the highway without a seat belt: most of the time it may not matter that you have it. But when it does, you'll sure wish you had one because if you don't the results can be fatal. A data recovery process  is how you go about recovering an encrypted file if, for some reason, the user you used to encrypt the file gets deleted or the profile gets corrupted or a whole host of other possibilities that amount to you have an encrypted file and you can't decrypt it as you normally would. After all, a system administrator working on little or no sleep point-and-clicking his way (or her way) through the GUI interface could slip and kill the user account. If you don't have a data recovery process, you just went flying through the windshield.

Developing a recovery process can be as simple as:

  1. Export the key used to encrypt the file
  2. Store it off on removable media like a floppy that is kept under lock and key
  3. Use the default recovery agent or set your own
  4. Export the file using Windows 2000 backup
  5. Send it to the recovery agent (if necessary)
  6. Install the key for the recovery agent
  7. Use cipher to decrypt the file

This is a really simple data recovery process, but it illustrates the need to have a defined process (kind of like one gory accident scene demonstrates the need for the seat belt). It also helps to test it on some dummy files first to make sure the process works. Data recovery methodology is a topic in its own right and since I can't do justice to it in this article, I'll direct you to the Windows 2000 Server Resource Kit: Distributed Systems Guide and it's section on Administrative Procedures for data recovery. The Distributed Systems Guide details all you need to know on how to set up the recovery process.

Log On As the Service Account

If you want SQL Server to read the encrypted files, encrypt them using the service account SQL Server runs under. The reason is simple: the private key used to decrypt the file encryption key used to decrypt the file itself is stored in the user's personal store. If you didn't follow that last sentence the Inside EFS articles explain how the encryption works. Suffice it to say that the valuable secret that unlocks the file is stored away in the user's private cache. As a result, if you weren't the user who did the encryption, you likely don't have to means to get to the file. Of course, there are exceptions using API calls and other methods, but to keep things simple, use the service account. Of course, if you have SQL Server running as LocalSystem (generally a big no-no because of the privileges LocalSystem has, that of a full Administrator), you need to create a user whether on the server or in the domain to run SQL Server under. Log on to the system as that account (which is why you can't use LocalSystem) and be ready to lock down those database files!

Understand the Hit

You don't get something for nothing. Encryption is a trade-off. You trade a little performance for a lot more security. Not a bad deal in my book, though your results may vary. An estimate in a PowerPoint presentation from Microsoft Australia (Slide 19) indicates the hit is typically less than 5%. If I can be sure some thief isn't going to walk off with my database files, I'm generally willing to take that hit. If I'm not, then I have a fundamental question to ask: "How can a system be so important that I can't afford the performance hit yet not be sensitive enough to need encryption?"

Granted, there are some environments that fit this scenario perfectly... not sensitive but gotta be fast, real fast. I can think of one example as a sports fan, and that's a database that's keeping track of stats for a live sports webcast. If an attacker steals that, the attacker has a bunch of sports scores, the same scores and stats that are on every other major sports website. The data isn't exactly sensitive. However, that database had better be blisteringly fast because we DBAs who are watching our favorite teams win (or lose) want up to the second scores while we work late into the night or on the weekend for another emergency project.

Step-by-Step

Encrypting database files is easy. Windows Explorer and My Computer do most of the work for you. There is also the cipher.exe command, which you can use to encrypt and decrypt from the command-line. I'll first cover the GUI tools, then talk about cipher.exe.

Before I go into detail about the encryption process, I'll first state that Microsoft recommends you encrypt at the directory level and include the files below.  Encrypting at the directory level is considered a best practice. The reason for this is if I have an encrypted directory and I save a file in it, it too becomes encrypted. Therefore, by setting the directory to encrypted, I ensure any new files (like new database files I might create) are also encrypted.

Using the GUI

The first step is to navigate to the folder just above the folder to encrypt (unless you've intending to just encrypt a file, in which case go to the directory it's in). For instance, if you want to encrypt C:\Program Files\Microsoft SQL Server\MSSQL\Data, navigate to C:\Program Files\Microsoft SQL Server\MSSQL\ so you can see the Data folder. Right-click on the folder (or file) and select Properties (Figure 1).

 Figure 1: Select Properties

The Properties dialog box will appear. In order to toggle encryption on, click on the Advanced button (Figure 2).

Figure 2: Click Advanced Button

Clicking on the Advanced button brngs up the Advanced Attributes dialog box. One of the choices is Encrypt contents to secure data which is normally unchecked. To turn on encryption, click the checkbox (Figure 3) to mark it and click OK to accept the change to Advanced Attributes.

Figure 3: Encrypt Contents

Click OK again to exit the Properties dialog box. If you are encrypting a folder, you should be prompted to confirm changes (Figure 4).

Figure 4: Apply changes to everything

Since this is for a folder, in order to get the files within the folder and encrypt them, you'll have to select Apply changes to this folder, subfolders and files and click OK. This will begin the encryption process. Depending on your systems memory and processors, this initial encryption can take a while.

If you select to encrypt just a file (same process, just bring up the Properties dialog box for the file instead of on a directory), you'll most likely receive the dialog box shown in Figure 5.

Figure 5: Encrypting a file

If you don't want to see this screen again, choose the option to Always encrypt only the file

If in the future you need to decrypt the files (you are going to move the files to a new server running a different service account for instance), you can follow the same process, only uncheck the box by Encrypt contents to secure data in Figure 3. You'll be prompted again if you've selected a directory (as in Figure 4 but with the attribute change as decrypt instead of encrypt) but not if you're decrypting a single file.

Using cipher.exe

I'm a command-line junkie, I'll admit it. I find it much easier to manage and administer my environment if I can do so from the command-line. Part of the reason is I can script my commands, double-check them, and when I'm sure I have everything the way I want, I can run the script. And then I can run the script through an automated process or just run it manually myself at some later time. While EFS isn't something you usually work with over and over again for a particular system, I explored the cipher.exe command the first time I took a hard look at EFS because I like command-line options. If you prefer to manage everything through the GUI, you'll should have all the functionality you require. There are a couple of things that can be done with the cipher.exe command you can't do through the Properties dialog boxes, so I'll cover them here.

Displaying the Encryption Status:

If you don't use any switches at all, cipher.exe will return the encryption status of all files and subfolders in the current directory. As you might expect, E means encrypted and U means unencrypted (or normal). Here is an example of a directory where there is a mix of encrypted and unencrypted files. I didn't set the directory to encrypt just to demonstrate how cipher.exe shows both. Remember the best practice above.:

C:\temp\EFSTest>cipher

Listing C:\temp\EFSTest\
New files added to this directory will not be encrypted.

U EFSSubFolder
E encrypt1.txt
E encrypt2.txt
U FileNoEncrypt.txt

The directory isn't set for encryption because any new files that are added to the directory won't be encrypted. Both EFSSubFolder (really a folder as the name would apply) and FileNoEncrypt.txt are not encrypted. The files encrypt1.txt and encrypt2.txt are encrypted and have an E before each file name.

You can also use wildcards and "qualifiers." For instance, if I just wanted to see all directories and files beginning with "encr" I'd do the following:

C:\temp\EFSTest>cipher encr*

Listing C:\temp\EFSTest\
New files added to this directory will not be encrypted.

E encrypt1.txt
E encrypt2.txt

The wildcards can be used when encrypting and decrypting as well. I'll use then in those sections.

Getting Help: The /? Switch

If you don't remember the switches for cipher, use the /? switch, like:

cipher /?

if you use a dash, cipher won't recognize you're asking for help. Rather, it'll check the directory and think -? is a wildcard.

Encrypting: The /E Switch

To encrypt, use the /E switch. For instance, if I want to encrypt the directory EFSTest under C:\Temp (notice I'm use EFSTest as a qualifier to ensure cipher only touches that directory):

C:\temp>cipher /E EFSTest

Encrypting directories in C:\temp\

EFSTest [OK]

1 directorie(s) within 1 directorie(s) were encrypted.

One thing I'll mention is that the command as is only encrypts directories. You'll have to add another switch to ensure you get the files, too. Also, it won't encrypt any subfolders, either. More on both coming up.

Decrypting: The /D Switch

To decrypt, use the /D switch. Again, I'll use EFSTest as a qualifier to ensure I only touch that directory under Temp:

C:\temp>cipher /D EFSTest

Decrypting directories in C:\temp\

EFSTest [OK]

1 directorie(s) within 1 directorie(s) were decrypted.

Using cipher like this, as with encryption, won't hit the files or subfolder. This brings me to the next switch, which includes files as well.

Include the Files: The /A Switch

If I do a directory listing on C:\Temp\EFSTest\ I see the following (I've changed files from the previous example where I looked at the encryption status):

C:\temp\EFSTest>dir
 Volume in drive C has no label.
 Volume Serial Number is E4D5-482E

 Directory of C:\temp\EFSTest

12/06/2002    02:00p    <DIR>            .
12/06/2002    02:00p    <DIR>            ..
12/06/2002    01:24p    <DIR>            EFSSubFolder
12/06/2002    01:58p                  15 encryptme.txt
                 1 File(s) 15 bytes
                 3 Dir(s) 13,081,638,912 bytes free

The following only encrypts the folder:

C:\temp\EFSTest>cipher /E

Encrypting directories in C:\temp\EFSTest\

EFSSubFolder [OK]

1 directorie(s) within 1 directorie(s) were encrypted.

If I want to get the file encryptme.txt, I need to use the /A switch:

C:\temp\EFSTest>cipher /E /A

Encrypting files in C:\temp\EFSTest\

EFSSubFolder [OK]
encryptme.txt [OK]

2 file(s) [or directorie(s)] within 1 directorie(s) were encrypted.

Converting files from plaintext to ciphertext may leave sections of old
plaintext on the disk volume(s). It is recommended to use command
CIPHER /W:directory to clean up the disk after all converting is done.

The last paragraph does seem a bit worrisome, so far as language goes. The reason it's there is simple: when EFS creates an encrypted file, it does so as a new file, so that if the process is cancelled, you've not lost your original file. When the encryption is done, it'll delete the unencrypted version. However, anyone who has done data recovery from a disk knows the data is possibly still on the drive. Anyone who has the proper tools can read the disk's contents as raw data and could potentially recover parts of the deleted file. This leads to the next switch.

Get Rid of the Evidence: The /W Switch

Keep in mind that while the file has been deleted so far as the operating system is concerned, bits and pieces of the file(s) may exist in readable form on the disk. If you want to eliminate this, use the /W switch. Now the /W switch will work to clear every bit of free space on the disk. After all, that's the only way to be safe (I won't get into arguments about techniques that still can be used to recover the data but there are some). The /W switch will write a null character (0x00) every where there is supposed to be free space. This may take a LONG time because it will clean the whole disk of possible fragments. If I want to clean up the C:\Temp\EFSTest directory, I would issue the following command:

cipher /W:C:\Temp\EFSTest

When you run this command, you'll receive a suggestion to close all applications to remove as much data as possible. Also, it'll slowly show it's working by periodically throwing up another period as it works. For example:

C:\Temp>cipher /W:c:\temp\EFSTest
To remove as much data as possible, please close all other applications while
running CIPHER /W.
Writing 0x00
.....^C

And yes, you can stop it by issuing a CTRL-BREAK if it is taking too long for your taste (I did, which is why you see the ^C at the end of the periods). Keep in mind that if you do so, some of the unencrypted data may still exist on the disk.

Force a Change for All Files: The /F Switch

One thing about decrypting and encrypting files is if cipher detects that a file or directory already exists in the desired state, cipher won't waste resources performing the operations on them. If, however, you want to force all files to be handled, use the /F switch. For instance:

C:\temp\EFSTest>cipher /E /A /F

Encrypting files in C:\temp\EFSTest\

EFSSubFolder [OK]
encryptme.txt [OK]

2 file(s) [or directorie(s)] within 1 directorie(s) were encrypted.

Converting files from plaintext to ciphertext may leave sections of old
plaintext on the disk volume(s). It is recommended to use command
CIPHER /W:directory to clean up the disk after all converting is done.

I had already encrypted the folder and the file, but the /F forces encryption anyway. 

Get the Children, Too: The /S Switch

So far I've not looked at anything that resembles the dialog box from Figure 4. In order to get everything, use the /S switch. The syntax is /S:<directory> in order to accomplish this. For instance:

C:\temp>cipher /E /A /S:c:\temp\EFSTest

Setting the directory c:\temp\EFSTest to encrypt new files [OK]

Encrypting files in c:\temp\EFSTest\

EFSSubFolder [OK]
encryptme.txt [OK]

Encrypting files in c:\temp\EFSTest\EFSSubFolder\

anotherFile.txt [OK]

4 file(s) [or directorie(s)] within 3 directorie(s) were encrypted.

Converting files from plaintext to ciphertext may leave sections of old
plaintext on the disk volume(s). It is recommended to use command
CIPHER /W:directory to clean up the disk after all converting is done.

Notice that the cipher command went into the EFSSubFolder and encrypted the file there as well. The /S switch will ensure all subfolders are covered, not just the top-level folder(s). 

Other switches

There are other switches available such as /H to handle hidden files and directories. However, since data files for SQL Server tend not to be either of these, I won't cover this switch here. The is also a switch to regenerate a new key to encrypt the file. There is another switch to continue on an error, /I, so that cipher will keep processing until it comes to the very end of what's is left to do. If you want to see these additional switches, use the /? switch.

Troubleshooting Issues with EFS and SQL Server

There are several common issues when using EFS with SQL Server, so let's take a look at them.

SQL Server won't Start

If you've selected the master datababase or if you've selected the Data directory as a whole and used an account other than the service account, SQL Server won't start. For instance:

C:\Program Files\Microsoft SQL Server\MSSQL\Data>net start mssqlserver
The MSSQLSERVER service is starting.
The MSSQLSERVER service could not be started.

The service did not report an error.

More help is available by typing NET HELPMSG 3534.

Again, this is because SQL Server doesn't have the ability to access the encrypted file and it needs the master database to start up. Decrypt the master database files (or the whole directory) and attempt to restart SQL Server. If SQL Server starts, you've encrypted the files using an account different that the service account. If SQL Server does not start, then it's not an EFS issue, though you may want to decrypt the files prior to beginning troubleshooting to make things easier (in case you've got to copy files, etc.)

A Database is Marked Suspect

If you are picking and choosing which databases to encrypt, the most likely problem if a database being marked suspect is the same as with SQL Server not starting: the database was encrypted using an account different than the service account. Figure 6 shows just such an occurrence (the pubs database):

Figure 6: Pubs marked suspect

Here the pubs database has been marked suspect and the reason is simple: I encrypted it using the wrong account (my account instead of the SQL Server service account). The pubs database file cannot be read by the SQL Server service, so it's marked as suspect. SQL Server doesn't know what's wrong with the file but it knows it should be able to access pubs because pubs is listed in its sysdatabases system table. The only way I can fix it is to decrypt the file and then have the SQL Server service account encrypt the file. You'll likely have to restart SQL Server if this is the case.

Encryption and Compression are Mutually Exclusive

Another problem you may run into is with compression. Windows 2000 cannot enact EFS on a compressed file. As a result, if you tell EFS to encrypt a compressed file it will encrypt the uncompressed format. Keep this in mind. If you are having space shortage issues and you've chosen to compress files (not recommended on SQL Server database files in any case).

Encryption is for NTFS only

EFS only works on NTFS. Therefore, if you copy the file from the server where you've got encryption in place to a drive that isn't NTFS, you'll lose encryption. You may not be prompted that you will lose the encryption status of the file, so keep this in mind. If you have FAT or FAT32 drives in your environment.

Access Denied!

Since I brought up the topic of copying files... should someone who is not the service account try it and the file is encrypted,  that person is likely to get the following error:

Figure 7: Access Denied

In Figure 7 I tried to move the file Brian.mdf, a data file of my personal database on the server. Since I don't have the key to decrypt the file (the SQL Server service account does), I can't do anything with it. If I had logged on as my service account that runs SQL Server, I would have been able to copy the file just fine. But since I didn't, the operating system stops me. If you have to copy or move a file, use the service account to do so. If you need to have someone access the file afterward, decrypt it as well. Keep in mind that we want the check that causes this access denied error. It is a good thing. This is what stops an attacker from moving our database files off SQL Server.

File in Use

Finally, when you go to encrypt or decrypt the file(s), you may receive the following error:

C:\Program Files\Microsoft SQL Server\MSSQL\Data>cipher /E /A pubs.mdf

Encrypting files in C:\Program Files\Microsoft SQL Server\MSSQL\Data\

pubs.mdf [ERR]
pubs.mdf: The process cannot access the file because it is being used by another
process.

0 file(s) [or directorie(s)] within 1 directorie(s) were encrypted.

The reason this error appears is because SQL Server has the files open. Open files can't be encrypted (the operating system needs exclusive use). Therefore, if you receive that error and you have the SQL Server service running, go ahead and stop the SQL Server service. Then, when the service is stopped, change the encryption status of the file.

Wrap Up

EFS is a great addition to the Windows 2000 operating system. It gives us the ability to encrypt our data files so that only SQL Server can access them. However, before implementing it in your environment, formulate your data recovery plan. Also keep in mind there's a slight performance hit. Finally, make sure you use the SQL Server service account to encrypt and decrypt the files.

Implementing EFS can be done through the GUI or with a command-line utility called cipher.exe. Both methods are pretty easy to use, pick the one that suits you best. Keep in mind there are a few things that you can do through cipher you cannot do through the normal GUI. If you have need of these additional features, take a look at cipher.

Finally, there are some common issues when implementing EFS with SQL Server. One is if SQL Server won't start. Chances are that the master database files were encrypted with a user account other than the service account. The same is probably the case if a database is marked suspect. Also, keep in mind that encryption doesn't work with compress and it's only supported on NTFS. Finally, encrypting or decrypting a file means the operating system has to be able to get exclusive use of the file. If SQL Server is running, the OS can't do anything with the file. Therefore, stop SQL Server until you complete your operations on the files and then restart the service.

Hopefully you've found this article informative. It's intended to be a hands-on article about how to implement EFS with SQL Server, so I intentionally stayed with from getting into the details of how EFS works. Check out the links and the book Inside Microsoft Windows 2000 for in-depth coverage of this technology.

Total article views: 29147 | Views in the last 30 days: 20
 
Related Articles
FORUM

SQL Server 2005 encryptation

SQL Server 2005 encryptation

FORUM

SQL server 2005 Encryption

Encryption

FORUM

Swapping the Sql Server Instance root directory with another server

Swapping the Sql Server Instance root directory with another server

FORUM

sql 2005 encryption

encryption

FORUM

Creating SQL Server 2000 Virtual Directories

Creating IIS SQL Server 2000 Virtual Directories

Tags
administration    
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones