SQL Server BE behind firewall

[Guest]

I have SQL Server 2000 (SP3) and MDAC 2.6 (SP2) running on a machine that is
part of a small wireless network with a router and a cable modem. I have set
up the router to listen on port 1433 and redirect all such traffic to the
internal IP address (10.0.1.5) of the machine with SQL Server on it.

Based on what I have read, especially in article 269882 - How to Use ADO to
Connect to a SQL Server That Is Behind a Firewall, I am using this code in
Access to try to connect:
Dim remoteConn As Object
Dim remoteCmd As Object
Dim remoteRs As Object
Set remoteConn = CreateObject("ADODB.Connection")
Set remoteRs = CreateObject("ADODB.Recordset")
Set remoteCmd = CreateObject("ADODB.Command")
remoteConn.Open "Provider=SQLOLEDB;User ID=sa;Password=sapw;Persist
Security Info=True;Data Source=" & strServerName & ";Network
Library=dbmssocn;Trusted_Connection=Yes;"
remoteCmd.ActiveConnection = remoteConn

strServerName is the IP Address I get when I go to "What is my real IP" on
the SQL Server machine, and is also, I believe, the dynamic IP address
assigned to me by my cable modem ISP. Even though it is dynamic, it stays
static for some period of time - sometimes many days. In any case, I tell my
user what my IP address is today and they key it in.

When the above code is executed, the user gets the following message back:
Error # -2147467259 was generated by microsoft OLE DB Provider for SQL Server
Login failed for user '(null)' Reason: Not associated with a trusted SQL
Server connection.

Is the user actually hitting the SQL Server machine? Is this error
generated by the code components on his machine or by the SQL Server? How do
I get by the error?

 

[david epsom dot com dot au]

Is the user actually hitting the SQL Server machine?
Yes

Is this error generated by the code components on his machine Yes, and
or by the SQL Server?

Yes, the error message is from his local machine, reporting a
response from SQL Server

How do I get by the error?

Trusted_Connection=No

You may need further configuration to get SQL Server
to accept connections from an untrusted connection

Or use a trusted connection, ie VPN.

(david)

 

[Sylvain Lafontaine]

You may want to check www.no-ip.com for your dynamic IP address problem.

 

[Guest]

Your answer helped, I think. My colleague no longer gets the previous error,
and the code proceeds, but when I look at the SQL Server logs, I see no
evidence of his having connected. (What should I be seeing?)

Nevertheless, now for the next part of the problem:

Assuming I am connected, I use the following code to open a database:

strConnect = "Provider=SQLOLEDB;Driver={SQL
server};SERVER={myServerName};DATABASE={myDB};UID=sa;PWD=saPW;Trusted_Connection=No;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)

This produces the following error:
Connection falied:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

I am sure I am using the correct sa password, and I reinstalled SQL Server
and SP3 and MDAC and SP 2 and made sure to select Mixed Mode.

What am I doing wrong now?

 

[Sylvain Lafontaine]

You have mixed ODBC with OLEDB parameters in your connection string. See
one of the following references for the proper syntax of connection strings:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Your answer helped, I think. My colleague no longer gets the previous
error,
and the code proceeds, but when I look at the SQL Server logs, I see no
evidence of his having connected. (What should I be seeing?)

Nevertheless, now for the next part of the problem:

Assuming I am connected, I use the following code to open a database:

strConnect = "Provider=SQLOLEDB;Driver={SQL
server};SERVER={myServerName};DATABASE={myDB};UID=sa;PWD=saPW;Trusted_Connection=No;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)

This produces the following error:
Connection falied:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

I am sure I am using the correct sa password, and I reinstalled SQL Server
and SP3 and MDAC and SP 2 and made sure to select Mixed Mode.

What am I doing wrong now?

Yes, the error message is from his local machine, reporting a
response from SQL Server


Trusted_Connection=No

You may need further configuration to get SQL Server
to accept connections from an untrusted connection

Or use a trusted connection, ie VPN.

(david)

 

[Guest]

