Why MS SQL remote connection works fine with same internet network (wifi), but not when network is changed for client machine.

Question

1.00/5 (1 vote)

See more:

My vb.net application needs user to connect SQL database remotely. Remote connection works fine when both computers are in same network (wifi) but can not connect to server when internet network is changed for user computer. SQL configuration, TCP/IP settings, firewall are done. Please note connection is established with IP address when same wifi network used. What could be missing ?

What I have tried:

SQL configuration, TCP/IP settings, firewall are done.

Posted 4-Aug-19 0:11am

Member 11793729

Updated 4-Aug-19 1:25am

Add a Solution

Comments

Richard MacCutchan 4-Aug-19 6:40am

Check the connectivity, IP addresses, ports etc.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-08-04T00:55:00

Solution 1

At a guess, you aren't changing the IP address to a "public" IP: if it starts 192.168.nnn.nnn then it's a local address which never leaves your LAN - internet IPs are never in that range.
So if the DB is hosted on your network, then "192.168.0.10" will address your server from your dev PC, but could be a printer, or a tablet on the customers.

Posted 4-Aug-19 0:55am

OriginalGriff

Comments

Member 11793729 4-Aug-19 7:09am

Thanks for reply, but I got my Public IP from third party web and tried. I didn't work. I am using SQL express & also developer editions. Are these have anything to do with ?

OriginalGriff 4-Aug-19 7:18am

Try not to use as few words as possible - we only get exactly what you type to work from, so we get no context beyond that.
So we don't know what "I got my Public IP from third party web and tried" means, and what happened when "I didn't work" is a mystery to us.
Try to explain exactly where the DB server is, where the computer you successfully tested form is, and where the client is. Tell us the connection string you used for each computer - censor out the passwords, please - and what happened when you did that. Any error messages are a definite bonus!

Member 11793729 4-Aug-19 7:39am

Sorry or typo and sincere thanks for your help. I got public IP of my SQL machine, from some web sites (all of them show same). I tested with this public IP but fired error saying, server not found. I am using below connection string :

Dim SQLConStr as string = "Data Source=" & VarServer & "," & VarIPSQLPort & ";Initial Catalog= " & VarSQLDB & ";Persist Security Info=True;User ID=" & IPSQLUID & ";Password=" & IPSQLPSS & ";integrated security=false;"

Variable VarServer consist = IP address + Machine Name + SQL Instance Name

OriginalGriff 4-Aug-19 7:57am

Ah. You are self hosting. OK ...
First, check your internet connection: is it a static or dynamic IP? If you don't know, it's probably dynamic - you generally have to pay your ISP extra for static IP. Dynamic is bad: if your connection fails, there is no guarantee at all that you will get the same IP address when the router reestablishes the connection. So a power cut, hardware failure, whatever - and your clients are permanently disconnected.
Second, are you really sure you want to do this? It's dangerous - you are opening your DB server up to everyone on the internet who wants to "have a go". Make absolutely sure that Remote Access is what you need, and change the login info for the admin account(s) before you do anything: if you leave the default "sa" account alive then you deserve everything you are going to get ...

Now check if "Remote Access" is enabled on your SQL server: if you are using SQL Express then it probably isn't: https://blogs.msdn.microsoft.com/sqlexpress/2005/05/05/how-to-configure-express-to-accept-remote-connections/

Member 11793729 4-Aug-19 8:29am

Your comment : "Sure you want to do this? It's dangerous" - Connection string details are not shared with any user and saved in encrypted way. App file assembles when needed. Do you think its not safe ? What is safe alternative to connect remotely. There are limited but scattered users and will be connecting from different locations.

OriginalGriff 4-Aug-19 8:47am

Do you think you have to tell someone who you are in order to be found? Or are there random checkers that will find your server and try to see what you have on it? Remember, it's probably not just the one DB you are hosting on that SQL instance, so all the data is at risk. Then there is password brute forcing, exploits, unknown-at-present security loopholes, uninstalled security updates, DDoS attacks, ...
It's not a good idea, really it isn't - particularly if you don't know what exactly you are doing!

I'd suggest either a VPN (good idea, not that much work) or WCF "man-in-the-middle" app to control access. Direct access is generally considered a bad idea.

Richard Deeming 8-Aug-19 13:31pm

How quickly we forget! :)
SQL Slammer - Wikipedia[^]

Wendelius · Answer 2 · 2019-08-04T01:25:00

Solution 2

Few things to check using Configuration Manager
- If possible, do not use dynamic ports: Configure a Server to Listen on a Specific TCP Port - SQL Server | Microsoft Docs[^]
- Ensure that port 1433 is allowed by all firewalls between the end-points
- If you use named instance, dynamic ports may be needed, depending on other instances, see Configure the Windows Firewall to Allow SQL Server Access - SQL Server | Microsoft Docs[^] , especially Dynamic Ports section[^]

Posted 4-Aug-19 1:25am

Wendelius

Comments

Member 11793729 4-Aug-19 7:52am

All working fine with same network using IP address, port 1433, name instance etc. Same thing don't work once internet network is changed for client machine.

Wendelius 4-Aug-19 8:01am

It's not only the firewall on your side but also on the client side and possible firewalls in-between. All of them need to allow network traffic to selected IP addresses and ports.

Just to make sure that the IP address is correct, have you tried pinging the database from the client side? Does ping work?

Member 11793729 4-Aug-19 8:20am

Thanks for help. You are right, I tried ping and its not working but working for same network ( i think thats using LAN IP address). Possibly problem with public IP address.