Encryption in SQL Server

Reference documents:

Elaborate on encryption in SQL Server

Transparent Data Encryption (TDE)

Database encryption Key (DEK) management

Introduction to encryption in SQL Server

In SQL Server2000 and previous versions, encryption is not supported. All cryptographic operations need to be done in the program. This leads to a problem where the data encrypted in the database is only meaningful for a particular program, and if the other program does not have a corresponding decryption algorithm, the data becomes meaningless. For example, I have written a Web application (refer to the blog: Custom SqlMembershipProvider method), want to let the user information in the password field with ciphertext saved, I was in the program to encrypt the user's plaintext password later saved to the database.

To SQL Server2005, column-level encryption was introduced. Allows encryption to be performed on a specific column, which involves 4 of built-in functions for encryption and decryption.

In the SQL Server 2008 era, the introduction of transparent Data encryption (TDE), the so-called transparent data encryption, is the encryption in the database, but from the point of view of the program as if there is no encryption, and column-level encryption is different, TDE encryption level is the entire database. A database file or backup that is encrypted with TDE cannot be attached or recovered on another instance that does not have a certificate.

Symmetric and Asymmetric encryption

In general, encryption can be divided into two main classes, symmetric (symmetric) and asymmetric (asymmetric) encryption.

Symmetric encryption is an encryption algorithm that encrypts and decrypts the same key, that is, the cryptographic key = Decryption key. Symmetric encryption is usually weak, because the data is used not only to transmit the data itself, but also to transfer the key in some way, which is likely to cause the key to be stolen during transmission.

Asymmetric encryption is a cryptographic algorithm that encrypts and decrypts a different key, which is the encryption key! = Decryption key. The key used for encryption is called the public key, and the key used for decryption is called the private key. As a result, security is much better than symmetric encryption. Of course, there will be a short, asymmetric encryption in the usual way the algorithm will be more complex than the symmetric key, so it can lead to a performance loss.

Therefore, a compromise is to use a symmetric key to encrypt the data and use an asymmetric key to encrypt the symmetric key. This can take advantage of the high performance of symmetric keys and the reliability of asymmetric keys.

Certificate and key creation

Each database has only one database master key (master key), which is a database-level key. Can be used to provide encryption for creating a database-level certificate or asymmetric key. Created through T-SQL statements, as shown in code 1.

-- Create certificate, symmetric and asymmetric keys require master key -- Create master Key CREATE KEY  by = ' [email protected] ' ; -- Remove Master Key Drop KEY

If you want to see if the database has master key, you can query by using the following T-SQL statement

-- There is no place to view master key in SSMs, but you can see whether the database has a master key in sys.databases Select  from sys.databases

The results of my query are as follows:

From this we can see that the master and TESTDB4 two databases contain the master key, and the database does not. So the master is not only under the master database, if we want to create a certificate and key in TESTDB4, then we must create a master key for the TESTDB4 database.

T-SQL code that creates a certificate, a symmetric key, and an asymmetric key is given below

 UseTESTDB3;--Create a certificateCREATECERTIFICATE certtest withSUBJECT= 'Test Certificate'GO--creating an asymmetric keyCREATEAsymmetricKEYTestasymmetric withAlgorithm=rsa_2048 Encryption byPASSWORD= '[email protected]'; GO--Create a symmetric keyCREATESymmetricKEYTestsymmetric withAlgorithm=aes_256 Encryption byPASSWORD= '[email protected]';GO

Once created, we can find the certificate and key we created in SSMs as shown in:

The certificate, symmetric key, and asymmetric key created above are encrypted by a fixed symmetric key [email protected]. In fact, when creating a certificate, symmetric key, asymmetric key, you can also use the certificate, symmetric key, asymmetric key encryption, T-SQL code as follows:

--To encrypt a symmetric key by a certificateCREATESymmetricKEYSymmetricbycert withAlgorithm=aes_256 Encryption byCERTIFICATE certtest;GO--Symmetric key encryption by symmetric keyOPENSymmetricKEYTestsymmetric Decryption byPASSWORD='[email protected]'--You must open the testsymmetric before you can use Testsymmetric to encryptCREATESymmetricKEYSymmetricbysy withAlgorithm=aes_256 Encryption bySymmetricKEYTestsymmetric;GO--symmetric key encryption by asymmetric keyCREATESymmetricKEYSymmetricbyasy withAlgorithm=aes_256 Encryption byAsymmetricKEYTestasymmetric;GO

Once created, we can see the results of our query in symmetric key:

Column-Level encryption

In the column-level encryption we will use the adventureworks2008r2 sample database, in Sales.creditcard This table we can see that the Cardnumber column is clear, as shown in:

In the actual production environment, if the user's credit card number is plaintext is very insecure, we would like to use this cardnumber in the column-level encryption. It is important to note that the columns that are encrypted or decrypted must be of type varbinary.

First we create a creditcard table structure with no data, and cardnumber This column is the varbinary type, the T-SQL code is as follows:

-- Create a table structure without data, with where 1<>1 to control SELECT  =CONVERT(varbinary,cardnumber), Expmonth, Expyear, ModifiedDateto from  AdventureWorks2008R2.Sales.CreditCard   WHERE1<>1

After creating the table structure, we import the data into this table:

