Connection String times out with network name, yet works with IP

Question

Machine 1

• Windows Server 2008
• SQL Server 2008

The database. Contains all the information our sites use.

---

Machine 2

• Windows Server 2012
• IIS 8

The webserver. Uses IIS to host two sites:

• Production site: (default) Has the most up-to-date UI and features
• Backup site: Older UI, but still using the latest data from Machine 1

---

Here's how it works:

1. User goes to one of the sites hosted on Machine 2 and enters their company information
2. Machine 1 is queried for that company's connection string.
3. The site uses the connection string to connect to the correct database on Machine 1.

The problem is that about 1/3 of the connection strings use the network name (e.g. "Data Source='Machine1';") while the other 2/3 use the IP address (e.g. "Data Source=192.168.1.200;"). When connecting via the Production site, a timeout occurs if uses a connection string with a network name. However if the same user, using the same credentials, logs in to the Backup site, everything works fine regardless of which 'Data Source' is used.

I created a simple Powershell script to test the connection from Machine 2; network names and ip addresses both work, which makes me suspect it is an IIS or web.config issue. I've gone through both extensively, and these are the only differences I've noted:

• Different Application Pools in IIS: However when I ran "Get-CimInstance Win32_Process" it showed both instances of w3wp.exe had been started with the same command and arguments (with the exception of different pipes)
• Slightly different web.config. The Backup site has an entirely self-contained web.config, while the Production on stores its connection strings is a separate file.

Been banging my head against this for several days. Very limited in the steps I can take considering this a production website and Database. Any advice is appreciated.

Answer 1

Try putting the network-library in the connection string to force tcp.

see connectionstrings.com/define-sql-server-network-protocol

;Network Library=DBMSSOCN;

PS

Yep. Been there, done that. 4 days of "on site" client visit.......and it was the protocol.. Thus how I learned to force it via the connection string. You can also try this:

Create a (temporary) System DSN (ODBC in Control Panel) with a weird name like "peanutbutter". There is a client connection button in there somewhere. Force it to tcp. Then search your registry for peanut butter and find out how the network library gets stored.

A picture is worth a thousand words. See left side of image below. (a random image from the old interweb)