Implement cell-level encryption - SQL Server Tutorial

- [Instructor] Data stored within tables in the database is sometimes referred to as at rest. This differentiates it from data that is in transit, or actively being passed to a client application or user. Encrypting data at rest ensures that it can't be read out of the table without the appropriate decryption keys present. One way to do this is to designate specific columns of a data table to hold encrypted values. When a new record is being entered, the value will get encrypted before being stored. When that piece of information is requested again, it'll need to get decrypted back into a legible format before being usable. Otherwise, the scrambled encrypted data is returned instead. The process of storing encrypted data in a table is either called cell level or column level encryption, depending on who you talk to. Let's take a look at how to implement this using the Wide World Importer's database. We're first going to create a new column in the Sales.Customers table to hold some credit card data and we want to make sure that we can encrypt this data so the credit card values will be stored as a varbinary (max) data type. Then, we'll select the CustomerID, CustomerName, and the new CreditCard column out of the Sales.Customers table, but I only want to see CustomerID number one. Here we can see Tailspin Toys currently doesn't have a credit card on file. I would like to add a credit card number here, but I want to make sure that it stays encrypted. In order to do that, we need to create a SYMMETRIC KEY in order to encrypt the data that we're about to enter. Let's go ahead and scroll down a little bit and we'll create a SYMMETRIC KEY using these lines here. Create the SYMMETRIC KEY called CreditCardKey. We'll use the encryption algorithm AES_128 and we'll encrypt this key using a password which I've got here as just StrongPassword. When I execute these lines, the SYMMETRIC KEY is created. In order to see it, let's expand the Databases folder in Object Explorer and then go into the Wide World Importers database, go down to Security, and then take a look at the Symmetric Keys folder. I'll right click on it and refresh and now we can expand it and see the new key that we just created right there. Now let's use this key in order to add some encrypted data to the table. I'm going to update the Sales.Customers table and we'll insert a value into the CreditCard column. To encrypt the data, I'll use the EncryptByKey function which requires two parts, the global unique identifier for the key that we want to use and the value that we want to encrypt. In this case, we can get the GUID by using the Key_GUID function against the name of the key that we want to use and then the value that I want to encrypt is the credit card number 5555-1234-5678-0000. I also want to make sure that I'm not using the same credit card number for all of my customers so we'll specify where the CustomerID is equal to one. I'll highlight these lines and execute them. Now we get the message that one row is affected, but if I scroll back up, and select everything out of that data table again, we'll see that Tailspin Toys currently still doesn't have a credit card number applied to it. Just because we have a key present in the database, doesn't mean that we have permission to use it. In order for SQL server to authenticate us, we need to actually open the key first before we can use it. I'll scroll down and take a look at that. We'll open the SYMMETRIC KEY called CreditCardKey and we'll decrypt it using the password that was established when the key was created. I've highlighted these lines to open the key. Then we can go ahead and add in the encrypted value into our column. Once again we get the message that one row was affected and if I scroll down to view the data, we'll see we have a new encrypted value stored inside of the CreditCard column. In order to view the actual credit card number, we'll need to decrypt this data. To do that, we'll run another SELECT statement. We'll select the CustomerID and CustomerName columns just like before, for these two columns here, but then instead of the CreditCard column, we'll use the convert function to convert it to a char(19) data type. The data that we want to convert is the DecryptByKey function applied to the CreditCard column and then we'll output it as an alias column with the alias of CreditCardNumber. Once again, this is all coming from the Sales.Customers table and just for CustomerID number one. When I execute these lines, we'll see the revealed credit card number appear there. When you're done working with encrypted data, you want to make sure that you close the SYMMETRIC KEY that you've got currently opened in order to protect the security of the information inside. I'll highlight line 46 and execute it to close the key. Now if I were try and select that data again by converting it and decrypting the data you notice how they give the value of null once again. The data is still there, it's just not visible. Let's go ahead and scroll down a little bit. So that first example created a SYMMETRIC KEY using a password to protect it. We can do the same thing by issuing a certificate first and then protecting the SYMMETRIC KEY with a certificate instead of the password. Let's take a look at that by creating a MASTER KEY for the database. I'll create a MASTER KEY by a password and I'll use this password here to protect the MASTER KEY. Then we'll create a certificate called Encryption Certificate and finally we can create a SYMMETRIC KEY and this time I'll call it CreditCardKeyCert. We'll use the same algorithm, AES_128, but this time instead of encrypting it by password, we'll encrypt it by certificate and use the certificate instead. That'll create a new certificate and if I go down to the Symmetric Keys folder you can right click and refresh it and we'll see that one there as well. Finally, you would open and close this key in exactly the same way. We'll open the SYMMETRIC KEY CreditCardKeyCert and we'll decrypt it by the certificate instead of the password and we'll close it in the same way. Finally, let's go ahead and remove that certificate from our system. This time I get a message that says that the certificate cannot be dropped because one or more entities are either assigned or encrypted using it. If we were to get rid of the certificate from our system, which we can see by right clicking and refreshing the Certificates folder, I'd no longer be able to use this CreditCardKeyCert SYMMETRIC KEY. Let's go through and reset our database back to its original state. First I'll alter the Table Sales.Customers and we'll get rid of the CreditCard column. Then we'll drop the SYMMETRIC KEY called CreditCardKey. Then we'll scroll down a little bit. We'll drop the SYMMETRIC KEY that we created called CreditCardKeyCert. Now that that's gone, we can get rid of the certificate and finally we can drop the database MASTER KEY. If we right click on the Security folder and choose refresh, you'll see that everything has been returned to the state it was in when we got started. So there's two different ways to create a SYMMETRIC KEY in SQL Server 2016, the first using a password and the second by using a certificate issued by the database's Master Key. Either way, the Key will need to be opened by a client or a user with the appropriate permissions in order to make sense of the data.