none
SSIS inserting into Azure SQLDB Always Encrypted encrypted using Key Vault RRS feed

  • Question

  • I have an Azure SQL Database 'Test' with one column encrypted using Always Encrypted.  I have stored the automatically generated column master key in a Azure Key Vault.  I have tried configuring an SSIS package to read from an unencrypted flat file and insert/update records in to the Azure SQL Database.  When I run the package from my workstation I get the following error:

     

    [ADO NET Destination [2]] Error: An argument exception has occurred while data provider tried to insert data to destination. The returned message is : 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.

     

    I have configured the ADO.net connection manager Column Encryption Setting to enabled

     

    How do I configure\register the Azure Key Vault provider with the ADO.net connection\driver?  How do I Integrate the Azure Key Vault Provider into SSIS so I can access the CMK?

    My next step if I can get this to work from SSDT is to deploy to the Azure Data Factory V2 integrated runtime that I have running and configured with SSISDB and SSIS Catalog on same Azure SQL Database as 'Test'.  

    Wednesday, October 24, 2018 11:20 PM

All replies

  • Hi Sergmis,

    First you have to interact with keyvault from SSIS, so create a script task and write the code as below

    public static async Task<string> GetSecretValue(string KvUrl)
            {
                AzureServiceTokenProvider azureServiceTokenProvider = new AzureServiceTokenProvider();
                var keyVaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
                var Secret = await keyVaultClient.GetSecretAsync(KvUrl).ConfigureAwait(false);
                var ConnectionString = Secret.Value;
                return ConnectionString.ToString();

            }

    I wrote this code to read the secrets stored into the key vault, here KvUrl is https://{Your key vault}.vault.azure.net/secrets/{Your created object in this case}. You can generate this url from keyvault itself once create the key/secrets. 

    Another important thing is how SSISwill interact with Azure resources if it runs outside Azure, in this case you have to create and registration an Azure app in Azure Active Directory and give proper access rights to this app under the same subscription. Once you regitstrater this app it will generate applicationid and application key and those you need to pass from ssis to interact the azure resources.



    Cheers,

    • Proposed as answer by Yang.Z Wednesday, October 31, 2018 7:54 AM
    Thursday, October 25, 2018 1:23 AM