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.
- I have created a Az 'Service Principal' (SP) and secret.
- The 'SP' created in step-1 has been created as a 'external provider' user under the database with the required read/write roles.
- 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).
The error returned when testing the DB connection...
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.