none
SSIS access to Azure Key Vault RRS feed

  • Question

  • I have an Azure SQL database with a table that has one field encrypted using Always Encrypted.  The column master key is store in Azure Key Vault.  Now I would like my on-premise SSIS application to be able to decrypt that column when retrieving data from Azure SQL database.  Can SSIS be configured to talk to Azure Key Vault for the master key?  If not, is there a way my SSIS application can decrypt the value?

    Monday, May 23, 2016 4:36 PM

All replies

  • Hi Typhoon,

    I am thinking you need to use PowerShell (find examples online) from within SSIS.


    Arthur

    MyBlog


    Twitter

    Monday, May 30, 2016 7:22 PM
    Moderator
  • Yes, if you configure SSIS to use ODBC 13.1 and specify Azure credentials (to access your key vault) in the connection string, you will be able to encrypt/decrypt columns.
    Friday, February 3, 2017 5:26 PM
  • Hi Jakub,

    I have installed ODBC 13.1 in my machine,Iam not able to find any option to specify the Azure credentials and Azure Key Vault.Can you please provide more details, how to specify the Azure credentials and key vault in ODBC 13.1


    M.Murugesan

    Monday, March 13, 2017 2:22 AM
  • Set this as your connection string in ADO.NET ODBC connection. For getting KeyStorePrincipalID and KeyStoreSecret, you need an Azure AD App setup with access to your key vault. 

    DRIVER=ODBC Driver 13 for SQL Server;SERVER=myServer;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret;KeyStorePrincipalId=<clientId>;KeyStoreSecret=<secret>
    Friday, May 17, 2019 8:52 PM