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:
- Export the key used to encrypt the file
- Store it off on removable media like a floppy that is kept under lock and
key
- Use the default recovery agent or set your own
- Export the file using Windows 2000 backup
- Send it to the recovery agent (if necessary)
- Install the key for the recovery agent
- 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
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.