Powershell can't connect to Azure SQL when running Data Factory pipeline

[icon2023]

I want to restore a database in Azure SQL using a PowerShell script in Data Factory. For this I have created a pipeline in Data Factory with a Custom Activity. I am connecting to a Batch account and a pool using System managed Identity and to a Storage account where I have the PowerShell script.

In the Pool Start Task I am running the following commands: powershell "Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force; Install-Module AzAccounts -force; Set-ExecutionPolicy Unrestricted -Force ; install-module -name sqlserver -force -allowclobber ; import-module SqlServer;".

I am not sure which type of access needs to be given to the Batch Account or to ADF to be able to connect to Azure SQL (using System managed Identity).

Also, under Managed Linked Services in ADF I have a linked service to the Azure SQL database, but I don't know if I need this linked service and I don't know how to add it in the pipeline to be able to connect to Azure SQL.

[NandanHegde · Accepted solution]

Hey,

since you are using powershell script to access the Azure SQL database and powershell is being executed within Azure batch, it is the Azure batch that needs to have access on the Azure SQL database and not the ADF.

There is not even the need to create a linked service to Azure SQL DB from ADF as ADF is just there to trigger the Azure batch,

Also for ADF reletaed queries, plz use the below MSFT forum :

https://learn.microsoft.com/en-us/answers/tags/194/azure-data-factory?source=docs

[icon2023]

Hello,

Thank you for your answer. Still, I can't find anywhere how you can give Azure Batch tasks the ability to connect to an Azure SQL Database. 

I will also post this on ADF related queries.

Thanks!

[Anonymous]

Hi @icon2023 ,

Thanks for using Fabric Community.

Can you please share whether you are working with Azure Data Factory or Fabric Data Factory?

Unfortunately Fabric Community only supports fabric related issues, incase if you want to resolve your issue related to Azure Data Factory - please raise your issue here - Ask a question - Microsoft Q&A .

[icon2023]

The question was related to Azure Data Factory. 

I will also post it on ADF related queries.