SQL Server 2016 Always Encrypted

Play SQL Server 2016 Always Encrypted

The Discussion

  • User profile image
    BradCathey

    Why not use standard server PK and rely on the symmetric encryption during the session? Let the server decide how to query and store down the data. Having to push the certs out to all of the clients seems like a sure way to make this a pain for everyone.

    Also, Why .Net 4.6 instead of a driver update?

    We need claims-aware access control at the db! THAT would be a real Oracle-killer feature.

  • User profile image
    SMARTech

    I would like to know which editions of SQL Server 2016 will support the Always Encrypted feature?

    Thanks.

  • User profile image
    Jakub​Szymaszek

    Thank you for you question. The decision about the availability of Always Encrypted in specific SKUs of SQL Server 2016 has not been finalized yet. Always Encrypted will be available in all service tiers (editions) of Azure SQL Database.

  • User profile image
    Jakub​Szymaszek

    @BradCathey:

    Thank you for post and great questions. Could you please clarify which specific SQL Server technologies are you referring to in "standard server PK" and "symmetric encryption during the session"? The goal of Always Encrypted is to ensure sensitive data and its corresponding keys are never seen in plaintext in SQL Server (or the machine hosting SQL Server), so that even database or system administrators cannot access the data in plaintext. None of the existing SQL Server technologies supports that goal. For example, Transparent Database Encryption and cell-level encryption only protect data at rest: the data appears in memory of SQL Server and, thus, system administrators can access the data and database administrators can retrieve the data using T-SQL queries.
     
    Regarding your comment about challenges associated with deploying certificates to client machines, I would like to point out that Windows Certificate Store is only one of the options for storing column master keys. Always Encrypted offers an extensibility mechanism, based on a key store provider model, which makes it possible to use an arbitrary key store. For example, one could choose to use a centralized key store, such as Azure Key Vault, which mitigates some of the key management problems (you still need to ensure each client application can access the central store). Please note the cost of managing keys outside of SQL Server is incurred to protect highly sensitive data.

    Currently, .NET Framework ships as one package, and we try and ship it as part of the framework update to ensure there is one package that customers install/update, cleaner simpler servicing model, so you cannot upgrade individual components, such as System.Data.dll (the driver). Please, let us know if that answers your question.

  • User profile image
    Niner350393
    Hi
    Why would we need a master key on the server? If it is what I think it is then it defeats the purpose.
    Regards
    Mandar
  • User profile image
    Mike

    Any chance you can post up the SQL / Application source code for this demo? Would like to take a look at how the C# code leverages the certificate / data connection etc.

    tx,

    Mike

  • User profile image
    Jakub​Szymaszek

    @Niner350393:The master key should never be stored in SQL Server or a machine hosting SQL Server. As you correctly observed, that would defeat the purpose of the feature. The master key should be stored in Certificate Store on each client machine, or in a central store that is accessible from client machines. The Certificate Store support is built into feature, but it possible to store a master key in an arbitrary store, via an extensibility mechanism (you can implement and register a provider for a custom store).

  • User profile image
    Jakub​Szymaszek

    @Mike: Here is a link to a blog post that contains an end-to-end example of a simple app using Always Encrypted.

  • User profile image
    steve

    How would this work with SSRS? Are you able to decrypt for the purposes of reports?

  • User profile image
    chhavi

    Does TDE support column level encryption in an azure SQL database?

  • User profile image
    Jakub​Szymaszek

    @chhavi:Always Encrypted, which supports column-level encryption, is not supported in Azure SQL Database yet, but we are working on enabling it.

  • User profile image
    Joseph Fallon

    +1 for Steve's question - How would this work with SSRS? Are you able to decrypt for the purposes of reports?

  • User profile image
    JustinS

    @JakubSzymaszek: When using SSMS 2016 + CTP3 and the 'column encryption setting=enabled' parameter, querying on a column protected with Always Encrypted results in the following error:

    Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.

  • User profile image
    Jakub​Szymaszek

    @ Steve and Joseph - We are working on enabling support in SSRS for Always Encrypted.

  • User profile image
    Jakub​Szymaszek

     @Justin: Thank you reporting the bug. The fix for this bug will ship in CTP3.1. Please see the comment on the following blog post for more details: https://blogs.msdn.com/b/sqlsecurity/archive/2015/11/10/using-the-azure-key-vault-key-store-provider.aspx.

  • User profile image
    Alex

    Hi,

    great Feature!I have been a Microsoft Certuified Trainer for almost 20 Years now. For trainingpreparation and for some upcoming early Trainings. Would it be possible to get the samples and just more important the sample database(s) you used in your Show.

    Many Thanks

    Alex

  • User profile image
    sunilprabha

    Technologies used: azure sql v12 always encryption  and azure web sites.

    My asp.net application works locally Dev area where I have the Master key certificate in my local cert store.  When I deploy top azure it fails, what additional configuration is needed for asp.net web apps. How do I export the certificates and import to azure.?

    Error:

    Certificate with thumbprint ' 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.
    Parameter name: masterKeyPath

  • User profile image
    Troy Herrick

    I'm having trouble determining what versions of SQL should be able to support Always Encrypted. There's both SQL Server and Azure SQL Database versions that make it confusing. It appears SQL Server needs to be build 13.0 which is SQL Server 2016, right?
    But what about Azure SQL Database? There appears to be V11(build 11.0) and v12(build 12.0). Do they both support Always Encrypted?
    Is that build 11.0 different from SQL Server 2012 that's build 11.0?

  • User profile image
    Jakub​Szymaszek

    @Troy Herrick:

    Hi Tom,

    Always Encrypted is supported in Azure SQL Database v12. Azure SQL Database V11 does not support Always Encrypted. Please, see the below links for more details:

    The features is also supported in SQL Server 2016. You can download the latest refresh/CTP of SQL Server 2016 (pre-RTM version) from here: https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/.

    Please, let us know, if you have any other questions.

  • User profile image
    Gabriel

    Hi Tom,
    which on premises versions of SQL 2016 will support the always encrypted?

  • User profile image
    Jakub​Szymaszek

    @Gabriel: Always Encrypted has been supported since Community Technology Preview (CTP) 2 of SQL Server 2016. You can download the latest/current CTP from here:

    https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

  • User profile image
    Gabriel

    Thanks Jakub. What I meant is, which SQL 2016 editions will support the always encrypted (std, bi, ent)?

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.