CODEWITHSUNDEEP
ssisazure-sql-databasessis-2012azure-service-principal
Ian Carrick
Ian Carrick

Reputation: 368

Connecting to Azure SQL Database Using Either Service Principal or Managed Identity

Using SSIS (Visual Studio 2019 Community), on my home PC I am trying to create a DB connection to the Azure SQL Database using either an AZ Service Principal or Managed Identity. Not sure if this is possible or not?

NB: I don't want to use either ADF or migrate the SSIS package to ADF.

NB: Using my personal Az Active Directory account with MFA - in SSIS I can successfully create a connection & connect to the Az database.

  1. I have created a Az 'Service Principal' (SP) and secret.
  2. The 'SP' created in step-1 has been created as a 'external provider' user under the database with the required read/write roles.
  3. In SSIS I create a new connection - I am using the Az Service Principal Name and the password return for the SP when creating via the Azure CLI. (I have also created a 'secret' on the SP however this does not work either).

SSIS Connection Setup

The error returned when testing the DB connection...

SSIS Connection Error

Second error message

Any idea of where I am going wrong? (NB: the Azure tenant name returned in the error msg is correct)

Maybe there's a step-by-step tutorial url you could provide on how to connect to a Azure SQL Server/DB from SSIS (not SSIS imported in to Az) using either an Azure Service Principal or Managed Identity.

Upvotes: 2

Views: 692

Answers (1)

Ian Carrick
Ian Carrick

Reputation: 368

In SSIS I need to use the appId returned from the below command as the 'username' in the connect type 'Active Directory - Service Principal'.

az ad sp create-for-rbac --display-name testapp --role reader --scope /subscriptions/xxxxx-d25a-xxx-xx-6eb1a75e8437/resourceGroups/xxxx-rg --years 2 
       
"appId": "7sdgs99d-xxxxx-xxx-8522-xxxxx",
"displayName": "testapp",
"password": "yZm8Q~z~9_2hLVibWrT5Cdo-p53UsxSftqyD0dqf",
"tenant": "xxxxx-xxxx-xxx-xxxxxx-xx"

Upvotes: 1

Related Questions