I have chosen to go with ODBC and am using the following code:
Dim strConnect As String
Dim dbsodbc As Database
strConnect = "Driver={SQL Server};" _
& "Server=xxx.xxx.xxx.xxx;" _
& "Address=xxx.xxx.xxx.xxx,1433;" _
& "Network=dbmssocn;" _
& "Database=myDbName;" _
& "UID=sa;" _
& "PWD=sapwd;" _
& "Trusted_Connection=No"
Set dbsodbc = OpenDatabase("", False, False, strConnect)

This elicits the following error:
Connectino Failed:
SQLState: '01000'
SQL Server Error: 10054
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv())
Connect failed:
SQLState: '08501'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation

Now what?

You have mixed ODBC with OLEDB parameters in your connection string. See
one of the following references for the proper syntax of connection strings:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Your answer helped, I think. My colleague no longer gets the previous
error,
and the code proceeds, but when I look at the SQL Server logs, I see no
evidence of his having connected. (What should I be seeing?)

Nevertheless, now for the next part of the problem:

Assuming I am connected, I use the following code to open a database:

strConnect = "Provider=SQLOLEDB;Driver={SQL
server};SERVER={myServerName};DATABASE={myDB};UID=sa;PWD=saPW;Trusted_Connection=No;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)

This produces the following error:
Connection falied:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

I am sure I am using the correct sa password, and I reinstalled SQL Server
and SP3 and MDAC and SP 2 and made sure to select Mixed Mode.

What am I doing wrong now?

Is the user actually hitting the SQL Server machine?
Yes

Is this error generated by the code components on his machine
Yes, and
or by the SQL Server?
Yes, the error message is from his local machine, reporting a
response from SQL Server

How do I get by the error?

Trusted_Connection=No

You may need further configuration to get SQL Server
to accept connections from an untrusted connection

Or use a trusted connection, ie VPN.

(david)



message I have SQL Server 2000 (SP3) and MDAC 2.6 (SP2) running on a machine
that
is
part of a small wireless network with a router and a cable modem. I
have
set
up the router to listen on port 1433 and redirect all such traffic to
the
internal IP address (10.0.1.5) of the machine with SQL Server on it.

Based on what I have read, especially in article 269882 - How to Use
ADO
to
Connect to a SQL Server That Is Behind a Firewall, I am using this code
in
Access to try to connect:
Dim remoteConn As Object
Dim remoteCmd As Object
Dim remoteRs As Object
Set remoteConn = CreateObject("ADODB.Connection")
Set remoteRs = CreateObject("ADODB.Recordset")
Set remoteCmd = CreateObject("ADODB.Command")
remoteConn.Open "Provider=SQLOLEDB;User ID=sa;Password=sapw;Persist
Security Info=True;Data Source=" & strServerName & ";Network
Library=dbmssocn;Trusted_Connection=Yes;"
remoteCmd.ActiveConnection = remoteConn

strServerName is the IP Address I get when I go to "What is my real IP"
on
the SQL Server machine, and is also, I believe, the dynamic IP address
assigned to me by my cable modem ISP. Even though it is dynamic, it
stays
static for some period of time - sometimes many days. In any case, I
tell
my
user what my IP address is today and they key it in.

When the above code is executed, the user gets the following message
back:
Error # -2147467259 was generated by microsoft OLE DB Provider for SQL
Server
Login failed for user '(null)' Reason: Not associated with a trusted
SQL
Server connection.

Is the user actually hitting the SQL Server machine? Is this error
generated by the code components on his machine or by the SQL Server?
How
do
I get by the error?

 

[Sylvain Lafontaine]

Won't work with a direct ODBC connection. ADO is not able to open an ODBC
connection directly and you will have to use the Microsoft OLEDB Provider
for ODBC
(http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForODBCDatabases
) - which is an hybride between OLEDB and ODBC - for opening an ODBC
connection under ADO.

