Azure DevOps "Hosted 2017" build agent not allowing SQL connection

Question

I have a very simple DacPac that is built by an Azure DevOps build and released by a release pipeline. The build works fine but the pipeline fails with a connectivity error. I have checked and re-checked the set up. The firewall rules get set properly on the target server, the credentials are valid but the release will just not go through. I have tried many many combinations, including specifying ports but I cannot get it to connect. If I try initiate a connection from PowerShell and Invoke-SqlCmd I get a similar connectivity error.

Can anyone shed some light?

Is there any port restriction that may be getting in the way (e.g. 1433 blocked) on the Hosted 2017 agent?

Do I need to update the agent?

Is there some additional config required for SQL connectivity?

Thanks

The error I get is:

Unable to connect to target server '.database.windows.net'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server. Login failed for user 'data...

Answer 1

It seems AD Auth is not supported by the standard SQL Publish activity. The UI only allows you to supply credentials for a SQL user. There is an option to supply a connection string through the /TargetConnectionString parameter for SqlPackage.exe however this does not work because the UI demands the SQL credentials, and these cannot be used in conjunction with the /TargetConnectionString parameter.

I worked around the issue by running SqlPackage.exe from a standard PowerShell release script.

& "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe" /SourceFile:"$(System.DefaultWorkingDirectory)/_MyProj.Infrastructure build/DBScripts/bin/Debug/MyProj.Database.dacpac" /Action:Publish /tcs:"Server=myprodsql.database.windows.net;Initial Catalog=OptimisedDb;Persist Security Info=False;User ID='$(adminUserEmail)';Password='$(adminUserPassword)';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password"