My name is Archana CM from Microsoft SQL Developer Support team, we support SQL Connectivity issue along with data access technologies and SSIS.
I had chance to work with SQL DBA who was having issues while connecting to his SQL server machine. We have seen many issue with connectivity to SQL but the solution we provider to his issue was sample and different.
In today's blog I am sharing my experience on how we could resolve the issue for him and what issues he was facing .
Main issue was When the BizTalk service is executed , it was throwing the below error message on the application server
Failed to contact the SSO database: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Data Source=SQLSERVERNAME;Integrated Security=SSPI;Initial Catalog=SSODB
Error code: 0x800710D9, Unable to read from or write to the database.
I followed all the steps that we do to troubleshoot an connectivity issue but none of those steps were able to resolve this issue. Some important steps are
Did UDL test, it was failing to connect to SQLServer "SQLSERVERNAME" from BIZTalk Server.
Microsoft Data Link Error
Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Created the SQL account and tested it , it was still failing.
Test connection failed because of an error in initializing provider. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
We forced Np, TCp with port 1433 but it was still same issue.
SQL Server Native Client Data Link Error
[Microsoft SQL Server Native Client 10.0]: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Made a registry change to " DisableLoopbackCheck" under " HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa"
We added this registry change and rebooted, still it was failing with error below
[Microsoft SQL Server Native Client 10.0]: Login timeout expired [Microsoft SQL Server Native Client 10.0]: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [Microsoft SQL Server Native Client 10.0]: Named Pipes Provider: Could not open a connection to SQL Server .
I collected Netmon and Profiler , I could see all the connections and communication happening from BIZTAlk server to SQL Server in SQL Profiler & Netmon but still we could see Login failed issue.
Checked for Kerberos, Kerberos was not enabled on Active Directory.
We enabled Kerberos on active directory. I could also see correct SPN for SQL account for SQL server but again it was same result.
Even after changes and correct settings BizTalk was not able to successfully connect to SQL server.
Thought may be issue with security.
We added the SQL account to "Access this computer from network" Policy under Local Security Policy -> Local Policies -> User Rights Assignment -> Access this computer from network"
This resolved the issue for us.
Yes, only this setting under Local security Policy didn’t resolve the issue along with that Kerberos was very important.
Hope this blog and my experience will help you to troubleshoot similar issues.
Author : Archana(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft
Thanks Archana! I assume this registers 18456 errors in the log; if so, can you share what state gets associated with the error message?