3

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

enter image description here enter image description here enter image description here

Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86
  • Did you try allow [access to azure services](https://i.stack.imgur.com/6qKWb.jpg) also try changing the `startip` and `endip` to `0.0.0.0` – Jayendran Oct 19 '18 at 03:24

5 Answers5

3

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"

enter image description here

Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86
  • 1
    Is the same case like here: https://stackoverflow.com/questions/67759635/sqlpackage-exe-publish-conjunction-exception – arielma May 30 '21 at 10:37
0

I've had the same problem before, the 'easiest' way to get around this is to white-list each and ever IP address that DevOps has - sadly though the list is quite large depending on your geography and it changes weekly.

I did write a PS1 script to parse the list and ensure that the necessary rules were in place.

https://www.microsoft.com/en-nz/download/details.aspx?id=41653

There are however a few obvious (and one less so) issues with doing this...

The list of IP addresses for some regions is huge, so big that the Azure Database cannot have them all, it exceeds the limit.

In opening the IP addresses up, you are (theoretically at least) exposing an attack vector to your database. Should someone know the address of your server, they could look to try to access it.

I did therefore try the 0.0.0.0/allow access to Azure setting - but this didn't seem to actually work for DevOps.

My end solution was to spin up a VM in Azure and install the build agent onto it. Not ideal but it did work.

Andy N
  • 11
  • 1
0

Allowing all IP ranges for my Azure SQL Database did it for me. Now I just have to figure out and narrow down what IP(s) my build agent runs on in my Azure DevOps build pipeline.

Automatic setting of firewall rules in the Azure DevOps build task doesn't work.

Emanuel Lindström
  • 1,607
  • 16
  • 25
0

I struggled a lot with the same issue. I did manage to fix it by doing below two steps

  1. Changed database connection to a different server and once that is a the success I revert it back to the current server.
  2. Changed the database compatibility level to below 150.
  3. Created fresh release

below references helped me. Hope this helps.

https://geeklearning.io/dacpac-and-azure-sql-updates/

https://github.com/microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlAzureDacpacDeploymentV1/README.md#troubleshooting-i

tech-gayan
  • 1,373
  • 1
  • 10
  • 25
-1

You need to specify the port: xxx.database.windows.net,1433.

Daniel Mann
  • 57,011
  • 13
  • 100
  • 120