[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error - connecting to SQL database in VB script

Question

I have a VB script which connects to a local SQL database to retrieve a value. The exact same script runs on about 100 servers, but a few of the servers produce this error:

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation

Here is the code that runs:

Function GetPrimaryServerID
On Error Resume Next
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=SQLOLEDB;Data Source=127.0.0.1;Initial Catalog=xxx;User ID=xxx;Password=xxx"
sqlquery = "SELECT ServerID FROM tblSettings"
objRecordSet.Open sqlquery,objConnection
objRecordSet.MoveFirst
GetPrimaryServerID = objRecordSet("ServerID")
objRecordSet.Close
objConnection.Close 
End Function

The error occurs on the 5th line when trying to open the connection string. I'm confused as to why this script is working on nearly all servers and failing on only a handful. The database that they connect to is identical on every server in terms of structure, its only the data that changes.

Answer 1

It's not a database error, but a client tools or config error

The failing servers will probably:

• have a different level of either SQL Server install /includes service pack)
• are configured for Windows Authentication only
• have an older MDAC (linked to SQL Service pack, OS Service pack etc)

Edit:

SQL Server SSL Encryption, server side, is described here. And in KB 316898 too

• "Server side" requires only a server certificate and all connections are encrypted
• "client side" requires client certs and is optional, and only for that client

Certain client libraries (notably MS JDBC) do not support this.

If I've guessed right, you'll have either client or server SSL encryption set based on the server (your script is acting as a client)

• a blog entry too

Answer 2

It could fail if the database is not configured to listen to TCP/IP traffic. The error "Named Pipes Provider, error: 0 - No process is on the other end of the pipe." points in that direction.

You can test that with:

telnet 127.0.0.1 1433

To configure where the server listens, use `SQL Server Network Utility" for SQL Server 2000, or "SQL Server Surface Area Configuration" for SQL Server 2005 and up.

Answer 3

Fixed the problem by doing the following: Opened SQL Server Configuration Manager and went to Protocols for MSSQLSERVER -> TCP/IP. In the 'IP Addresses' tab, I noticed that IP2 which has address of 127.0.0.1 was active but not enabled. Changed to enabled and restarted SQL services. My VB script now successfully opens the connection to 127.0.0.1.