?
Solved

Access to sQL over VPN

Posted on 2011-10-10
30
Medium Priority
?
856 Views
Last Modified: 2013-11-21
I have a remote server. I am able to connect via remote desktop. I am able to connect via VPN though I do not see any network resources when I do. I have an Access database that can connect to SQL Server Express when it is loca, but not over the VPN. I have opened ODBC ports 1433 & 1434 and VPN port 1701 on the firewall.
0
Comment
Question by:rawilken
  • 12
  • 4
  • 3
  • +5
28 Comments
 
LVL 5

Expert Comment

by:Prashant Shrivastava
ID: 36944919
did you try following command:

c:\>Telnet ServerIPAddress 1433

If it connects your VPN is fine and connection is OK. If that is true - Just Configure correct DNS server and everything will work.
0
 

Author Comment

by:rawilken
ID: 36944945
Access is denied.
0
 

Author Comment

by:rawilken
ID: 36944980
The server is MS Small Business Server 2003. The OS firewall is off. The router firewall has been configured to pass port 1701 to the server LAN IP. When I start the VPN from my remote location is reports I am connected.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 85
ID: 36945379
Has the SQL Server been configured to allow remote connections? This is turned OFF by default.

How are you attempting to connect the Access app to the remote SQL Server? By that, I mean are you just trying to link the tables to that SSE device, or are you doing something else.

0
 
LVL 5

Expert Comment

by:Prashant Shrivastava
ID: 36945414
now try running command prompt with the same user credentials as on server and try telnetting again. if it still not connects then check if remote connections are allowed.
0
 
LVL 9
ID: 36949154
As LSMConsulting suggests, the most likely cause is that you haven't allowed remote connections.  You don't say which version of SQL Server you're using.

For 2005, Look at the Surface Area Configuration tool and enable remote connections (TCP/IP).

For 2008, Look at the SQL Server Management tool and Management Studio.  Here's an article that describes common settings:
http://decipherinfosys.wordpress.com/2009/01/29/no-surface-area-configuration-tool-in-sql-server-2008/

Hope this helps,
Armen
0
 

Author Comment

by:rawilken
ID: 36971544
I checked SQL Server 2005 and it was set to accept TCP/IP in the Surface Area Config Tool. I can ping the server from my pc remotely using the VPN connection.
On the LAN I can connect to the SQL Server database. I am using VBA to connect remotely using the following code...

Public Sub UpdateSQLConnections()
On Error GoTo err_Procedure

    Dim strLocalIP As String
    strLocalIP = DLookup("BackEndServer", "zstblInformation")
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Update zstblDataFiles set DataFileLocation = '" & DLookup("BackEndDatabase", "zstblInformation") & " on " & strLocalIP & "' Where DataFileID = 1"
    DoCmd.SetWarnings True
   
    Dim rs As Recordset
    Dim db As Database
   
    Set rs = CurrentDb.OpenRecordset("Select * from zstjncDataFiles_Tables Where DataFileID = 1")
    Set db = CurrentDb
   
    While Not rs.EOF
'''optional values for user and password at the end.
        UpdateSQLConnection rs!SourceTableName, rs!DisplayTableName, strLocalIP, DLookup("BackEndDatabase", "zstblInformation"), "Administrator", "1nter5pan"
'        UpdateSQLConnection rs!SourceTableName, rs!DisplayTableName, strLocalIP, DLookup("BackEndDatabase", "zstblInformation")
'''former code using global variables gLocalSQLIP gLocalDatabaseName set at top of this module
'        UpdateSQLConnection rs!SourceTableName, rs!DisplayTableName, gLocalSQLIP, gLocalDatabaseName
        rs.MoveNext
    Wend
   
    rs.Close
    db.Close
   
exit_Procedure:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
   
err_Procedure:
    MsgBox Err & ": " & Err.Description
    Resume exit_Procedure
End Sub

