The DMK protects the certificate, and the certificate protects the database encryption key (DEK) in the user database. As you can see, the next level down our hierarchy is a certificate, which you also create in the master database. SQL Server uses the SMK and a user-supplied password to encrypt the DMK with the 256-bit AES algorithm.īefore 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. With TDE, you create the DMK in the master database, even though you’ll be encrypting a user database. However, with column-level encryption, you create the DMK in the user database where the column data will be encrypted. The DMK is a symmetric key, just like you find with column-level encryption. In the TDE encryption hierarchy, the SMK sits below the DPAPI, and a DMK sits below the SMK. You can use the key to encrypt credentials, linked server passwords, and the database master keys (DMKs) residing in different databases. SQL Server creates the SMK the first time the instance is started. The approach used for implementing TDE is similar, but different enough to take a closer look.Īs 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 database.
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. If you’re new to SQL Server encryption, you might want to review that article first. The first one ( Encrypting SQL Server: Using an Encryption Hierarchy to Protect Column Data) covers column-level encryption. The article is the second in a series about SQL Server encryption.
In this article, we look at how to implement TDE on a user database. Even so, TDE is relatively easy to enable, once you’ve decided this is the path you want to travel. You cannot pick-and-choose like you can with column-level encryption. When enabled, TDE encrypts all data in the database, as well as some outside the database. SQL Server TDE takes an all-or-nothing approach to protecting data. 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. 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. With the release of SQL Server 2008, Microsoft expanded the database engine’s security capabilities by adding Transparent Data Encryption (TDE), a built-in feature for encrypting data at rest.
0 kommentar(er)
