none
Questions concernant TDE

    Question

  • Hi,

    i'm learning about encryption TDE in SQL Server 2008.. and there is some questions that i don't have the answers yet !!

    I know that TDE encrypts the data on disk so if one user wants to connect to sql server 2008 and to connect to the database to see for example an encrypted database composed of one, he can see the table in clear ?? and where we can see the encryption of data in this case ? more clearly, what's the proof that the database has been encrypted ? where we can see that ??

    I want to know some things about TDE that i can't find until now like :

    where the keys are stored ? symmetric keys ? asymmetric keys ? master key ? certificate ? database encryption key ?

    I know that the symmetric and asymmetric keys are stored in EKM but EKM is an HSM which does not reside in SQL Server so if i don't use EKM where all these keys are stored ?

    how we manage those keys ?

    if one key is expired, what does it happen to the database encrypted and how to renew the key and if the database is lost how to restore it ?

    how i can do to not loose the data encrypted if one key is expired ??

    i built a small database with one table named encrypted_DB and i create a master key in the master database, and i want to create an asymmetric key which is ecnrypted by the master key and encrypt the database encryption key with this asymmetric key, but until now i have a problem !! i can create the asymmetric key in the user database but when i want to create the database encryption key encrypted by this asymmetric key i can't !! i apply this query in thye master database to create the asymmetric key :

    create asymmetric key AsymetricKey
    with algorithm = RSA_512
    encryption by password ='1234ADora'; 

    when the asymmetric key is created i apply this query in the user database to encrypt the DEK with this asymmetric key :

    create database encryption key
    with algorithm = AES_256
    encryption by server asymmetric key AsymetricKey;

     i have an error in SQL Server : In order to encrypt the database encryption key with an asymmetric key, please use an asymmetric key that resides on an extensible key management provider.

    so i have to use an asymmetric key in EKM ? is it an obligation ? please help me how i install the EKM provider to can use the asymm key ???

    I ask for some help because it's confused in my mind until now !!

    Thank you

    Tuesday, January 11, 2011 5:00 PM