'''optional declaration with user and password coming in
Public Function UpdateSQLConnection(pSourceTableName, pDisplayTableName, pIP, pDatabase, pUser, pPwd)
'Public Function UpdateSQLConnection(pSourceTableName, pDisplayTableName, pIP, pDatabase)
On Error GoTo err_Procedure

    Dim strConnect
'    strConnect = "ODBC;Driver=SQL Server;Trusted_Connection=yes;SERVER=" & pIP & ";DATABASE=" & pDatabase & ";WSID="
'''optional code to connect if pUser and pPwd are to be used in stead of trusted connection
    strConnect = "ODBC;Driver=SQL Server;UID=" & pUser & ";PWD=" & pPwd & ";SERVER=" & pIP & ";DATABASE=" & pDatabase & ";WSID="
   
    Dim db As DAO.Database
    Dim tdf As TableDef
   
    Set db = CurrentDb
   
    'delete existing table
    DoCmd.DeleteObject acTable, pDisplayTableName
   
    'create table
    Set tdf = New TableDef
    tdf.Connect = strConnect
    tdf.SourceTableName = pSourceTableName
    tdf.Name = pDisplayTableName
    db.TableDefs.Append tdf
   
    db.Close
   
    UpdateSQLConnection = True

exit_Procedure:
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
   
err_Procedure:
    If Err = 3011 Or Err = 7874 Then 'we tried to delete a table that doesn't exist
        Resume Next
    ElseIf Err = 3059 Or Err = 3264 Then
        UpdateSQLConnection = False
        Resume exit_Procedure
    Else
        UpdateSQLConnection = False
        MsgBox Err & ": " & Err.Description
        Resume exit_Procedure
    End If
End Function

This code works across the LAN on site at the facility. Using VPN I cannot connect at all.

0
 
LVL 9
ID: 36971789
Disclaimer:  I'm not a networking expert!

Is it possible that your VPN is filtering the SQL port (1433) somehow?

To check that, if you just create a brand new Access database and configure new SQL linked tables using a new DSN, does it work over VPN?

Have you tried using the internal IP address instead of the server name, in case it's a DNS name resolution issue over VPN?  That doesn't seem like the case though, because you said you could ping it over VPN.

Beyond that, I'm stumped.  Maybe someone else can help?
0
 

Author Comment

by:rawilken
ID: 36990139
Port 1433 and 14334 are forwarded to the server through the firewall. I have tried using the WAN IP address as well as the LAN IP address. I can ping using the LAN IP Address when connected using the VPN. What I am concerned about is that the IP address and DNS Source for the remote local network and my local network are the same. Is it possible that since both LANs use 192.168.1.1 for the DNS server, even though 191.168.1.2 is static to the server and is not used on my local LAN, to be confused?
0
 
LVL 85
ID: 36991404
If you feel this is rooted in network topology/design, then I'd suggest you use the Request Attention button to ask the Moderators to add those Zones.

<This code works across the LAN on site at the facility. Using VPN I cannot connect at all.>

This would almost certainly indicate that something in the VPN setup is causing the trouble. Could be that something in that setup is still blocking on of your ports. Again, the networking experts could probably help you a lot more with this sort of thing.



0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37001748
You said that you forwarded port 1433 to the server through the firewall. In that case you don't need VPN at all; try telnet external-ip 1433 , where external-ip is public IP address of the remote network with sql server, probably the same one where you connect by VPN.

If this works, stop here; if not, keep reading.


If you already have connected by VPN, then you don't need to forward 1433 on the firewall.

You previously said that when you tried telnet serverIPaddress 1433, you received "access denied". Let's look into this little further. You received "access denied" immediately in response to telnet command? If so, see http://technet.microsoft.com/en-us/library/cc758631%28WS.10%29.aspx#BKMK_2 and address that.

Also, when you have connected via VPN, your computer gets another IP address, which is IP address on the remote network you have connected to. Please from command line issue ipconfig/all before you connected by VPN, and after you connected, and post the results here. And then tell what is the IP address of sql server on the remote network where you are trying to connect.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37001799
also, to isolate issues related to sql server from network-related, download port listener to the computer with sql server, and have it listening on port 1433 - you should receive error that the port is already in use. If there's no error, this means that the port is not 1433. Then stop sql server, have listener listening on 1433, and try to telnet from your network to it.
0
 
