none
Issues With SQL 2005 Encryption

    Question

  • Are there any pitfalls i should look out for when using the encryption in SQL 2005?

    Tuesday, October 03, 2006 3:41 PM

Answers

  •   Here are the most common issues that probably you should be aware of before using encryption:

    • Encryption is not deterministic. This affects the ability to index encrypted data. I wrote an article describing this problem in detail as well as a few suggestions that may help you to solve this problem, you can find this article in my blog: http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx.
    • Performance. Encryption/decryption will consume some extra cycles. Unfortunately I don’t have any numbers to share here, but a suggestion would be to try to minimize the encryption/decryption operations to the minimum.
    • Key backup. In SQL Server 2005 there is no way to backup individual symmetric keys, you need to backup the whole database. As an alternative, you could use a passphrase-derived key in order to be able to recover the key without the need of recovering the whole database.
    • Symmetric keys are not schema-binded. Encryption is based on builtins, and the tables/columns are unaware of encryption; it is possible to drop a key by mistake. Avoid dropping any symmetric keys unless you are absolutely sure it is not needed anymore, and I strongly recommend to have a backup of your database (see point above) or using a passphrase-derived key to prevent data loss.
    • The space needed to store an encrypted column is larger than for the plain text. I wrote an article that explains this issue in detail in http://blogs.msdn.com/yukondoit/.

       I strongly recommend to read other encryption related articles in the following blogs:

    * Laurentiu Cristofor's blog (http://blogs.msdn.com/lcris/)

    * Raul Garcia's blog(http://blogs.msdn.com/raulga/)

     

      I hope this information will be useful, please let us know if you have any further questions.

     

      Thanks,

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, October 03, 2006 6:29 PM
  •   That is really strange. The key ring is session based, may it be possible that you are using 2 different sessions to open the key and to alter it? If not, I will appreciate if you can give us some more information to try to repro this problem (what client you are using, the script you are trying to run, etc.)

     

      BTW. You can run SELECT * FROM sys.openkeys to see the keys opened in the key ring.

     

      Thanks a lot,

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, October 04, 2006 5:58 PM

