Eighth SQL Server secure data encryption

This article is the eighth of the SQL Server Security series, please refer to the original text for more information.

Relational databases is used in an amazing variety of applications with connections from a dizzying array of clients over Widely distributed networks, especially the Internet, makes data almost accessible to anyone, anywhere. Databases can contain a considerable amount of human knowledge, including highly sensitive personal information and critical data.
These characteristics of the database make it possible for someone to steal data or tamper with it to damage its owner. Ensuring that your data is secure is a critical part of configuring SQL Server and developing applications to store data. This series explores the SQL Server 2012 Security Foundation so that you can protect your data and server resources, getting as granular as you need to being to protect against the unique security Threats that can affect your data. Most of the features apply to earlier versions of SQL Server, but I'll also discuss features that apply only to SQL Server 2012 and later versions.
An important security concept is defense-in-depth, which means that the best security is layered rather than relying on a single layer of protection. After your network, server, instance of SQL Server, and database security, you can add the last powerful layer of data protection by encrypting sensitive data. This article explores how to protect data through encryption, you'll learn about the encryption key hierarchy and various keys that you can use to encrypt data, and how to manage keys or let SQL Server do it for you.
Data Encryption
So far in this series you've learned a lot about security features in SQL Server to protect your data and database objects. Is your data secure when you combine network security, such as firewalls, user account privileges, and other tools?
No, although SQL Server 2012 and 2014 are the safest versions available today, it is possible to be successful if you use all of the security features wisely and efficiently. Hackers are extremely intelligent people, so it's very likely that someone, sometime, can attack your database library server and access your data. Even if you have hundred percent of confidence such an attack is impossible, you might find yourself on the receiving end of a insider attack-a trusted employee Gone bad.
A security principle is defense-in-depth – it can help you understand how to make your data or other resources that need to be protected to the highest level of security. Defense in depth means that you cannot rely on a single security measure to protect valuable things. You should add a layer of protection to the type of security that is not available, so that attackers must first break through complex, difficult obstacles to get your data.
This is why many medieval castles have moats as their first line of defense. The next layer of protection is a reinforcing plate, a few metres thick oak doors and stone walls. Often there are other thick, sturdy walls inside the outer walls. It's a layer of protection. The people and the treasure.
In a similar way, SQL Server has many security layers and tools for creating and maintaining them. But when your data is extremely valuable, you should add the last defense: encryption.
Since SQL SERVER2005 began to support data encryption, later versions have been upgraded.
You can use a variety of encryption types, including keys, that are several types of secrets that convert data into readable gibberish unless the user unlocks the key. SQL Server supports multiple cryptographic algorithms. And best of all, you can have servers doing all the work, managing keys and keeping secrets, which has been the hardest part of encryption.
Tip: Encryption is a very processor-intensive operation because it requires complex calculations. An unreasonable query requires a huge pre-read/overhead that could paralyze the server. Use encryption to protect data that is worth encrypting.
encryption Key
SQL Server allows you to use any of the three encryption keys. The encryption key is a small chunk of data, and when inserting the algorithm, converting the data to garbled, without the correct decryption key is almost impossible to convert back to unencrypted values.
- an asymmetric key:This type of encryption uses a public/private key pair. One key encrypts the data, and the other key is decrypted. You can share the public key with anyone so that it encrypts data on any of them, only you can decrypt it by using the private key. SQL Server uses the Rivest-shamir-adelman (RSA) encryption algorithm. You can learn more RSA algorithms at Wikipedia's RSA page.
symmetric key:In this type of encryption, the cryptographic key and decryption key are the same. Sometimes called a shared secret, because both sides of the shared data must have the same key. In some cases, it is difficult to use a symmetric key because it is a problem to pass the secret from one side to the other. Symmetric keys are ideal for use in databases because they never leave the database. SQL Server supports RC4, RC2, DES, and AES algorithms. You can view these algorithms in symmetric encryption.
Certificate:The certificate is part of asymmetric key encryption and is used for public key cryptography of the digital wrapper. SQL Server can create a certificate for your use, or you can obtain it from a third-party certification authority. SQL Server uses the Internet Engineering Task force ' s specifications.
SQL Server uses the encryption key hierarchy, as shown in 8.1, to encrypt and protect the keys that you store in the database

