I am investigating methods of encrypting data in a large number of databases. It seems to me that encrypting by passphrase would be optimum because it would prevent me from having to create all those database and symmetric keys on the various databases (there wil lbe dozens of them). Can anyone give me any advice as whether or not using encryption by passphrase is a good secure method of encrypting sensitive data in a large-scale production environment? I would most likely create a unique passphrase for each database and pass it to a stored procedure as an imput parameter.
Thanks,
Al
If you encrypt using passphrases, your application will have to manage the passphrases that you use, and it will have to store them securely. Depending on how you pass the passphrase around in your application, you might allow a SQL administrator to figure it out by using SQL Profiler and tracing through your application's TSQL requests.
You can use passphrases if you really want to, but they're more difficult to manage. By using the key encryption mechanisms in SQL Server, you can minimize the work you need to do for managing passwords.
Thanks
Laurentiu
Hey, Laurentiu. Thanks for the reply. Your comments make sense, but I have some concerns with the encryption mechanism. First of all, the process and syntax for encrypting/decrypting data seems to be quite a bit more involved, given the fact that you need to create a database master, certificate and symettric keys for each database (remember, I will have a large number of databases). Even if much of this is managed in the background (as you mentioned), it seems I would have to keep track of a lot of things. I am also troubled by the thought that I might have to move my database to a new server at some point. Am I incorrect in assuming this could have a dire affect on my ability to decrypt data on the new server due to the fact that the system master key is a server installation-based item?
Al
|||Regarding your concern for moving a DB from one server to another, the builtin key hierarchy supports this scenario. The reason why a DB master key (DBMK) requires to be protected at least by one password is to allow the recovery of the DBMK when the DB is being moved from one server to another, or when a backup is being recovered. For this reason it is very important to keep track of the DBMK password and to keep such passwords in a secure place.
If you use the builtin key management infrastructure, most likely your application will still need to manage the DBMK password for each database, but the main difference would be that you will only need to access this password when you create the key and when you move the DB from one server to another, and not every time you try to encrypt/decrypt the data. Because the DBMK passwords won’t be needed for regular operations, you can store them out of band in a safe place.
Another advantage of using the builtin key infrastructure is that when creating/modifying the master key using the DDL directly the password will be automatically removed from the trace logs.
-Raul Garcia
SDE/T
SQL Server Engine
|||Ok, please let me know if I am getting this right...
1) I create a DBMK for each database on my SQL Server instance using:
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'.
2) I backup the DBMK for each database to some offline storage using:
BACKUP MASTE KEY TO FILE='filepath' ENCRYPTION BY PASSWORD='password'.
3) If I change servers, then I just have to run the following for each database in order to restore the same DBMK as I had on the original server:
RESTORE MASTER KEY FROM FILE='filepath' DECRYPTION BY PASSWORD='password' ENCRYPTION BY PASSWORD=password'.
This seems straightforward. Then I can create a symmetric key for each database which will be used in the encrypting/decrypting of data. Am I right in assuming that after a server switch, as long as I use the same DBMK and symmetric key creation attributes (passphrase, algorithm, IV and password), then the newly created symmetric key should work to encryp/decrypt my data on the new server?
|||
Creating a backup of the master keys (DB master keys and service master key) is a really good practice I always try to encourage. The MK backup main purpose is for disaster recovery, and many people also use it to to replicate the same master key in multiple machines/DBs to simplify offline key maintenance.
For the scenario you describe in particular (detach a DB, and reattach it in a different server) there is an ever simpler solution. After attaching the DB in the new server, the DBMK will still be present in the DB itself, but it cannot be automatically used as it is protected by at a password, but we have lost the old server master key protection. We need to explicitly open the DBMK with the correct password and add the encryption by the new server service master key.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
After the DBMK has been protected by the new server MK, it can be automatically used again.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, Raul. That all makes a lot of sense. One last question. It seems to me that when you use the encryp/decrypt by key functions, you always need to specify the password within the stored procedure. If an unauthorized person gets access to your db and its tables/procedures (hense the reason for encrypting data in the first place), doesn't the existence of these passwords in the stored procedures essentially eliminate the security you have by encrypting the data?
-Al
|||You don't need to specify a password with the encryptbykey/decryptbykey functions. The keys just have to have been previously opened.
If you use the key protection hierarchy in SQL Server, you don't need to specify passwords at all, except in the scenario that Raul explained previously, for restoring the service master key encryption of a database master key, after moving a database from one server to another.
If you choose to not use the key protection hierarchy and you have symmetric or asymmetric keys protected by passwords, you can restrict the need to specify the password to the time when you open the keys, which should not happen within a stored procedure. Note that while the password management is similar to the passphrase management, you only need the password for opening the key - you don't need it again for encryptions and decryptions, as it would be the case with the encryptbypassphrase use, so the password needs to be specified less often. You could, for example, specify the key password within your application as part of the login process to the database (the application would login and open the key using the password you specified). You should not hardcode passwords in TSQL procedure or in your application, but have the user specify them instead.
Thanks
Laurentiu
<<You don't need to specify a password with the encryptbykey/decryptbykey functions. The keys just have to have been previously opened.>>
I'm not an expert in TSQL, but I was under the impression that you had to have syntax like the following in the stored procedure:
OPEN SYMMETRIC KEY keyname DECRYPTION BY ASYMMETRIC KEY asymmetricName WITH PASSWORD='asymmetric password'
select cast(decryptBykey.....
CLOSE SYMMETRIC KEY keyname
How else (and where) would you open the symmetric key, if not within the stored procedure that uses it?
-Al
|||The syntax you are using is designed for the last scenario Laurentiu described, where the asymmetric key/certificate private key is not protected by the DBMK, but instead it is protected by a user provided password.
<< If you choose to not use the key protection hierarchy and you have symmetric or asymmetric keys protected by passwords, you can restrict the need to specify the password to the time when you open the keys, which should not happen within a stored procedure. …>>
In this case, as the password protecting the private key is never stored in the DB, there is no way for the system to automatically open it therefore the caller must provide the password every time. If your application works as a middle-tier between your users and SQL Server and you have control over the session, you can explicitly open the key at the beginning of the session, before giving control to the app user.
On the other hand, if you don’t want to use passwords or you don’t have control over the session, you can use the full key management infrastructure. I wrote a small demo to demonstrate this feature.
CREATE DATABASE db_Demo
go
USE db_Demo
go
-- Create the master key
-- protected by a password
--
-- NOTE: The only time in this demo where
-- a password is needed is for this DBMK creation
-- The rest of the objects will be protected by
-- other objects in teh key hierarchy model,
-- having the DBMK as the root for the DB objects,
-- and the service master key (SMK) as the root for the instance
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
go
-- Create a certificate
-- protecting the private key with the DBMK
-- No password is needed
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
go
-- Let's take a look to the MD
-- Notice that the cert private key
-- is protected by the DBMK
SELECT name, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name = 'cert_demo'
go
-- Now let's create a symmetric key
-- and protect it with this cert
CREATE SYMMETRIC KEY symkey_demo
-- You can use AES in Windows 2003 machines
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
go
-- Open the symmetric key using the cert
-- the cert private key is protected by the DBMK
-- As the DBMK is protected by the SMK,
-- it can be used automatically.
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
go
-- Create a simple SP to test it
CREATE PROC sp_demo @.PlainText varchar(100)
AS
DECLARE @.blob varbinary(256)
SET @.blob = EncryptByKey(
key_guid( 'symkey_demo' ),
@.PlainText )
SELECT
@.blob as 'encrypted blob',
convert( varchar(100), DecryptByKey( @.blob )) as 'recovered plaintext'
go
-- Let's try our SP
-- We expect to see the encrypted blob and
-- the original value
EXEC sp_demo 'Hello World!'
go
-- after we close the symmetric key...
CLOSE SYMMETRIC KEY symkey_demo
go
-- ... our SP won't be able to encrypt/decrypt anymore
EXEC sp_demo 'Hello World!'
go
-- Create a store procedure that takes care of
-- opening and closing the symmetric key
CREATE PROC sp_demo2 @.PlainText varchar(100)
AS
DECLARE @.Key_opened int
BEGIN TRY
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.Key_opened = 1
END TRY
BEGIN CATCH
PRINT 'Failed to open the symmetric key'
PRINT error_message()
END CATCH
if( @.Key_opened = 1 )
BEGIN
DECLARE @.blob varbinary(256)
SET @.blob = EncryptByKey(
key_guid( 'symkey_demo' ),
@.PlainText )
SELECT
@.blob as 'encrypted blob',
convert( varchar(100), DecryptByKey( @.blob ))
as 'recovered plaintext'
-- NOTE: Make sure to always close the symmetric key
-- In this case teh above calls will not raise
-- an error that may abort the SP and leave
-- the key opened for teh session
-- if any such call may be present,
-- you can use TRY/CATCH to help you close
-- the smmetric key in case of error
CLOSE SYMMETRIC KEY symkey_demo
END
go
-- Let's create a user to test our app
CREATE USER AppUser WITHOUT LOGIN
go
-- Grant our user permission to execute the app
GRANT EXECUTE ON sp_demo2 TO AppUser
go
-- Let's give it a try ...
EXEC ('EXEC sp_demo2 ''Hello World!''')
AS USER = 'AppUser'
go
-- ... it failed for 2 reasons:
-- 1) We need VIEW DEFINITON permission on
-- the symmetric key. We can potentially
-- give this permission to all app users
-- 2) We also need CONTROL permission on
-- the certificate protecting the symmetric key,
-- and most likely we don't want to grant
-- such elevated permission to this user!
-- What can we do?
-- We can use digital sigantures for this case
-- and grant permissions via the siganture
-- We can actually grant both permissions needed
-- to the siganture
CREATE CERTIFICATE cert_signing
WITH SUBJECT = 'signing App'
go
CREATE USER cert_signing
FOR CERTIFICATE cert_signing
go
GRANT VIEW DEFINITION ON
SYMMETRIC KEY::symkey_demo
TO cert_signing
go
GRANT CONTROL ON
CERTIFICATE::cert_demo
TO cert_signing
go
ADD SIGNATURE TO sp_demo2 BY CERTIFICATE cert_signing
go
-- Let's give it another try ...
EXEC ('EXEC sp_demo2 ''Hello World!''')
AS USER = 'AppUser'
go
-- ... it works!!!
-- And our SP doesn't require any passwords
-- Now let's take a quick look
-- at the opened keys
-- it should be empty.
SELECT * FROM sys.openkeys
go
--
-- End of demo
|||Thanks for the help. I see what you mean. The only thing I wasn't sure about was using a certificate instead of an assymetric key (and password) to do the encrypting of the symmetric key. Don't certificates need to be created with an expiration date, and wouldn't using them cause me to have to recreate the certificates periodically?|||
Hey, just as a quick aside. Since I am new to Encryption in SQL Server 2005, I have encountered a few issues when trying to implement what I need to do and have been reading a lot in these forums and elsewhere. One of the issues I have encountered is the idea of searching on an encrypted field. I made a post under the T-SQL area called "filtering on encrypted fields", but no one seams to have any ideas. I did more research and found your articles on hashing the clear text with MAC and storing in a new column. This would work for direct searches, but wouldn't seem to work for "LIKE" searches. If this is the type of search I need to do, is the only alternative to include the decryptbykey function in the WHERE clause? I was wondering if there was a way to use temportary tables, or something to that effect to do this kind of search without incurring the second function call hit.
Additionally, at what table size do I really need to worry about the additional function call. If my table is only on the order of 10000-100000 records, is this that big a concern?
-Al
|||See this link for a more in-depth discussion of searching encrypted data: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx. Your question is discussed in the comments.
Short story is that LIKE searches cannot be performed efficiently on data that is encrypted with a strong algorithm, you will have to do a full table scan. A decryption of 100000 entries could take a couple of seconds - the result might vary depending on server load and machine specifications.
Thanks
Laurentiu
For the question of certificates vs asymmetric keys, see http://blogs.msdn.com/lcris/archive/2006/03/13/550904.aspx. For encryption/decryption purposes, certificates do not expire.
Thanks
Laurentiu
Thanks for all your help, Raul and Laurentiu.
-Al
