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

2023-09-16T19:48:08.9566667+00:00

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).

IMG_3128

The error returned when testing the DB connection...

IMG_3129

IMG_3130

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.

Azure SQL Database
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,591 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Tushar Kumar 3,331 Reputation points MVP
    2023-09-16T20:10:53.6533333+00:00

    Update: Issue resolved by Ian,

    ‘appId’ value returned from the azure cli “create service principal for RBAC command” in the ‘username’ field in the connection within SSIS connection manager.

    You can follow this to setup your Service Principal

    1. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql

    Please "Accept as Answer " if this helps

    0 comments No comments

  2. 2023-09-17T08:58:08.2233333+00:00

    I needed to use the ‘appId’ value returned from the azure cli “create service principal for RBAC command” in the ‘username’ field in the connection within SSIS connection manager

    0 comments No comments

  3. ZoeHui-MSFT 37,671 Reputation points
    2023-09-18T03:40:26.02+00:00

    Hi @CARRICK, Ian (NHS NORTH EAST LONDON CCG)

    Check below steps to see if it is helpful.

    [Step by Step] Connecting to Azure SQL Server using OLEDB Connection from SSIS

    You may also check this thread which mentioned use ADO.NET Destination Editor to connect to Azure DB.

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.