Even if you can make it to work with ODBC, the best answer in your case is
simply to use the native OLEDB provider for SQL-Server instead. ODBC is old
stuff and shouldn't be used anymore excerpt when necessary (for example with
linked tables in a MDB file).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have chosen to go with ODBC and am using the following code:
Dim strConnect As String
Dim dbsodbc As Database
strConnect = "Driver={SQL Server};" _
& "Server=xxx.xxx.xxx.xxx;" _
& "Address=xxx.xxx.xxx.xxx,1433;" _
& "Network=dbmssocn;" _
& "Database=myDbName;" _
& "UID=sa;" _
& "PWD=sapwd;" _
& "Trusted_Connection=No"
Set dbsodbc = OpenDatabase("", False, False, strConnect)

This elicits the following error:
Connectino Failed:
SQLState: '01000'
SQL Server Error: 10054
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv())
Connect failed:
SQLState: '08501'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation

Now what?

You have mixed ODBC with OLEDB parameters in your connection string. See
one of the following references for the proper syntax of connection
strings:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Your answer helped, I think. My colleague no longer gets the previous
error,
and the code proceeds, but when I look at the SQL Server logs, I see no
evidence of his having connected. (What should I be seeing?)

Nevertheless, now for the next part of the problem:

Assuming I am connected, I use the following code to open a database:

strConnect = "Provider=SQLOLEDB;Driver={SQL
server};SERVER={myServerName};DATABASE={myDB};UID=sa;PWD=saPW;Trusted_Connection=No;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)

This produces the following error:
Connection falied:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'sa'

I am sure I am using the correct sa password, and I reinstalled SQL
Server
and SP3 and MDAC and SP 2 and made sure to select Mixed Mode.

What am I doing wrong now?



:

Is the user actually hitting the SQL Server machine?
Yes

Is this error generated by the code components on his machine
Yes, and
or by the SQL Server?
Yes, the error message is from his local machine, reporting a
response from SQL Server

How do I get by the error?

Trusted_Connection=No

You may need further configuration to get SQL Server
to accept connections from an untrusted connection

Or use a trusted connection, ie VPN.

(david)



message I have SQL Server 2000 (SP3) and MDAC 2.6 (SP2) running on a machine
that
is
part of a small wireless network with a router and a cable modem. I
have
set
up the router to listen on port 1433 and redirect all such traffic
to
the
internal IP address (10.0.1.5) of the machine with SQL Server on it.

Based on what I have read, especially in article 269882 - How to Use
ADO
to
Connect to a SQL Server That Is Behind a Firewall, I am using this
code
in
Access to try to connect:
Dim remoteConn As Object
Dim remoteCmd As Object
Dim remoteRs As Object
Set remoteConn = CreateObject("ADODB.Connection")
Set remoteRs = CreateObject("ADODB.Recordset")
Set remoteCmd = CreateObject("ADODB.Command")
remoteConn.Open "Provider=SQLOLEDB;User
ID=sa;Password=sapw;Persist
Security Info=True;Data Source=" & strServerName & ";Network
Library=dbmssocn;Trusted_Connection=Yes;"
remoteCmd.ActiveConnection = remoteConn

strServerName is the IP Address I get when I go to "What is my real
IP"
on
the SQL Server machine, and is also, I believe, the dynamic IP
address
assigned to me by my cable modem ISP. Even though it is dynamic, it
stays
static for some period of time - sometimes many days. In any case,
I
tell
my
user what my IP address is today and they key it in.

When the above code is executed, the user gets the following message
back:
Error # -2147467259 was generated by microsoft OLE DB Provider for
SQL
Server
Login failed for user '(null)' Reason: Not associated with a trusted
SQL
Server connection.

Is the user actually hitting the SQL Server machine? Is this error
generated by the code components on his machine or by the SQL
Server?
How
do
I get by the error?

 

[Sylvain Lafontaine]

It might also be a good idea to know in what context you are trying to
connect to the SQL-Server: are you using a MDB file with linked tables and
SQL passthrough queries, a MDB file with VBA code and ADO objects or an ADP
project?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Won't work with a direct ODBC connection. ADO is not able to open an ODBC
connection directly and you will have to use the Microsoft OLEDB Provider
for ODBC
(http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForODBCDatabases
) - which is an hybride between OLEDB and ODBC - for opening an ODBC
connection under ADO.

