It is not stored on SQL Server.<\/li>\n<\/ul>\n<\/div>\nSince Column Master Key is not stored on SQL Server and data encrypted with Column Encryption Key cannot be opened without this key, a high level security solution is provided.<\/p>\n
The data is sent in an encrypted manner from the application server to the sql server and is sent back from the sql server to the application server in an encrypted manner. On the application server, the ADO.NET library decodes the password using the Column Master Key to allowing the application to view the data as clear text.<\/p>\n
In this way, it is ensured that data is sent encrypted over the network.<\/p>\n
Let’s make an example to understand the subject in more detail and to examine the details.<\/p>\n
Example<\/h3>\n
We will implement the installation process through the application server to be realistic.<\/p>\n
Let’s connect to the database server using SSMS via the application server and run the following script.<\/p>\n
USE Test\r\nGO\r\nCREATE TABLE AlwaysEncrypted\r\n(\r\n Id int identity(1,1) PRIMARY KEY,\r\n Name varchar(250),\r\n identification_number varchar(50)\r\n)\r\nINSERT INTO AlwaysEncrypted VALUES ('Nurullah CAKIR','12345678901'),\r\n ('Faruk ERDEM','12345678902')\r\nSELECT * FROM AlwaysEncrypted\r\n<\/pre>\nAfter running the script, as you can see, the results returned as clear text.<\/p>\n
Lets encrypt name and identification_number columns.<\/p>\n
Right-click on the table and click Encrypt Columns….<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cab9a9548c1f.png\")
<\/p>\n
The first screen shows an explanation that Always Encrypted is designed to protect some of the information stored in the SQL Server Database and that encryption is performed on the application side. You can proceed by clicking “do not show this page again”.<\/p>\n
On the next screen we select the columns we want to encrypt.<\/p>\n
In the Encryption Type section, you can see Deterministic or Randomized options.<\/p>\n
Deterministic<\/h3>\n
Encrypts the same data in the same way. For example, if there is a data with its value “Ahmet” in the database and the value of “Ahmet” is encrypted as ‘gferty’, it will also encrypt other “Ahmet” values in the database as ‘gferty’. If you select deterministic encryption, someone who obtains the data can discover the encryption model by working on the data. On the other hand, you can perform sorting, grouping, combining, and indexing operations.<\/p>\n
Randomized<\/h3>\n
Encrypts data in a less predictable way. However, you cannot perform sort, group, merge, and indexing operations. That is, it does not generate the same encryption value for all “Ahmet” values \u200b\u200bin the database as in the Deterministic option. A different encryption value is generated randomly each time. Thus, it becomes more difficult to solve the encryption model.<\/p>\n
In the Encryption Key section, you will create a new Encryption Key named CEK_Auto1 (New) for Name and identification_number columns. Click next after choosing below options.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cab9b100f7a0.png\")
<\/p>\n
Who Can Access the Data<\/h2>\n
The next screen asks whether to store the master key on windows certificate store or on Azure Key Vault. In our example, we select the Windows certificate store.<\/p>\n
In the “select a master key source” section;<\/h3>\n\n- If we select Current User, only the user who is installing can access the data(the current user can access the data as clear text using any sql login that can access the data. That is, current user is a local user, not a sql login.).<\/li>\n
- If we select Local Machine, all users who are authorized on the application server can access the data.<\/li>\n<\/ul>\n
We continue by selecting Current User. Then click next and next and finish to complete the process.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cab9b689920e.png\")
<\/p>\n
You can create the Encryption Key and Master Key from the Security tab under the database instead of the wizard, as shown in the following screen.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cab9b92c6a74.png\")
<\/p>\n
When we create the Master Key over SSMS instead of the wizard, we have two options where the master key can be stored except Windows Cetificate Store and Azure Key Vault.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cab9bb417935.png\")
<\/p>\n
<\/p>\n
How To See the Data as Clear Text<\/h2>\n
After installation, you must add “Column Encryption Setting=ENABLED<\/strong>” to the connection string to access the data in a clear text manner.<\/p>\nIf you want to access data from the application server with SSMS as clear text, you must add “Column Encryption Setting = ENABLED<\/strong>” to the “Options \/ Additional Connection Parameters” tab on SSMS.<\/p>\nWhen you try to read the data as clear text from the database server by adding “Column Encryption Setting = ENABLED” to “Options \/ Additional Connection Parameters” tab on the SSMS, you will receive the below error.<\/p>\n
Msg 0, Level 11, State 0, Line 0<\/em><\/span><\/p>\nFailed to decrypt column ‘Name’.<\/em><\/span><\/p>\nMsg 0, Level 11, State 0, Line 0<\/em><\/span><\/p>\nFailed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’. The last 10 bytes of the encrypted column encryption key are: ’52-F8-A2-E7-FD-F2-C7-BF-C9-9A’.<\/em><\/span><\/p>\nMsg 0, Level 11, State 0, Line 0<\/em><\/span><\/p>\nCertificate with thumbprint ‘559CBB33121DA44988155DC43F2445DBBDD6FC30’ not found in certificate store ‘My’ in certificate location ‘CurrentUser’. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location\/store.<\/em><\/span><\/p>\nParameter name: masterKeyPath<\/em><\/span><\/p>\nIf you want to read the data without adding\u00a0 “Column Encryption Setting = ENABLED” to the “Options \/ Additional Connection Parameters” tab on SSMS, you will see data as follows. We suppose you have read privilege on the table.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cab9c2d340c9.png\")
<\/p>\n
Some Commands for Always Encrypted<\/h2>\n