LVL 30

Expert Comment

by:pgm554
ID: 37001988
Can you access the internet while vpn;d in?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37002067
Ok,

How, why and what kind of VPN are you using.
An MS PPTP VPN requires ports 1723 and Port 47 (GRE) to be open
<MS Small Business Server 2003>
You absolutely must use the wizard when mucking with any settings.
Settings that the wizard controls, but can be seen in other control panel / admin tools, may look correct but may not function as expected if changed outside the wizard.  Running through the wizard twice, making a minor change, and changing it back, can resolve some network inconsistencies

If you are NOT using PPTP but L2TP, you do not have enough of the needed ports open
http://blogs.technet.com/b/rrasblog/archive/2006/06/14/which-ports-to-unblock-for-vpn-traffic-to-pass-through.aspx

Next
<I am able to connect via VPN though I do not see any network resources when I do>
So what can you ping?
Just the VPN server?
Anything else on the  network?
The SQL server?
If you can't ping it, you ain't connecting to it!
You'll need to check RRAS settings and ensure that you are allowing access to the remote network for VPN clients.

Are you pulling an IP from the VPN server?
Is it an IP from the same subnet as the SQL server?
Your VPN set up should be to hand out addresses from the DHCP pool.
Once you can ping by IP address, can you ping by hostname?
0
 

Author Comment

by:rawilken
ID: 37002746
I am using a Windows 7 VPN connection to Small Business Server 2003. It reports as being connected and I can ping thje remote server IP. I am not able to access SQL Server however. Port 1433 and 1434 are forwarded to the SQL Server.

I will check the PPTP vs L2TP.

I am pulling my Ip from my local DNS. The remote 192168.1.2 is a static IP on the remote LAN. I have modified my DNS not to assign IP 192.168.1.2. I was just thought that since I can ping the remote PC. The subnets are the same on both DNS devices.

I will try to ping by name in a bit.
0
 

Author Comment

by:rawilken
ID: 37002765
I can ping by name and IP
0
 

Author Comment

by:rawilken
ID: 37002769
VPN seems connected. Why can't I connect to SQL Server?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37003034
You can get into evil there (if both the remote and local subnet have the same address scheme) but if you are pinging, then you will be ok.

Now,
Dumb question
Can you access the SQL Server on the local network?
Can you connect to ANYTHING on the remote network?
0
 

Author Comment

by:rawilken
ID: 37017554
I can access the SQL Server whenI am local. As to other devices, the server is to my knowledge the only device configured to allow remote access.
0
 

Author Comment

by:rawilken
ID: 37017559
Interesting feature of running the VPN. When I do run the VPN connection, it blocks all other internet activity from my PC.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37017673
> When I do run the VPN connection, it blocks all other internet activity from my PC.

Uncheck "use default gateway on remote network" in tcp/ip properties.

Did you have a chance to look at my two earlier comments?
0
 
LVL 30

Expert Comment

by:pgm554
ID: 37018559
That's the reason I asked if he could access the internet while VPN"D in..

Sonded like that was the issue.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37018630
If he forwarded the port, he does not need vpn at all.
0
 

Author Comment

by:rawilken
ID: 37019827
I downloaded PuTTY to use for Telnet. I think I am using it correctly. When I enter the IP and port and click open, a window pops up as if to connect, but after a few seconds suddenly closes. Is there something I need to have running on the server?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37019899
0
 

Author Comment

by:rawilken
ID: 37019933
IPConfig post VPN IPConfig post VPN IPConfig Pre VPN  

Ip config pre and post vpn
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 750 total points
ID: 37062994
Please show us the output of route print pre and post VPN.

If the remote network has the same IP scheme, you should set "Use default gateway on remote network" (to prevent from having an additional route set for 192.168.1.0). After connecting, you are required to set up the specific host route to 192.168.1.2 using the PPP adapter as gateway.

If you uncheck that settting, you will have to remove the route set for 192.168.1.0/24 over "Interspan", and replace it by the same host route as above.
0
 

Author Closing Comment

by:rawilken
ID: 37228795
i worked most of it out myself,. this helped
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question