using dacFX deployment on SQL 2019

Question

Hello,

I have a problem using sql dacpac deployment using WebDeploy 3.6 and SQL server 2019.
(this infrastructure works without error using SQL 2012 server)

The infrastructure:
1; deployment server
Windows Server 2019 + IIS 10 + WebDeploy 3.6 installed
(installed prereq: DacFramework_18.2 x86 and x64; SQLSysClrTypes_15.0.2000.5; SQL Native Client 2012 11.4.7001)
2; SQL server
Windows Server 2019 + SQL 2019 Standard installed (windows authentication only)

The commend:
msdeploy.exe -verb:sync -source:dbDacFx="C:\Temp\WebDeploy\DacTest2012.dacpac" -dest:dbDacFx="Data Source=phadb;Integrated Security=True;Database=helloworldwd_test",computername="https://192.168.0.1:8172/msdeploy.axd?site=HelloWorldWD",userName="xxxxxx",password="xxxxx",authtype=basic -verbose -allowUntrusted

The error is:
Error Code: ERROR_EXECUTING_METHOD
More Information: Could not deploy package.
Unable to connect to target server 'phadb'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

On SQL side, I see this failed logon in the SQL logs.
When I grand permission to user "ANONYMOUS LOGON" the deploy successful.

Do you have any idea what was the problem?

BR,
Istvan

Answer 1

Hello,

The root cause was a wrong service user. (Web Deployment Service was run under Local System account). After I set to a right domain user, the problem solved.

BR,
Istvan

Answer 2

Hi @István Erdélyi ,

...Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.Do you have any idea what was the problem?

This is an authentication problem。
At this time, the client is probably running under the Local System account, and SQL Server is not registered with SPN. Thus, NTLM was used. Since the client and SQL Server are on different machines, the client's Local System account cannot be authenticated using NTLM, so the client's identity is regarded as ANONYMOUS LOGON.

Workaround1:Manually register the SPN under the service account of SQL Server
Steps:

1. run cmd as Administrator;
2. check whether there is one correct SPN;
   setspn -L <Account>
   Account could be one domain account or one machine name,it depends on your situation.
3. If there is no SPN, please add one as next:
   setspn -A setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 redmond\accountname

Workaround2:register-spn-automatically
Steps:

1. Close firewall;
2. Use TCP\IP protocol and TCP port:1433;
3. Use your domain account restart your sqlserver service; Of course, you need firstly grant the account permissons to read and write SPN,try steps as my reply from this case: the-target-principal-name-is-incorrect-cannot-generate-sspi-context-windows-authentication-sql
   ...
   In your DC, run->”adsiedit.msc”
   Assume that the start account is YX\Administrator, Administrator->Properties->Security->Advanced->Permissions->Add->Select a principle->Input “SELF”->OK (as next screenshot shows)
   Choose “Read serverPrincipalName” and “Write serverPrincipalName” (as next screenshot shows)
   Use this account restart your server and browser;
   ...

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.