All replies

  •   Here are the most common issues that probably you should be aware of before using encryption:

    • Encryption is not deterministic. This affects the ability to index encrypted data. I wrote an article describing this problem in detail as well as a few suggestions that may help you to solve this problem, you can find this article in my blog: http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx.
    • Performance. Encryption/decryption will consume some extra cycles. Unfortunately I don’t have any numbers to share here, but a suggestion would be to try to minimize the encryption/decryption operations to the minimum.
    • Key backup. In SQL Server 2005 there is no way to backup individual symmetric keys, you need to backup the whole database. As an alternative, you could use a passphrase-derived key in order to be able to recover the key without the need of recovering the whole database.
    • Symmetric keys are not schema-binded. Encryption is based on builtins, and the tables/columns are unaware of encryption; it is possible to drop a key by mistake. Avoid dropping any symmetric keys unless you are absolutely sure it is not needed anymore, and I strongly recommend to have a backup of your database (see point above) or using a passphrase-derived key to prevent data loss.
    • The space needed to store an encrypted column is larger than for the plain text. I wrote an article that explains this issue in detail in http://blogs.msdn.com/yukondoit/.

       I strongly recommend to read other encryption related articles in the following blogs:

    * Laurentiu Cristofor's blog (http://blogs.msdn.com/lcris/)

    * Raul Garcia's blog(http://blogs.msdn.com/raulga/)

     

      I hope this information will be useful, please let us know if you have any further questions.

     

      Thanks,

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, October 03, 2006 6:29 PM
  • Yes, Thank you. I'm having an issue with altering the Symmetric Key. I open the key By running Open Symmetric Key EncryptTestbase decryption by Password = 'mypassword' . it runs sucessfully and then I run the Alter Syntax to alter the Key and it tells me The key 'EncryptTestbase' is not open. Please open the key before using it. Whats up with that. Thanks for your help.



    Jeff Welch

    Wednesday, October 04, 2006 4:27 PM
  •   That is really strange. The key ring is session based, may it be possible that you are using 2 different sessions to open the key and to alter it? If not, I will appreciate if you can give us some more information to try to repro this problem (what client you are using, the script you are trying to run, etc.)

     

      BTW. You can run SELECT * FROM sys.openkeys to see the keys opened in the key ring.

     

      Thanks a lot,

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, October 04, 2006 5:58 PM
  • Yes, but the feilds are blank. Doesn't show any keys. As if no keys were created. Thank you



    Jeff Welch

    Wednesday, October 04, 2006 6:02 PM
  •   Can you help us to reproduce the problem? I would need to know what client you are using (i.e. sqlcmd, MS SQL Server Management Studio (query), etc.) and if possible the script you used. Any additional information you think is relevant will also be greatly appreciated.

     

      Thanks a lot,

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, October 04, 2006 6:12 PM
  • I'm using Create symmetric Key EncryptTestbase  with Algorithm = AES_128 encryption by Password = 'password' to create the key. to Open the key I use Open symmetric Key EncryptTestbase Decryption By Password = 'password'. and to Alter the Key I'm using

    Alter Symmetric Key EncryptTestbase
                Add Encryption by Password = 'new password'.

    Wednesday, October 04, 2006 6:13 PM
  • MS Management Studio is the client sorry

    Wednesday, October 04, 2006 6:14 PM
  • The database I'm doing all the testing on a test database and it is a copy of the real database. i wanted to test the encryption on real data without using the main database. thank you for your help.



    Jeff Welch

    Wednesday, October 04, 2006 6:18 PM
  • I'm just learning SQL and i'm very green but hope to become more seasoned in time. Thank you.



    Jeff Welch

    Wednesday, October 04, 2006 6:19 PM
  • Heres the Message error: Msg 15315, Level 16, State 1, Line 1

    Wednesday, October 04, 2006 6:24 PM
  • Just to clarify, have you tried calling "select * from sys.openkeys" right after your open key statement (before your alter)?  Was this result blank?

    For the database, are you backing up the database from your main server and restoring this onto your test server?  If so, are these two machines in different time zones?  If you are restoring a database in two different time zones, you may get errors trying to use symmetric keys (depending on what version of SQL Server you are currently running).

    Sung

    [edit] - corrected syntax error on sys.openkeys

    Wednesday, October 04, 2006 9:10 PM
  • I opened the key then used the Alter Statement. Thanks

    Thursday, October 05, 2006 2:53 PM
  • I imported the data thru the import tool inside the Managment Studio. I ran it from the server not from a workstation.However i did not put and _ between the open and keys. should i try that? i haven't seen it typed like that. Thank you

    Thursday, October 05, 2006 2:58 PM
  • From the Managment Studio I open the Key and then run the statement Select * From sys.openkeys. and it does not show anything. as if no keys were ever created. I ran both statements from the server not from my workstation. Thank you for your help.



    Jeff Welch

    Thursday, October 05, 2006 3:44 PM
  • I also opened the Master Key and the command completed Successfully but I run Select * From sys.openkeys and shows nothing. as if no keys were created. I also have gone under Security and looked at Symmetric keys and I can see the Key I created. Thank you.



    Jeff Welch

    Thursday, October 05, 2006 3:53 PM
  • If I run the statement select * From sys.symmetric_keys. it shows my master Key and the Symmetric Key. and all the details. Thank you for your help.

    Thursday, October 05, 2006 6:01 PM
  • Sorry, my mistake, it should be sys.openkeys (corrected in original post above)

    As a quick summary, are these the steps you take?

    1. export database from main system (computer 1)

    2. import database to test system (computer 2)

    3. computer 1 and computer 2 are in the same time zone

    4. on the test system (computer 2), attempt to create a new symmetric key

    "create symmetric key..."

    5. attempt to open symmetric key

    "open symmetric key..."

    6. check sys.openkeys, this returns zero rows

    7. attempt to alter key, get an error

    Does that look right?  Please let me know if I'm missing any steps that you may have done.  Can you confirm that the symmetric key was actually created successfully?  Do you see the key in the sys.symmetric_keys view? ("select * from sys.symmetric_keys")

     

    Sung

    Thursday, October 05, 2006 8:53 PM
  • But you don't get any errors when you call "open symmetric key"?  Can you possibly provide the script you are using (please first remove any passwords you may have).  We can try to run this on our machines to repro this issue.

    Thanks,

    Sung

    Thursday, October 05, 2006 8:55 PM
  • Same server I created a new database and Imported it in to live data from the original database.
    Friday, October 06, 2006 3:10 PM
  • Like i said in previous post i'm just learning and i'm very green. script? would that be .....

    Open Symmetric Key EncryptTestbase Decryption By Password = 'mypassword'

    Select * From sys.openkeys

    once I run this it is blank but if i go under security and Symmetric Keys  can see the key. 'EncryptTestbase'

    Friday, October 06, 2006 3:19 PM
  • If i run the statement

    select * from sys.symmetric_keys

    I can see the Symmetric Key and the Master Key. Thanks

     

    Jeff Welch

    Friday, October 06, 2006 3:23 PM
  • Can you please post how you create the symmetric key?

    Thanks,

    Sung

    Friday, October 06, 2006 6:46 PM
  • Hi Jeffrey.

    Please post each statement that you issued, and the results that you obtained. There is no issue with using keys the way you described, so you're probably just missing something simple.

    Here are the steps I would use to add a new password encryption to a key:

    -- create a database for our test
    create database test_encryption

    -- switch context to that database
    use test_encryption

    -- create a key; note that no master key is needed because the key is encrypted by password
    create symmetric key skey with algorithm = triple_des encryption by password = 'SimplePassword01'

    -- open the key for use
    open symmetric key skey decryption by password = 'SimplePassword01'

    -- verify that the key was opened - a row should be returned for our key
    select * from sys.openkeys

    -- now add another password encryption to the key - this command should succeed
    alter symmetric key skey add encryption by password = 'SimplePassword02'

    -- cleanup
    use master
    drop database test_encryption

    You can execute these steps in Management Studio or using sqlcmd, just make sure you're executing all commands in the same connection.

    Let us know if you hit any errors while executing these steps.

    Thank you
    Laurentiu

    Friday, October 06, 2006 9:41 PM
  • Sorry took so long to get back to you... Create symmetric Key EncryptTestbase  with Algorithm = AES_128 encryption by Password = 'password' to create the key.
    Tuesday, October 10, 2006 3:15 PM
  • Hi,

    I'm wondering if the fact that I have created a Master Key w/password and then created a Symmetric Key w/password. If SQl is in fact opening the Symmetric Key but it's looking at the Master Key and seeing that it's closed. Even thought I'm trying to Alter the Symmetric Key . So I should drop the Master Key? thank you for your help.

     

    Jeff

    Tuesday, October 10, 2006 3:24 PM
  • So did the steps above work for you? If yes, then please post the commands you executed and the errors returned if you need help finding out where the problem is. Your description isn't detailed enough to figure out where the problem is. Even with the master key created, you shouldn't have any issue, but I wanted to simplify the picture for you.

    Thanks
    Laurentiu

    Tuesday, October 10, 2006 5:49 PM
  • Sorry, let me start from the begining. I have a live SQl database that i have people adding data to. I want to encrypt the database before I go live on the web. So to test and learn about the encryption I created a new databse on the same local server and imported the data from the live local data and put it in the new database I created. Once I verified that all the data was there I created a master key by running the statement

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'
    It was successful. Then I ran the statement 
    Create symmetric Key EncryptTestbase  with Algorithm = AES_128 encryption by Password = 'otherpassword' 
    I wanted to test the alter statement. So I Opened the symmetric key and it was successful. Then I ran the 
    alter statement I needed to open the key first. If i run the statement 
    SELECT * FROM sys.openkeys 
    after I open the the symmetric key it shows no open keys. all feilds are blank. as if i never ran the open statement.
    Thats my problem. I can't alter the open key. If I run the to view the encryption keys it shows me the 
    Master Key and the Symmetric Key 
    Thanks
     
    Jeff
    Tuesday, October 10, 2006 6:19 PM
  • Hey Jeff,

    We're trying to figure out what caused this error as we haven't seen anything like this before.  From what you describe, this should work as we have already explicitly tested this scenario.  Can you please try to either execute Laurentiu's script line by line to see if this resolves your problem or provide the exact SQL syntax you are using (again, with private info removed).

    I would like to try and run your script on one of our test machines to try and reproduce this issue.

    Thanks,

    Sung

    Tuesday, October 10, 2006 9:05 PM
  • Hey Guys,

    to create the Symmetric Key I used the Statement ....

    Create symmetric Key EncryptTestbase with Algorithm = AES_128 encryption by Password = 'password'

    I will try Laurentiu's script. to see if that fixes it. I will create another key using his script. From what i can see you don't want any data in the new database correct.

     

    Wednesday, October 11, 2006 3:19 PM
  • Laurentiu ,

    Here are the steps I did as per your request.

    create database test_encryption

    Command(s) completed successfully.

    Create Symmetric Key Testkey With Algorithm = Triple_Des

    Encryption By Password = 'mypassword'

    Command(s) completed successfully.

    Open Symmetric Key testkey Decryption By Password = 'k3y1sluk3'

    Command(s) completed successfully.

    Select * From sys.openkeys . no results. I just can't figure out what I'm doing wrong. could it be a setting I have set wrong.

    If I run the statement

    Select * From sys.symmetric_Keys I see the key.

     

    Wednesday, October 11, 2006 3:38 PM
  • Do you have Service Pack 1 installed?

    One thing you missed in the steps above is to switch context to the newly created database and attempt to create the key in it. Also, the password you used for OPEN is different from the one you used for CREATE - have you copied in this message the exact commands you executed or did you write them down from memory?

    Thanks
    Laurentiu 

    Wednesday, October 11, 2006 6:24 PM
  • But if I click on the new database and then open a new quiry. Wouldn't it create the key for that database?

    Thanks for your help.

    Jeff

    Wednesday, October 11, 2006 6:33 PM
  • On the password I type the correct one in there. I just forgot to make them match in my message. Sorry

     

    Jeff

    Wednesday, October 11, 2006 6:36 PM
  • Even if I use the statement.

    use test_encryption

    and recreate the key I still get no results. when I run the statement

    Select * From sys.openkeys

    Wednesday, October 11, 2006 6:47 PM
  • I do have Sp1 on the SQL server. thanks

     

    Jeff

    Thursday, October 12, 2006 5:01 PM
  • Based on the information you provided so far, you seem to follow the right steps. I am not aware of any issue causing the behavior you describe.

    Do you have more than one computer available? Have you tried this operation on a different machine?

    I suggest you open a report at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68 and then we can continue to investigate this issue through that channel.

    Thanks
    Laurentiu

    Thursday, October 12, 2006 5:37 PM
  • Yes I do. I'll try it on that one. Ok I'll try that to. Thank you for all you help.

     

    Jeff 

    Thursday, October 12, 2006 6:07 PM