sql server 加密
透明数据加密（TDE） ( Transparent Data Encryption (TDE) )
SQL Server has two ways of encrypting data. One way is by protecting data on the table, record or column level, and the other way is by protecting data “at the rest”. One of the best crypto features in the database world today is known as a Transparent Data Encryption.
SQL Server有两种加密数据的方式。 一种方法是保护表，记录或列级别的数据，另一种方法是“其余”保护数据。 当今数据库世界中最好的加密功能之一就是透明数据加密。
Imagine the following scenario. Someone has an unauthorized access to your database system environment. That person finds a way to get the last database backup file, copies it and takes it in an unsecured environment. In this moment, the security mechanism just fell apart.
想象以下情况。 有人未经授权访问您的数据库系统环境。 该人员找到了一种获取最后一个数据库备份文件的方法，将其复制并在不安全的环境中进行处理。 在这一刻，安全机制刚刚崩溃。
This scenario illustrates what can happen when someone illegally copies, detaches, and restores your database. The consequences for such activity can be substantial, depending on the sensitivity of your data environment.
此方案说明了有人非法复制，分离和还原数据库时可能发生的情况。 根据数据环境的敏感性，此类活动的后果可能非常严重。
TDE performs real-time I/O encryption and decryption of the database files (data and log). The encryption uses a Database Encryption Key (DEK) which is stored in the database boot record for availability during recovery. Backup of databases that have implemented TDE are also encrypted by using the DEK.
TDE对数据库文件（数据和日志）执行实时I / O加密和解密。 加密使用数据库加密密钥（DEK），该密钥存储在数据库启动记录中，以在恢复期间保持可用性。 还使用DEK对已实现TDE的数据库备份进行加密。
DPAPI encrypts the SMK (created at the time of a SQL Server setup). SMK encrypts the DMK of the master database. DMK of the master database creates a certificate in the master database. The certificate encrypts the DMK in the user database. The entire user database is secured by DEK of the user database by using TDE.
DPAPI加密SMK（在SQL Server安装时创建）。 SMK加密主数据库的DMK。 主数据库的DMK在主数据库中创建一个证书。 该证书对用户数据库中的DMK进行加密。 整个用户数据库通过使用TDE由用户数据库的DEK保护。
To implement TDE, we need to follow these steps:
要实现TDE，我们需要遵循以下步骤：
Create a master key创建一个主密钥 Create or obtain a certificate protected by using the master key创建或获取使用主密钥保护的证书 Create a database encryption key and protect it by using the certificate创建数据库加密密钥并通过使用证书对其进行保护 Set the database to use encryption设置数据库以使用加密
USE master;GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd';GOCREATE CERTIFICATE TDE WITH SUBJECT = 'My TDE Certificate';GOSELECT * FROM sys.certificatesCREATE DATABASE CryptoDBGOUSE CryptoDBGOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVER CERTIFICATE TDE;GOALTER DATABASE CryptoDBSET ENCRYPTION ON;GOUSE masterGODROP DATABASE CryptoDBGODROP CERTIFICATE TDEGODROP MASTER KEYGO
Key point of TDE is that an entire database is encrypted on the fly. Data in an encrypted database are encrypted before they are written to a disk and decrypted when read into memory. In this case you should pay attention on the performance issue on heavily transaction load system.
TDE的关键是整个数据库都在动态加密。 加密数据库中的数据在写入磁盘之前先经过加密，然后在读入内存时解密。 在这种情况下，您应该注意事务负载较重的系统上的性能问题。
加密备份 ( Encrypted backup )
In some case scenarios you only want to encrypt backup files to protect data “at the rest”. SQL Server have one new crypto feature and that is encrypted backup. In a nutshell almost same implementation like TDE, but major difference that database is not on the crypto “pressure” during regular transaction processes. The idea is that you have a business need to keep your backup secured. In this case there is no performance issues because the only encryption/decryption is during the backup/restore operation.
在某些情况下，您只想加密备份文件以“其余”保护数据。 SQL Server 具有一项新的加密功能，即加密备份。 简而言之，类似于TDE的实现，但是主要区别在于，在常规事务处理过程中，数据库不在加密“压力”上。 这样做的目的是您需要确保备份安全。 在这种情况下，没有性能问题，因为唯一的加密/解密是在备份/还原操作期间进行的。
USE master;GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'GOCREATE CERTIFICATE CryptoBackup WITH SUBJECT = 'My BackUp Certificate';GOCREATE DATABASE CryptoDBGOBACKUP DATABASE CryptoDBTO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CryptoDB.bak'WITHCOMPRESSION,ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = CryptoBackup),STATS = 10GODROP CERTIFICATE CryptoBackupGODROP MASTER KEYGODROP DATABASE CryptoDBGO
Or you can do it through SSMS GUI:
或者，您可以通过SSMS GUI执行此操作：
Before you can access the Encrypted backup option on this dialog window, you need to check (under the Medio Options tab) this option:
在访问此对话框窗口上的“加密的备份”选项之前，需要检查（在Medio Options选项卡下）此选项：
对称加密 ( Symmetric encryption )
Symmetric encryption is the type of encryption that uses the same key for encryption and decryption. SQL Server allows you to choose from several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
对称加密是使用相同密钥进行加密和解密的加密类型。 SQL Server允许您从几种算法中进行选择，包括DES，三重DES，TRIPLE_DES_3KEY，RC2，RC4、128位RC4，DESX，128位AES，192位AES和256位AES。
When a symmetric key is created, it must be encrypted by using at least one of the following:
创建对称密钥时，必须使用以下至少一种加密方式：
Certificate证书 Password密码 Symmetric key对称密钥 Asymmetric key非对称密钥
Example of creating a symmetric key encrypted by certificate:
创建通过证书加密的对称密钥的示例：
-- Create a AES 256 symmetric keyCREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = AES_256ENCRYPTION BY CERTIFICATE MyCert;GO
实施对称加密 ( Implementing Symmetric Encryption )
In this exercise, you will pass all necessary steps for implementing symmetric encryption in a user-created sample database. Also, you will encrypt data in a user table.
在本练习中，您将通过所有必要的步骤，以在用户创建的示例数据库中实施对称加密。 同样，您将加密用户表中的数据。
--1. Create sample database.CREATE DATABASE SymCryptDBGOUSE SymCryptDBGO-- 2. Create DMK.CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd';GO--3. Create certificate and symmetric key.CREATE CERTIFICATE SymCertWITH SUBJECT = 'Certificate for sym key',START_DATE = '-01-06',EXPIRY_DATE = '-01-06';GOCREATE SYMMETRIC KEY SymKeyWITH ALGORITHM = AES_256ENCRYPTION BY CERTIFICATE SymCert;GO--4. Create sample table:CREATE TABLE EncryptedCustomer(CustomerID int NOT NULL PRIMARY KEY,FirstNamevarbinary(200),MiddleName varbinary(200),LastName varbinary(200));--5. Open the key that is protected by the certificate.OPEN SYMMETRIC KEY SymKeyDECRYPTION BY CERTIFICATE SymCert;GO--6. Insert the sample from AdventureWorks and encrypt the data.INSERT INTO EncryptedCustomer(CustomerID,FirstName,MiddleName,LastName)SELECTBusinessEntityID,EncryptByKey(Key_Guid(N'SymKey'), FirstName),EncryptByKey(Key_Guid(N'SymKey'), MiddleName),EncryptByKey(Key_Guid(N'SymKey'), LastName)FROM AdventureWorks.Person.Person;GO--7. Close the key.CLOSE SYMMETRIC KEY SymKey;GO --8. Check the encrypted data.SELECT *FROM EncryptedCustomer;GO
--9. Decrypt the sample data.OPEN SYMMETRIC KEY SymKeyDECRYPTION BY CERTIFICATE SymCert;GOSELECTCustomerID,CAST(DecryptByKey(FirstName) AS nvarchar(100)) ASDecryptedFirstName,FirstNameFROM EncryptedCustomer;GO
--10. Cleaning partDROP SYMMETRIC KEY SymKeyGODROP CERTIFICATE SymCertGODROP MASTER KEYGO
非对称加密 ( Asymetric encryption )
Asymmetric key pair is made up of a private key and a public key. Each key can decrypt data encrypted by the other key pair. Asymmetric encryption/decryption are relatively resource-intensive, but they provide a higher level of security than the symmetric encryption.
非对称密钥对由私钥和公钥组成。 每个密钥可以解密由另一个密钥对加密的数据。 非对称加密/解密相对耗费资源，但是与对称加密相比，它们提供更高的安全级别。
A asymmetric key is a database object protected at the database level. When executed without the FROM clause, CREATE ASYMMETRIC KEY generates a new key pair. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly.
非对称密钥是在数据库级别受保护的数据库对象。 在不使用FROM子句的情况下执行时，CREATE ASYMMETRIC KEY会生成一个新的密钥对。 与FROM子句一起执行时，CREATE ASYMMETRIC KEY从文件中导入密钥对或从程序集中导入公钥。
By default, the private key is protected by the DMK. If no DMK has been created, a password is required to protect the private key. If a DMK does exist, the password is optional.
默认情况下，私钥受DMK保护。 如果尚未创建任何DMK，则需要输入密码来保护私钥。 如果确实存在DMK，则密码是可选的。
The private key can be 512, 1024, or 2048 bits long.
私钥的长度可以为512、1024或2048位。
CREATE ASYMMETRIC KEY StrongKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd'; GOCREATE ASYMMETRIC KEY KeyPairFromFileAUTHORIZATION Denis FROM FILE = 'C:\Keys\Asymmetric\DenisCert.tmp'ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd';GO
对称与非对称 ( Symmetric vs. Asymmetric )
Is one type of encryption key recommended over the other? Yes, but as always, performance is an issue. Symmetric key algorithms are mathematically simpler, and as a result, faster. The difference in speed can be significant even into the 100x faster range. Therefore, symmetric key algorithms are the way to go when encrypting data.
是否建议一种加密密钥优于另一种？ 是的，但与往常一样，性能是一个问题。 对称密钥算法在数学上更简单，因此速度更快。 速度差异甚至可以达到100倍的速度范围。 因此，对称密钥算法是加密数据时要走的路。
In moust cases asymmetric encryption in SQL Server is used to defend a symmetric key
在极少数情况下，SQL Server中的非对称加密用于保护对称密钥
散列 ( Hashing )
Sometimes business requirements will demand to hide some data without using encryption/decryption. There are plenty of mechanisms to make some data/information unreadable. Hashing is one of those mechanisms. Hash functions are powerful, fast and efficient ways to hide data and to check data integrity.
有时，业务需求会要求隐藏一些数据而不使用加密/解密。 有很多机制使某些数据/信息不可读。 散列是这些机制之一。 散列函数是隐藏数据和检查数据完整性的强大，快速和高效的方法。
A cryptographic hash function is a function that implements an algorithm that takes some data and returns a fixed-size bit string.
加密哈希函数是一种实现算法的函数，该算法需要一些数据并返回固定大小的位字符串。
The hash function has the following main properties:
哈希函数具有以下主要属性：
Easy to compute the hash value for any given message轻松计算任何给定消息的哈希值 Impossible to generate a message that has a given hash无法生成具有给定哈希值的消息 Impossible to modify a message without changing the hash无法修改消息而不更改哈希值 Impossible to find two different messages with the same hash.不可能找到两个具有相同哈希值的不同消息。
Example;
例;
SELECT HashBytes('SHA1', 'Jasmin Azemović');Result is:------------------------------------------0x026A71290A685DA3E09A11CA110014D3E0118191
In next example we will just add an extra space after name:
在下一个示例中，我们将在名称后添加一个额外的空格：
SELECT HashBytes('SHA1', 'Jasmin Azemović');GOSELECT HashBytes('SHA1', 'JasminAzemović');GO
As you see, hash output is totally different
如您所见，哈希输出完全不同
Hashing and encryption are not the same function and each have a totally different usage in practice. The basic point to remember is that hashing is used when there is no need to make a reversible operation, whereas encryption is used when you need to decrypt data at some later point in time.
散列和加密功能不同，实际上它们的用法完全不同。 要记住的基本要点是，在不需要进行可逆操作时使用哈希，而在以后需要解密数据时使用加密。
摘要 ( Summary )
As you have seen so far, protecting data is the most important thing in database environments. When all security elements fail (i.e. installation errors, authentication, authorization, bad access policy, etc.), there is no more protection. This two articles taught you how to implement advanced techniques for protecting data such as cryptography.
到目前为止，您已经知道，保护数据是数据库环境中最重要的事情。 当所有安全元素均失败（例如，安装错误，身份验证，授权，错误的访问策略等）时，将没有更多保护措施。 这两篇文章教您如何实现用于保护数据的高级技术，例如密码学。
翻译自: /sql-server-confidential-part-ii-sql-server-cryptographic-features/
sql server 加密
