locked
Encryption in SQL Server 2005 RRS feed

  • Question

  • Hi I am quite new to this topic.

    I hit some problems while trying to experiment with the built-in encryption provided by mssql 2005.

    I am currently running mssql 2005 on Windows XP SP2 for testing. I have read somewhere that actually Windows XP SP1 already support AES encryption so I am wondering does it still does not apply to mssql 2005? I had hit problems with not being able to create the key.

    I would also like to check can the keys be transport out of the server and then still be able to be called from the original server? Our objective is to protect the data from the database adminstrator so we are actually thinking to remove the keys from the server so that the database administrator does not have access to it but at the same time still being able to encrypt and decrypt the data at rest. But is it also true that the transaction logs would be able to track the keys? If so, am I able to customise the log such that it does not log the keys? I'm sorry if I do sounds confusing and illogical here.

    Thanks in advance.

    Jingshi
    Wednesday, April 1, 2009 8:18 AM

Answers

  •   The sample code you provided seems to work for me, both on SQL Server 2005 & SQL Server 2008, I am not sure what could be the cause of the error. I noticed a couple of potential code bugs that, depending on the circumstances, may be affecting.

    create database retest
    go
    
    use retest
    go
    
    create table retest
    ( username varchar ( 50), 
    --password varchar ( max )) 
    -- * varbinary/varchar(max) are not really supported by the encryption/decryption APIs,
    --   I would recommend explicitly using varbinary() up to 8000 bytes
    -- * Using varchar/nvarchar to store binary data is discourage as the binary data
    --   may not represent a valid string, or may be transformed as a sides effect by code trying to interpret it
    password varbinary ( 8000 )) 
    go
    
    select * from sys.asymmetric_keys 
    
    create master key encryption by password = 'p@ssw0rd' ; 
    go
    
    create certificate passwordCert with subject = 'Password' ; 
    go
    
    create symmetric key passSym
    -- with algorithm = triple_des
    -- Whenever it is possible, use AES family of algorithms
    with algorithm = AES_256
    encryption by certificate passwordCert
    go
    
     
    open symmetric key passSym
    decryption by certificate passwordCert
    go
    
    
    create view retest_view
    as
    -- convert(varchar, ..) may truncate the output if the plaintext is > 30 characters
    select username, convert ( varchar(128), decryptByKey( password)) as password
    from retest
    go
    
    CREATE trigger enpass on retest_view
    instead of insert 
    as
    begin
    -- supress the trigger insert row count
    set nocount on
    insert into retest ( username, password) 
    --select username, encryptByKey( Key_guID( 'passsym' ), cast ( password as varbinary ( max )))
    -- the correct casing for the key name is "passSym".
    -- If your system is case sensitive, this call may fail
    -- There is no need for an explicit cast.
    select username, encryptByKey( Key_guID( 'passSym' ), password ) 
    from inserted
    -- reenable the trigger insert row count
    set nocount off
    end
    go
    
    
    Insert into retest_view values('test', 'password_for_test')
    go
    
     
    select current_timestamp 
    go
    select * from retest_view
    go
    select current_timestamp 
    go
    


     Besides these small changes, I would recommend the following article on this topic, the examples there may be useful: http://blogs.msdn.com/raulga/pages/589691.aspx

      Please let us know if you were able to make any progress or if you are still experiencing the EncryptByKey builtin always retuning null.

      -Raul Garcia
       SDE/T
       SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 3, 2009 10:24 PM

