AzureDevOps - Azure SQL Server deployment Problems and Solution

0
votes

What are the solutions to well known issues encountered while deployment Azure SQL server.

Problem 1

##[error]System.Management.Automation.ParentContainsErrorRecordException: *** Deployment cannot continueAn error occurred during deployment plan generation.

Problem 2

##[error]Publishing to database 'testdb' on server 'ajyatest.database.windows.net'. Initializing deployment (Start) Initializing deployment (Failed) *** Could not deploy package. Unable to connect to master or target server 'testdb'. You must have a user with the same password in master or target server 'testdb'.

Problem 3

##[error]System.Management.Automation.RuntimeException: No resource found with serverName ajyatest1, serverType Microsoft.Sql/servers in subscription <SubscriptionId>. Specify the correct serverName/serverType and try again.

Problem 4

Server name 'ajyatest1' is not in the right format. Use FQDN format like 'yyy.database.windows.net'

Problem 5

##[error]System.Net.WebException: The remote server returned an error: (404) Not Found.

Problem 6

##[error]Start IP address of firewall rule cannot exceed End IP address.

Problem 7

##[error]Invalid value provided for parameter: startIpAddress

Problem 8

##[error] Named pipes provider, error: 40 – could not open a connect to SQL. 
Possible suggestions

Problem 9

How to configure timeout for SQLPackage.exe ?

Problem 10

Does Azure SQL Database deployment with AAD integrated authentication supported on hosted agent?

Problem 11

Azure SQL Database Deployment - Imported Invoke-Sqlcmd doesn't support connectionString

Problem 12

SqlPackage.exe argument /p:BackupDatabaseBeforeChanges=true fails on Azure

Problem 13

Azure SQL Publish - SQL Script File - Not able to pass secret value as Variable

1
sql-serverazureazure-devopsazure-sql-databaseazure-pipelines
So what is the question? – Dale K
@Dale Burrell - Actually no question. Rather I wanted to provide it as the guidance to the users who encounter issues mentioned above. It contains general problems as well as solutions. – Ajay Yadav
Thats not how the site works. If you want to provide advice you ask a clear question that could benefit someone else. Then self answer. – Dale K
Very appreciate for your solution sharing. Do you mind edit your question with only include problem and share the relevant solution in answer? This is good for others understanding and get clearly help from it. And also, this can help us archive this ticket. – Merlin Liang
@MerlinLiang-MSFT - Thanks for the suggestion. I have updated the problems only to the question section and moved the solutions to the Answer section of the thread. – Ajay Yadav

1 Answers

2
votes

Problem 1

##[error]System.Management.Automation.ParentContainsErrorRecordException: *** Deployment cannot continueAn error occurred during deployment plan generation.

Cause

Target platform of SQL server on which you are trying to deploy your dacpac is different than target platform of dacpac file.

Possible solutions

  1. Change target platform in your dacpac project as same as target platform of SQL server. enter image description here

  2. Add /p:AllowIncompatiblePlatform=true argument as additional argument to WinRm SQL server task.

Problem 2

##[error]Publishing to database 'testdb' on server 'ajyatest.database.windows.net'. Initializing deployment (Start) Initializing deployment (Failed) *** Could not deploy package. Unable to connect to master or target server 'testdb'. You must have a user with the same password in master or target server 'testdb'.

Cause

Wrong username or password to SQL server.

Possible solutions

Check if username and password are right for SQL server. [You can verify username password using SSMS]

Problem 3

##[error]System.Management.Automation.RuntimeException: No resource found with serverName ajyatest1, serverType Microsoft.Sql/servers in subscription <SubscriptionId>. Specify the correct serverName/serverType and try again.

Cause

Wrong SQL server name is wrong.

Possible solutions

Check if SQL server with same name exist on Azure Portal.

Problem 4

Server name 'ajyatest1' is not in the right format. Use FQDN format like 'yyy.database.windows.net'

Cause

SQL server as input in not being provided as FQDN.

Possible solutions

As mentioned in error itself provide SQL server in format 'yyy.database.windows.net'

Problem 5

##[error]System.Net.WebException: The remote server returned an error: (404) Not Found.

Cause

Check for endpoint URL for creating firewall rules. Verify if URL for endpoint is current.

Possible solutions

Check what’s wrong with endpoint. How user have created endpoint.

Problem 6

##[error]Start IP address of firewall rule cannot exceed End IP address.

Cause

End IP is smaller than Start IP for creating firewall rule.

Possible solutions

IP address for start IP should be smaller than end IP address.

Problem 7

##[error]Invalid value provided for parameter: startIpAddress

Cause

IP address is not in right format.

Possible solutions

IP address for start IP should be smaller than end IP address.

Problem 8

##[error] Named pipes provider, error: 40 – could not open a connect to SQL. 
Possible suggestions

Suggestions

For onPrem SQL server 1. SQL server name should be in right format server\instance. 2. Verify connection string. 3. Check if Named Pipes (NP) is being enabled on the SQL instance. 4. Is Remote connection enabled.

  For Azure SQL server

1. It could cause as per application throttled. 2. Try to give azure SQL server name with port 1433. E.g. servername.database.windows.net,1433;

Problem 9

How to configure timeout for SQLPackage.exe ?

Solution

/p:CommandTimeout=1200  /TargetTimeout: 1200

Problem 10

Does Azure SQL Database deployment with AAD integrated authentication supported on hosted agent?

Solution

No, AAD integrated authentication tries to login to SQL server with same user with which Agent is running. Since hosted agent user won't have permission on the SQL server because of security reasons.Hence it will fail. In other words, AAD integrated authencation is not supported for Hosted agent. Thought you can use Active Directory - Password authentication.

Problem 11

Azure SQL Database Deployment - Imported Invoke-Sqlcmd doesn't support connectionString

Solution Imported Invoke-Sqlcmd doesn't support connectionString. Install SQLServer PS module.

Problem 12

SqlPackage.exe argument /p:BackupDatabaseBeforeChanges=true fails on Azure

Solution

/p:BackupDatabaseBeforeChanges=true options is for on-premise databases. That option doesn’t apply to Azure since the backup system is completely different.

Problem 13

Azure SQL Publish - SQL Script File - Not able to pass secret value as Variable

Solution

SQL script file supports secret variables. Make sure you are passing arguments for variables as follow.

Invoke-Sqlcmd -ServerInstance "test.database.windows.net" -Database "Testdb" -Username "test"  -Password ******  -Inputfile "C:\test.sql" -variable "MYVAR1='$(variablevalue1)'", "MYVAR2='String2'" -ConnectionTimeout 120

Debug tools and suggestions

Documentation for SQLPackage.exe arguments

SQL Package arguments

Configure diagnostics logs for SQLPackage.exe

/diagnostics:true

Get debug logs from the task

Set release definition variable system.debug with value true

SqlCMD arguments

SqlCmd arguments

Compare SqlCmd and Invoke-SqlCmd arguments

SqlCmd vs Invoke-SqlCmd arguments