locked
encryption RRS feed

  • Question

  • Hello,

    I am rather new when it comes down to Microsoft SQL but from what I was able to figure out is that Microsoft SQL allows encrypting the entire DB files using a certificate that is stored securely by the engine so I wanted to ask if this is possible and if so how can I get it done using the Studio Management tool?

    Sunday, April 3, 2011 3:15 AM

Answers

  • Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server 2008. It is designed to provide protection for the entire database at rest without affecting existing applications. Implementing encryption in a database traditionally involves complicated application changes such as modifying table schemas, removing functionality, and significant performance degradations. For example, to use encryption in Microsoft SQL Server 2005, the column data type must be changed to varbinary; ranged and equality searches are not allowed; and the application must call built-ins (or stored procedures or views that automatically use these built-ins) to handle encryption and decryption, all of which slow query performance. These issues are not unique to SQL Server; other database management systems face similar limitations. Custom schemes are often used to resolve equality searches and ranged searches often cannot be used at all. Even basic database elements such as creating an index or using foreign keys often do not work with cell-level or column-level encryption schemes because the use of these features inherently leak information. TDE solves these problems by simply encrypting everything. Thus, all data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk. While cell-level encryption cannot offer these benefits, two Windows® features, Encrypting File System (EFS) and BitLocker Drive Encryption, are often used for the same reasons as TDE—they provide protection on a similar scale and are transparent to the user.

    Please check here for more http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx

    Sunday, April 3, 2011 7:30 AM
  • Hi ,

    Use this link to read more about TDE http://msdn.microsoft.com/en-us/library/bb934049.aspx and basically it works with below principle or steps

    * Create a master key * Create a certificate based on master key * create the database encryption from the certificate * Use alter database to set the encryption ON

    More links http://msdn.microsoft.com/en-us/library/bb510663.aspx , http://msdn.microsoft.com/en-us/library/ff773063.aspx and http://msdn.microsoft.com/en-us/library/bb510476.aspx


    Thanks, Leks
    Monday, April 4, 2011 5:58 AM

All replies

  • Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server 2008. It is designed to provide protection for the entire database at rest without affecting existing applications. Implementing encryption in a database traditionally involves complicated application changes such as modifying table schemas, removing functionality, and significant performance degradations. For example, to use encryption in Microsoft SQL Server 2005, the column data type must be changed to varbinary; ranged and equality searches are not allowed; and the application must call built-ins (or stored procedures or views that automatically use these built-ins) to handle encryption and decryption, all of which slow query performance. These issues are not unique to SQL Server; other database management systems face similar limitations. Custom schemes are often used to resolve equality searches and ranged searches often cannot be used at all. Even basic database elements such as creating an index or using foreign keys often do not work with cell-level or column-level encryption schemes because the use of these features inherently leak information. TDE solves these problems by simply encrypting everything. Thus, all data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk. While cell-level encryption cannot offer these benefits, two Windows® features, Encrypting File System (EFS) and BitLocker Drive Encryption, are often used for the same reasons as TDE—they provide protection on a similar scale and are transparent to the user.

    Please check here for more http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx

    Sunday, April 3, 2011 7:30 AM
  • Please read this article to guide  you how to restore TDE database on another server

    http://www.mssqltips.com/tip.asp?tip=1507


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, April 3, 2011 7:34 AM
  • Hi ,

    Use this link to read more about TDE http://msdn.microsoft.com/en-us/library/bb934049.aspx and basically it works with below principle or steps

    * Create a master key * Create a certificate based on master key * create the database encryption from the certificate * Use alter database to set the encryption ON

    More links http://msdn.microsoft.com/en-us/library/bb510663.aspx , http://msdn.microsoft.com/en-us/library/ff773063.aspx and http://msdn.microsoft.com/en-us/library/bb510476.aspx


    Thanks, Leks
    Monday, April 4, 2011 5:58 AM