Getting Started with Always Encrypted with SSMS

Sign in to queue

Description

In this episode of Data Exposed Scott welcomes Jakub Szymaszek and Kaivalya Hanswadkar, Program Managers in the SQL Server group to discuss the recent enhancements in Always Encrypted – a new security technology in Azure SQL Database and SQL Server. Jakub and Kaiv show a demo of the new user interface for Always Encrypted in SQL Server Management Studio.

As you learned in a previous video, Always Encrypted is a client-side encryption technology that ensures the database system never sees sensitive data or its corresponding keys in plaintext. While this makes data more secure, it also complicates the process of setting up encryption in the database – the data needs to be downloaded a secure location, where it is encrypted and uploaded back to the database.

Jakub and Kaiv demonstrate how the new column encryption wizard hides these complexity making it possible to encrypt selected columns in just a few clicks. In addition, they show to use the UI to generate Always Encrypted keys in Azure Key Vault.

Embed

Download

Download this episode

The Discussion

  • User profile image
    Masayuki

    Is the setting of the CEK / CMK, encryption is complete

    but, when you run a query from SSMS by setting the
    " Column Encryption Setting = Enabled " ,
    it will result in an error of under .

    ==========================================
    Msg 0, Level 11, State 0, Line 0
    Failed to decrypt column 'Column2'.
    Msg 0, Level 11, State 0, Line 0
    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'.
    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.
    ==========================================

    SSMS are using the Windows Server 2012 R2 + CTP 3.0 (October Update) , Do I need additional settings, etc. on the client side ?

  • User profile image
    JustinS

    @Masayuki: I have the exact same issue. I'm using Azure SQL + SSMS 2016 CTP3. Encryption succeeded, but trying to query the column results in the AZURE_KEY_VAULT provider error.

  • User profile image
    Jakub​Szymaszek

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

Add Your 2 Cents