“Always Encrypted” app times out when delivering data for non-enabled user RRS feed

  • Question

  • I watched the "Uncover data protection in the world of Panama Papers" video, in which the presenters demonstrate how you can turn on plain-text visibility for different users by simply enabling/disabling their access to the private keys in the “Always Encrypted” certificate. In the demo, the enabled users see plain text, while non-enabled users see encrypted values in their apps. Sounds simple.

    I built my own "Always Encrypted" demo database on my local machine using SQL Server Express (2016) along with an app to access it. When encrypting columns I ran SSMS as "Administrator" so that I could create the certificate in the Local Machine store, rather than in the Current User store, so I could demonstrate the ability to enable/disable multiple users for plain-text visibility.

    When I run as a non-administrative user that has "read" permission enabled in the certificate, everything works fine and the user sees plain text. But if I turn off that same user's private key permission, instead of delivering encrypted text in the app, the application times out with this InnerException:

    "Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '11-F0-FF-31-DF-61-23-AD-38-9A'. Keyset does not exist."

    It's the same user with the same permissions, so there is no question that the user can access the Local Machine certificate; the only difference is the private key certificate access that was shown in the demo. What am I doing wrong? Thanks.

    Friday, April 20, 2018 4:07 PM

All replies

  • I'm not entirely sure what the actual question is here. Is the issue that you get an exception instead of unencrypted data? That would be expected, since there is a handshake process between the client API and the server The client API asks the server what key is used, the server reply and the client API then tries to open that key. Since it isn't there (or permissions) you get an exception.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, April 23, 2018 11:15 AM
  • Tibor: thanks much for your response. Yes that was my question: I'm expecting ADO to simply pass the encrypted data through if ADO cannot find the key in the certificate. The reason I expect this is that I watched "Uncover data protection in the world of Panama Papers", a video published by Microsoft, which clearly gave the impression that non-provisioned users simply see encrypted data; see the video from 30:00 to 34:30, particularly from 33:00 onward, where the presenters clearly convey the idea that non-provisioned users see encrypted text, and the presenters provide an example where this clearly happens. They further imply (elsewhere) that "Always Encrypted" is implemented at a low enough level of ADO that applications using it (including legacy apps written before "Always Encrypted" existed) are unaware of it; so a legacy application running under a non-provisioned account should not have to be changed to handle the timeout exception that occurs in the event that its user is not provisioned. At 43:45 in this same video, the presenters demonstrate the "permissioning mechanism", which is to turn on/off the Read permission for the private key in the certificate. This is precisely how I manage the permissions for my toy app. So the video very clearly creates the expectation that the response of an app running under a non-provisioned user would display encrypted data (rather than timing out).
    Monday, April 23, 2018 1:53 PM
  • I'm not at a place where I can watch video now, so I can't comment on that part.

    A legacy app won't have the attribute for always encrypted in the connection string, so the API won't be looking for any key in the first place. I.e., the app will return cipher-data, not having a clue that it is in fact encrypted data. Perhaps that i what they meant by legacy app?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, April 24, 2018 7:22 AM
  • Tibor: Clearly the legacy app would have to change its connection string, but that is the only respect in which the app is supposed to need to change; the implication is that no code needs to be recompiled. In any event, the video is not using a legacy app (but the above implication is mentioned during a discussion of legacy apps).

    The video shows two pages from what appears to be the same web site, presumably both running the same code. One page is logged in as a provisioned user and it shows plain text. The other screen is logged in as a non-provisioned user and it shows encrypted text. If this code had a try/catch clause to capture the timeout for the non-provisioned user (after which the app would have to reconnect using a different connection string that does not enable "Always Encrypted" in order to see the encrypted text without timing out again), then presumably there would be a multi-second delay in the display of every bit of data containing encrypted fields, and surely this would be unacceptable. The video plainly implies that both non-provisioned and provisioned users using the same code can expect to operate normally, with the only being that the non-provisioned user sees encrypted text.

    Given my experience, it seems to me that the only way the folks in the video could have made the app work as depicted is to have the app test at login whether the user is provisioned, and then connect users via different connection strings thereafter. That would produce the results in the video, but the video does not mention such a test and clearly the video implies that the whole mechanism is supposed to be transparent.

    Tuesday, April 24, 2018 2:04 PM
  • Another approach here would be for me to stop hammering on what appears to be presented in the video, and instead simply ask you how to do things. So for example:

    I have a single table with two columns that are Always Encrypted. However, one column is encrypted using a key in Certificate A, and the other column is encrypted using a key in Certificate B. I have some users who can access the private key in only Certificate A, others who can access the private key in only Certificate B, and users who can access both or neither. I want all users to be able to look at this table on a web page, each seeing only the info that he/she is entitled to see. When a user views a column that he/she cannot decrypt, the user should see either encrypted text or a message of my choice. It is not acceptable for any user to have to wait for a timeout that would occur when trying to access a certificate in which he/she has no access to the private key (all users have access to the certificate store, however).

    How would I do this?

    Tuesday, April 24, 2018 2:58 PM
  • I see, Dave. This is a bit over my head (the API part) so I've asked around, giving the link to this thread and hopefully we'll see some replies for other more knowledgeable people on this topic. :-)

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, April 25, 2018 8:38 AM
  • Hi Dave,

    Here are the three possible outcomes of running a query against encrypted column:

    1. If the app has access to all CMKs protecting all encrypted columns (accessed in the query) and it is connected to the database with column encryption setting=enabled, the query will succeed and return plaintext.
    2. If the app does not have access to at least oneCMKs protecting any encrypted columns (accessed in the query) and it is connected to the database with column encryption setting=enabled, the query will fail.
    3. If the app is connected to the database with column encryption setting=disabled (the default): the query will succeed and return ciphertext.

    The web application, shown in the Ignite/Panama Papers demo, is implemented as follows:

    • There is only one CMK/one CEK for all encrypted column
    • The app opens two different connections to the database: One connection is opened with a connection string that contains column encryption setting=enabled the service account identity that has access to the CMK. Another connection is opened with a connection string with column encryption setting=disabled and a service identity that does not have access to the CMK
    • Application users are assigned to one of the two different AD groups: one group is permissioned to see the unencrypted data and the other is only permitted to see encrypted data.
    • When a user connects to the web app, the app checks which group the user belongs to. Based on that, the app decides which connection not use for user’s queries.

    As an alternative of the above design, the app could open a new connection for each user and decide whether to include column encryption setting=enabled in the connection string or not, based on user’s group membership.

    In your scenario, do both users run the same query e.g. SELECT * from T) and does the query fetch all encrypted columns (some encrypted with cert A and some with cert B)? If so, there is no easy way to implement it. You would need to somehow separate that queries for each user. Note that Always Encrypted is (currently) primarily designed as a solution to protect sensitive data from malicious high privilege users (e.g. DBAs who have no access to CMKs, or machine admins who attempt to scan the memory of SQL Server to steal sensitive data. It was not designed as a solution for isolating data of different users/tenants, although we do have some customers (including the customer who showed the demo), who managed to achieve that for relatively simple scenarios. 



    • Proposed as answer by Visakh16MVP Sunday, April 29, 2018 9:34 AM
    Friday, April 27, 2018 12:56 PM
  • Thanks Jakub. I have already tried your suggested approach of setting "column encryption setting=disabled" in my toy app, just to see what would happen with this setting when I try to read an encrypted column. There is just one certificate and one encrypted column in my example. In my code I am loading the Entity Framework model of the table, which is then used as the data source for a "BindingSource" that holds data for a grid view control. The table containing the encrypted column is called "DangerousData_Safe". I load my entity for this table using this statement (SandboxEntities is my entity object):


    This works fine when encryption is enabled. When I disable encryption (in my connection string) I get this exception:

    System.InvalidOperationException was unhandled
      Message=The 'SocialSecurity' property on 'DangerousData_Safe' could not be set to a 'System.Byte[]' value. You must set this property to a non-null value of type 'System.String'.

    SocialSecurity is the encrypted column, declared as char[9]. Apparently my entity expects to receive a char[9] even though it should know the column is encrypted because the model was created after the encryption was applied to the column. So I don't understand how someone using Entity Framework, or indeed any application that expects the plain-text size of an encrypted column, could just turn off encryption for the database.

    Friday, April 27, 2018 4:12 PM
  • Obviously the application has to be coded to handle the two alternatives of getting data undecrypted or decrypted, since the data types are different.

    The one exception is if you always stored data as binary, but you still need to be able to handle the two cases differently, since only it is only if encryption is enabled for the connection it make sense to convert the binary value to presentable string, date, integer number or whatever.

    • Proposed as answer by Visakh16MVP Sunday, April 29, 2018 9:34 AM
    Friday, April 27, 2018 9:47 PM