All replies

  • Hi,

    TDE is a SQL Server feature for protecting data-at-rest. This means that when TDE is enabled, data on disk and logs will be encrypted. However, data pages in memory(bufferpool) are decrypted.

    Where are the keys stored?
    SQL Server manages all the encryption keys within the database. Keys are stored in the system tables.

    You could query the following views to get an understanding of the secrets stored within SQL Server -

    For symmetric keys - sys.symmetric_keys
    For asymmetric keys - sys.asymmetric_keys
    For certificates - sys.certificates

    Additionally, you may also want to check the encryption key hierarchy documented at http://msdn.microsoft.com/en-us/library/ms189586.aspx to understand how keys are protected in SQL Server.

    If EKM, is not used the keys are stored in the database.

    Managing sql server keys can be done through SQL DDL/DML statements.
    For example - Creating symmetric keys - http://msdn.microsoft.com/en-us/library/ms188357.aspx

    With respect to protecting the Database Encryption Key (DEK) with the asymmetric key, the asymmetric key
    must be in an EKM. This is documented at http://msdn.microsoft.com/en-us/library/bb934049.aspx.

    "The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module"

    Hope this helps.

     

     


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Tuesday, January 11, 2011 7:10 PM
  • thank you so much for your answer Don Pinto, sure it will help me to better understand TDE !!

    so if i understand well, the asymmetric key which protect the symmetric key DEK, it HAS TO BE in EKM module ! it's an obligation ?!!

    it can't provide from somewhere else ?? i can't for example import the asymmetric key from another place ( file ) ?

    In fact, i Have a file .asc which contains an asymmetric key generated by GPG with (its public and private keys) and i want to use it to encrypt the DEK but i can't use the format .asc !! do you have an idea about transforming this type of file from .asc to .snk or .tmp ( temporary file ) to the purpose of using it to encrypt the DEK by this asymmetric key ?

    I have another questions please if you can give me clear answers :

    does TDE can be used to manage database access by users ?

    Is it possible to encrypt the database by a DEK and allow just only certain users to use the DEK of TDE to decrypt the database?

    Does TDE cab control the access of databases to see them or not ?

    Do you have an idea about the expira&tion of the keys ?

    Thank you for help

    Dora_TDE

    Wednesday, January 12, 2011 2:10 PM
  • Hi,

    I want to know how to install an EKM provider ?

    i don't really understand this step !! i wanted to create an asymmetric key to encrypt the DEK with it but i have an error in SQL :

    in order to encrypt data with an asymmetric key yoy have to use an asymmetric key in Extensible key management (EKM) !

    So what's the provider EKM i have to use .. to install !!???!!

    please help me to jump this step because i m blocked !!!

    Thanks

    Wednesday, January 12, 2011 3:58 PM
  • Hi,

    Following are the answers to your questions -

    it can't provide from somewhere else ?? i can't for example import the asymmetric key from another place ( file ) ?

    > For asymmetric keys, currently we restrict it to be loaded from an EKM device. We cannot load it from a file.

    does TDE can be used to manage database access by users ?

    > No. To manage database access there are 2 parts - SQL Server Authentication and Authorization.

    Authentication controls who can connect to SQL Server

    Authorization controls access to database objects within SQL Server.

    Links -

    Authentication - http://msdn.microsoft.com/en-us/library/ms144284.aspx

    Authorization - Grant T-SQL (http://msdn.microsoft.com/en-us/library/ms187965.aspx), Revoke T-SQL (http://msdn.microsoft.com/en-us/library/ms187728.aspx), Deny T-SQL (http://msdn.microsoft.com/en-us/library/ms188338.aspx).

    Is it possible to encrypt the database by a DEK and allow just only certain users to use the DEK of TDE to decrypt the database?

    No. DEK is stored in the boot record page of the database common for the entire database. No permissions can be given on the DEK to particular users.

    For more information on DEK storage check - http://blogs.msdn.com/b/sqlsecurity/archive/2010/06/14/database-encryption-key-dek-management.aspx

    http://blogs.msdn.com/b/sqlsecurity/archive/2010/07/14/dek-and-the-log.aspx

    Does TDE cab control the access of databases to see them or not ?

    If I understand this question correctly, the data and log files are still visible but their content is encrypted.

    Do you have an idea about the expiration of the keys ?

    We do not control key expiration inside SQL Server. However, it is a good security practise to rotate keys occasionally.

    In the case of certificate we also do not check for the EXPIRY_DATE field in the case of TDE.

    Using EKM to setup TDE

    The following article has this info - http://technet.microsoft.com/en-us/library/cc645957.aspx

    Hope this helps!

     


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Wednesday, January 12, 2011 5:57 PM
  • Thank you Don pinto for your replies !!

    it will really help me !!!

    thanks again

    I want to know how to install an EKM provider ? if you can help me to do this, because now i can't use an asymmetric in EKM as i don't have provider EKM !!

    i wanted to create an asymmetric key to encrypt the DEK with it but i have an error in SQL :

    in order to encrypt data with an asymmetric key yoy have to use an asymmetric key in Extensible key management (EKM) !

    So what's the provider EKM i have to use .. to install !!???!!

    Thanks

    Thursday, January 13, 2011 10:28 AM
  • Hi,

    Is the DBA which gives rights to users can or not see datas which are encrypted ?

    Does the DBA see the data in clear or encrypted where datas are encrypted ?

     

    Thanks for help

    Thursday, January 13, 2011 2:18 PM
  • Excuse me Don Pinto but for my question :

    it can't provide from somewhere else ?? i can't for example import the asymmetric key from another place ( file ) ?

    you replied this :

    > For asymmetric keys, currently we restrict it to be loaded from an EKM device. We cannot load it from a file.

    In this link, at the end of the page, we can see that we can load an asymmetric key from a file .tmp !!!!!

    http://msdn.microsoft.com/fr-fr/library/ms174430.aspx

    Are you sure that it's not possible to load it from a file ?

    Thanks

    Thursday, January 13, 2011 2:59 PM
  • Hi,

    You are correct that an asymmetric key can be created from a file but in the case of TDE, such an asymmetric key cannot be used to protect the Database Encryption Key (DEK). To protect a DEK, the asymmetric key must reside in an EKM device.

    i.e. it cannot be used in this context -

    CREATE DATABASE ENCRYPTION KEY
           WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
       ENCRYPTION BY SERVER
        {
            CERTIFICATE Encryptor_Name |
            ASYMMETRIC KEY Encryptor_Name
        }
    [ ; ]

    For the next question -

    Is the DBA which gives rights to users can or not see datas which are encrypted ?

    Does the DBA see the data in clear or encrypted where datas are encrypted ?

    TDE operates at a database level and there is no mechanism to give certain users rights to see encrypted data in the TDE context.

    However, column level grants can be used in combination with cell-level encryption to control access to sensitive data.

    With TDE turned on, you will not see data encrypted in SSMS but the physical data and log files on disk are encrypted. You can think of TDE as analogous to bitlocker.

    If you want certain fields of the table to be encrypted, you will have to use cell-level encryption. For more information of cell level encryption you can check - http://msdn.microsoft.com/en-us/library/cc278098.aspx.

    Additionally, you can take a look at the GRANT, DENY syntax at http://msdn.microsoft.com/en-us/library/ms187965.aspx and http://msdn.microsoft.com/en-us/library/ms188338.aspx respectively.

    Thanks


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Thursday, January 13, 2011 5:51 PM
  • Hello,

    The following article explain how to use TDE feature of SQL Server 2008 Using Transparent Data Encryption Feature of SQL Server 2008

    Hope this helps!

    Cheers,
    Ashish

     

    Thursday, January 13, 2011 7:16 PM
  • hi,

    Thank you for your help Ashish.

    Don Pinto, if i understand what you said :

    1. if i want to use or to load an asymmetric key to encrypt DEK, it has to be loaded from EKM device, else if i load it from a file it can't be used to encrypt DEK.

    2.there is no one who can see the data encrypted, with TDE, phisycal data and log files are encrypted, not data in SSMS.

    3.the DBA does not see the data encrypted in SSMS.

    So what's exactly the role and the function of DBA in this case ? is it to give rights and some permissions et privileges to  certain users ?

    Thank you for your help Don pinto

    Dora_TDE

     

    Friday, January 14, 2011 10:03 AM
  • There is something that i couldn't do until now !!

    Installing EKM device to use its asymmetric key !!

    i saw that link : http://msdn.microsoft.com/en-us/library/cc645957.aspx which describe how to use an asymmetric key in an EKM but i have a problem of understanding on the first step :

    1. Install an EKM provider. The procedures for this step will vary depending on the requirements of the EKM provider, but might include installing a certificate from the EKM provider in the local certificate store, and copying EKM DLL files onto the SQL Server computer.

     How can i install an EKM provider before configuring EKM ???? where dan i find EKM DLL ??

     Tell me if you know what to do exactly to execute this first step !!!! 

    i need to use EKM to encrypt my DEK by an asymmetric key !!

    Thanks for help

    Friday, January 14, 2011 1:09 PM
  • Hi,

    1. if i want to use or to load an asymmetric key to encrypt DEK, it has to be loaded from EKM device, else if i load it from a file it can't be used to encrypt DEK.

    Correct.

    2.There is no one who can see the data encrypted, with TDE, phisycal data and log files are encrypted, not data in SSMS.

    In this case, I mean that if for example we issue a SELECT query from a table through SSMS, the data will be in plain-text unless explictly encrypted using cell-level encryption.

    However, the .mdf and .ldf files will be encrypted.

    3.the DBA does not see the data encrypted in SSMS.

    So what's exactly the role and the function of DBA in this case ? is it to give rights and some permissions et privileges to  certain users ?

    Just TDE alone does not protect the DBA or other users from querying the data. (i.e if TDE is enabled and a select query is issued, the results could be in plain-text unless explictly encrypted using cell-level encryption).

    To enable TDE, you need ALTER priviledges on the database and a DBA has this.


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Friday, January 14, 2011 8:36 PM
  • Hi,

    Thank you Don Pinto for your reply !!!

    please can you help for this step !!

    There is something that i couldn't do until now !!

    Installing EKM device to use its asymmetric key !!

    i saw that link : http://msdn.microsoft.com/en-us/library/cc645957.aspx which describe how to use an asymmetric key in an EKM but i have a problem of understanding the first step :

    1. Install an EKM provider. The procedures for this step will vary depending on the requirements of the EKM provider, but might include installing a certificate from the EKM provider in the local certificate store, and copying EKM DLL files onto the SQL Server computer.

     How can i install an EKM provider before configuring EKM ???? where dan i find EKM DLL ??

     Tell me if you know what to do exactly to execute this first step !!!! 

    i need to use EKM to encrypt my DEK by an asymmetric key !!

    Thanks for help

    Monday, January 17, 2011 10:42 AM
  • Hi,

    Is there someone who can see the DEK and have access to the DEK ?? if yes, who ??

    How the DMK Database master key in managed ? is there someone who can see the DMK ? if yes who ?

    To decrypt data, we need to have the DEK ? which person can have this information to see data encrypted ?

     

    Thank you for help

    Tuesday, January 18, 2011 11:06 AM
  • Hi,

    I forgot another important question that i have to know : where is the DEK is stored and how is it managed ?

    Because when i query this view : For symmetric keys - sys.symmetric_keys in SQL i just see the database master key and the service master key and not the DEK !!!! where can i see this key or it's impossible to see it .. if it's possible to see the DEK where ??????

    Thank you for help and help me to reply to ALL my questions

    Dora_TDE

    Tuesday, January 18, 2011 11:24 AM
  • Hi,

    i need to have answers please for my 4 messages, this one and the three before !!!!

    I want to know something very important about keys in the master database SMK DMK and DEK :

    what are the rights of each user ( DBA (sa), user which does not have special privileges ) about these keys ?

    can they see keys ??

    Thank you for help

     

    Tuesday, January 18, 2011 4:37 PM
  • From Understanding Transparent Data Encryption (TDE) "The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery." So the DEK is stored in the TDE database, not in master. And it's a special purpose key, so it doesn't appear in sys.symmetric_keys.

    Nobody really has rights to the key. Only the Database Engine, and even the Database Engine only has access to it if it has access to the certificate or asymmetric key that protects the DEK. Also from Understanding Transparent Data Encryption (TDE) "The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module." So if the database is attached to a new instance of the Database Engine, the Database Engine can't get at the database unless the cert/asym key is available to the Database Engine.

    Regarding the DMK, the same topic describes how access to the DMK is controlled by the SMK, which is controlled by the DPAPI. sys.symmetric_key in master shows those two, but users/logins don't get access. The Database Engine gets access. The DPAPI stores the access info in the local security store for the login that is used to start the Database Engine. As a side effect, that's one of the reasons you must use SQL Server Configuration Manager (SSCM) to change the account used by the Database Engine. When changing the account, SSCM grants the new account access to that info in the local security store and removes access for the old account. So the Database Engine can still get to the SMK.

    Need more?

     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, January 18, 2011 5:28 PM
  • One more thing. I'm the Books Online writer for this topic. The Program Managers and Developers understand this better than I do. When their comments differ from mine, beleive them, not me.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, January 18, 2011 5:30 PM
  • thank you Rick for your help

    there is a query SQL which show me the DEK -----> key_id , type, algorithm, ....

    when i do sys.symmetric_keys -----> i just have DMK and SMK only why not the DEK ?

    where can i see it ?

     

    Thanks

    Tuesday, January 25, 2011 12:27 PM
  • The DEK is stored on the boot page of the database. More detailed information about this can be found - https://blogs.msdn.com/b/sqlsecurity/archive/2010/06/14/database-encryption-key-dek-management.aspx

    We have a DMV (sys.dm_database_encryption_keys) that returns information about the encryption state of a database and it's associated database encryption keys-http://msdn.microsoft.com/en-us/library/bb677274.aspx

    Hope this helps.

     


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Tuesday, January 25, 2011 10:02 PM
  • Thank you Don Pinto for your reply

    encryption_state is put on 3 and

    for this query : select name, is_encrypted from sys.databases --------> in_encrypted is put on 1 so i understand that the database is encrypted

    But there is something which is not normal !!! when i query : select * from sys.dm_database_encryption_keys ----------> percent_state is put on 0 so there no state change !!!!!!!!!!!!! do the database is encrypted or not ???? why percent_state is always 0 and why tempdb is not put on 1 ... so tempdb is not encrypted ????

    Wednesday, January 26, 2011 10:55 AM
  • Hi,

    I don't really understand how does CELL-LEVEL encryption work ??? is cell-level encryption = column-level encryption ?? is it the same encryption method ?????

    what's the diffrent between TDE and cell-level encryption ?

    I don't find many documents and books talking about cell-level encryption ... if you can help me to find some articles or documents or books about cell-level encryption, it will help me so much !!! 

    I Have studied TDE well and now i'm looking for another methods of encryption like cell-level .. another ones if you have an idea of methods of encryption databases help me please !!!

    Thank you very much

    Dora_TDE

    Tuesday, February 01, 2011 4:17 PM
  • You have probably already seen this on How to: Encrypt a Column of Data http://msdn.microsoft.com/en-us/library/ms179331.aspx. If you want to encrypt a single cell, instead of all values in the column, then you can use ENCRYPTBYKEY, ENCRYPTBYASYMKEY, ENCRYPTBYCERT, or ENCRYPTBYPASSPHRASE. Your statement encrypts the clear text before inserting or updating the cell in the table. To do cell level encryption you must write this into your insert statement, proc, whatever. There is no particular reason to encrypt a cell instead of the whole column, unless the clear text for one row is more sensitive than the clear text for the other rows. Unlikely.

    Some key differences: Once you turn on TDE, you are done. You don't have to change anything in your database or application. It just works. But you will encrypt everything, not just the row or cell, and since encryption is done by the processor, you will have a slight increase in processor usage. If you have a processor bottleneck that could be significant, but OLTP database applications are more likely to be slowed by disc access bottlenecks than the processor. 

    A significant drawback of encrypting a cell is that indexes on encrypted values are not very efficient. That is, you can index employees by salary, but if the salary is encrypted, then the index isn't storing salaries in order, it's storing them in the order of the encrypted values, which is pretty much random. This doesn't bother TDE, because everything is unencrypted automatically when read, so with TDE the Database Engine doesn't see encrypted data. It only sees clear text, and the index is just fine. But if you are encrypting something like a credit card number, you are probably doing a singleton look up of a specific credit card number, (not a range of credit card numbers) and a singleton look up of an encrypted value in an index, works fine.

    So which solution should you use? It depends upon the threats you are trying to protect against. TDE protects against theft of the whole database, such as someone accessing the file system and copying the mdf file. This is a great feature for a database on a laptop, which might be stolen. But it doesn't do anything for protecting against someone who hacks in via SQL Injection. Once someone has authorized access to the Database and elevates their priveledges, the Database Engine will happly serve up TDE protected data. To protect against a SQL Injection attack, encrypting the credit card using column level encryption would add a meaningful additional security level. And there is nothing wrong with doing both. I don't see much point in encrypting a cell, and then re-encrypting the cell at the column level though.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, February 03, 2011 4:18 PM