Post reply

SSIS Run As profile to grab Azure data!

  • July 26, 2017 at 5:46 am

    #331988

    Hi all

    I'm having issues setting up a proxy to grab data from an Azure database.
    The proxy will only accept a domain account and obviously that isn't going to connect to the Azure database.

    Do I need to use an Azure specific connector instead of the OLE DB Source?
    Any help appreciated 🙂

    Thanks
    Mattie

  • July 27, 2017 at 5:24 pm

    #1953130

    Mattie,

    Using a proxy requires a Windows account - you can't use a SQL login for this. However, you can configure the connection manager inside the SSIS package to use a username/password login instead.

    I hope this helps!
    ~Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • July 28, 2017 at 4:03 am

    #1953167

    Tim Mitchell - Thursday, July 27, 2017 5:24 PM

    Mattie,

    Using a proxy requires a Windows account - you can't use a SQL login for this. However, you can configure the connection manager inside the SSIS package to use a username/password login instead.

    I hope this helps!
    ~Tim

    Hi Tim
    Thanks for the response. I can run the package locally on my PC using the SQL credentials in the connection but once I deploy the package to the Integration Services Catalogue it fails.
    There must be a way of forcing the job to run with non-windows credentials under the SSIS Service which is running as a domain account?

    I'll keep researching, hopefully there is an answer out there.

    Thanks
    Mattie

  • July 28, 2017 at 6:04 am

    #1953180

    Mattie,

    The reason it works locally but fails when deployed to the catalog is most likely a result of the PackageProtectionLevel setting.

    By default, the package will encrypt any sensitive information (including database connection passwords) using the Windows credentials of the user who saved the package. This works well as long as you're executing on your machine under your own login, but requires additional setup when deployed to the SSIS catalog. Your best bet for solving this problem is to create an SSIS environment on the server where you deploy the package, and store the login credentials for the Azure DB in that environment.

    I hope this helps,
    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply