Azure SQL Server - can't authenticate linked service from pipeline

Dmitriy Ryabin 0
Viewed 56

So, I was provisioned an Azure SQL server into my subscription of Azure, and I am using Azure Data Factory. Well, starting to use it

The problem I ran into is the following. I am able to connect enter image description here to Azure SQL Server database via

 - SQL Management Studio, 
 - via Visual Studio Code with SQL Tools extension, 
 - and with Azure data studio.

I was able to create tables and stored procedures. All this I am doing by logging in using Authentication type

"Azure Active Directory - Universal With MFA"

.. and providing my "Active Directory Admin". (The "Server Admin" does not work as I do not know what the password was when it was created and our sys admin said "it was blank" ??) enter image description here

However, when I try to set up a linked service on Azure Data Factory and connect to the same SQL Server - I do not see that option (Azure Active Directory - Universal With MFA) in the drop down for Authentication type.

Those available

- SQL Authentication
- System Assigned Managed Identity
- Service Principal
- User Assigned Managed Identity

I tried them all, and I can't connect using either.
My understanding is that there is more to be done on the server management side, but I do not understand what.

azure-data-factory azure-pipelines azure
1 Answers

You can use service principal method to create a success linked service to your SQL database using Azure active directory authentication. You need to follow the steps below:

  • First create an app registration in Azure active directory. Go to azure active directory and select App registrations. enter image description here

  • Create a new app registration with certain name. As you can see in the above image, I have created it with the name for_sql_db.

  • Since you already have set your SQL server with Azure Active directory admin, create a user using the name of the app registration created above. Assign this user any required role. I have used the following queries in my query editor of SQL database.

CREATE  USER [for_sql_db] FROM  EXTERNAL  PROVIDER;
ALTER  ROLE [db_owner] ADD  MEMBER [for_sql_db];

  • Once the user is created in your SQL database, go to data factory to create linked service.

  • Select the SQL server and database you want to create linked service to. Choose the Authentication as Service Principal.

  • Then you can see Tenant , Service principal ID and Service principal key. Go to the app registration you have created. Here you can see Directory tenant ID and application client ID. Give them as following:

linked service Tenant: Directory Tenant ID
Service principal ID: application client ID

enter image description here

  • For Service principal key, you need to create a secret in your app registration (for_sql_db).

enter image description here

  • After creating the client secret, copy its value and use it as Service principal key in data factory linked service.

  • You can see that, I have followed the above procedure and successfully created linked service to my SQL database.

enter image description here

Saideep Arikontham0
Related