All replies

  • Hi,

    The following reference provides details of the Encryption Hierarchy for SQL Server 2005.

    http://msdn.microsoft.com/en-us/library/ms189586(SQL.90).aspx

    The following reference provides examples on how to use encryption in SQL Server 2005:

    http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx


    The creation of the various keys, service master key and database master key are not logged in the transaction logs becuase they are not transactional activity but rather are configuration settings.

    Hope this helps.

    Cheers,
    John Sansom (www.johnsansom.com) | DBA for Santech Solutions (www.santechsolutions.co.uk) Expert SQL Server Consultants
    • Proposed as answer by John Sansom Wednesday, April 1, 2009 12:42 PM
    Wednesday, April 1, 2009 12:39 PM
  • I would also like to check can the keys be transport out of the server and then still be able to be called from the original server?

    See the EKM feature.

    Our objective is to protect the data from the database adminstrator so we are actually thinking to remove the keys from the server so that the database administrator does not have access to it but at the same time still being able to encrypt and decrypt the data at rest.
    Removing the keys from the server may protect the keys from the administrator, but keep in mind that if the data is still being processed in decrypted form inside the server, it still is vulnerable to a database administrator during that time.

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 1, 2009 9:24 PM
  • Hi

    Thanks for your reply. Actually we are worried when the keys are in used instead of when the keys are created. Is is possible that the logs are being set to log only what we want it to log?
    Thursday, April 2, 2009 12:47 AM
  • Hi

    Thanks for your reply.

    If so, could I check whether there are other ways to prevent the database administrator to view the sensitive information inside the database? I've read around and couldn't really find a solution.


    Also, I was actually trying to use views and triggers to do encryption at rest. Strangely, the method could only work with asymmetric keys but not symmetric keys.

    Below are my codes:

    <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:none; mso-hyphenate:none; text-autospace:ideograph-numeric; font-size:12.0pt; font-family:Arial; mso-fareast-font-family:Arial; mso-fareast-language:EN-US; mso-bidi-language:EN-US;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    use customers

    go

     

    create table customers

    (username varchar (50),

      password varchar ( max ))

     

    -- create master key

    create master key encryption by password = 'p@ssw0rd'

    go

     

    -- create certificate

    create certificate passwordCert

    with subject = 'Password' ;

     

    -- create asymmetric key

    create asymmetric key passwordEncrypt

    with algorithm = RSA_1024

     

    -- create view to let users use

    create view customers_view

    as

    select username, convert ( varchar ( max ), decryptByAsymKey(AsymKey_ID( 'passwordEncrypt' ), password) ) as password

    from customers

    go

     

    --create a trigger to write the data into the table instead of into the view

    create trigger encr_passwords on customers_view

    instead of insert

    as

    begin

    insert into customers (username, password)

    select username, convert ( varbinary ( max ), encryptByAsymKey(AsymKey_ID( 'passwordEncrypt' ), password))

    from inserted

    end

    go

     

    insert into customers_view

    values ( 'John' , 'password123' )

    go

     

    select * from customers_view

     

    select * from customers



    This works perfectly fine for me. But when I try to use symmetric keys, I would be return null values. Anyone knows why is it so?

    Thanks alot.
    Thursday, April 2, 2009 12:57 AM
  • Hi,

    On a side note, I must admit that I am struggling to see why you would want to limit the access that your DBA has to the platform that they are to be responsible for. There should be, in my opinion, at least one person that has full sysadmin rights to your SQL Server Instance, so that it may be managed effectively.

    If you do not or cannot trust a DBA, then you should not employ them, period. If you are concerned about compliance with for example PCI or DSS regulations etc. , all professional DBA's should be well versed in these areas and conduct their behaviour accordingly.

    Were I to be offered an opportunity to work on a project where the client was not prepaired to provide me with the access required in order to my job, I would take this personally as a statement of disstrust and turn down the project.

    It sounds as though there may be trust issues at work here and I would suggest that these be addressed by your business decision makers, before you decide upon and devise a solution.


    John Sansom (www.johnsansom.com) | DBA for Santech Solutions (www.santechsolutions.co.uk) Expert SQL Server Consultants
    Thursday, April 2, 2009 6:20 AM

  • This works perfectly fine for me. But when I try to use symmetric keys, I would be return null values. Anyone knows why is it so?


    Hi,

    Would you be able to post the source code that you are refering to here please.

    Many Thanks,

    John Sansom (www.johnsansom.com) | DBA for Santech Solutions (www.santechsolutions.co.uk) Expert SQL Server Consultants
    Thursday, April 2, 2009 6:23 AM
  • This works perfectly fine for me. But when I try to use symmetric keys, I would be return null values. Anyone knows why is it so?

      When builtins fail the typical behavior is to return null. In the case of EncryptByKey, the most common cause for failure is to use a key that has not been opened (http://msdn.microsoft.com/en-us/library/ms190499.aspx). You can take a look to the opened keys using the sys.openkeys DMV (http://msdn.microsoft.com/en-us/library/ms365811.aspx).

      If opening the SYMMETRIC KEY does not resolve the problem, please share the the code with us as John suggested, we should be able to find the root cause of the failure.

      I hope thsi information helps,

      -Raul Garcia
       SDE/T
       SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 2, 2009 6:41 PM
  • If so, could I check whether there are other ways to prevent the database administrator to view the sensitive information inside the database? I've read around and couldn't really find a solution.
    That's because the only simple solution is to not make an untrustworthy person the administrator of your database server. I suggest you also read these posts:

    http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
    http://blogs.msdn.com/lcris/archive/2007/09/24/security-and-copy-protection.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 2, 2009 11:52 PM
  • Regarding the question about DBA, as our company is very much into security, we would actually like to explore into the area. We are not wanting to come out with a solution, just wanting to research to find whether is there such a possibility. Sorry for any misconceptions.


    Here is the code that I used for the symmetric keys.


    <!-- /* Font Definitions */ @font-face {font-family:SimSun; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:宋体; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"\@SimSun"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:SimSun;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    create database retest

    go

     

    use retest

    go

     

    create table retest

    ( username varchar ( 50),

    password varchar ( max ))

    go

     

    select * from sys.asymmetric_keys

     

    create master key encryption by password = 'p@ssw0rd' ;

    go

     

    create certificate passwordCert with subject = 'Password' ;

    go

     

    create symmetric key passSym

    with algorithm = triple_des

    encryption by certificate passwordCert

    go

     

    open symmetric key passSym

    decryption by certificate passwordCert

    go

     

    create view retest_view

    as

    select username, convert ( varchar , decryptByKey( password)) as password

    from retest

    go

     

    create trigger enpass on retest_view

    instead of insert

    as

    begin

    insert into retest ( username, password)

    select username, encryptByKey( Key_guID( 'passsym' ), cast ( password as varbinary ( max )))

    from inserted

    end

     

    select current_timestamp

    go

    select * from retest_view

    go

    select current_timestamp

    go

     

    select current_timestamp

    go

    select * from retest

    go

    select current_timestamp

    go



    Selecting from both the view and the original table, the encrypted column "password" would always return "null".
    I've opened the key and not closed it throughout the whole session.

    Thanks a lot for the help.
    Friday, April 3, 2009 1:58 AM
  •   The sample code you provided seems to work for me, both on SQL Server 2005 & SQL Server 2008, I am not sure what could be the cause of the error. I noticed a couple of potential code bugs that, depending on the circumstances, may be affecting.

    create database retest
    go
    
    use retest
    go
    
    create table retest
    ( username varchar ( 50), 
    --password varchar ( max )) 
    -- * varbinary/varchar(max) are not really supported by the encryption/decryption APIs,
    --   I would recommend explicitly using varbinary() up to 8000 bytes
    -- * Using varchar/nvarchar to store binary data is discourage as the binary data
    --   may not represent a valid string, or may be transformed as a sides effect by code trying to interpret it
    password varbinary ( 8000 )) 
    go
    
    select * from sys.asymmetric_keys 
    
    create master key encryption by password = 'p@ssw0rd' ; 
    go
    
    create certificate passwordCert with subject = 'Password' ; 
    go
    
    create symmetric key passSym
    -- with algorithm = triple_des
    -- Whenever it is possible, use AES family of algorithms
    with algorithm = AES_256
    encryption by certificate passwordCert
    go
    
     
    open symmetric key passSym
    decryption by certificate passwordCert
    go
    
    
    create view retest_view
    as
    -- convert(varchar, ..) may truncate the output if the plaintext is > 30 characters
    select username, convert ( varchar(128), decryptByKey( password)) as password
    from retest
    go
    
    CREATE trigger enpass on retest_view
    instead of insert 
    as
    begin
    -- supress the trigger insert row count
    set nocount on
    insert into retest ( username, password) 
    --select username, encryptByKey( Key_guID( 'passsym' ), cast ( password as varbinary ( max )))
    -- the correct casing for the key name is "passSym".
    -- If your system is case sensitive, this call may fail
    -- There is no need for an explicit cast.
    select username, encryptByKey( Key_guID( 'passSym' ), password ) 
    from inserted
    -- reenable the trigger insert row count
    set nocount off
    end
    go
    
    
    Insert into retest_view values('test', 'password_for_test')
    go
    
     
    select current_timestamp 
    go
    select * from retest_view
    go
    select current_timestamp 
    go
    


     Besides these small changes, I would recommend the following article on this topic, the examples there may be useful: http://blogs.msdn.com/raulga/pages/589691.aspx

      Please let us know if you were able to make any progress or if you are still experiencing the EncryptByKey builtin always retuning null.

      -Raul Garcia
       SDE/T
       SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 3, 2009 10:24 PM
  • That's because the only simple solution is to not make an untrustworthy person the administrator of your database server. I suggest you also read these posts:

    http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
    http://blogs.msdn.com/lcris/archive/2007/09/24/security-and-copy-protection.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Laurentiu,

    Some well written articles there.

    Thanks for sharing!

    John Sansom | SQL Server Consultant for Santech Solutions
    Saturday, April 4, 2009 11:09 AM
  • Raul,

    Thanks for getting involved, much appreciated.

    Cheers,
    John Sansom | SQL Server Consultant for Santech Solutions
    Saturday, April 4, 2009 11:10 AM
  • Hey guys

    Thanks a lot for all the help.

    Thanks Raul for the codes too!

    I've managed to use the symmetric keys in that situation. Now I am trying to do the encryption process without views. Still using triggers, now I'll like to use procedures such that when the user wants to retrieve data, it will auto-decrypt as well. Hit some problems yesterday, will continue today. Will update you guys as well.

    Thanks a lot!
    Tuesday, April 7, 2009 12:58 AM
  • Hey guys

    I've met some problems with my encryption codes using procedures. Would anyone be able to help?

    I am able to execute the procedure but when I tried to select directly from the table, the password column does not contain anything.


    use rubbish
    go
    drop database trigproc
    go

    create database trigproc
    go

    use trigproc
    go

    drop table retest

    create table retest
    (username varchar(50),
     password varchar(8000))
    go

    create master key encryption by password = 'p@ssw0rd';
    go

    create certificate passwordCert with subject = 'Password';
    go

    create symmetric key passSym
    with algorithm = triple_DES
    encryption by certificate passwordCert
    go

    open symmetric key passSym
    decryption by certificate passwordCert
    go

    create procedure select_record
    @username varchar(50)
    as
    select username, convert (varchar(128), decryptByKey(password)) as password
    from retest
    where username = @username
    go

    drop trigger enpass

    create trigger enpass on retest
    instead of insert
    as
    begin
    set nocount on
    insert into retest (username, password)
    select username, encryptByKey(key_guID('passSym'), password)
    from inserted
    set nocount off
    end
    go

    insert into retest
    values ('test', 'passwordForTest')
    go

    select * from retest
    go

    exec select_record 'test'


    If I set the password column to varbinary(8000), I would have problem inserting data.

    Thanks in advance!
    Tuesday, April 7, 2009 9:20 AM