Even if you can make it to work with ODBC, the best answer in your case is
simply to use the native OLEDB provider for SQL-Server instead. ODBC is
old stuff and shouldn't be used anymore excerpt when necessary (for
example with linked tables in a MDB file).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have chosen to go with ODBC and am using the following code:
Dim strConnect As String
Dim dbsodbc As Database
strConnect = "Driver={SQL Server};" _
& "Server=xxx.xxx.xxx.xxx;" _
& "Address=xxx.xxx.xxx.xxx,1433;" _
& "Network=dbmssocn;" _
& "Database=myDbName;" _
& "UID=sa;" _
& "PWD=sapwd;" _
& "Trusted_Connection=No"
Set dbsodbc = OpenDatabase("", False, False, strConnect)

This elicits the following error:
Connectino Failed:
SQLState: '01000'
SQL Server Error: 10054
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv())
Connect failed:
SQLState: '08501'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation

Now what?

You have mixed ODBC with OLEDB parameters in your connection string.
See
one of the following references for the proper syntax of connection
strings:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message Your answer helped, I think. My colleague no longer gets the previous
error,
and the code proceeds, but when I look at the SQL Server logs, I see
no
evidence of his having connected. (What should I be seeing?)

Nevertheless, now for the next part of the problem:

Assuming I am connected, I use the following code to open a database:

strConnect = "Provider=SQLOLEDB;Driver={SQL
server};SERVER={myServerName};DATABASE={myDB};UID=sa;PWD=saPW;Trusted_Connection=No;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)

This produces the following error:
Connection falied:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'sa'

I am sure I am using the correct sa password, and I reinstalled SQL
Server
and SP3 and MDAC and SP 2 and made sure to select Mixed Mode.

What am I doing wrong now?



:

Is the user actually hitting the SQL Server machine?
Yes

Is this error generated by the code components on his machine
Yes, and
or by the SQL Server?
Yes, the error message is from his local machine, reporting a
response from SQL Server

How do I get by the error?

Trusted_Connection=No

You may need further configuration to get SQL Server
to accept connections from an untrusted connection

Or use a trusted connection, ie VPN.

(david)



message I have SQL Server 2000 (SP3) and MDAC 2.6 (SP2) running on a machine
that
is
part of a small wireless network with a router and a cable modem.
I
have
set
up the router to listen on port 1433 and redirect all such traffic
to
the
internal IP address (10.0.1.5) of the machine with SQL Server on
it.

Based on what I have read, especially in article 269882 - How to
Use
ADO
to
Connect to a SQL Server That Is Behind a Firewall, I am using this
code
in
Access to try to connect:
Dim remoteConn As Object
Dim remoteCmd As Object
Dim remoteRs As Object
Set remoteConn = CreateObject("ADODB.Connection")
Set remoteRs = CreateObject("ADODB.Recordset")
Set remoteCmd = CreateObject("ADODB.Command")
remoteConn.Open "Provider=SQLOLEDB;User
ID=sa;Password=sapw;Persist
Security Info=True;Data Source=" & strServerName & ";Network
Library=dbmssocn;Trusted_Connection=Yes;"
remoteCmd.ActiveConnection = remoteConn

strServerName is the IP Address I get when I go to "What is my real
IP"
on
the SQL Server machine, and is also, I believe, the dynamic IP
address
assigned to me by my cable modem ISP. Even though it is dynamic,
it
stays
static for some period of time - sometimes many days. In any case,
I
tell
my
user what my IP address is today and they key it in.

When the above code is executed, the user gets the following
message
back:
Error # -2147467259 was generated by microsoft OLE DB Provider for
SQL
Server
Login failed for user '(null)' Reason: Not associated with a
trusted
SQL
Server connection.

Is the user actually hitting the SQL Server machine? Is this error
generated by the code components on his machine or by the SQL
Server?
How
do
I get by the error?

 

[david epsom dot com dot au]

Dim dbsodbc As Database

If you are trying to create a DAO database object,
write that as
Dim dbsodbc As dao.Database

