locked
Encrypt Data in SQL 2005 RRS feed

  • Question

  • Hello every one!!!

    I want to store very sensitive data in my database (SQL 2005) and won't allow anyone to see the data I stored. I can use symmetric keys etc ... but any one who can access code, can retrieve data....

    I am thinking about SSL certificates ... but I would like to ask is there any way that my application or database get certificates/key at runtime ...

    The bottom line is that all stakeholders must not be able to access/see keys or data .... including developer, DBAs etc...

    Any Idea???
    Monday, July 23, 2007 7:43 AM

Answers

  • So if you can use the 'CREATE CERTIFICATE' to install a third party certificate into sql server 2005 and then use the ENCRYPTBYCERT & DECRYPTBYCERT then you have the methods you need to encrypt & decrypt data when doing CRUD operations in the your database, or am i missing something?

     

    HTH

     

    Ollie Riches

     

     

    Monday, July 23, 2007 12:42 PM

All replies

  • Muhammad,

     

    Have you asked the question why does the sensitive data need to be encrypted?

     

    For instance if it is only a password you wish to store you can hash the value before storage - some hashing techniques and routines have been cracked but it would add an extra layer of security to the data, this technique could be extended to other sensitive fields if required.

     

    If you do require a more secure approach the first thing I would consider is the authentication & authorisation model defined in sql server - you know accounts\permissions\roles etc that can be defined for tables\SP's\trigger\views. If this is configured correctly then only the authorised people will have access to the data.

     

    Why can't you use this model to control access to your sensitive data?

     

    HTH

     

    Ollie Riches

     

     

    Monday, July 23, 2007 8:34 AM
  • I considered it ... but when information is very sensitive then need something more...

    For example .. if we use encryption and decryption then developer or DBA can decrypt these information .... if I wan to prevent such cases .... means the generation of key is completely dependent to third party like Verisign ...

    ????


    Monday, July 23, 2007 8:43 AM
  • you said

     

    'if we use encryption and decryption then developer or DBA can decrypt these information .... '

     

    why does the developer have an account that can access sensitive data in a production environment, users, roles & permissions are exactly what this is for, only a 'trusted' DBA should access to such sensitive data. IMO developers should only have access to test data.

     

    if you do want to use asymmetric cryptography why can't you generate your own keys and use them, is there a requirement to have an industry approved key provider?

     

    Update: also after a quick google I found this interesting article:

     

    http://www.databasejournal.com/features/mssql/article.php/3483931

     

    HTH

     

    Ollie Riches

     

     

    Monday, July 23, 2007 8:56 AM
  • First of all thanks for your kind support.

    Yes, the requirement is "generation of key must be independent" ... like, if we store information like Credit Card Number (not in my case)... then these information must not be visible to all stake holders...

    I believe , the only way to achieve it is encrypt it with invisible key (for developers, DBA, etc) .... So, we can create certificates .... but they key to encrypt the certificate must be provide by third party like Verisgin ... so ..end user can trust on us ...

    CREATE CERTIFICATE ElmerEncryptionCertificate
    WITH
    SUBJECT = 'Elmer Certificate',
    EXPIRY_DATE = '01/01/2006 '
    EncryptByCert ( Cert_ID('ElmerEncryptionCertificate'), '????' )
    Hopefully, now it is more clear ... and you can identify where I am missing something....
    Monday, July 23, 2007 9:27 AM
  • So if you can use the 'CREATE CERTIFICATE' to install a third party certificate into sql server 2005 and then use the ENCRYPTBYCERT & DECRYPTBYCERT then you have the methods you need to encrypt & decrypt data when doing CRUD operations in the your database, or am i missing something?

     

    HTH

     

    Ollie Riches

     

     

    Monday, July 23, 2007 12:42 PM
  • Yes I agree ... in this case we will be doing like
    CREATE CERTIFICATE Shipping11 
    FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'
    WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',
    DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
    [from http://msdn2.microsoft.com/en-us/library/ms187798.aspx]

    Shall I place these files on my servers? any Idea?
    Monday, July 23, 2007 1:10 PM
  • during installation they will be required for install into sql server 2005 but when the server go into production they should be removed as far as i understand. You don't want people to have access to these files do you?

     

    HTH

     

    Ollie Riches

    Monday, July 23, 2007 1:20 PM