General Network Error: SQL Connection

[bobbarker]

Question from a Newby...
Have installed on a customer's server MSDE. They connect to the server using terminal servers (which I have no experience off).
I have a bit of code that I use to text the connection from the terminal to the SQL server:

    Dim cnnConn As ADODB.Connection
    Dim strConn As String
    
    On Error GoTo ConnectSQL_Err
    'Create Connection

    strConn = "User ID=sa;Password=password;data source=servername;Initial Catalog=databasename"
    Set cnnConn = New ADODB.Connection
    
    With cnnConn
        .Provider = "SQLOLEDB.1"
        .ConnectionString = strConn
        .ConnectionTimeout = 20
        .CommandTimeout = 240
        .Open
        If .State <> 1 Then
            MsgBox "Not Connected. State " & Str(.State)
        Else
            MsgBox "Connected OK"
        End If
    
    End With 'cnnconn

When running this directly on the SQL server it connects OK.

When running on the terminal server I first got "Specified SQL Server Not Found" error.

I have got past this bychanging the default network library from named pipes to TCP/IP.

When running from the terminal server I now get "-2147467259 [DBMSSOCN] General Network Error. Check Your network documentation"

Any suggestions on how to progress past this error?

I am able to ping the SQL server from the terminal servre, and the SQL server is running.

It's my birthday today and this is kind of spoiling it. Any guidance would be greatly appreciated.

 

[SQLBill]

First, security issue...it's VERY bad practice to use the SA login. That gives the user access to EVERYTHING.

Second, security issue...you need to give SA a strong password.

Third, is the SQL Server set for Mixed Mode Authentication or Windows Only Authentication? If it's set for Windows Only, the SA account won't work.

Lastly, I believe you are trying to make the connection to the database. You need to make it to the server first.

-SQLBill

 

[bobbarker]

Thanks for your response SQLBill.
1. I am only using the sa login in this test programme I use to test connection.
2. The 'password' I gave is not the actual password, I am using a strong password.
3. It is set for Mixed Mode Authentication.
4. Are you able to provide any further guidance re connecting to server first. I have looked back at the notes I have (again apols as I am a newby) and this is the command I have to connect to sql server and initial catalog (database).

As mentioned in my first thread, if I run my code on the actual sql server it connects OK but does not from a terminal server.
The error message "General Network Error. Check Your network documentation" appears a little ambiguous. It may be an actual network error, though I am able to ping the sql server from the terminal server.
Are there any considerations I need to give with regards to connecting to a sql server from a terminal server?
Thanks again.

 

[SQLBill]

I'm not familiar with ADO connections. But I don't see the server name anywhere in your script. So I believe the connection is trying to connect to 'database' on the local computer (where the script is being run). Since 'database' doesn't exist on the local computer, it's failing.

If I can find the syntax for including the server name, I will follow-up this post.

-SQLBill

 

[SQLBill]

OOOPPPSSS, I just now saw the servername in your script. Try making the words 'data source' just one word 'datasource'.

Ref: BOL, use Index tab, enter "ADO, connection object" then select the option for 'Using connection string' at the location 'Analysis Service' then scroll down to the bottom for the example.

-SQLBill

 

[bobbarker]

Thanks again SQLBill.
I checked BOL and you are right it says the syntax is 'datasource'. However when I try this I get an error. As previously mentioned my code does connect OK when run directy on the SQL server. I have also proven it to work across a local network.

Having gathered information elsewhere, I now wonder whether it is either a client or server configuration issue that is causing the 'General Network Error'. I am not to sure the way to progress and am unsure as what to configure and how. Any guidance would be appreciated.

It seems the next step is to check my ADODB connection string and/or to check server and client network library configurations.

My ADODB connection string is:
"User ID=sa;Password=Astrongpassword;data source=servername\MSDEservername;Initial Catalog=databasename;Network Library=DBMSSOCN"

If I run SQL Server Network Utility (svrnetcn.exe) on the MSDE server it tells me:
My enabled protocols are named pipes and tcp/ip. My default port for tcp/ip is 2047 (I have read somewhere it should be 1433?)

If I run SQL Server Client Network Utility (cliconfg.exe) on the terminal (client) server it tells me:
My default network utility is named pipes.
Should I add a tcp/ipconnection here? If so what should be my server alias (servername? MSDEservername? server IP address?) ? What should be the Computer name? What should be the port number?

 

[bobbarker]

Problem solved.
For anyone who may be interested
I added a tcp/ip connection as follows:
1. Start the Client Network Utility.
2. In the General tab of the Server Alias Configuration dialog box, click Add, and then click TCP/IP.
3. Enter an alias name in the Server Alias text box. The alias can contain any name.
4. Enter the IP address in the Computer Name text box. Do not add an instance name.
In the Port Number text box, entered the port number (in my case 2047). Click OK twice.

I think the problem was further caused by the client end having v2.5 of MDAC registered. v2.6 or later is required to connect to a named instance using the \\computer_name\instance_name format.