WCF-SQL Adapter: Connecting to the LOB system has failed. A network-related or instance-specific error occurred while establishing a connection to SQL Server
I been delivering a lot of BizTalk Server Training Courses for Developers and for Administrator in the past years that I normally like to call them “Developing BizTalk Server version Course” or “BizTalk Server Operations, Administration, and Maintenance (OAM) course” – yes I know, I am very creative in terms of names – and one of the good things about using Azure Virtual Machines for that is that we can easily create several developer machines in a short period of time and manage them for not consuming resources. And normally on of the task that is present in both courses is to configure BizTalk Server environment, because I believe that all BizTalk Developers should know the basics of BizTalk Server in terms of installation, configuration and optimizations – tasks that are normally done by BizTalk Administrations – I can go further and say that, in my personal opinion, if you don’t know these basic concepts you are not truly a BizTalk Developer because many things can be done by configurations and not code.
One of these tasks is to install and configure BizTalk Server LOB adapters, in especially SQL Server adapter, since in a Developer standalone machine we will need to have SQL Server, so it is easy to test this LOB Adapter. However, if we create the Azure BizTalk Server 2016 Developer machine, configure BizTalk Server and then install and configure LOB adapters without doing anything more and you try to create a BizTalk Server solution using Visual Studio and generate SQL Server schemas:
- In the Solution Explorer, right-click your project, click “Add”, and then click “Add Generated Items…”
- In the “Add Generated Items – <Project Name>” dialog box, select “Consume Add Service”, and then click “Add”.
- Select the sqlBinding and properly configuring the URI
We will get the following error message:
Connecting to the LOB system has failed.
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: TCP Provider, error: 0 – The remote computer refused the network connection.).
when we try to connect to the SQL Server configured.
Cause
This error may occur for several reasons like BizTalk Server cannot communicate with SQL Server machine because some firewall restrictions or SQL Server does not accept Remote Connections and so on.
However, in our case, we are talking about a standalone BizTalk machine that is trying to access a database in the local SQL Server.
Our problem was that by default what Microsoft could possibly configure wrong in terms of SQL Server protocols on the BizTalk Server developer image on Azure… is actually set up wrongly!
And as I described in my BizTalk Server Installation and configuration tutorial (see here), one of the important steps is to configure SQL Server Network Protocols, in special ensuring that TCP/IP is enabled and Shared Memory is disabled. You can see how to accomplish this using SQL Server Configuration Manager tool here.
The actual problem that was causing the connecting to fail while trying to connect to the LOB system, in this particular case the SQL Server is because the TCP/IP protocol is disabled.
Solution
To properly configure the protocols for SQL Server, especially the TCP/IP protocol, you should:
- Press the “Windows key” to switch to Metro UI and type “SQL Server 20016 Configuration Manager” and click on “SQL Server 2016 Configuration Manager” option on Apps menu.
- In SQL Server Configuration Manager windows, from the left-hand pane expand “SQL Server Network Configuration” option and then click “Protocols for MSSQLSERVER”
- Verify that both “TCP/IP” and “Named Pipes” are enabled;
- If not, right-click in the protocol, and then click “Enable”
- Repeat to enable the other protocol if necessary.
- Verify that “Shared Memory” is Disable.
- If not, right-click Shared Memory, and then click “Disable”
- In the left-hand pane, click “SQL Server Services”, right-click “SQL Server (MSSQLSERVER)”, and then click “Restart”.
- Close SQL Server Configuration Manager.
- Click “OK” and then “Apply”
After correctly configure the SQL Server protocols, again, especially the TCP/IP, you will be able to successfully connect and generate the SQL Server Schemas that you need for your BizTalk Server Solution.
