Azure DevOps Connect to Azure SQL Connection String error

Question

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?

Kevin Lu · Accepted Answer · 2020-08-03 03:07:02Z

1

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.

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

answered Aug 3, 2020 at 3:07

Kevin Lu

36.9k4 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rk92 · Accepted Answer · 2020-07-31 16:58:15Z

0

https://learn.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.

edited Jul 31, 2020 at 16:58

answered Jul 31, 2020 at 16:40

rk92

6031 gold badge10 silver badges28 bronze badges

2 Comments

delucaezequiel Over a year ago

Same exception. ##[error]Exception setting "ConnectionString": "Keyword not supported: 'authentication'." Seems that the authentication in the connection string is not supported on Azure PowerShell in Azure DevOps.

rk92 Over a year ago

@delucaezequiel I have not attempted what you are trying to do before but I was just googling around to see if I could help any further. Can you try to add your UID and PWD similar to this template and see if you can connect? connectionstrings.com/sqlconnection/…

Collectives™ on Stack Overflow

Azure DevOps Connect to Azure SQL Connection String error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related