2

SSPI security failed to connect from application server to remote database server.

I am trying to edit the web.config in an aspx page which is embedded in Dynamics CRM. The previous web.config connectionstring is like below:

<add name="conn" connectionString="Server=10.138.50.133;MultipleActiveResultSets=True;Database=dbserverip;User Id=domain\CRMADMINUAT;Password=p@ssword" providerName="System.Data.SqlClient" />

Due to security concern, now I need to change the integrated security to SSPI.

 <add name="conn" connectionString="Data Source=SGARCRMDBUAT;Initial Catalog=DB_MSCRM;Integrated Security=SSPI" providerName="System.Data.SqlClient" />

However, it is always failed to connect with the database server and throwing me this error:

 [SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]

below are steps that I have taken but still not working:

  1. change the CRM Application Pool identity from Local System to a sysadmin user which has admin rights on both App and DB server.

  2. add <identity impersonate="true"/> into web.config

  3. Disable Anonymous Authentication of the site. Enable ASP.NET Impersonatation

  4. Change Integrated Security=true

  5. No special Delegation configuration in AD

And i realized that the site is working normally in server localhost, only showing anonymous error when i try to access from my PC which is in same LAN network as the server.

There's another weird thing happened. When i create a new login user 'NT AUTHORITY\ANONYMOUS LOGON' in Database, the error was gone and the page was working perfectly as before!! Seems like the 'NT AUTHORITY\ANONYMOUS LOGON' is a hard coded user or configuration?? But i have checked my code, no such things exist in my code.

Really hope that someone could help me.. Thanks ahead.

Hongxuan
  • 117
  • 1
  • 2
  • 9

1 Answers1

1

It seems the 'double hop issue'. When the client, web server and SQL server are all on different machines, then delegation must be enabled between this web server and SQL server. This is set in AD Users and Computers tool, via the Delegation tab of the computer properties.

More details about what is 'double hop issue' and how to solve it, you could refer to below article: https://blogs.msdn.microsoft.com/crm/2012/09/19/enabling-kerberos-for-microsoft-dynamics-crm-2011/

https://blogs.technet.microsoft.com/taraj/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server/

How to run an R script (which has database connection using integrated windows authentication) on a remote machine under local username?

Brando Zhang
  • 22,586
  • 6
  • 37
  • 65