locked
Hiding paswword that are use dfor encryption RRS feed

  • Question

  • I have just worked through Michael Coles' articleTongue TiedQL 2005 Symmetric Encryption

    By Michael Coles, 2007/05/11

    http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sql2005symmetricencryption/2291/

     

    Now I understand how to use Master Keys, Certificates and Symmetric keys to encrypt/decrypt the data in a table.

     

    I took Michael's suggestion and set up my database in non-"automatic key management" mode.

    This is so that even the sysadmins can't read my encrypted data without the password to the Master Key.

    But I am a little confuse as to how to hide the password to the Master Key.

     

    I tried putting this into a function (without the /**  **/)

    /*******************************

    OPEN MASTER KEY ENCRYPTION BY PASSWORD = 'password'

              OPEN SYMMETRIC KEY TestSymmetricKey

                 DECRYPTION BY CERTIFICATE TestCertificate;

    ********************************************/

    on compile, I get :

    /*******************************************

    Msg 443, Level 16, State 14, Procedure EncryptData, Line 22

    Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.

     

    *******************************************/

     

    AND on the decrypt function, if I try to pass the Password in (so the user can't just run it), I fail with

    Msg 443, Level 16, State 14, Procedure EncryptData, Line 24

    Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function. (I had to concatenate the passed in variable to the OPEN MASTER KEY statement and run it with 'exec', else I got a another error)

     

    So I like this concept of the MasterKey/Certificate/Symmetric keys as I can back them up and restore them without loss of the encrypted data.

     

    But How Do I hide the Password for the Master KEY from the sysadmins and everybody else?

     

    I could use he ASymmetric key functioanlity, but I can't back it up independant of the data.

     

    Where am I going wrong?

     

    Regards

     

    GregJF

    Thursday, August 7, 2008 1:43 AM

Answers

  • I understand that the  WITH ENCRYPTION clause is just an obfuscation, But how else am I to protect/hide the password?

     

    I just wanted to make sure you understand the limitations of this feature.

     

    As illustrated in the examples on  DecryptByKeyAutoCert,, any body could run this function  against the data and decrypt it.

    Or am I missing something here?

     

    Unfortunately, Books Online doesn't provide all the information. To successfully execute this function you need to have some permission on the symmetric key and CONTROL permission on the certificate. In addition, if a password is used, you also need to know that password. More than that, the key is opened in a special way that makes it inaccessible outside the current transaction. I'll ask that the article be updated with such information.

     

    See these examples:

     

    http://blogs.msdn.com/lcris/archive/2005/06/10/sql-server-2005-column-encryption-demo-part-2.aspx

    http://blogs.msdn.com/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx

     

    Note how the permissions granted to access a symmetric key are VIEW DEFINITION on the key and CONTROL on the certificate protecting it (ownership implies CONTROL).

     

    That it is not very secure if a sysadmin can break it.

     

    Others have wondered on similar lines and you can still find those discussions in this forum with a search. I already commented on this here: http://blogs.msdn.com/lcris/archive/2005/12/20/about-security-and-encryption-with-references-to-sql-server-2005.aspx. And here too: http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx.

     

    If you think you have a scheme that works, I'll tell you how it could be broken. Because something that could protect data from the administrator while still allowing that data to be used by an application on the machine is the Holy Grail of DRM and you may be aware of how successful DRM is. For my thoughts on DRM, see: http://blogs.msdn.com/lcris/archive/2007/09/24/security-and-copy-protection.aspx.

     

    Encryption in SQL Server, if used properly, can be very effective in protecting data at rest. If you lose a backup disk, database encryption helps you. If you lose the entire machine, strong account passwords, database encryption, and an OS encryption feature like BitLocker will help you. But protecting data in use (as you are trying to do) is a different problem altogether and is better addressed via proper authentication/authorization as well as through the use of proper coding techniques. The reason for this is that data in use is used unencrypted - encryption no longer protects it.

     

    Hope this helps

    Friday, August 8, 2008 4:48 AM

All replies

  • Greg,

     

    I moved this post from the Database Engine to the Security Forum.  Laurentiu Cristofor, is probably the best person to answer this question, and he frequents this forum.

    Thursday, August 7, 2008 2:56 AM
  •   By definition, user defined functions cannot include statements that produce a side effect in the body. In this case, opening a key (including master key) is a side effect as it affects the current session, hence the error. You would need to wrap the open key statement inside stored procedures instead.

     

      In the case of your decryption function, since the T-SQL OPEN statement is available for compilation, the server will fail immediately; on the other hand, on your encryption function the statement is not available at compilation, only at run-time.

     

       In order to give you better recommendations regarding the password protection, it would be a good idea to have more information on what are the assets you are trying to protect (besides the password itself), and the risks/adversaries that you are trying to protect against as well as the level of guarantee that you are looking for.

     

      BTW. Regarding the need to use dynamic SQL for specifying the password in the OPEN MASTER KEY statement, please make sure you protect properly against SQL injections. I strongly recommend reading the following article from the SDL blog:

    http://blogs.msdn.com/sdl/archive/2008/05/15/giving-sql-injection-the-respect-it-deserves.aspx

     

      I hope this information helps,

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Thursday, August 7, 2008 4:00 AM
  • Thanks for repling Raul.

    Ok, so now I see some  limits on the Functions. And I will try to wrap the OPEN statements in Stored Procedures and call them from the functions.

     

    As far as the assets I am try to protect.

    These are Credit card and Bank Account details for a BANK customers.

    So I need these fields (which are in one table) to be encrypted.

    I need the encryption to happen when inserts are performed on this table. Which I hope to do on an Insert trigger.

    There is a business rule that ensures that only Inserts are done. Which I can enforce by trigger.

     

    I am to storing my Create MASTER KEY WITH password syntax in Source control ONLY.

     

    I need to hide this data from all query persons, including sysadmins and engines,including Profiler, except for am TEXT ENCRYPTED stored procedure that supplies the password so as to decrypt  the data. This Store Procedure will be compiled WITH ENCRYPTION and stored in source safe. Only certain roles can run this procedure. But there are other "hiding" techniques being used as well.

    All SQL objects particating in this will be compiled using the WITH ENCRYPTION option.

    So only persons with rights to source control can see the password.

     

    My issue is that I wish to use the same MASTER KEY in other functionality in the database and want a central place from which to call OPEN MASTER KEY WITH DECRYPTION PASSWORD = 'mysecret'

     

    But when the Encryption is in  non-"automatic key management" mode I run into issue as I have outline din the post above.

     

    Regards

     

    GregJF

    PS as fara as SQL Injectio, I was using a SQL Parameterized Query and checking the string for spaces and other key words.  

     

     

    Thursday, August 7, 2008 6:19 AM
  • Note the the WITH ENCRYPTION clause is not secure and it only means that the text of the procedure will be obfuscated. It is fairly trivial to break. The feature is present for backward compatibility reasons. You can use it, but don't expect it to protect your password.

     

    Rather than opening the master key all the time during decryption, you should look instead at using a password protected certificate to protect the symmetric key that is used to encrypt the data. Then for decryption you can use a builtin function like DecryptByKeyAutoCert, which will open the key just for the duration of the statement.

     

    Finally, while a password protected encryption key will provide some protection from a sysadmin, that protection is not absolute - a sysadmin can find out a way to discover your password. My advice is to not overengineer your design and make sure instead that you address the basic requirements well. I would rather look at not making people sysadmins than at trying to protect from sysadmins - the former can be achieved with much less cost than the latter.

     

    Hope this helps

     

     

     

    Thursday, August 7, 2008 6:34 PM
  •  

    Laurentiu

    Thanks for you reply

    Here are my comments, please don't take them as critism of your comments but as comments in general.

     

    I understand that the  WITH ENCRYPTION clause is just an obfuscation, But how else am I to protect/hide the password?

     

    As to your comments in the second paragragh, I just don't understand how that secures the data. As illustrated in the examples on  DecryptByKeyAutoCert,, any body could run this function  against the data and decrypt it.

    Or am I missing something here?

     

    As with your third paragragh, this leads me to believe two things about data encryption in SQL SERVER.

    1. That it is not very secure if a sysadmin can break it. Only the Password "manager", armed with the password, ought to be able to break the encryption code. Notwithstanding that the brute force of a supercomputer or the like is not being used. (In contrast:in  WCF encryption no admin sniffing tool can see the data)

     

    2. There has been a lot of effort by Microsoft to encrypt the data using MasterKey/Certificate/Symmetric keys  and with Asymmetric keys, but comparatively little in the protection of the the KEYs (ie passwords) being used. Again, only the KEY (ie password) manager  ought ot be able to decrypt  the data. AND do so with the confidence that no one else can.

    Else what is the point of using encryption?!!

     

    Of course these comments of mine are made not knowing the total "ins and outs" of encrytion in SQL SERVER, hence this post.

    So I would be very happy to hear of any way to securely protect the KEY(is Password) from all persons, query engines and sniffrs, including Profiler.

     

    Kindest regards

     

    GregJF

    Friday, August 8, 2008 12:10 AM
  • I understand that the  WITH ENCRYPTION clause is just an obfuscation, But how else am I to protect/hide the password?

     

    I just wanted to make sure you understand the limitations of this feature.

     

    As illustrated in the examples on  DecryptByKeyAutoCert,, any body could run this function  against the data and decrypt it.

    Or am I missing something here?

     

    Unfortunately, Books Online doesn't provide all the information. To successfully execute this function you need to have some permission on the symmetric key and CONTROL permission on the certificate. In addition, if a password is used, you also need to know that password. More than that, the key is opened in a special way that makes it inaccessible outside the current transaction. I'll ask that the article be updated with such information.

     

    See these examples:

     

    http://blogs.msdn.com/lcris/archive/2005/06/10/sql-server-2005-column-encryption-demo-part-2.aspx

    http://blogs.msdn.com/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx

     

    Note how the permissions granted to access a symmetric key are VIEW DEFINITION on the key and CONTROL on the certificate protecting it (ownership implies CONTROL).

     

    That it is not very secure if a sysadmin can break it.

     

    Others have wondered on similar lines and you can still find those discussions in this forum with a search. I already commented on this here: http://blogs.msdn.com/lcris/archive/2005/12/20/about-security-and-encryption-with-references-to-sql-server-2005.aspx. And here too: http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx.

     

    If you think you have a scheme that works, I'll tell you how it could be broken. Because something that could protect data from the administrator while still allowing that data to be used by an application on the machine is the Holy Grail of DRM and you may be aware of how successful DRM is. For my thoughts on DRM, see: http://blogs.msdn.com/lcris/archive/2007/09/24/security-and-copy-protection.aspx.

     

    Encryption in SQL Server, if used properly, can be very effective in protecting data at rest. If you lose a backup disk, database encryption helps you. If you lose the entire machine, strong account passwords, database encryption, and an OS encryption feature like BitLocker will help you. But protecting data in use (as you are trying to do) is a different problem altogether and is better addressed via proper authentication/authorization as well as through the use of proper coding techniques. The reason for this is that data in use is used unencrypted - encryption no longer protects it.

     

    Hope this helps

    Friday, August 8, 2008 4:48 AM
  •      

      DecryptByKeyAutoCert requires CONTROL permission on the certificate (in order to access the private key) and VIEW DEFINITION on the symmetric key in addition of the password (if the certificate is protected by a password); if the caller doesn’t  hold any of these permissions or the password is not correct, the call would fail, returning null.

     

      To try to answer the second part of the question, I would like to say that for different threats there are different mitigation mechanisms, encryption is a common tool used by many of these mechanisms:

    ·       Attacks against data in transit (over the wire).

    ·       Attacks against data at rest (disk).

    ·       Attacks from other (low-privielged) users in the system (DB engine).

    ·       Attacks from privileged (sysadmin) users in the system.

     

      SSL support in SQL Server is designed to provide protecting against data in transit.

     

      The SQL Server infrastructure introduced in SQL Server 2005 (key hierarchy) was mainly designed to protect data at rest. Once the data is encrypted in disk, it is necessary to have access to the key to recover it. In SQL Server 2005 & 2008 encryption model, the keys are stored within the data (in the database) protected in a hierarchy where there are two possible roots for the key hierarchy:  

    * The Service Master Key. The SMK is protected by DPAPI, this means that the SQL Server process and the Windows account used to run this service are the root protection in this case, if the server is compromised, so is the data

    * Password. When using a password to protect data, there are different risks, when the data is at rest, the root of the key is not (or should not) be in the database itself, but depending on the password relative entropy, it could be a dictionary-attack away from being broken. Another risk in this model is that fact that the password has to be used every time in order to encrypt/decrypt data, giving attackers more opportunities to get unauthorized access to the data.

     

       For protection against other low-privileged users of the system, the permission model and a following the least-privilege principle is the best defense mechanism.

     

      When defending against a sysadmin adversary, it is essential to note that our adversary not only controls everything in the database, he also controls the process itself, hence the warning from Laurentiu to not fall into the trap of having a false sense of security under these circumstances.

     

      One thing that is very important to notice on SQL Server encryption is that when the server is running (data in use) if the SQL Server process is compromised, the data is also compromised (i.e. the attacker can hijack the process itself and execute arbitrary code in behalf of SQL Server).

     

      There is no absolute protection against a sysadmin, but this is not a problem exclusive on SQL Server there same way there is no absolute protection against a box administrator in Windows or against root in *nix. The protection mechanism in these cases typically relies in security policies that include good auditing in such a way that it is possible to trust-but-verify the administrator. In SQL Server 2008 we introduced a new auditing infrastructure that could be helpful.

     

      A final note, in SQL Server 2008 we introduced a new infrastructure, called Extensible Key Management (EKM), that will allow third party vendors to hook into SQL Server in order to provide off-box key management in HSM that use a different approach. For detailed information on this new feature please refer to BOL.

     

     I hope this helped to clarify the existing key management and infrastructure available today, but please, if you have any further questions and/or feedback, we will be glad to hear them and help, after all this is the best way for us to improve our product and our documentation.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, August 8, 2008 5:34 AM
  • Laurentiu  and Raul

    Gents, you have given me a lot to review and disgest. Thanks for that.

     

    I will get back to you if Ihave furthur questions. But I am getting a handle on all this..

     

    Thanks Again

     

    GregJF

    Sunday, August 10, 2008 11:59 AM