--Open a previously created symmetric key that was encrypted by the certificateOPENSymmetricKEYSymmetricbycert decryption byCERTIFICATE certtest;--Using this key to encrypt cardnumber this data column, insert the new table, use the EncryptByKey functionInsertCreditcard_encrypt (Cardtype,cardnumber_encrypt, Expmonth, Expyear, ModifiedDate)Select Top TenCardtype,cardnumber_encrypt=EncryptByKey (Key_GUID ('Symmetricbycert'), Cardnumber), Expmonth,expyear, ModifiedDate fromAdventureWorks2008R2.Sales.CreditCard

Query Creditcard_encrypt This table, we can find that the Cardnumber column is ciphertext, as shown in:

But we can decrypt the Cardnumber column with a symmetric key.

-- Open a previously created symmetric key that was encrypted by the certificate OPEN KEY  by CERTIFICATE certtest; -- View Cardnumber_encrypt Select Convert (nvarcharfrom creditcard_encrypt;

The result of using this statement is the plaintext of the cardnuber. Note that the open symmetric key is required and the query result is null if you do not open the symmetric private key. If you have previously opened this private key, you may not have to open it again here.

Transparent Data encryption

Transparent data encryption was introduced in SQL Server 2008 (Transparent Data Encryption, hereinafter referred to as TDE), because the encryption is called transparent because it appears to the program or user using the database as if it were not encrypted. TDE encryption is database-level. The encryption and decryption of data is performed by the data Engine, in page units. Encrypted at write time, decrypted when read out. The client program doesn't have to do anything at all. (Encryption of the database file is performed on the page level. The pages in an encrypted database is encrypted before they is written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.)

The primary role of TDE is to prevent a database backup or data file from being stolen, and the person who steals the database backup or file cannot recover or attach the database without the data encryption key. I copy a encrypted database to another server, the view attach this database, but the error is as follows:

The above error indicates that the server does not have a corresponding certificate. This from another point of view also reminds us that if it is a encrypted database, I will not only back up the databases, but also to make a backup of the certificate, if our own certificate is lost, it will not open the database.

TDE encrypts using a data encryption Key (DEK). The DEK is present in the master database protected by the service master key, as shown in the protection hierarchy.

The four steps for using TDE are:

2. Creates a master key (create a master key)

    use   master;  go  --  Create a master key  in the master database create  MASTER key  encryption by  PASSWORD =   [email protected"  Span style= "color: #ff0000;" > '  

4. Create or obtain a certificate protected by master key (create or obtain a certificate protected by the master key)

   -- Create a certificate with Masterkey Myservercert CREATE  with = ' My DEK Certificate ' ; Go

5. Create a database key using a certificate (create a database encryption key and protect it by the certificate)

   1. However, after the database encryption KEY (DEK) is created, the following warning is reported:

       Use TESTDB2; GO -- Create a database encryption key and use Myservercert to encrypt the certificate CREATE DATABASE KEY  with =   by SERVER CERTIFICATE Myservercert; GO

      
      

      Warning:the Certificate used for encrypting the database encryption key have 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 has Backups of both the certificate and the private key or you are not being able to open the database.

      This is because we use a certificate to create a DEK and encrypt the database, so we recommend a backup of the certificate, otherwise we cannot open the database ourselves if the certificate is compromised. The method of certificate backup can refer to: Backup certificate, my T-SQL shows:

      BACKUP  to FILE = ' D:\storedcerts\MyServerCert ' --

      It is important to note that our Myservercert is not encrypted by private key, but only with master key encryption. If you use private key, you also need to back up the private key. If we do a backup of the Myservercert, the private key operation will error as follows:

      No decryption password should be provided because the private key of this certificate are encrypted by a master key.

7. Set the database to TDE (set the encryption)

The last thing we need to do is say the database is set to encrypt, and the T-SQL statement looks like this:

ALTER DATABASE SET on;

At this point we can also right-click Testdb2->tasks->manage Database encryption to view the encryption settings as shown in:

We can also use T-SQL statements to see which databases have TDE encryption, and the T-SQL statements are as follows:

/* The value 3 represents an    encrypted state */ SELECT DBName=db_name from sys.dm_database_encryption_keysWHERE = 3 ; GO

The query results show that tempdb is also transparently encrypted, for reasons we can find on MSDN, I put the original digest out:

Transparent Data Encryption and the tempdb System Database

The tempdb system database would be encrypted if any and database on the instance of SQL Server are encrypted by using TDE . This might has a performance effect for unencrypted databases on the same instance of SQL Server. For more information on the tempdb system database, see tempdb database.

Backup and restore of master key and certificate

--Backup Master KeyBACKUPMASTERKEY  to FILE = 'D:\storedkeys\masterkey'Encryption byPASSWORD= '[email protected]'Go--Backup CertificateBACKUPCERTIFICATE Myservercert to FILE = 'D:\storedcerts\MyServerCert';--Save address of certGo--Restore master Key UseMasterRESTOREMASTERKEY      from FILE = 'D:\storedkeys\masterkey'decryption byPASSWORD= '[email protected]'Encryption byPASSWORD= '[email protected]';GO--because I still have the original certificate here, I will prompt the following message: The old and new master keys is identical. No data re-encryption is required.--Restore CertificatesCREATECERTIFICATE MyServerCert2 from FILE = 'D:\storedcerts\MyServerCert' GO --because the certificate already exists, the following information is prompted:--a certificate with Name ' MyServerCert2 ' already exists or this certificate already have been added to the database.--It is important to note that certificates are not differentiated according to the title of the certificate. My original certificate name is Myservercert, the certificate created here is named MyServerCert2, but it is a backup from Myservercert, or an error. 

Encryption in SQL Server