Azure DevOps Connect to Azure SQL Connection String error

0
votes

I am trying to create a solution with Azure DevOps. I need to create a connection to an Azure SQL Database using an Azure Powershell Task in a Release/Pipeline. On my machine I was able to achieve this using the following

function new-connection($ServerIsntance, $DatabaseName)
{
    $obj = New-Object System.Data.SQLClient.SQLCommand
    $obj.Connection = New-Object System.Data.SQLClient.SQLConnection
    $obj.Connection.ConnectionString = "Server=tcp:$($ServerInstance),1433;Initial Catalog=$($DatabaseName);Persist Security Info=False;User Id=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password"
    $obj.Connection.Open()
    .....
}

However when I use this code in Azure DevOps I get the following exception: ##[error]Exception setting "ConnectionString": "Keyword not supported: 'authentication'."

The database that I am trying to use is an AzureSQL one, so I can not use Integrated Security = true as it is not supported. Do you have an idea of how should I face this issue?

2
azure-devopsazure-sql-databaseazure-powershell

2 Answers

1
votes

Test with the same script, it seems that the cause of this issue is agent type.

When I use the Windows agent(e.g. vs2017-win2016), the script could work as expected.

But when I test with Linux agent (e.g. ubuntu 16.04), I could get the same issue.

enter image description here

So you could check the agent type (windows or linux).

You could try to use the Windows Microsoft-hosted agent (e.g. vs2017-win2016 , windows-2019) .

0
votes

https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15

Does your code include spaces between Active Directory Password? If so try removing them since the doc does not have any spaces included.