{"id":8452,"date":"2016-06-30T07:39:57","date_gmt":"2016-06-30T05:39:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/"},"modified":"2016-06-30T07:39:57","modified_gmt":"2016-06-30T05:39:57","slug":"sql-server-2016-always-encrypted-part-2","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/","title":{"rendered":"SQL Server 2016: Always Encrypted \u2013 part 2"},"content":{"rendered":"<p>In my last blog post about SQL Server 2016 Always Encrypted, <a href=\"http:\/\/dbi-services.com\/blog\/sql-server-2016-always-encrypted\/\">here<\/a>, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.<\/p>\n<p>Let\u2019s see how to build those environment.<br \/>\nIn my security server named SQL2016-2, I first create a Self-signed certificate with PowerShell after having import the PKI module. The certificate is stored in the local machine with:<\/p>\n<ul>\n<li>DnsName, DNS name of the certificate: Demo_AlwaysEncrypted.cert<\/li>\n<li>CertStoreLocation, specifies the certificate store in which to store the new certificate: Cert:\\LocalMachine\\MY<\/li>\n<li>Provider, specifies the name of the KSP or CSP that this cmdlet uses to create the certificate: Microsoft Strong Cryptographic Provider<\/li>\n<li>KeyAlgorithm, specifies the name of the algorithm that creates the asymmetric keys that are associated with the new certificate: RSA<\/li>\n<li>KeyLength, specifies the length, in bits, of the key that is associated with the new certificate: 2048<\/li>\n<li>HashAlgorithm, specifies the name of the hash algorithm to use to sign the new certificate: sha256<\/li>\n<\/ul>\n<p>PowerShell script:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">Import-Module PKI\nNew-SelfSignedCertificate -DnsName \"Demo_AlwaysEncrypted.cert\" -CertStoreLocation Cert:\\LocalMachine\\MY -Provider \"Microsoft Strong Cryptographic Provider\" -KeyAlgorithm RSA -KeyLength 2048 -HashAlgorithm sha256<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_15.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9513\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_15.jpg\" alt=\"Blog_AlwaysEncrypted_part2_1\" width=\"300\" height=\"62\" \/><\/a><\/p>\n<p>I have now my new certificate.<br \/>\nI open my SQL Server instance and navigate in my database SecurityDB to Security, Always Encrypted Keys and right click on Column Master Keys and select \u201cNew Column Master Key\u2026\u201d.<br \/>\nI create a new Column Master Key with the certificate I have just created on my local Machine and I name it CMK2:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9505\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_2.jpg\" alt=\"Blog_AlwaysEncrypted_part2_2\" width=\"300\" height=\"183\" \/><\/a><\/p>\n<p>The code to create my Master Key is the following, you can have it by clicking on the Script button in the above screen:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">CREATE COLUMN MASTER KEY [CMK2]\nWITH\n(\n       KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',\n       KEY_PATH = N'LocalMachine\/My\/A3DF88FA53ED5347ABCF709D7A15621CDC358456'\n)\nGO<\/pre>\n<p>I create now a Column Encryption Key named CEK2 with the Column Master Key I have create before and named CMK2:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_3.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9506\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_3.jpg\" alt=\"Blog_AlwaysEncrypted_part2_3\" width=\"300\" height=\"272\" \/><\/a><\/p>\n<p>The code to create my Encryption Key is the following:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">CREATE COLUMN ENCRYPTION KEY [CEK2]\nWITH VALUES\n(\n       COLUMN_MASTER_KEY = [CMK2],\n       ALGORITHM = 'RSA_OAEP',\n       ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F00610033006400660038003800660061003500330065006400350033003400370061006200630066003700300039006400370061003100350036003200310063006400630033003500380034003500360098721948A1283B0E5375B4E7F0A5F2EED05EDB0A87814BF66EABCBE2B4E75B5A759CF48D7D87D6A2C44A6DB2B587992E412EAF20D3FE826844FE76B1BC764E769C911486150E08EB972DBF406FF51738EFAA7F831A27A540F948CBC673273B1205A2CEF33AE77A0AF01DB3589A4A746151DC8C699DAB7AD1B99910A598EA7258670F1D5C0600704A9BB440F86761F41E8C72E52FFF6A7348B65F62E60D90B60CF1FC00B1B473F40E82B140219A317760CBC8591E07215AD114FAF80F08361C607823DB44037C455514B3789661560613638D058D7965846962B9FD3D9ECF0AC62325043AB40D892683744DC994E868C5F0E3FD14277D8941977426AC59DEA7EEF1394641DB314C2DCB083D3D5A12B97A131986D9834CBF2837908D3EF54C9104D92371C8A9EDC9B22555D8D615440D21B03393303FD4A9C0047282091178F31323A6EF4313576470349F32F106D38ABC3B0A1C4F47E870EA43F7B90D85D4F6C6686368E4E5EFC416EA08241F75F2D7B4F4B700114CA545AA4310829B2691A6D2C553F8F8B7E3983FC99F4E0A28BD79C84F50B7F6ABB17419DEAE06ACA694D68B00B48DBC0D66B46847A389C8A587F138CEF7791845A999EA45849D2FAA45BDA88FE134C70E592937981A7B2A18397CC615BB435A1EDDD66CF58119D89B24190744F4326718CBCDC1182AD20C8023EECB3994569844270DC90020B20D70B5CCC5\n)\nGO<\/pre>\n<p>I will now transfer those two certificates to my SQL Server VM, named SQL2016-1, in order to encrypt my table columns with them. To do that, I will execute the scripts I have generated before in my SQL Server instance in my security VM. I have created a second database named TestDB2 where I will execute my scripts:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_4.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9507\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_4.jpg\" alt=\"Blog_AlwaysEncrypted_part2_4\" width=\"300\" height=\"128\" \/><\/a><\/p>\n<p>After execution of the script I have my Column Master Key and my Column Encryption Key in the Security\\Always Encrypted Keys path of my TestDB2 database.<\/p>\n<p>I will now create the same table that I have on my TestDB database (see my previous blog post) in my new database TestDB2. For that I will execute the following script with the same encrypted columns (CustAccountNumber, CustAccountBalance, CustCreditCardNumber) but with my new Column Encryption Key CEK2:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_5.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9508\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_5.jpg\" alt=\"Blog_AlwaysEncrypted_part2_5\" width=\"300\" height=\"136\" \/><\/a><\/p>\n<p>My table is created with the specified columns encrypted.<br \/>\nI will now export my certificate from my security server to my application server with PowerShell.<br \/>\nFirst, I retrieve my certificate from the store and export it to a file with a password:<\/p>\n<p>PowerShell script:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\"># I retrieve the Certificate from the store\n$MyCert = Get-ChildItem Cert:\\LocalMachine\\My -DnsName \"Demo_AlwaysEncrypted.cert\"\n# I create a password to protect my certificate\n$pwd = ConvertTo-SecureString -String \"Demo\" -Force \u2013AsPlainText\n# I export my certificate as PFX Certificate Archive\nExport-PfxCertificate -Cert $MyCert -FilePath C:\\Temp\\MyCert.pfx -Password $pwd<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_6.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9509\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_6.jpg\" alt=\"Blog_AlwaysEncrypted_part2_6\" width=\"300\" height=\"100\" \/><\/a><\/p>\n<p>I\u2019m able now to copy my certificate in my application server and import it:<\/p>\n<p>PowerShell script:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\"># I import the certificate from my file\n$pwd = ConvertTo-SecureString -String \"Demo\" -Force \u2013AsPlainText\nImport-PfxCertificate -CertStoreLocation Cert:\\LocalMachine\\My -FilePath C:\\Temp\\MyCert.pfx -Password $pwd<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_7.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9510\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_7.jpg\" alt=\"Blog_AlwaysEncrypted_part2_7\" width=\"300\" height=\"70\" \/><\/a><\/p>\n<p>My certificate is now in the store of my application server and I will be able at this time to insert and read plain text data from my application server. I will use a PowerShell script to insert data. I will not explain all this script but it\u2019s good to know that:<\/p>\n<ul>\n<li>The connection string needs to enable Always Encrypted for the client application, here PowerShell, with this parameter: Column Encryption Setting=enabled<\/li>\n<li>The application passes plain text data in SqlParameter objects to the driver. The driver will encrypt the values and send encrypted values to the SQL Server VM<\/li>\n<\/ul>\n<p>PowerShell script:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">$sqlConn = New-Object System.Data.SqlClient.SqlConnection\n$sqlConn.ConnectionString = \"Server=SQL2016-1\\SQL2016_1;Integrated Security=true; Initial Catalog=TestDB2; Column Encryption Setting=enabled;\"\n$sqlConn.Open()\n$sqlcmd = New-Object System.Data.SqlClient.SqlCommand\n$sqlcmd.Connection = $sqlConn\n$sqlcmd.CommandText = \"INSERT INTO dbo.Customer (CustNb, CustName, CustSurname, CustAccountNumber, CustAccountBalance, CustCreditCardNumber, CustCountry) VALUES (@CustNb, @CustName, @CustSurname, @CustAccountNumber, @CustAccountBalance, @CustCreditCardNumber, @CustCountry)\"\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustNb\",[Data.SQLDBType]::uniqueidentifier)))\n$sqlcmd.Parameters[\"@CustNb\"].Value = [GUID]::NewGuid()\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustName\",[Data.SQLDBType]::VarChar,50)))\n$sqlcmd.Parameters[\"@CustName\"].Value = \"Durand\"\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustSurname\",[Data.SQLDBType]::VarChar,50)))\n$sqlcmd.Parameters[\"@CustSurname\"].Value = \"Laurent\"\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustAccountNumber\",[Data.SQLDBType]::VarChar,50)))\n$sqlcmd.Parameters[\"@CustAccountNumber\"].Value = \"1234-234589\"\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustAccountBalance\",[Data.SQLDBType]::Int)))\n$sqlcmd.Parameters[\"@CustAccountBalance\"].Value = 1256890\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustCreditCardNumber\",[Data.SQLDBType]::VarChar,50)))\n$sqlcmd.Parameters[\"@CustCreditCardNumber\"].Value = \"1234-4245-4563-4564\"\n$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(\"@CustCountry\",[Data.SQLDBType]::VarChar,200)))\n$sqlcmd.Parameters[\"@CustCountry\"].Value = \"France\"\n$sqlcmd.ExecuteNonQuery();\n$sqlConn.Close()<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9498\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9.jpg\" alt=\"Blog_AlwaysEncrypted_part2_9\" width=\"300\" height=\"188\" \/><\/a><\/p>\n<p>I have now inserted a row in my Customer table. If I try to do the same insert without the parameter Column Encryption Setting=enabled, I receive an error message:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_10.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9499\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_10.jpg\" alt=\"Blog_AlwaysEncrypted_part2_10\" width=\"300\" height=\"191\" \/><\/a><\/p>\n<p>Now, if I want to read my table from my application server I can execute a simple select * with this PowerShell script and my parameter Column Encryption Setting=enabled:<\/p>\n<p>PowerShell script:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">$sqlConn = New-Object System.Data.SqlClient.SqlConnection\n$sqlConn.ConnectionString = \u201cServer=SQL2016-1\\SQL2016_1;Integrated Security=true; Initial Catalog=TestDB2; Column Encryption Setting=enabled;\u201d\n$sqlConn.Open()\n$sqlcmd = $sqlConn.CreateCommand()\n$query = \"SELECT * FROM dbo.customer\"\n$sqlcmd.CommandText = $query\n$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd\n$data = New-Object System.Data.DataSet\n$adp.Fill($data) | Out-Null\n$data.Tables<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_11.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9500\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_11.jpg\" alt=\"Blog_AlwaysEncrypted_part2_11\" width=\"300\" height=\"106\" \/><\/a><\/p>\n<p>I see plan text data but without the parameter:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_12.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9501\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_12.jpg\" alt=\"Blog_AlwaysEncrypted_part2_12\" width=\"300\" height=\"100\" \/><\/a><\/p>\n<p>I\u2019m able to read the table but the encrypted columns appear as blob and cannot by readable.<br \/>\nIf I go back to my SQL Server VM, even with SYSADMIN right and with the connection string parameter Column Encryption Setting=enabled I am not allowed to query the table:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_13.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9502\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_13.jpg\" alt=\"Blog_AlwaysEncrypted_part2_13\" width=\"300\" height=\"131\" \/><\/a><\/p>\n<p>The error indicates that I don\u2019t have access on my SQL Server VM at the certificate used to encrypt the column master key and so I\u2019m not able to decrypt the encrypted columns.<br \/>\nIf I don\u2019t use the parameter Column Encryption Setting=enabled during my connection via SQL Server Management Studio and try to query my table:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_14.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-9503\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_14.jpg\" alt=\"Blog_AlwaysEncrypted_part2_14\" width=\"300\" height=\"99\" \/><\/a><\/p>\n<p>I can see my table row(s) but the encrypted columns appear as BLOB and so are protected to unexpected reader which is exactly the goal of this new feature: avoid that SQL Server DBAs or users with some rights should able to visualize critical data.<br \/>\nIn order to validate this security, the certificate must not be saved in the certificate store of the SQL Server VM.<br \/>\nThat\u2019s all Folks! \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the [&hellip;]<\/p>\n","protected":false},"author":21,"featured_media":8466,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,48],"tags":[864,25,51,566],"type_dbi":[],"class_list":["post-8452","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-technology-survey","tag-always-encrypted","tag-security","tag-sql-server","tag-sql-server-2016"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2016: Always Encrypted \u2013 part 2 - dbi Blog<\/title>\n<meta name=\"description\" content=\"In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016: Always Encrypted \u2013 part 2\" \/>\n<meta property=\"og:description\" content=\"In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-30T05:39:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1219\" \/>\n\t<meta property=\"og:image:height\" content=\"764\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Savorgnano\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Savorgnano\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/\"},\"author\":{\"name\":\"St\u00e9phane Savorgnano\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"headline\":\"SQL Server 2016: Always Encrypted \u2013 part 2\",\"datePublished\":\"2016-06-30T05:39:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/\"},\"wordCount\":860,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/Blog_AlwaysEncrypted_part2_9-1.jpg\",\"keywords\":[\"Always Encrypted\",\"Security\",\"SQL Server\",\"SQL Server 2016\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Technology Survey\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/\",\"name\":\"SQL Server 2016: Always Encrypted \u2013 part 2 - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/Blog_AlwaysEncrypted_part2_9-1.jpg\",\"datePublished\":\"2016-06-30T05:39:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"description\":\"In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/Blog_AlwaysEncrypted_part2_9-1.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/Blog_AlwaysEncrypted_part2_9-1.jpg\",\"width\":1219,\"height\":764},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-always-encrypted-part-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016: Always Encrypted \u2013 part 2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/b6bce7d75118b35bdb3b439ad6a9ca3c\",\"name\":\"St\u00e9phane Savorgnano\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Savorgnano\"},\"description\":\"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \\\/ Financial Services, Chemicals &amp; Pharmaceuticals, etc.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/stephane-savorgnano\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2016: Always Encrypted \u2013 part 2 - dbi Blog","description":"In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016: Always Encrypted \u2013 part 2","og_description":"In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/","og_site_name":"dbi Blog","article_published_time":"2016-06-30T05:39:57+00:00","og_image":[{"width":1219,"height":764,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9-1.jpg","type":"image\/jpeg"}],"author":"St\u00e9phane Savorgnano","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Savorgnano","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/"},"author":{"name":"St\u00e9phane Savorgnano","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"headline":"SQL Server 2016: Always Encrypted \u2013 part 2","datePublished":"2016-06-30T05:39:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/"},"wordCount":860,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9-1.jpg","keywords":["Always Encrypted","Security","SQL Server","SQL Server 2016"],"articleSection":["Database Administration &amp; Monitoring","Technology Survey"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/","name":"SQL Server 2016: Always Encrypted \u2013 part 2 - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9-1.jpg","datePublished":"2016-06-30T05:39:57+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"description":"In my last blog post about SQL Server 2016 Always Encrypted, here, I showed how to use this new functionality but also that you have to separate the different execution context with an Application server, a database server and a security server to avoid that certificate will be available for all users and break the segregation.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog_AlwaysEncrypted_part2_9-1.jpg","width":1219,"height":764},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-always-encrypted-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016: Always Encrypted \u2013 part 2"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c","name":"St\u00e9phane Savorgnano","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","caption":"St\u00e9phane Savorgnano"},"description":"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals &amp; Pharmaceuticals, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8452","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8452"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8452\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/8466"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8452"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8452"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8452"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8452"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}