ADO connection to SQL server via Internet uses wrong port on some computers 
Author Message
 ADO connection to SQL server via Internet uses wrong port on some computers

The following code

    Dim WmConn as ADODB.Connection
    Set WMConn = New ADODB.Connection
    With WMConn
        .Provider = "SQLOLEDB"
        .ConnectionString = "User ID=MYUSER;Password=PWD;Data
Source=xxx.xxx.xxx.xxx;Initial Catalog=MyDB"
        .Open
    End With

Connects to a SQL database across the Internet without problems on most
machines.
We've run into a problem on some machines, the connection can not be
established. We traced the problem to the fact that on some client machines
the connection attempts to connect via a port other than 1433 (on one it
tried to go in to the server using port 137). On other client machines it
defaults to port 1433 OK and the connection can be established.

On one problem machine we decided to test the connection to our SQL server
by creating an ODBC connection to our server specifying TCPIP instead of
named pipes and specifying port 1433 explicitly in the ODBC setup for that
connection. When we tested that connection it worked fine on that client.
Then we ran our code again that had failed previously on that machine and
now it always succeeded although we specified the use of the OLEDB provider
as above, not an ODBC connection. In theory at least there should not be any
interrelation between ODBC and using SQLOLEDB and ADO.

We tried using the syntax
ConnectionString = "User ID=MYUSER;Password=PWD;Data
Source=xxx.xxx.xxx.xxx:1433;Initial Catalog=MyDB"
or
ConnectionString = "User ID=MYUSER;Password=PWD;Data Source=xxx.xxx.xxx.xxx
1433;Initial Catalog=MyDB"
to force the provider to use port 1433 to connect to the SQL server. Both
these syntaxes failed to establish the connection.

Question is: how can we make sure that the client using our code above will
always connect via port 1433 without having to create what amounts to a
dummy ODBC connection on the client machine?

--
Robert Dufour, MCP, MCT
President SGI IMS Inc.
www.sgiims.com



Sun, 11 May 2003 13:14:15 GMT  
 ADO connection to SQL server via Internet uses wrong port on some computers
Hello Robert,

Thanks for reporting this. I will do some research first and will get back
to you as soon as I have further information.

Regards,
Benjamin Liu
Microsoft Developer Support



Mon, 12 May 2003 14:18:16 GMT  
 ADO connection to SQL server via Internet uses wrong port on some computers
Hi Robert,

If you inspect your registry after you configure ODBC on the problematic
client machine, you will find a string value added under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client. This value is
server specific and is referenced no matter ODBC or OLEDB is used to access
that server. That's why you worked arounded the problem by configuring an
ODBC data source while using OLEDB provider. However, it is not necessary.
Please append your ConnectionString with:

"network=dbmssocn;address=xxx.xxx.xxx.xxx,1433"

It should achieve what you wanted. If you have any questions or further
concerns, feel free to let me know.

Regards,
Benjamin Liu
Microsoft Developer Support



Mon, 12 May 2003 03:00:00 GMT  
 ADO connection to SQL server via Internet uses wrong port on some computers
Hi

The reason that the SQL server is connected on port 137 may be that is that
the port number 137 is for the netbios service.  This is what windows uses
to communicate / authenticate.  If you are using NT authentication for the
SQL server connection then it may use port 137 first to authenticate then go
for 1433 for the data communication.  If you have a firewall / proxy-server
between your server and ANY client machine then port 137 will almost
certainly be blocked.

If you are using the SQL server authentication (instead of NT
authentication) then it shouldnt happen. The problem with this is that I
can't find any way of dissabling the SA account - which makes it easy to
hack as people can try to guess the password for SA 1000's of times.

Good Luck
Marc Ferbrache


Quote:
> The following code

>     Dim WmConn as ADODB.Connection
>     Set WMConn = New ADODB.Connection
>     With WMConn
>         .Provider = "SQLOLEDB"
>         .ConnectionString = "User ID=MYUSER;Password=PWD;Data
> Source=xxx.xxx.xxx.xxx;Initial Catalog=MyDB"
>         .Open
>     End With

> Connects to a SQL database across the Internet without problems on most
> machines.
> We've run into a problem on some machines, the connection can not be
> established. We traced the problem to the fact that on some client
machines
> the connection attempts to connect via a port other than 1433 (on one it
> tried to go in to the server using port 137). On other client machines it
> defaults to port 1433 OK and the connection can be established.

> On one problem machine we decided to test the connection to our SQL server
> by creating an ODBC connection to our server specifying TCPIP instead of
> named pipes and specifying port 1433 explicitly in the ODBC setup for that
> connection. When we tested that connection it worked fine on that client.
> Then we ran our code again that had failed previously on that machine and
> now it always succeeded although we specified the use of the OLEDB
provider
> as above, not an ODBC connection. In theory at least there should not be
any
> interrelation between ODBC and using SQLOLEDB and ADO.

> We tried using the syntax
> ConnectionString = "User ID=MYUSER;Password=PWD;Data
> Source=xxx.xxx.xxx.xxx:1433;Initial Catalog=MyDB"
> or
> ConnectionString = "User ID=MYUSER;Password=PWD;Data

Source=xxx.xxx.xxx.xxx

- Show quoted text -

Quote:
> 1433;Initial Catalog=MyDB"
> to force the provider to use port 1433 to connect to the SQL server. Both
> these syntaxes failed to establish the connection.

> Question is: how can we make sure that the client using our code above
will
> always connect via port 1433 without having to create what amounts to a
> dummy ODBC connection on the client machine?

> --
> Robert Dufour, MCP, MCT
> President SGI IMS Inc.
> www.sgiims.com



Thu, 15 May 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. ADO Connection Pooling & SQL Server : wrong behavior

2. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

3. Connecting to remote SQL server via Internet using ASP page and Comm object

4. Connecting to SQL Server via VBScript from Outlook using ADO

5. Internet Connection through Ado (Internet Server Error)

6. Internet Connection through Ado (Internet Server Error)

7. How can I get Role for Current User from SQL Server using VB6 ADO connection

8. Using ADO To Connect To ACCESS/SQL Server Across Internet

9. vb connection to sql via internet

10. Wide Area connection to SQL server 2008 Express via broadband

11. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

12. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

 

 
Powered by phpBB® Forum Software