Figure 8.1 Encryption Key hierarchy
At the server level, each DB instance has a service Master key that is used to encrypt low level keys. This key is created when an instance of SQL Server is installed. You can back it up and restore it, or even regenerate it, but for the most part you'll leave it alone. SQL Server manages for you, and you never explicitly use it. Service Master key is stored and protected in Windows, using Windows Data protection Api,or Dpapa.
Service Master Key has some internal use, but here for US SQL Server uses it to encrypt and protect the database Master key you created. You need a database Master key in any of the databases where you want to encrypt your data. It is a symmetric key used to encrypt and protect any key you create. You must explicitly create it in the database environment, as shown in code 8.1:

--Set up sample Encryption Database UseMaster;GO--Set up a loginIF Suser_sid('User1') is  not NULL DROPLOGIN User1;CREATELOGIN User1 withPassword= '[email protected] $fWDY 3qvp&k0';GOIF db_id('encryptiondb') is  not NULL DROP DATABASEencryptiondb;CREATE DATABASEencryptiondb;GO Useencryptiondb;GOCREATE USERUser1 forLOGIN User1;GO Useencryptiondb;GO--Databases do not has a master key by default and so is must create it before you can use it:CREATEMASTERKEYEncryption byPASSWORD= 'gk#3hbqkdfqy0of';GO

View Code

Code 8.1 Creating database Master Key in the ENCRYPTIONDB library
Database Master key is stored two times: It is first encrypted by the service Master key and then encrypted again by the password you provided. You can delete one of the two, but not all. Typically, they are not manipulated.
As a symmetric key, the Database Master key must be opened before you can use it. Open a key to load into memory and then decrypt it, and then you can use it. Because the server-level service master key encrypts the database Master key, it automatically opens the key for you and rarely requires you to show it to open. Similar to service master key, you can back up, restore, and modify database Master key.
You will use the other keys in Figure 8.1 to encrypt the data. The arrows indicate which key to use to encrypt and protect the other keys. For example, you can use database Master key to encrypt a certificate or asymmetric key. Certificates and asymmetric keys can only protect symmetric keys. A symmetric key can be protected by a certificate, asymmetric key, or other symmetric key.
Key Management
Figure 8.1 shows another aspect of the encryption key: You can use another key or a password to create the key. This is called Key management, which is a primary service that SQL Server can manage for you.
Key management is the single hardest thing to get right about encryption. History is full of secrets of various states, when the key is intercepted/leaked. During World War II, the United States, the United Kingdom and other allies put a lot of resources into the encryption keys used in Germany and Japan to enable them to intercept and read highly sensitive information. In the most recent time, many high-security applications were compromised because the attacker was able to find the key that was embedded in the application or on the computer. It's hard to share secrets.
If you choose to use a password, you can manage the SQL Server encryption key yourself, and then you assume responsibility for keeping the key. Most people do not want to do this because it requires highly specialized technical skills. However, if you want to assume this task, use the password option whenever you create a key. The password is basically the key, and you have to make sure that you can keep the key in a safe place and transfer it securely when you need it.
But you don't need to deal with these details, because SQL Server will take care of key management for you. It encrypts the new key for you, uses any method you specify, and stores the data as securely storing any other sensitive information.
You have the right to manage keys, but there is no good reason why you should not do so.
Encrypt Data
Now let's look at an example of encrypted data in SQL Server. In this scenario, the Customer table usually has general information about the client. Customer name and city, not sensitive data, no encryption required, but credit card type, account number may contain sensitive personal information, should be encrypted.
You will use a symmetric key to encrypt the data in the table, remembering that a symmetric key requires a certificate or asymmetric key to be protected in the database. Therefore, first use code 8.2 to create an asymmetric key to protect the symmetric key:

-- Create an asymmetric key to protect the new symmetric key CREATE KEY User1asymmetrickey     AUTHORIZATION User1      with = rsa_2048;

Code 8.2 Creating an asymmetric key
This asymmetric key is called User1asymmetrickey and is owned by the User1 user. This key uses 2048-bit RSA encryption, which is a very strong/complex encryption algorithm. This kind of data is definitely very, very important!
Next, use code 8.3 to create a symmetric key user1symmetrickey. This example uses the Triple_des algorithm and is protected by the asymmetric key that you just created:

-- Create A symmetric key, protected by the asymmetric key CREATE KEY User1symmetrickey      with = triple_des      by KEY User1asymmetrickey;

Code 8.3 Creating a symmetric key
If you want to list the symmetric encryption keys under the database, you can use the Sys.symmetric_keys catalog view to view them. Code 8.4 Executes the result 8.2 as shown. Note that because database Master key is a symmetric key, it also appears in the results.

-- List The symmetric keys in the database SELECT *  from Sys.symmetric_keys;

Code 8.4 Lists the symmetric keys under the database

