Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON + SQL Server

Question

Hi Everyone,

I am seeking help with analyzing an issue related to SQL Server linked server connections. Here is a description of the problem.

All SQL server databases servers are on-premises.

Scenario 1: (Working Properly)

We tried to connect SQL server instances from on-prem VM machine from SSMS.

The connection of individual SQL server instances is functioning properly. When trying to perform a test connection from linked servers, they are successful. Also, fetching data from linked servers is successful.

Scenario 2: (Not working)

We tried to connect SQL server instances from Azure VM machine from SSMS. Azure VM is also part of same domain as of on-premise servers.

The connection of individual SQL server instances is functioning properly When trying to perform a test connection from linked servers, an error is mentione below. screenshot is as attached.

__Fout! Bestandsnaam niet opgegeven.__TITLE: Microsoft SQL Server Management Studio ------------------------------

The test connection to the linked server failed. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. OLE DB provider "SQLNCLI11" for linked server "ServerXXXXX" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 18456) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error ------------------------------ BUTTONS: OK ------------------------------

 

When initiated connection from Azure VM. As checkedin the logs on individual server it observed that NTLM V1 was used at the time of authentication in place of Kerberos.

Note: Same SQL server instances were connected during both the scenarios.

Answer 1

@Sushant Yadav

Issue

We tried to connect SQL server instances from Azure VM machine from SSMS. Azure VM is also part of same domain as of on-premise servers.

The connection of individual SQL server instances is functioning properly When trying to perform a test connection from linked servers, an error is mentione below. screenshot is as attached.

__Fout! Bestandsnaam niet opgegeven.__TITLE: Microsoft SQL Server Management Studio ------------------------------

The test connection to the linked server failed. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. OLE DB provider "SQLNCLI11" for linked server "ServerXXXXX" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 18456) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error ------------------------------ BUTTONS: OK ------------------------------

Resolution:

1. Few MSSQL server were using MSA account as a logon account and few as a domain account.
2. Full delegation was provided to service account. (i.e. Unconstrained delegation)
3. When Credential Guard is enabled. Unconstrained delegation will not work.
4. We disabled the credential guard on server side.

You can resolve this issue by changing delegation to constrained delegation and adding required services. When Credential Guard is enabled constrained delegation will work.

Note: I have used sqlcheck utility for analysis.

Answer 2

I have managed to resolve the issue by disabling credential guard on server. Observations:

1. Few MSSQL server were using MSA account as a logon account and few as a domain account.
2. Full delegation was provided to service account. (i.e. Unconstrained delegation)
3. When Credential Guard is enabled. Unconstrained delegation will not work.
4. We disabled the credential guard on server side.

You can resolve this issue by changing delegation to constrained delegation and adding required services. When Credential Guard is enabled constrained delegation will work.

Note: I have used sqlcheck utility for analysis.

Hope this will help someone while truobleshooting.

Answer 3

Hi @sushant yadav , Welcome to MS Q&A

What you describe a scenario 2>VM A –>linked server -> SSMS

Please make sure delegation has to set for the middle tier SQL instance service account(VM A). This means that make sure SQL Server service account was trusted for delegation in AD.

You can go to domain controller -> open active directory users and computers -> users -> right-click the SQL Server Service account in users folder -> Properties. Then go to delegation tab in the Properties dialog box, ensure that "Trust this user for delegation to any service (Kerberos only)" or "Trust this user for delegation to specified services (Kerberos only) – Use Kerberos only "is selected. If you choose the " Trust this user for delegation to specified services (Kerberos only)", please add the SQL Server service. ( please do the same for the delegation tab in the Properties of server's computer object in active directory users and computers.)

Then go to the account tab in properties and ensure that the "account is sensitive and cannot be delegated" option is not selected.

Please refer to this blog or this one.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Hi Deepanshu katara,

Thanks for sharing the details.

As checked in AD, mentioned settings are already in place. As checked at MSSQL end individual connections and query sessions are using kerberos authentication but issue is only while using linked servers.

**Issue is observed only when I try to connect linked servers on on-premise SQL server from SSMS on Azure VM (Azure virtual desktop) machines

Queries on same SQL servers and linked servers are executing sucessfully when tried from on-premise VM.**

I have checked below parameters.

1. SPN's are registered for all SQL servers.
2. Delegation is configured for SQL Server service account. In my case one server is using domain account as a service account and all remaining servers are using managed service account.
3. Delegations are configured for computer objects in AD.
4. using klist I have checked if kerberos tokens are also generated for the service accounts.
5. Tried purging kerberos (klist purge) tokens and reinitiating the test connection. It still failed.
6. On-premise AD servers and Azure AD servers are also in sync.
7. Property mentioned by you are also in sync.
8. SQL server logon account is part of local admin group on server.
9. System time is in sync in all servers and AVD machines.

Please correct me if I am wrong. The issue doesn't seem to be with SPN and delegation, as the same is working in the on-premise environment.

Please help me understand if any parameters I need to check from the Azure side or the Azure virtual desktop configuration side.

Answer 5

Hi Experts,

It would be greatly appreciated if you could provide any assistance.