Transparent data encryption

<h2 id="microsoft-sql-server-tde">Microsoft SQL Server TDE</h2>
<p>SQL Server utilizes an encryption hierarchy that enables databases to be shared within a cluster or migrated to other instances without re-encrypting them.  The hierarchy consists of a combination of symmetric and asymmetric ciphers:<a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a>
</p>
<ul><li>Windows <a href="/facts/Data_Protection_API/e5xU1vmI">Data Protection API (DPAPI)</a> protects a single instance-wide Service Master Key (SMK).</li>
<li>The Service Master Key encrypts the Database Master Key (DMK).</li>
<li>The Database Master Key is used in conjunction with a certificate to encrypt the Database Encryption Key.</li>
<li>The Database Encryption Key is used to encrypt the underlying database files with either the <a href="/facts/Advanced_Encryption_Standard/HdXGBY2Q">AES</a> or <a href="/facts/Triple_DES/sgzSD50P">3DES</a> cipher.</li>
<li>The <i>master</i> database that contains various system level information, user accounts and management services is not encrypted.</li></ul>
<p>During database backups, <a href="/facts/Data_compression/gMFuY4FT">compression</a> occurs after encryption. Due to the fact that strongly encrypted data cannot be significantly compressed, backups of TDE encrypted databases require additional resources.
</p><p>To enable automatic booting, SQL Server stores the lowest level encryption keys in persistent storage (using the <a href="/facts/Data_Protection_API/e5xU1vmI">DPAPI</a> store).  This presents a potential security issue because the stored keys can be directly recovered from a live system or from backups and used to decrypt the databases.<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a>
</p>
<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/Disk_encryption/v7E50jGw">Disk encryption</a></li>
<li><a href="/facts/Encryption/16q29Fdv">Encryption</a></li>
<li><a href="/facts/Hardware_security_module/dhpo8qoR">Hardware security module</a></li></ul>

<h2 id="external-links">External links</h2>
<ul><li><a href="https://www.database-encryption.com/">Alternative 3rd party solution for all SQL Server Editions</a></li>
<li><a href="https://www.netlibsecurity.com/">Another alternative 3rd party solution for all SQL Server Editions</a></li>
<li><a href="https://technet.microsoft.com/en-us/library/cc645993%28v=sql.105%29.aspx#Enterprise_security">Enterprise Security Features Supported by Microsoft SQL Server 2008 R2 Editions</a></li>
<li><a href="https://technet.microsoft.com/en-us/library/cc645993.aspx#Enterprise_security">Security Features Supported by Microsoft SQL Server 2012 Editions</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/bb934049.aspx">Understanding Transparent Data Encryption (TDE) (Microsoft)</a></li>
<li><a href="http://www.oracle.com/technology/obe/11gr1_db/security/tde/tde.htm">Using Transparent Data Encryption in Oracle Database 11g</a></li>
<li><a href="http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf">Oracle Transparent Data Encryption best practices</a></li>
<li><a href="http://www.oracle.com/technology/deploy/security/database-security/transparent-data-encryption/index.html">TDE column encryption and TDE tablespace encryption in Oracle Database 11gR1</a></li>
<li><a href="http://download.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm#BABDFHHH">http://download.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm#BABDFHHH</a></li>
<li><a href="https://www.p6r.com/articles/2014/11/22/p6rs-pkcs-11-provider/">P6R's PKCS#11 Provider and Oracle TDE</a></li>
<li><a href="https://techcommunity.microsoft.com/t5/sql-server/sql-server-2019-standard-edition/ba-p/986121">[1]</a></li></ul>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>"SQL Server TDE vs CLE". Retrieved 2017-06-02. <a href="https://info.townsendsecurity.com/sql-server-tde-vs-cell-level-encryption-a-brief-comparison" target="_blank">https://info.townsendsecurity.com/sql-server-tde-vs-cell-level-encryption-a-brief-comparison</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>"SQL Server 2019 Standard Edition"Microsoft Tech Community <a href="https://techcommunity.microsoft.com/t5/sql-server/sql-server-2019-standard-edition/ba-p/986121" target="_blank">https://techcommunity.microsoft.com/t5/sql-server/sql-server-2019-standard-edition/ba-p/986121</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>"Fix pack summary". IBM. <a href="https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.wn.doc/doc/c0061179.html" target="_blank">https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.wn.doc/doc/c0061179.html</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
<li id="fn:4"><p>"Transparent Data Encryption (TDE)" Microsoft TechNet <a href="https://technet.microsoft.com/en-us/library/bb934049(v=sql.110).aspx" target="_blank">https://technet.microsoft.com/en-us/library/bb934049(v=sql.110).aspx</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li>
<li id="fn:5"><p>Simon McAuliffe, "The Anatomy and (In)Security of Microsoft SQL Server Transparent Data Encryption (TDE)", 19-Mar-2016 <a href="https://medium.com/@s.mcauliffe_17464/the-anatomy-and-in-security-of-microsoft-sql-server-transparent-data-encryption-tde-or-how-to-d164eb08564" target="_blank">https://medium.com/@s.mcauliffe_17464/the-anatomy-and-in-security-of-microsoft-sql-server-transparent-data-encryption-tde-or-how-to-d164eb08564</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li>
</ol>

Transparent data encryption open-in-new

Transparent data encryption