Figure 8.2 Sys.symmetric_keys catalog view return results
Code 8.5 is the structure of the Customer table under the ENCRYPTIONDB database:

 Useencryptiondb;GOCREATE TABLECustomer (CustIdint, Namenvarchar( -), Cityvarchar( -), Creditcardtypevarbinary( +), Creditcardnumbervarbinary( +), Notesvarbinary(4000));GO--Grant access on the table to userGRANT SELECT,INSERT  onCustomer toUser1;

Code 8.5 creating a Customer table
Note that because the last three columns will save the encrypted binary type data instead of the original string data, the column type is set to varbinary. The length of the column depends on the size of the data and the algorithm. The database has a User1 user with select and insert permissions on the Customer table.
It's time to encrypt some data and insert it into the database. The first step is to use code 8.6 to open the symmetric key, which causes SQL Server to store the lookup key internally, ensure that the user has permission to use the key, and then decrypt the key to memory to prepare for use:

OPEN KEY User1symmetrickey      by KEY User1asymmetrickey;

Code 8.6 Open Symmetric key
Data encryption uses the T-SQL statement EncryptByKey function, which distinguishes the key with a unique GUID. You can use the Key_GUID function to retrieve GUIDs instead of passing values directly. Otherwise, code 8.7 is a normal T-SQL INSERT statement:

INSERT  intoCustomerVALUES(1,'Sally Roe','Chatinika', EncryptByKey (Key_GUID ('User1symmetrickey'),'Visa'), EncryptByKey (Key_GUID ('User1symmetrickey'),'1234-5678-9009-8765'), EncryptByKey (Key_GUID ('User1symmetrickey'),         'One of our best customers. Treat like royalty.'));

Code 8.7 inserting encrypted data
The final step is to turn off the symmetric key using code 8.8. It removes the key from memory and frees the resource. As long as you do not need to encrypt the key should be closed as soon as possible, because leaving it in memory may be exploited by the attacker.

CLOSE KEY User1symmetrickey;

Code 8.8 Closing the symmetric key
Tip: If you want to encrypt or decrypt large amounts of data in a single batch using a key, you can open it first. Opening and closing the key requires processing time. But when you're done, don't forget to close it!
Now run a query statement to view the data in the table, as shown in 8.3. You can see that the data in the CustID, Name, and city columns is not encrypted, but the encrypted column text is random binary data. Your data is safe!

Figure 8.3 The encrypted data stored in the table
The data in the table is worthless unless there is a way to retrieve it. In this case you need to use a regular query statement and use the DecryptByKey function to decrypt the data. This function returns varbinary data because the encrypted data may be of any data type. So retrieving the original text requires you to convert the result of the DecryptByKey function.

OPENSymmetricKEYUser1symmetrickey Decryption byAsymmetricKEYUser1asymmetrickey;SELECTCustID, Name, City,CONVERT(VARCHAR, DecryptByKey (Creditcardtype)) asCardtype,CONVERT(VARCHAR, DecryptByKey (Creditcardnumber)) asCardnumber,CONVERT(VARCHAR, DecryptByKey (Notes)) asNotes fromCustomer;CLOSESymmetricKEYUser1symmetrickey;

Code 8.9 Open the key and execute the query statement

Figure 8.4 Decrypting data results

--* * * Encryption Catalog views * * *-- ********************************--Existing KeysSELECT *  fromsys.certificates;SELECT *  fromSys.asymmetric_keys;SELECT *  fromSys.symmetric_keys;SELECT *  fromsys.database_principals;SELECT *  fromsys.key_encryptions;SELECT *  fromsys.crypt_properties;--information about keys--Returns a row for each symmetric key encryption specified using the encryption by clause of the CREATE symmetric key Statement.SELECT *  fromsys.key_encryptions;--Returns one row for each cryptographic property associated with a securableSELECT *  fromsys.crypt_properties;--* * * * up * * *-- **************** UseMaster;GOIF Suser_sid('User1') is  not NULL DROPLOGIN User1;IF db_id('encryptiondb') is  not NULL DROP DATABASEencryptiondb;GO

View Code

Summarize
SQL Server's Data encryption feature provides an additional layer of protection for your data, allowing you to defend in depth. SQL Server attackers must compromise network security, server security, SQL Server instance security, and database security to get your data. Then, so close to victory, they have to deal with strongly encrypted data before they can use it. Combined with strong database security, take advantage of granular permissions to provide minimal privileges to the data the principal needs to access, and you can build strong protection for the most sensitive data.

Eighth SQL Server secure data encryption