How do I connect a Batch Account to an Azure SQL database

icon_2023 0 Reputation points
2023-12-11T16:09:00.6033333+00:00

I have an Azure Data Factory pipeline with a Custom Activity. I am connecting to a Batch account to run a Powershell script to restore an Azure SQL database.

But I can't find anywhere how I can give Azure Batch tasks the ability to connect to the Azure SQL database.

Azure SQL Database
Azure Batch
Azure Batch
An Azure service that provides cloud-scale job scheduling and compute management.
320 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,012 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 33,341 Reputation points MVP
    2023-12-11T19:58:43.0566667+00:00

    Here you will find how to connect and perform many tasks from Azure PowerShell used by Azure Batch. Below an example.

    function Get-SqLCliData ($SQLconnString, $SQLqry) {  
$SQLconnString  
$SQLConn = New-Object System.Data.SqlClient.SqlConnection($SQLconnString)  
$SQLConn.open()  
$readcmd = New-Object System.Data.SqlClient.SqlCommand($SQLqry,$SQLConn)  
$readcmd.CommandTimeout = "300"  
$da = new-object System.Data.SqlClient.SqlDataAdapter($readcmd)  
$dt = New-Object system.Data.datatable  
[void]$da.fill($dt)  
$SQLConn.close()  
return $dt  
}  
  
$SQLqry = 'select * from dbo.mytable'  
$SQLconnString = 'Data Source=tcp:myserver.database.windows.net;Initial Catalog=mydatabase;Authentication=Active Directory Password;User Id=myuser@mydomain.com;Password=mypassword;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;'  
$DB_Results = Get-SqLCliData "$SQLconnString" $SQLqry  
$DB_Results
    0 comments