I'm not quite sure whether you have resolved the port-forwarding issue or
not. If you haven't, or aren't sure, this Web site might help ...
http://www.portforward.com/
--
Brendan Reynolds
Access MVP
Hi Sylvain and thank you for the detailed reply,
As you suspected, my router did not have Port Forwarding configured to
route
ODBC connections to the local machine 192.168.0.xx.
Unfortunately, I am still unable to connect.
1. There is no firewall on the W2K machine acting as the SQL server.
2. I tried making the SQL machine a "trusted" on the router. As I read
the
manual, that means that there should be no blocking of anything whatsoever
that comes to that machine.
3. In SQL 2005 Express, TCP/IP and the browsing service are both enabled
and
running (and I've rebooted).
The problem is establishing the ODBC connection on my remote machine that
runs XP home edition. As I understand it, in order for the Access app. to
work on this remote machine, you have to establish a "data source" by
going
into Start/Control Panel/Administrative Tools/Data Sources (ODBC). Once
there, you have to select one of the tabs in the box that pops up. I've
tried to establish the connection both as "User DSN" and a "System DSN."
With either tab, when you click "add" you get to a screen where you go to
the bottom of the list and highlight "SQL Server." When you then click
"finish," you get to a second box that asks for the name you are giving
the
data source [this can be any name] and the server you want to connect to.
In
this server box, I've put the IP address for the router. (BTW, I can ping
this address.)
When you click "next," you get to a screen that asks for the UserID and
password (after you click that you want to use SQL authentication).
After you've entered the UserId and password, you click "next." At this
point, I know from successful connections that you go to a further screen
where you specify options, and there is then a test button to see that the
connection works.
Again, as I understand it, you need this ODBC connection set up in Windows
because that is the only way you can use the link table manager in Access
to
link the SQL server tables to your app.
Anyway, when I click "next" after entering the userID and password, there
is
a long pause before a screen comes up telling me the connection has failed
and/or access denied.
Thank you in advance for any further insight. I absolutely know there is
simply some simple click I haven't made.
Bruce Maston
:
You must tell the Firewall/router to open the port 1433 and to redirect
it
to the Win2K machine. On many routers, this is called Port Forwarding
and
is often located under the Advanced options. Take a look around and you
should find it.
Normally, forwarding a port will open it through the firewall but not
necessarily. Take a look at your documentation.
To find the IP address of your computer inside the network, look at the
list
of attached devices in your router or open a CMD (or DOS) session on your
local machine and run the command IPCONFIG /all. It's also a good idea
to
give a fixed local address to your machine on your router; something that
you should find under the LAN IP setup of your router.
When you're done, all you have to do will be to give the fixed IP address
of
the network as the name of the machine; the router will do the rest when
it
will receive a connection on the port 1433 and redirect it to your Win2K
server. It's best to add ,1433 at the end to make sure that the OBDC or
OLDB provider will know that this is a TCP/IP address but it should work
without it. It's also a good idea to add Network Library=DBMSSOCN; (with
a
space between network and library) for OLEDB provider to make sure that
the
provider will understand that it's a tcp/ip connection; for exemple:
oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
http://support.microsoft.com/kb/q238949/
If the SQL-Server is not the default instance but a named instance, then
you
must add the name of the instance after the address of the server (but
before the port number). Finally, you can take a look at the following
articles to help you troubleshooting connection problem:
http://www.datamasker.com/SSE2005_NetworkCfg.htm
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
http://msdn2.microsoft.com/en-us/library/ms345318.aspx
Using a remote desktop/terminal service is another way of accessing your
work. With TS, you will have the same configuration problem with your
router (excerpt that the port number is different) but with the advantage
of
a better result in many situations (see below); however, it's not
supported
on W2K Pro (you must have a server to use TS, not a workstation). (You
can
have one remote session on WinXP Pro but the user must be there to accept
the incoming connection; so it's probably of no use in your situation;
however, I might be wrong on this.)
Probably that the speed of accessing your remote database over the
internet
will not blast you away. If the system is too slow, then you will have
to
use TS or to optimize your frontend/backend or to use another technologie
like .NET or ASP.NET. (TS is by far the easiest way but it will cost you
some $ because you must buy Win2003 Server.) However, many of my clients
are using ADP directly over the internet without to much problem of
speed.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
message
I want to create my own SQL Server 2005 Express backend server with
Access
as
the front end.
I have a three computer network behind a firewall router. Two machines
are
XP pro with the Access front end, and I added a third machine that is
W2K
pro
with the SQL Server back end. I've upsized my tables, and I've got
this
network working.
My next problem is to be able to connect to the SQL server inside my
network
from another computer at home with the same Access on it as the front
end,
and this is where I am stumped.
I perceive that I need to do "something" to the SSMS and/or to the
router
to
allow the connection, and I suspect that I need some sort of other
downloaded
MS piece of software or tool to enable this. Finally, I do not exactly
know
what the IP address is for a computer "inside" my network. (I have a
fixed
IP address for the network.) I've tried the IP address followed my the
computer's name in the network, the computers local address, etc. I've
used
colon separators, back slashes, commas, etc. All no go.
I see things like "remote desk top" and "terminal services," but I'm
not
sure which one I might need (if that one or some other).
I'm sure that there must be other threads addressing this problem, but
I
haven't been able to locate one.
Thank you.
