Access project doesn''t connect to SS2005 over VPN?! (again)
-
Hi folks,
This is my first post and I hope it's not going to freak you out.
Here is the problem. I am trying to connect from my home PC (Windows XP, Access 2002)
through an Access project to my SQL Server 2005 database which is stationed on a
Windows 2003 server. I have the right permissions and the SQL Server is running in
mixed mode.
Now, I want to utilize a DIRECT VPN connection from my home PC to the server but
I am forced so far to first connect via VPN to another PC (using 'Remote Desktop
Connection' from Windows XP) which is already on the local network and run the *.adp
from there. When I try to run the same *.adp (which is my front-end with forms, reports
and VB code) from my home PC I get:
Run-time error '-214767259 (80004005)'
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied
I tried
1) To connect using the Wizard in Acces
2) Adjusting the settings in File/Connection in the Access project (giving the IP of the server and 'sa' password)
3) Creating an ODBC connection in Windows XP and using it as the 'Data Sourse' in the
same File/Connection menu (the Data Link Properties)
and ... nothing. And I know that this has to work because of the follwoing small test.
I was able to connect from SAS 9.1 (from my home PC and over VPN!) DIRECTLY to the SQL
Server database using the simple:
libname mydblib odbc
complete="dsn=SS2005;
database=MyDB;
uid=sa;
password=;
access=readonly;"
stringdates=yes;
I was able to use all tables/views with no problem in my SAS code. Here 'SS2005' is the
ODBC connection I mentioned in 3) and 'MyDB' is clearly the db.
The fact that 2 MS (database) products can't connect over internet directly and a 3rd
party software does it is driving me nuts and I can't afford to run a PC on the local
network only to serve as a (VPN) terminal plus it's sloppy! My Internet search about the
error was futile, so please help!
Thanks,
Iordan
-
This was removed by the editor as SPAM
-
So, it seems that my post does freak you out... Not to worry, I feel the same about the "situation".
By the way, I don't insist on using VPN (virtual private network) - just looking for a way to access my DB from outside the local network without having to translate my whole front-end to WEB pages. -
When you are running from your home PC across the VPN, the credentials supplied by your PC do not place you inside the domain as a credentialed user. You have, by virtue of the VPN, a tunneled connection into some network resources, but even there you often have to login if you try, for instance, to navigate to a directory on the network. The system is getting credentials for you from that network.
When you connect to a workstation through Remote Desktop, that's what you're getting, credentials form a workstation that is a member of the domain.
You might be able to pull it off this way: After establishing your VPN connection, open your Start Menu and locate the item that will open Microsoft Access, but before you open it do something extra. Hold down the Shift key, then right click the Microsoft Access shortcut. From the popup menu select Run As. In the dialog that follows choose the option to run as "The Following User". Be sure to enter your user name as domain\user. Enter the password. That might get that process running with the credentials you need and therefore make the SQL Server resource available through the links (or straight in if it's an ADP) using the credentials of the new context.
If you get a chance to try it today some time let us know how it turns out. I'm going to try it from home tonight myself.
By the way, if this works, thanks go completely to our network admin, who made me aware of this. Anything I got wrong in the writeup is my mistake. Thanks, Curtis!!
-
I think it is not so much credentials as a Firewall issue. I do have cases like that with exact same error message. The problem is that the firewall is not open from /to:
1. From your Remote Desktop Connection PC (second pc) to the server - as you explained that you have to terminal services to the second PC first
2. From the remote access server to your server. This is the case when you try to use your PC's client to connect to the server using RAS. THe VPN. RAS and VRAS servers give your connection a certain IP address, the Firewall have to be opened from the range of IP addresses your VPN server gives out. All this is in case your workstation is DHCP
Please, see your network administrator, he will be able to look up what ports are "dropped" (their expression, means what connections don't go through what ports). You will have to give him the exact time of when you tried to connect and the IP address of the server and a client.
Regards,Yelena Varsha
-
Well...It didn't work for me. (run as)
-
Thanks for the replies, guys! I really apreciate it.
Probably Yelena is right that it's a Firewall issue bacause the sysadmin mentioned the same but we haven't looked into it yet. I'll let you know if we solved the problem because it looks like an intresting case. It's still a mystery to me why SAS is connecting without a problem and the ADP can't. Is Access "overly secure" or I have some version problem?
-
You might need an entry in your workstation's hosts file such as the following:
sqlservername 10.0.0.10 (replace with the appropriate name/ip address)
The hosts file is in c:\windows\system32\drivers\etc.
Greg
-
I thought I had read somewhere that connecting up an Access adp to SQL Server 2005 was going to have issues because there wasn't any support inside Access for some of the new features in SQL Server. You may have to convert to a .mdb instead.
Larry
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply