Tuesday, March 04 2014
Keywords: SQL Server database, external hard disk drive, HDD, USB Key, flash drive, encryption, TDE, transparent database encryption
Create a SQL Server database on an external disk and secure it with Transparent Database Encryption
In this article, I'm going to show you step by step:
1) How to create and use a SQL Server 2012 database that is on a USB stick.
2) How to encrypt your database using Transparent Database Encryption (TDE). So that if your key gets lost or stolen you don't have to worry about a thing.
3) How to use your encrypted database on all the different SQL Server instances in question. Remember that a TDE encrypted database cannot be simply attached to a different instance...after all that is what the TDE encryption is there for! I will show you how to get it done quickly and efficiently.
On thing that I will not discuss in details is disaster recovery...and by that I mean that you should have proper backups of your removable drives...in case they get lost, stolen or damaged.
The idea of mounting a database on a USB key or external HDD was unthinkable before...due to many valid factors:
*) Most DBMSs did not even allow creating a database on an external/removable storage area.
*) External/Removable drives were too expensive.
*) External/Removable drives had too small of a capacity, to be a contender.
*) External/Removable drives were too slow, to be taken seriously.
*) External/Removable drives were not deemed secure by their very nature...easy to steal...easy to lose
The above concerns were valid in mid 1990s when I started to work on databases, and most points are still valid at the time of writing (2014)...although Firewire, DAS, or eSATA stand out amongst the group and are useful in some cases.
Another alternative for some folks would be to use a UNC \\server\sharedfolder path to store their database files. There are some database integrity risks involved with doing so...I would not personally use this feature...but for now just wanted you to know that this is yet another available option.
There are three scenarios I have known of, where you would want to store your SQL Server database on a removable storage device...note that I only condone doing this this for personal databases and only during the development phase...the idea of doing such thing for a production database would give any DBA the shivers. Any USB connected device is going to perform so poorly that I'm sure it would be quite unbearable.
A) You are working on a personal or school project...and you continue working on it while you move from one environment to another...per example from Home to Work #1 then to School and then finally to Work #2. You do not wish to leave the database you are working on, on any of those four environments, but rather keep it on your USB stick...you also don't want to use Cloud to get this done.
B) Working on a personal database with ultra sensitive information. You just don't trust leaving it anywhere alone by itself.
C) The team works on the database during 9-5, and then removes the external disk and places it into a secure vault at night...just in case.
Step 1: Create a new database and save the database files on a removable storage. Note that you could also move the files pertaining to an existing database to a USB drive...to move an existing database refer to this article.
I connected a USB stick to my workstation at home...it received the drive letter K.
I launched SQL Server 2012, and created a database called MyPrecious...easily placing its data and log file on k:\db folder at creation time...since SQL Server 2012 gracefully allows us to do so.
Here is one very important tip: you must DETACH the database once you are done using it for the day...and before disconnecting your removable disk. Once the database has been properly detached, it can then be attached to the same or even a different instance of SQL Server. Failure to detach/attach a database could put the integrity of your database at risk. If you forgot to detach the database and you have already shut down your PC or have put it to sleep, then resume Windows again, and properly detach your database.
It's also a good idea to verify the TDE Enctyption Status of your SQL Server by running the code found in "TDE Verify Encryption Status.sql".
Step 2: Encrypt your database with Transparent Database Encryption (DTE).
Follow the easy step by step guidelines in "TDE Encrypt a Database.sql".
During this procedures I will show you how to create a master key, a certificate, a private key and how to backup your Certificate. You will need these backup files in order to attach your encrypted databases on another SQL Server later on.
Step 3: Detach the database, and safely eject or remove the USB drive from the first (source) server.
To peform steps 3, 4, 5 & 6, follow the easy step by step guidelines in "TDE Detach and Attach Encrypted Database to Another Server.sql".
Step 4: Attach the removable drive to the second (destination) server.
Step 5: Create an identical master key and certificate on the second (destination) server.
I faced an interesting situation once when I attempted to create a master key on a server...it gave me an error message stating "Error 15118: The password does not meet Windows policy requirements because it is not complex enough". You could bypass that problem in two ways: use a more complex password, or try the fix I wrote in this article.
Step 6: Attach the database to the new server, from the USB key...since the database had already been encrypted...you don't need to perform the encryption process on it again.
Step 7: Optional step: if at any time you would like to remove encryption from the database/server...then follow the easy step by step guidelines in "TDE Remove Encryption.sql".
Written by Ramin Haghighat