Export (0) Print
Expand All

Enable TDE Using EKM

This topic describes how to enable transparent data encryption (TDE) in SQL Server 2014 to protect a database encryption key by using an asymmetric key stored in an extensible key management (EKM) module with Transact-SQL.

TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. The database encryption key can also be protected using a certificate which is protected by the database master key of the master database. For more information about protecting the database encryption key by using the database master key, see Transparent Data Encryption (TDE).

In This Topic

Limitations and Restrictions

  • You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. That user must be able to be authenticated by the EKM module.

  • Upon start up the Database Engine must open the database. To do this, you should create a credential that will be authenticated by the EKM, and add it to a login that is based on an asymmetric key. Users cannot login using that login, but the Database Engine will be able to authenticate itself with the EKM device.

  • If the asymmetric key stored in the EKM module is lost, the database will not be able to be opened by SQL Server. If the EKM provider lets you back up the asymmetric key, you should create a back up and store it in a secure location.

  • The options and parameters required by your EKM provider can differ from what is provided in the code example below. For more information, see your EKM provider.

Security

Permissions

This topic uses the following permissions:

  • To change a configuration option and run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

  • Requires ALTER ANY CREDENTIAL permission.

  • Requires ALTER ANY LOGIN permission.

  • Requires CREATE ASYMMETRIC KEY permission.

  • Requires CONTROL permission on the database to encrypt the database.

Arrow icon used with Back to Top link [Top]

To enable TDE using EKM

  1. Copy the files supplied by the EKM provider to an appropriate location on the SQL Server computer. In this example, we use the C:\EKM folder.

  2. Install certificates to the computer as required by your EKM provider.

    Note Note

    SQL Server does not supply an EKM provider. Each EKM provider can have different procedures for installing, configuring and authorizing users. Consult your EKM provider documentation to complete this step.

  3. In Object Explorer, connect to an instance of Database Engine.

  4. On the Standard bar, click New Query.

  5. Copy and paste the following example into the query window and click Execute.

    -- Enable advanced options.
    sp_configure 'show advanced options', 1 ;
    GO
    RECONFIGURE ;
    GO
    -- Enable EKM provider
    sp_configure 'EKM provider enabled', 1 ;
    GO
    RECONFIGURE ;
    GO
    -- Create a cryptographic provider, which we have chosen to call "EKM_Prov," based on an EKM provider
    
    CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov 
    FROM FILE = 'C:\EKM_Files\KeyProvFile.dll' ;
    GO
    
    -- Create a credential that will be used by system administrators.
    CREATE CREDENTIAL sa_ekm_tde_cred 
    WITH IDENTITY = 'Identity1', 
    SECRET = 'q*gtev$0u#D1v' 
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
    GO
    
    -- Add the credential to a high privileged user such as your 
    -- own domain login in the format [DOMAIN\login].
    ALTER LOGIN Contoso\Mary
    ADD CREDENTIAL sa_ekm_tde_cred ;
    GO
    -- create an asymmetric key stored inside the EKM provider
    USE master ;
    GO
    CREATE ASYMMETRIC KEY ekm_login_key 
    FROM PROVIDER [EKM_Prov]
    WITH ALGORITHM = RSA_512,
    PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
    GO
    
    -- Create a credential that will be used by the Database Engine.
    CREATE CREDENTIAL ekm_tde_cred 
    WITH IDENTITY = 'Identity2' 
    , SECRET = 'jeksi84&sLksi01@s' 
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
    
    -- Add a login used by TDE, and add the new credential to the login.
    CREATE LOGIN EKM_Login 
    FROM ASYMMETRIC KEY ekm_login_key ;
    GO
    ALTER LOGIN EKM_Login 
    ADD CREDENTIAL ekm_tde_cred ;
    GO
    
    -- Create the database encryption key that will be used for TDE.
    USE AdventureWorks2012 ;
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM  = AES_128
    ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
    GO
    
    -- Alter the database to enable transparent data encryption.
    ALTER DATABASE AdventureWorks2012 
    SET ENCRYPTION ON ;
    GO
    

For more information, see the following:

Arrow icon used with Back to Top link [Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft