Implementing Encrypting File System (EFS) with SQL Server

,

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.

Rate

4.6 (5)

Share

Share

Rate

4.6 (5)