Hi!

I'm using consume adapter service> sqlbinding >  to connect to sql database in order to generate sql schemas.

I'm getting below error:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

The problem is that "consume adapter service" wizard not able to connect to the SQL server instance name provided. Check the instance name given and check the connectivity between your development server and the SQL server.  Also few things you can check which are nicely listed with steps here..http://stackoverflow.com/questions/18060667/cannot-connect-to-server-a-network-related-or-instance-specific-error. Also another suggestion (along with the ones already given), try to give the IP rather than the name, sometime name resolution could also create issues with the connectivity.

Anyway the basic issue is connectivity to the mentioned SQL server instance. Check it.

The cause of the error message is exactly what is says: The address to the SQL Server instance that you have specified could either not be found at all or could not be accessed.

You should make sure that the address specified in your connection string you set it is correct and that the SQL Server is up an running. You should also make sure that your database engine is configured to accept remote connections.

If you're on the same machine, can you access the SQL Management Studio and see if the database and associated tables are visible for the current logged in user. Fix it if not visible and then try Schema Generation.

Rachit

I found this good article.. Refer the troubleshooting steps mentioned here.

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Rachit

It's a clearly SQL connectivity issue where BizTalk is just a victim. 

Try to perform the below troubleshooting

1) Create a blank notepad file on the desktop of BizTalk Server with Name as "New Text Document.txt" later rename it to - New Text Document.udl. Open the file and give the SQL Server details like server name etc and try to perform Test Connection.

Check if it was successful, if not you need to figure out the issue with SQL connectivity. This will prove that BizTalk is just a victim.

2) Check the DTC settings on both the systems

Check the below link- 

TCP settings that can impact BizTalk Server

Is this a new environment? or was working fine earlier but stopped working suddenly?

Was there any change performed like installing security updates etc.

This is a new environment.

I'm using BT 2010, SQL Server 2008 R2 and Windows 7 professional.

I tried using add adapter metadata to generate the schema using SQL and I got this error just to verify that it is a DTC issue.

Hi Christiane,

This suggests that you may be hitting DTC issues. Is the database you are calling on a different server to your BizTalk Server? If so, you may need to configure the DTC settings.

The machine which you are trying to connect as SQL Server make sure its DTC is enable and configured. to enable and configure DTC Type Dcomcnfg in run then select component services > Computers > My Computer , Right Click properties MSDTC Tab Configure and start DTC.

Security Configuration

Also refer: Sql Adapter MSDTC Problem

Rachit

Hi, 

As suggested earlier this seems to be a DTC related issue. It can only occur if the DTC settings are not proper or if firewalls don't allow the default ports to communicate.

Check DTC settings on both SQL and BizTalk Box and also check if MSDTC service is up and running.

1. Go to Start Run (type in) Dcomcnfg. You should be inside Component Services MMC.
2. Expand Component Services Computers My Computer (right click) Properties MSDTC tab Security Configuration button.
3. Select the checkboxes as following : 

Check the below article from Microsoft Support Team, they have listed all the errors which may come during setting up a new system. They also have your MSDTC error listed.

Resolving the issues you may face during BizTalk Runtime Configuration