2

I have some confusion about Always Encrypted concept. Please follow below scenario.

I have created CMK (name - CMK_01) on Machine A under CurrentUser/My folder. By this way I have Always Encrypted certificate generated on Machine A which I have exported.

After this I have created CEK (name - CEK_01) from Machine A using this CMK (CMK_01).

After this I have created table (name - TBL_01) on Machine B and used this CEK_01 to encrypt its column (name – COL_01).

Now to test Always Encrypted concept I have installed Always Encrypted certificate on Machine B and applied Column Encryption Setting = Enabled in SSMS.

After doing this I am able to insert data with parameterized query in this table (TBL_01). I queried on this table and I found that data is in decrypted form (i.e. plain text).

After this I queried on this table from Machine C without installing certificate and I found data is in encrypted form. So basically it works perfectly from db side. This works based on certificate installed on individual machine and depends on folder in which it installed (Current or Local computer).

If Current then it works only for user which certificate is installed and if Local then works for all the users on that machine.

Now issue comes over here,

I tried to leverage this functionality to .NET side. So I have included Column Encryption Setting = Enabled in connection string of .NET code. And then I deployed .NET code on server machine (Machine D). After this I have installed always encrypted certificate on one of the user machine (Machine E) under CurrentUser/My folder.

Now when user on Machine E is trying to see the data in UI, it is giving an error. Basically encryption does not work here.

To resolve this I have created new CMK (name – CMK_01) with same certificate key but path of CMK is under LocalMachine\My.

Then I have added this new CMK value in existing CEK (CEK_01).

So now basically we have 2 CMK - same certificate key with different path and 1 CEK with 2 different CMK values which means both CMK can access this CEK.

After this we installed Always Encrypted Certificate on IIS server (under LocalMachine\My path) where our code has been deployed (Machine D). After this error has been removed but all the users who can login to that web page they can encrypt/decrypt data because we have installed certificate on IIS server (Machine D).

Now my ask is -

  1. Is this our implementation correct?

  2. Is this how Always Encrypted works? I mean can't we have individual user wise encryption and decryption by installing certificate on user's individual machine so who has that certificate access that user is only able to encrypt/decrypt data otherwise rest of the users who does not have that certificate those users can only see data in encrypted form on that web page?

Shekhar Patel
  • 641
  • 10
  • 20
Anuj soni
  • 21
  • 3
  • Is any part of this question related to SQL? – Muhammad Vakili Mar 14 '19 at 08:37
  • It is about encryption in SQL. If you have any idea then please share your knowledge. – Anuj soni Mar 14 '19 at 08:44
  • 1
    The client for always encrypted is the thing *processing* the results of queries or submitting them. In this scenario, that would appear to be Machine D, which is processing the results and turning them into something web-appropriate. – Damien_The_Unbeliever Mar 14 '19 at 08:48
  • @Damien_The_Unbeliever - Thanks Damien for understanding the whole scenario what I am trying to explain. Now my ask is if we want that only individual user can encrypt and decrypt data by installing certificate on user's machine instead of hosting server then will it work? – Anuj soni Mar 14 '19 at 09:00
  • Then *that* machine need to be the one creating `SqlConnection`, `SqlCommand` etc objects. – Damien_The_Unbeliever Mar 14 '19 at 09:02
  • @Damien_The_Unbeliever - Can you explain in detail. I got confused. Are you trying to say that we need to create sqlConnection in user's machine ? What exactly we need to do to achieve individual user wise encryption? – Anuj soni Mar 14 '19 at 09:04
  • "all the users who can login to that web page they can encrypt/decrypt data"...how do you think that could happen? All they can do is use whatever is on your web page. They have no access to anything underlying your server. The encryption is between the SQL Server machine, and the machine which is running the query (this takes the role of "client" as far as SQL is concerned). In this case, the code running the query appears to be the ASP.NET code on Machine D. – ADyson Mar 14 '19 at 09:50
  • Of course, in a web-app scenario like this there is also a separate HTTP connection between the browser and the IIS Server - this request from the browser is what caused the ASP.NET code to be executed, and run a SQL query. But this has nothing to do with your SQL connection. You can encrypt a HTTP connection separately using SSL certificates (i.e. HTTPS). That encryption is between the browser and the IIS Server. If code on the IIS Server then connects to SQL Server, that is a new, separate connection which is encrypted separately and uses a different protocol entirely. – ADyson Mar 14 '19 at 09:51
  • Now, you said _"when user on Machine E is trying to see the data in UI, it is giving an error. Basically encryption does not work here."_. What UI? You mean the web UI? And what error, _exactly_? Is Machine E running a browser which is connecting to Machine D via HTTP? Please be clear. If that's the situation, then Machine E knows nothing about your SQL server and doesn't need to, either. All it cares about is the HTTP connection to Machine D. The SQL part is taken care of between Machine D and your SQL Server (is that machine A or, B? It's not clear??) – ADyson Mar 14 '19 at 09:54

0 Answers0