BizTalk Server 2010, WCF-SQL Adapter does not connect to the Failover Partner
Recently we came across the following error while configuring BizTalk WCF – SQL Adapter to connect to the failover partner:
A message sent to adapter "WCF-SQL" on send port "SendPortName" with URI "mssql://SQL-SERVER-1:50105//DbName?FailoverPartner=SQL-SERVER-2" is suspended.Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException: 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) ---> System.Data.SqlClient.SqlException: 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) ---> System.ComponentModel.Win32Exception: Logon failure: unknown user name or bad password
Scenario:
There are 2 servers SQL-SERVER-1 and SQL-SERVER-2. We are configuring BizTalk WCF-SQL Adapter such that every operation should be performed on SQL-SERVER-1 until this server goes down. In that case the adapter should contact to SQL-SERVER-2 (which is the failover partner) and continue to do the processing in that.
Error:
As per the error, it seems that we are not providing the correct user name and password and therefore it is not able to connect to the SQL server.
But as per the concept of database mirroring, it states that, if one SQL Server is the Failover partner of the other SQL-Server then it should have the same database name and the same username and password, as the primary server.
This means that if I have a connection string mssql://SQL-SERVER-1:50105//DbName?FailoverPartner=SQL-SERVER-2, where SQL-SERVER-1 is the primary database and SQL-SERVER-2 is the Failover partner, then SQL-SERVER-2 should have the same logon credentials and DbName as the SQL-SERVER-1.
However we verified that both the servers have the same user name and password and Database name is also the same.
Then why are we getting this error !!!
Here is the catch. When we tried to connect to the SQL Server using SSMS, and gave the server name as SQL-SERVER-2, PortNumber, we were able to connect without any issues. But if we connect to this as SQL-SERVER-2 we got the same error.
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)
The detailed error message was completely misleading. There was an issue with the way we connect to the SQL Server and not with the user credentials.
Resolution:
The logic here is that if we have a default instance of SQL Server then it should run on the default port (1433/1434). However, the named instance could run on any port. So in case of we have default instance of the SQL Server running on a different port, we need
to create the alias for the same.
As per the connection string mentioned in WCF_SQL adapter:
mssql://SQL-SERVER-1:PortNumber//DbName?FailoverPartner=SQL-SERVER-2, we can specify the port number for the primary server and only the Server name for the failover partner.
We created the alias for both the SQL Servers using the following steps:
Go to run -> cliconfg. Following window will appear. Select the Aliases tab:
Click on Add and type the name of the SQL Server against the Server alias:
Select the TCP/IP radio button and Uncheck the Dynamically determine port. Enter the port number.
Apply these changes. Do the same for the SQL-SERVER-2 also.
After this step the modified string was:
mssql://SQL-SERVER-1//DbName?FailoverPartner=SQL-SERVER-2
We were now able to connect to the SQL Server Failover partner without any error..
Points to Remember:
Have a default instance of the SQL Server run on the default port (1433/1434). In case if you need to run this on the other ports, make sure that you create the SQL Server Alias.
Hope this helps…!!!!
Written by
Rasika Chaudhary
Reviewed by
Shailesh Agre
Microsoft India GTSC