can't connect to SQL via VPN

[1DMF]

Hi,

Our DB application used to work fine, and remote users had no problems running it over the VPN allthough it was slow.

However no VPN user can now use our system, the apps just error saying they can't find the SQL server instance.

if we ping the server a reply is recieved, so why can't we get any data out of SQL anymore?

All we get is server doesn't exist or access denied?

The same application on the same laptop while connected internally works fine.

any ideas?

Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[gmmastros]

Did you check for firewalls that may be blocking the TCP/IP port that SQL Server is trying to use?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

I did add port forwarding to the firewall just in case, but It can't be that, we have a web app that accesses this SQL server via a dedicated VPN tunnel and that works fine.

It also can't be a firewall setting on the laptop otherwise it wouldn't work inhouse either would it?

I thought once connected to a netwrok via VPN, you were in an all ports were available.

I can ping the server, so I can see it OK.

Something is blocking it , but what? and why?

I was going to try to telnet to the port to check I could see it ok, but telnet doesn't exist in Win7, what can I use to track the problem?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[gmmastros]

http://technet.microsoft.com/en-us/library/cc771275(WS.10).aspx#bkmk_installVista

Install Telnet Client on Windows 7 or Windows Vista

On Windows 7, Windows Vista, you can use the Windows Features tool to install optional components.

To install Telnet Client on Windows 7 or Windows Vista
Click Start, and then click Control Panel.

On the Control Panel Home page, click Programs.

In the Programs and Features section, click Turn Windows features on or off.

If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.

In the Windows Features list, select Telnet Client, and then click OK.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George.

I can confirm that it isn't the firewall.

I have just VPN'd to the company network, opened the DB and refreshed the table links to the web server SQL which is at the other end of a router 2 router dedicated firewall connection and the tables links refreshed fine.

So the app is connecting through my client VPN to our server, then going back over the 2nd VPN connection to the remote web server and all works fine.

It has to be something to do with the new instance created by the tech guys who dialled in and trashed our server, was unable to get it working again under the default instance so created another instance and re-mapped the MDF files to the new instance.

So what could they have done or failed to do with this new instance that would be causing the problem?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[SQLBill]

Was the application password changed recently? We see issues like this when users or application Windows passwords are changed while they are still logged into to SQL Server. Active Directory has the new password, but the connection is still using the old password. If this is the case, try having the users close out all SQL Server connections and then reopen/reconnect to SQL Server. We tell our users to reboot their computer just to make sure they are really closed out from all SQL Server connections.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

Sorry Bill, you've lost me.

Windows passwords and AD ?

Our application uses username & password and is the 'sa' account.

There is no link between domain users and access to the SQL database.

I cannot refresh the linked tables in our access DB when connected via VPN when using the master 'sa' login details.

It isn't related to the user i'm logged onto the system as, unless there is some setting on the SQL server that needs to be changed?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

I've just ran telnet and I can connect to the main ip address on port 1433.

If I put IP/Instance , it doesn't but is that a valid address for telnet?

so it works with

telnet 192.168.0.50 1433

but not if i put

 telnet 192.168.0.50\NEW 1433

I've installed SP4 and that hasn't worked either, any other ideas?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[gmmastros]

You should check to see what PORT your instance is using.

Open up SQL Server configuration manager (assuming sql 2005 or newer).
click "Protocols"
Right click TCP/IP, click properties
click IP Addresses tab.
Scroll down to IPAll and take note of the port number.

For SQL2000:
Click Start -> Run
Type cliconfg
Click OK
Make sure TCP/IP is enabled.
Click TCP/IP, click Properties.
You will see the port number that SQL Server is using.

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

1DMF,

Sorry about that, but you never said you were using the SA account or at least I didn't see that mentioned anywhere.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jsteph]

1DMF,
So the access db is linking via ODBC? Have the table links been deleted then refreshed (as opposed to just doing a 'refreshlink' via menu or code)?
I've found that the ODBC connect properties of linked tables don't necessarily refresh themselves when a new instance of the db or even a new design of table is put there behind it's back--the table links have to be deleted and then re-linked.
Just a thought,
--Jim

 

[1DMF]

George -> 1433 , I've checked this, telneted to it, i've even changed it to 1444 and then telneted and connected just to make sure the port changes and was listening for connections. All seems to be OK.

Bill-> No problem, sorry If I confused you, yes using username/password to connect, any other ideas?

Jim-> I cannot refresh the links when connected via VPN , as mentioned I cannot see the SQL server.

However, I run a method I wrote each time tables are changed which relinks the tables via DNSLess connection using the 'sa' account before releaseing the app to the user.

The tables relink and refresh fine when connected inhouse, but when you run the app from VPN it doesn't work and can't find the in-house SQL server?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[jsteph]

1DMF,
Another thought...when you ping the server, are you pinging with IP or Servername? If the former, does your VPN network properties have the office DNS server listed and the office domain as an appended suffix?
--Jim

 

[SQLBill]

Since you can log into the server locally, but not via VPN...maybe the issue is that allow remote access got 'turned off'. Run sp_configure and look at what the value is for 'remote access'.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

Finally got it working!

For some reason when connected via VPN 'named pipes' doesn't work.

I changed the DB connection string to 'Server IP, Port' instead of 'Server IP\Instance' and it now works no matter how the user is logged onto the system

That was a royal pain, but got there in the end!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts