For one of our customer, we have implemented an integration solution for their on-premise CRM and ERP application using BizTalk Server 2016. Now it’s ready to go LIVE and we are helping them to build PROD environment. But in next 6 months or so they would move their current database server farm to a new one. So, we suggested them to create a SQL server alias as it will not require to re-configure BizTalk server once the database server is changed. While configuring BizTalk server we started getting error - SSO service failed to start. 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=SQLBIZPROD;Integrated Security=SSPI;Initial Catalog=SSODB Error code: 0x800710D9, Unable to read from or write to the database.  

Troubleshooting and fix

First we try to connect the database server from BizTalk server using SQL server Management studio for the SSO admin user and it connected successfully. Next we started looking into the SQL server alias configurations. You can create aliases using SQL Server Client Network Utility. These aliases need to be implemented on each BizTalk Servers in the group.

There are two cliconfig.exe on the BizTalk server

• C:\Windows\System32\cliconfg.exe (64bit) and
• C:\Windows\SysWOW64\cliconfg.exe (32bit)

But the alias was only configured using the 64bit cliconfig.exe and not with 32bit. Once we configured the alisa using 32bit cliconfig.exe, we got rid of the error and BizTalk configured successfully. So basically the SQL server alias must be configured using both cliconfig.exe, 32bit and 64bit otherwise you might get the error - SSO service failed to start.  

Related Links

https://blogs.msdn.microsoft.com/biztalknotes/2013/05/15/biztalk-and-sql-server-alias/ https://msdn.microsoft.com/en-us/library/aa577661.aspx