And just to be clear, you may also write
set dbsodbc = Application.Dbengine(0).OpenDatabase(

or, to be more concise but slightly less clear
set dbsodbc - dao.OpenDatabase(

Note that instead of using dbengine.workspace(0),
(a jet workspace) you could create an ODBCdirect
workspace, which gives something like a "pass-through"
database connection, but for most people ADO is
a better option.

Note that dbmssocn is the TCP/IP library in both
ODBC and OLEDB, but the syntax would be slightly
different in an OLEDB/ADO connect string.

Now what?

Create an ODBC dsn and test that you can connect
from the ODBC Datasource Administrator.

(david)

I have chosen to go with ODBC and am using the following code:
Dim strConnect As String
Dim dbsodbc As Database
strConnect = "Driver={SQL Server};" _
& "Server=xxx.xxx.xxx.xxx;" _
& "Address=xxx.xxx.xxx.xxx,1433;" _
& "Network=dbmssocn;" _
& "Database=myDbName;" _
& "UID=sa;" _
& "PWD=sapwd;" _
& "Trusted_Connection=No"
Set dbsodbc = OpenDatabase("", False, False, strConnect)

This elicits the following error:
Connectino Failed:
SQLState: '01000'
SQL Server Error: 10054
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv())
Connect failed:
SQLState: '08501'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation

Now what?

You have mixed ODBC with OLEDB parameters in your connection string. See
one of the following references for the proper syntax of connection
strings:

http://www.connectionstrings.com/

http://www.carlprothman.net/Default.aspx?tabid=81

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Your answer helped, I think. My colleague no longer gets the previous
error,
and the code proceeds, but when I look at the SQL Server logs, I see no
evidence of his having connected. (What should I be seeing?)

Nevertheless, now for the next part of the problem:

Assuming I am connected, I use the following code to open a database:

strConnect = "Provider=SQLOLEDB;Driver={SQL
server};SERVER={myServerName};DATABASE={myDB};UID=sa;PWD=saPW;Trusted_Connection=No;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)

This produces the following error:
Connection falied:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'sa'

I am sure I am using the correct sa password, and I reinstalled SQL
Server
and SP3 and MDAC and SP 2 and made sure to select Mixed Mode.

What am I doing wrong now?



:

Is the user actually hitting the SQL Server machine?
Yes

Is this error generated by the code components on his machine
Yes, and
or by the SQL Server?
Yes, the error message is from his local machine, reporting a
response from SQL Server

How do I get by the error?

Trusted_Connection=No

You may need further configuration to get SQL Server
to accept connections from an untrusted connection

Or use a trusted connection, ie VPN.

(david)



message I have SQL Server 2000 (SP3) and MDAC 2.6 (SP2) running on a machine
that
is
part of a small wireless network with a router and a cable modem. I
have
set
up the router to listen on port 1433 and redirect all such traffic
to
the
internal IP address (10.0.1.5) of the machine with SQL Server on it.

Based on what I have read, especially in article 269882 - How to Use
ADO
to
Connect to a SQL Server That Is Behind a Firewall, I am using this
code
in
Access to try to connect:
Dim remoteConn As Object
Dim remoteCmd As Object
Dim remoteRs As Object
Set remoteConn = CreateObject("ADODB.Connection")
Set remoteRs = CreateObject("ADODB.Recordset")
Set remoteCmd = CreateObject("ADODB.Command")
remoteConn.Open "Provider=SQLOLEDB;User
ID=sa;Password=sapw;Persist
Security Info=True;Data Source=" & strServerName & ";Network
Library=dbmssocn;Trusted_Connection=Yes;"
remoteCmd.ActiveConnection = remoteConn

strServerName is the IP Address I get when I go to "What is my real
IP"
on
the SQL Server machine, and is also, I believe, the dynamic IP
address
assigned to me by my cable modem ISP. Even though it is dynamic, it
stays
static for some period of time - sometimes many days. In any case,
I
tell
my
user what my IP address is today and they key it in.

When the above code is executed, the user gets the following message
back:
Error # -2147467259 was generated by microsoft OLE DB Provider for
SQL
Server
Login failed for user '(null)' Reason: Not associated with a trusted
SQL
Server connection.

Is the user actually hitting the SQL Server machine? Is this error
generated by the code components on his machine or by the SQL
Server?
How
do
I get by the error?