{"id":69571,"date":"2017-01-12T16:20:23","date_gmt":"2017-01-12T16:20:23","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=69571"},"modified":"2021-08-24T13:39:26","modified_gmt":"2021-08-24T13:39:26","slug":"encrypting-sql-server-transparent-data-encryption-tde","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/encrypting-sql-server-transparent-data-encryption-tde\/","title":{"rendered":"Encrypting SQL Server: Transparent Data Encryption (TDE)"},"content":{"rendered":"

With the release of SQL Server 2008, Microsoft expanded the database engine\u2019s security capabilities by adding Transparent Data Encryption (TDE), a built-in feature for encrypting data at rest. TDE protects the physical media that hold the data associated with a user database, including the data and log files and any backups or snapshots. Encrypting data at rest can help prevent those with malicious intent from being able to read the data should they manage to access the files.<\/p>\n

SQL Server TDE takes an all-or-nothing approach to protecting data. When enabled, TDE encrypts all data in the database, as well as some outside the database. You cannot pick-and-choose like you can with column-level encryption. Even so, TDE is relatively easy to enable, once you\u2019ve decided this is the path you want to travel.<\/p>\n

In this article, we look at how to implement TDE on a user database. The article is the second in a series about SQL Server encryption. The first one (Encrypting SQL Server: Using an Encryption Hierarchy to Protect Column Data<\/a>) covers column-level encryption. If you\u2019re new to SQL Server encryption, you might want to review that article first.<\/p>\n

The TDE encryption hierarchy<\/h1>\n

When I introduced you to column-level encryption, I discussed the encryption hierarchy and how SQL Server uses a series of keys and certificates to protect column data. The approach used for implementing TDE is similar, but different enough to take a closer look.<\/p>\n

As with column-level encryption, the Windows Data Protection API (DPAPI) sits at the top of the hierarchy and is used to encrypt the service master key (SMK), a symmetric key that resides in the master<\/code> database. SQL Server creates the SMK the first time the instance is started. You can use the key to encrypt credentials, linked server passwords, and the database master keys (DMKs) residing in different databases.<\/p>\n

In the TDE encryption hierarchy, the SMK sits below the DPAPI, and a DMK sits below the SMK. The DMK is a symmetric key, just like you find with column-level encryption. However, with column-level encryption, you create the DMK in the user database where the column data will be encrypted. With TDE, you create the DMK in the master<\/code> database, even though you\u2019ll be encrypting a user database. SQL Server uses the SMK and a user-supplied password to encrypt the DMK with the 256-bit AES algorithm.<\/p>\n

Before we go any further with our description, take a look at the following figure, which shows the entire TDE encryption hierarchy, starting with the Windows DPAPI at the top and the SQL Server data at the bottom. As you can see, the next level down our hierarchy is a certificate, which you also create in the master<\/code> database.<\/p>\n

\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/Screen%20Shot%202017-01-0\"<\/p>\n

The DMK protects the certificate, and the certificate protects the database encryption key (DEK) in the user database. The DEK is specific to TDE and is used to encrypt the data in the user database in which the key resides.<\/p>\n

You can skip the DMK and certificate altogether and instead use an Extensible Key Management (EKM) module to secure the DEK. SQL Server 2008 introduced the EKM infrastructure as a way for encryption keys to be stored in hardware outside of the database, essentially integrating the hardware into the encryption stack. That said, the topic of EKM is outside of the scope of this article, but one we might tackle later in this series.<\/p>\n

For now, we\u2019ll focus on the TDE encryption hierarchy as it is represented in the figure. From this, we can deduce that to implement TDE on a user database, we must take the following steps:<\/p>\n

    \n
  1. Create the DMK in the master<\/code> database, if it doesn\u2019t already exist.<\/li>\n
  2. Create a certificate in the master<\/code> database for securing the DEK.<\/li>\n
  3. Create the DEK in the user database to be encrypted.<\/li>\n
  4. Enable TDE on the user database.<\/li>\n<\/ol>\n

    What is not included in the figure or in the steps is the importance of backing up the SMK, DMK, and certificate. If anything goes wrong in our production environment or we need to restore or move an encrypted database, we might need those keys or certificate, so we better make sure we have copies of them, stored securely in a separate location.<\/p>\n

    Later in the article, we\u2019ll review how to back them up, but first let\u2019s look at how to implement TDE on a user database. For that, we\u2019ll need to set up a test database such as the one shown in the following T-SQL script:<\/p>\n

    USE master;\r\nGO\r\n\r\nCREATE DATABASE EmpData2;\r\nGO\r\n\r\nUSE EmpData2;\r\nGO\r\n\r\nCREATE TABLE EmpInfo(\r\n  EmpID INT PRIMARY KEY,\r\n  NatID NVARCHAR(15) NOT NULL,\r\n  LoginID NVARCHAR(256) NOT NULL);\r\nGO\r\n\r\nINSERT INTO EmpInfo(EmpID, NatID, LoginID) \r\nSELECT BusinessEntityID, NationalIDNumber, LoginID\r\nFROM AdventureWorks2014.HumanResources.Employee\r\nWHERE NationalIDNumber IS NOT NULL;\r\n<\/pre>\n
    The database and table created here are only slightly different from the ones we created in the first article in this series. The database includes the EmpInfo<\/code> table and uses an INSERT<\/code> statement to retrieve data from the HumanResources.Employee<\/code> table in the AdventureWorks2014<\/code> database. However, I\u2019ve named the new database EmpData2<\/code> in case you want to retain the database from the other article. (Note that I created all the examples on a local instance of SQL Server 2016.)<\/p>\n
    You don\u2019t need to use this database to try out the examples in this article. If you have a different one you want to use (and it\u2019s safe to experiment with it), just substitute the name for the database accordingly. You\u2019ll want to keep the database small, however, so you don\u2019t get bogged down during the initial encryption process.<\/p>\n
    Create the DMK<\/h1>\n
    To create the DMK that will support a TDE-enabled database, you take the same steps you take when creating the DMK to support column-level encryption, except for one important difference. You must create the key in the master<\/code> database, as shown in the following T-SQL code:<\/p>\n
    USE master;\r\nGO\r\n\r\nCREATE MASTER KEY\r\nENCRYPTION BY PASSWORD = 'pw1234!';\r\n<\/pre>\n
    The CREATE<\/code> MASTER<\/code> KEY<\/code> statement supports no optional arguments; we need only specify a password, in addition to the basic syntax. (Of course, we would want to use a more robust password in the real world.)<\/p>\n
    To verify that the DMK has been created, we can query the sys.symmetric_keys<\/code> catalog view:<\/p>\n
    SELECT name KeyName,\r\n  symmetric_key_id KeyID,\r\n  key_length KeyLength,\r\n  algorithm_desc KeyAlgorithm\r\nFROM sys.symmetric_keys;\r\n_keys;<\/pre>\n
    The SELECT<\/code> statement returns the results shown in the following table.<\/p>\n\n\n\n\n\n
    \n
    KeyName<\/b><\/p>\n<\/td>\n
    		\n
    KeyID<\/b><\/p>\n<\/td>\n
    		\n
    KeyLength<\/b><\/p>\n<\/td>\n
    		\n
    KeyAlgorithm<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    ##MS_DatabaseMasterKey##<\/p>\n<\/td>\n
    		\n
    101<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    AES_256<\/p>\n<\/td>\n<\/tr>\n
    \n
    ##MS_ServiceMasterKey##<\/p>\n<\/td>\n
    		\n
    102<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    AES_256<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Notice that the results include both the DMK and SMK. As already noted, SQL Server creates the SMK in the master<\/code> database automatically. As you can see, the two keys are based on the 256-bit AES encryption algorithm.<\/p>\n
    Create the certificate<\/h1>\n
    The next step is to create a certificate in the master<\/code> database using a CREATE<\/code> CERTIFICATE<\/code> statement. In SQL Server, a certificate is a digitally signed, database-level securable that binds the public and private keys.<\/p>\n
    To keep things simple, we\u2019ll create a self-signed certificate, which is automatically protected by the DMK. Normally, a certificate authority (CA) would issue and sign the certificate, which we would then incorporate into our encryption infrastructure, but a self-signed certificate can be handy for developing and testing, as well as checking out functionality like we\u2019re doing here.<\/p>\n
    To create a self-signed certificate, we need only provide a name for the certificate and a WITH<\/code> SUBJECT<\/code> clause, as shown in the following statement:<\/p>\n
    CREATE CERTIFICATE TdeCert\r\nWITH SUBJECT = 'TDE certificate';<\/pre>\n
    The WITH<\/code> SUBJECT<\/code> clause supposedly specifies the issuer name; however, it can be just about any value, although a relevant description is normally the best option. In this case, I\u2019ve gone with TDE<\/code> certificate<\/code>.<\/p>\n
    Note that, in addition to self-signed certificates, the CREATE<\/code> CERTIFICATE<\/code> statement lets us define a certificate based on a certificate file as well as retrieve the private key from a file or use a password to encrypt the certificate.<\/p>\n
    After we run the CREATE<\/code> CERTIFICATE<\/code> statement, we can verify that the certificate has been created by querying the sys.certificates<\/code> catalog view:<\/p>\n
    SELECT name CertName,\r\n  certificate_id CertID,\r\n  pvt_key_encryption_type_desc EncryptType,\r\n  issuer_name Issuer\r\nFROM sys.certificates\r\nWHERE issuer_name = 'TDE certificate';\r\n<\/pre>\n
    On my system, the SELECT<\/code> statement returned the results shown in the following table.<\/p>\n\n\n\n\n
    \n
    CertName<\/b><\/p>\n<\/td>\n
    		\n
    CertID<\/b><\/p>\n<\/td>\n
    		\n
    EncryptType<\/b><\/p>\n<\/td>\n
    		\n
    Issuer<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    TdeCert<\/p>\n<\/td>\n
    		\n
    258<\/p>\n<\/td>\n
    		\n
    ENCRYPTED_BY_MASTER_KEY<\/p>\n<\/td>\n
    		\n
    TDE certificate<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the value in the EncryptType<\/code> column is ENCRYPTED_BY_MASTER_KEY<\/code>, which confirms that SQL Server has used the DMK to encrypt the certificate.<\/p>\n
    Create the DEK<\/h1>\n
    Now we switch over to our EmpData2<\/code> database to create the DEK, the next level down our hierarchy. When we create the DEK, we must specify the algorithm to use for the encryption key and the certificate to use to encrypt the DEK. Starting with SQL Server 2016, all algorithms have been deprecated except 128-bit AES, 192-bit AES, and 256-bit AES. (The higher the number of bits, the stronger the algorithm.)<\/p>\n
    To create the DEK, we can use a CREATE<\/code> DATABASE<\/code> ENCRYPTION<\/code> KEY<\/code> statement, as shown in the following example:<\/p>\n
    USE EmpData2;\r\nGO\r\n\r\nCREATE DATABASE ENCRYPTION KEY\r\nWITH ALGORITHM = AES_256\r\nENCRYPTION BY SERVER CERTIFICATE TdeCert;\r\n<\/pre>\n
    In this case, we\u2019ve specified the 256-bit AES algorithm and the TdeCert<\/code> certificate we created in the previous step. When you run the statement, you should receive the following warning.<\/p>\n
    Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.<\/pre>\n
    This is an important message and one you should heed. We\u2019ll discuss backing up your keys and certificates later in the article, but know that it is something you should be doing whenever you\u2019re using them as part of your encryption process.<\/p>\n
    Now let\u2019s return to our DEK. Once we\u2019ve created the key, we can verify its existence by querying the sys.dm_database_encryption_keys<\/code> dynamic management view:<\/p>\n
    SELECT DB_NAME(database_id) DbName,\r\n  encryption_state EncryptState,\r\n  key_algorithm KeyAlgorithm,\r\n  key_length KeyLength,\r\n  encryptor_type EncryptType\r\nFROM sys.dm_database_encryption_keys;\r\n<\/pre>\n
    The sys.dm_database_encryption_keys<\/code> view returns details about a database\u2019s encryption state and its associated DEKs. The following table shows the results returned by our SELECT<\/code> statement.<\/p>\n\n\n\n\n
    \n
    DbName<\/b><\/p>\n<\/td>\n
    		\n
    EncryptState<\/b><\/p>\n<\/td>\n
    		\n
    KeyAlgorithm<\/b><\/p>\n<\/td>\n
    		\n
    KeyLength<\/b><\/p>\n<\/td>\n
    		\n
    EncryptType<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    EmpData2<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    AES<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    CERTIFICATE<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Notice that the EncryptType<\/code> column has a value of CERTIFICATE<\/code>, which confirms that a certificate was used to encrypt the DEK.<\/p>\n
    Also notice that the EncryptState<\/code> column shows a value of 1<\/code>. This indicates that the database is in an unencrypted state. According to SQL Server documentation, the column can display any one of the values described in the following table.<\/p>\n\n\n\n\n\n\n\n\n\n\n
    \n
    Value<\/b><\/p>\n<\/td>\n
    		\n
    Description<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    0<\/p>\n<\/td>\n
    		\n
    No database encryption key present, no encryption<\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    Unencrypted<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    Encryption in progress<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    Encrypted<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    Key change in progress<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    Decryption in progress<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    The certificate or asymmetric key encrypting the DEK is being changed<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Enable TDE on the user database<\/h1>\n
    We now have all the pieces in place to enable TDE on the EmpData2<\/code> database. The only step left is to turn encryption on.<\/p>\n
    Before we do that, I want to point out that there are many considerations to take into account before actually enabling TDE. For example, if any filegroups associated with the database are set as read-only, the encryption operation will fail. You\u2019ll also run up against a number of restrictions when trying to implement TDE, such as not being able to drop a database during the initial encryption process.<\/p>\n
    Before you implement encryption on anything other than a test database in a test environment, I highly recommend that you review the MSDN article Transparent Data Encryption (TDE)<\/a>, which explains the various considerations and restrictions to take into account before implementing TDE.<\/p>\n
    With that in mind, let\u2019s return to the matter at hand, which is to enable TDE on the EmpData2<\/code> database. To do so, we need only run a simple ALTER<\/code> DATABASE<\/code> statement that sets encryption on, as shown in the following example:<\/p>\n
    ALTER DATABASE EmpData2\r\nSET ENCRYPTION ON;<\/pre>\n
    That\u2019s all there is to it. Because our database is so small, the encryption process will be very quick. Not surprisingly, the larger the database, the longer this process will take.<\/p>\n
    If we again query the sys.dm_database_encryption_keys<\/code> view, we\u2019ll get the results shown in the following table, which verify that the EncryptState<\/code> value is now 3<\/code>.<\/p>\n\n\n\n\n\n
    \n
    DbName<\/b><\/p>\n<\/td>\n
    		\n
    EncryptState<\/b><\/p>\n<\/td>\n
    		\n
    KeyAlgorithm<\/b><\/p>\n<\/td>\n
    		\n
    KeyLength<\/b><\/p>\n<\/td>\n
    		\n
    EncryptType<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    tempdb<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    AES<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    ASYMMETRIC KEY<\/p>\n<\/td>\n<\/tr>\n
    \n
    EmpData2<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    AES<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    CERTIFICATE<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The results also show something else that\u2019s very important to note\u2014the addition of a row for the tempdb<\/code> database. When you implement TDE on any<\/em> user table, SQL Server also encrypts the tempdb<\/code> database.<\/p>\n
    If you consider the logic behind this, you can see why Microsoft has taken this step. The database contains such items as temporary user objects, internal objects, and row versions, any of which can expose sensitive data. The downside to this is that unencrypted databases can take a performance hit, although Microsoft claims that the impact is minimal.<\/p>\n
    This issue aside, as long as our certificate and keys are in place, we can query the TDE-encrypted database just like we did before we enabled TDE. For example, we can run the following SELECT<\/code> statement against the EmpInfo<\/code> table:<\/p>\n
    SELECT TOP 5 * FROM EmpInfo;<\/pre>\n
    Notice that we do not have to take any special steps with our query like we do with column-level encryption. We simply run the query as before, a fact that should make application developers happy. The following table shows the results returned by our SELECT<\/code> statement.<\/p>\n\n\n\n\n\n\n\n\n
    \n
    EmpID<\/b><\/p>\n<\/td>\n
    		\n
    NatID<\/b><\/p>\n<\/td>\n
    		\n
    LoginID<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    295847284<\/p>\n<\/td>\n
    		\n
    adventure-works\\ken0<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    245797967<\/p>\n<\/td>\n
    		\n
    adventure-works\\terri0<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    509647174<\/p>\n<\/td>\n
    		\n
    adventure-works\\roberto0<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    112457891<\/p>\n<\/td>\n
    		\n
    adventure-works\\rob0<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    695256908<\/p>\n<\/td>\n
    		\n
    adventure-works\\gail0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, we\u2019re getting exactly the results we would expect. From the user\/application perspective, it\u2019s business as usual.<\/p>\n
    Disable TDE on the user database<\/h1>\n
    At some point, you might decide that you want to disable encryption on a user database. The process is as simple as enabling it. You again run an ALTER<\/code> DATABASE<\/code> statement, only this time turning off the encryption, as shown in the following example:<\/p>\n
    ALTER DATABASE EmpData2\r\nSET ENCRYPTION OFF;<\/pre>\n
    You can verify that encryption has been disabled by again querying the sys.dm_database_encryption_keys<\/code> dynamic management view, which now returns the results shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    DbName<\/b><\/p>\n<\/td>\n
    		\n
    EncryptState<\/b><\/p>\n<\/td>\n
    		\n
    KeyAlgorithm<\/b><\/p>\n<\/td>\n
    		\n
    KeyLength<\/b><\/p>\n<\/td>\n
    		\n
    EncryptKey<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    tempdb<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    AES<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    ASYMMETRIC KEY<\/p>\n<\/td>\n<\/tr>\n
    \n
    EmpData2<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    AES<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    CERTIFICATE<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the EncryptState<\/code> value for the EmpData2<\/code> database is now 1<\/code>, indicating that it is in an unencrypted state. But notice that the tempdb<\/code> database is still encrypted. As it turns out, the database will stay encrypted until it is re-created, which occurs whenever the SQL Server service restarts.<\/p>\n
    For a development or test environment, restarting the service might not be a big deal, but restarting a production instance is an entirely different matter. One more reason to give careful consideration to implementing TDE.<\/p>\n
    In the meantime, if you do have control over your instance of SQL Server, you can restart the service to see for yourself what happens with the tempdb<\/code> database. From there, you can again query the sys.dm_database_encryption_keys<\/code> view, which should return the results shown in the following table.<\/p>\n\n\n\n\n
    \n
    DbName<\/b><\/p>\n<\/td>\n
    		\n
    EncryptState<\/b><\/p>\n<\/td>\n
    		\n
    KeyAlgorithm<\/b><\/p>\n<\/td>\n
    		\n
    KeyLength<\/b><\/p>\n<\/td>\n
    		\n
    EncryptKey<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    EmpData2<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    AES<\/p>\n<\/td>\n
    		\n
    256<\/p>\n<\/td>\n
    		\n
    CERTIFICATE<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the tempdb<\/code> database is no longer included in the results because the database has not been encrypted or subjected to TDE.<\/p>\n
    If you disable TDE on your database, you\u2019re also free to drop the DMK, certificate, and DEK, using the DROP<\/code> MASTER<\/code> KEY<\/code>, DROP<\/code> CERTIFICATE<\/code>, and DROP<\/code> DATABASE<\/code> ENCRYPTION<\/code> KEY<\/code> statements, respectively. Or you can re-enable TDE on the user database at any point. Just keep in mind the impact on the tempdb<\/code> database.<\/p>\n
    Back up the certificate and keys<\/h1>\n
    As already noted, you should back up your certificates and keys, preferably right after you create them. This is also true for the SMK, before you start relying on it to protect your DMKs.<\/p>\n
    To back up the SMK, you can use a BACKUP<\/code> SERVICE<\/code> MASTER<\/code> KEY<\/code> statement, as shown in the following example:<\/p>\n
    Use master;\r\nGO\r\n\r\nBACKUP SERVICE MASTER KEY \r\nTO FILE = 'C:\\DataFiles\\MsSqlServer\\SvcMasterKey.key'\r\nENCRYPTION BY PASSWORD = 'pw1234!';\r\n<\/pre>\n
    The statement itself is fairly straightforward. You must provide the full path for the backup file and a password for encrypting the key in that file. One thing to note, however, is that not all the examples in the Microsoft documentation clearly demonstrate that you must provide a full path, including the file name and its extension. Without these, you will receive an error.<\/p>\n
    It\u2019s also worth noting that the BACKUP<\/code> SERVICE<\/code> MASTER<\/code> KEY<\/code> statement includes no logic for what to do when the file already exists. If it does exist, you\u2019ll again receive an error.<\/p>\n
    Backing up the DMK works much the same way, except that you use a BACKUP<\/code> MASTER<\/code> KEY<\/code> statement:<\/p>\n
    BACKUP MASTER KEY \r\nTO FILE = 'C:\\DataFiles\\MsSqlServer\\DbMasterKey.key'\r\nENCRYPTION BY PASSWORD = 'pw1234!'\r\n<\/pre>\n
    Again, you must provide the full path and file name, along with a password. In addition, the file cannot already exist.<\/p>\n
    Backing up a certificate is a little different because you want to be sure to explicitly back up the private key along with the certificate. For that, you must use a BACKUP<\/code> CERTIFICATE<\/code> statement that includes the WITH<\/code> PRIVATE<\/code> KEY<\/code> clause, as shown in the following example:<\/p>\n
    BACKUP CERTIFICATE TdeCert \r\nTO FILE = 'C:\\DataFiles\\MsSqlServer\\TdeCert.cer'\r\nWITH PRIVATE KEY(\r\n  FILE = 'C:\\DataFiles\\MsSqlServer\\TdeCert.key',\r\n  ENCRYPTION BY PASSWORD = 'pw1234!'\r\n);\r\n<\/pre>\n
    In this case, we\u2019re generating a file for both the certificate and the private key, as well as providing a password for the private key.<\/p>\n
    That\u2019s all there is to backing up the certificate and keys. Of course, you should store your backup files in a remote locate separate from the database files, ensuring that they\u2019re protected from any sort of mischief or recklessness.<\/p>\n
    The rest of the story<\/h1>\n
    Under the right circumstances, TDE can be a useful addition to your data protection strategies. However, TDE protects only data at rest, such as the data and log files. It does not protect data in memory or data transmitted between an application and SQL Server. As a result, your database is still vulnerable to such risks as SQL injection attacks or hijacked administrative permissions. Once data has been unencrypted for use, it is just as vulnerable as it has always been.<\/p>\n
    If you decide to implement TDE, you should do so only as one piece of a much more comprehensive security strategy that takes into account all possible risks to your data. You might also consider alternatives to TDE, such as the new Always Encrypted feature introduced in SQL Server 2016 (a topic we\u2019ll cover in a future article).<\/p>\n
    When Microsoft introduced TDE in SQL Server 2008, it represented an important step in data protection, but it was only one step and was never meant to be the sole means of safeguarding your data. Be sure to keep that in mind when working with TDE.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Transparent Data Encryption (TDE) encrypts the data within the physical files of the database, the ‘data at rest’. Without the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen. The data in unencrypted data files can be read by restoring the files to another server. TDE requires planning but can be implemented without changing the database. Robert Sheldon explains how to implement TDE.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,143531],"tags":[],"coauthors":[6779],"class_list":["post-69571","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69571","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=69571"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69571\/revisions"}],"predecessor-version":[{"id":91256,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69571\/revisions\/91256"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=69571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=69571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=69571"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=69571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}