다음을 통해 공유

Security and Encryption for SQL Server Compact

  • 아티클

The nice thing about SQL Server Compact is the database can be treated as a document.  It's a single file, it's only "open" when being used and can use the standard set of security mechanisms that any other document can.  You can set ACLs on the files, and you can encrypt the file with the Windows Encrypted File System.

Any security expert worth their weight in "keys" will tell you security is a multi-layer process.  There's no "one master lock" you can use and be considered secure.  It's a layered approach. 

Here's a set of recommendations you can use for SQL Server Compact, but many of these will apply to any documents.

Use the Windows Encrypted File System
Encrypt the directory of all your "document", including the directory of your SQL Server Compact Data File

System.IO.DirectoryInfo dirInfo = new System.IO.DirectoryInfo(System.Windows.Forms.Application.LocalUserAppDataPath);

dirInfo.Attributes = System.IO.FileAttributes.Encrypted;

This will flip the EFS flag.

Alternatively, you could simply set the Encrypted flag for just the Compact data file

using (SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.LocalConnectionString)) {

    System.IO.File.Encrypt(conn.Database);

}

This will encrypt the data file, and the directory of documents to the specific user.  This means evil people can't simply map a drive and copy the file.  It also means evil people can't just steal the hard drive and log on as an administrator and copy the file.  There's some caveats here, and a full study of EFS is recommended, but that's a different conversation. 

What EFS doesn't do is secure the file while "in flight".  If you email the database, copy it to a USB key, copy it to a network share you must remove the EFS flag.  So now how do you protect the database while in flight?

Use the SQL Server Compact File Encryption
Within the SQL Server Compact Connection String you can tell the engine to encrypt the datafile with a password.  By simply specifying a password, the database is automatically encrypted.  We used to support an encrypt = true name/value pair, but it was sort of silly to have a password without encryption, and encryption without a password is about as useful as putting a lock on a door but leaving the key in the lock (on the outside of the door).  So, we no longer use this name/value pair and will likely throw an error  in a future release if the engine sees it.  Today Compact just ignores it.

To turn on Encryption, simply set the Password like the following:

Data Source=|DataDirectory|\Localdatabase.sdf;Password=Foo

What level of encryption do you want?  
That turns out to almost be a silly question as well.  Does anyone want a somewhat secure encryption algorithm?  Of course not.  But, the evil people keep cracking the encryption algorithms, and the older operating systems don't actually support the newer algorithms.  Laxmi posted the different versions supported here.  For 4.0 we'll do the latest and greatest at the time we release. 

To set the extra bit, simply specify the Encryption Mode like the following:

Data Source=|DataDirectory|\Localdatabase.sdf;Password=Foo;Encryption Mode=Engine Default

This may be needed when creating a database on the server, and streaming it to a PPC 2003 device, or Win2k which uses the same algos as PPC 2003.

In an upcoming updated post on deploying database with scripts, and versioning databases, I'll show how you can incorporate this into your "Health Check" api to assure newly created databases are encrypted.

Steve

Comments

  • Anonymous
    May 13, 2008
    PingBack from http://windows.wawblog.info/?p=31884

  • Anonymous
    May 14, 2008
    Несколько раз проводил демонстрацию новой возможности SQL Server 2008 - Transparent Database Encryption,

  • Anonymous
    May 15, 2008
    I set the Encryption Mode=Engine Default but couldn't get it to work on windows 2000. Also, I have an app. that reads from the db file and opens a second program based on what's read. Sometimes that second program(that accesses the same DB file) crashes with a cannot read access error. I thought that multiple processes could access the same db file, no?

  • Anonymous
    May 16, 2008
    Hi Bydia, Win2k is actually PPC2003 Compat mode.  Technically 3.5 doesn't "support" Win2k, which is why we didn't say PPC2003/Win2k Compat.  The reasons behind not "supporting" Win2k are simply the typical test cost issue.  Since FX 3.5 doesn't support Win2k, we felt this was a reasonable decision.  In practicality, there's nothing specific about Win2k that won't work.  …except that you need to know Win2k = PPC2003 compat :) As for the multi-process issue, I’m not sure what problem you’re having as we do support multi-proc access.  Are you opening the file from two processes on the same logged in user account? Steve

  • Anonymous
    May 18, 2008
    ok, I had it set to Engine Default on w2k, guess that was not clear to me to use PPC2003 comp. mode.  Yes, I am opening the file from 2 processes with the same logged in user account.  I did not see any documentation that said I could not do it this way. So can I?

  • Anonymous
    May 18, 2008
    One more thing, I am just doing a read to dataset operation... in both the first app. and then then the second app. that crashes.

  • Anonymous
    May 19, 2008
    If you could send a repro, that would be helpful as from what you're saying, this should just work.  Since we're not doing full test passes on Win2k, it would be interesting to know if you're having the same issue on XP or Vista. Steve

  • Anonymous
    May 19, 2008
    Sorry, for not making myself clear. On the W2k system I just could not getting it working at all (but now only because of time).  The crashes have been on XP SP2/3 and W2003 SP2... and it is only 1 in 10 times.  Hard to reproduce, but I will try.

  • Anonymous
    June 02, 2008
    I am unable to catch the exception by using try..catch.  So it is much more serious. Hard to reproduce. But when it does happen, I have the option to send the crash data to Microsoft. If I do this and tell you the name of the program, are you able to get the data and determine it's problem?  If so then I will submit the crash data and let you know what to look for.

  • Anonymous
    August 05, 2008
    I would like to know if I can put the password in a connectring string variable to be built at run time.  The problem is that in the appconfig the password can be viewed if you explore the files of the computer.   If is possible to do this what is the sintax for the connection string? Thanks.