SQL2008 Through firewall, C# Winform

I have an interesting problem I need help with.

I developed a C# Winform, which works fine on my internal network and connects without issue to my SQL2008R2 server.

1) The connection string points to the named instance of the SQL DB via one of my Public IP's.
2) My firewall allows requests from the LAN AND any External address to the public IP, and is Port Forwarded back to SQL Server. Ports 1433, 1434 UDP are listening and I can connect my app from the internal LAN
3) From my home PC (Outside the network), I can Telnet to the public IP on all relevant ports and they are open.
4) When I launch my app from outside, it times out with the generic "Error:26 ..Server not found or is inaccessible...) message
5) I do have two Named Instances, each set with a static port in the IPALL settings, and only one is required for this app.
6) My connection String in the C# app is:
string ConString = @"Data Source=TCP:(public ip),2214\(instance name);Initial Catalog=(dbname);Persist Security Info=True;User ID=sa;Password=(password)";


I cannot figure this out. Ive even tested by opening "Any-Any" traffic through the firewall for that particular server, but the app will not connect.

Thanks in advance for your help!
hhnetworksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hhnetworksAuthor Commented:
I should clarify that TCP 2214 is the custom port for the Named Instance Im trying to connect to. Telnet from outside shows it's open
0
arnoldCommented:
To point out that exposing the sql server to the outside world .....

Double check your sql server instance tcp/ip configuration to make sure it is the ip/port that you have on your firewall for port 2214

I.e. Sql server\instance tcp/ip 192.168.0.5 1433 and tcp/ip is enabled.
On your firewall, you have public IP 2214 <=> 192.168.0.5:1433

If the two do not match, look at where your firewall is pointing to.
0
arnoldCommented:
Oh, in case of two instances, you have to have one on port 1433 and the other if you only have one IP on the system besides 127.0.0.1 would need to have a different port within tcp/ip settings of the instance.
Instance 1 IP1 1433
Instance 2 IP1 1533

Then make sure your firewall port forward for 2214 points to the correct instance.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

hhnetworksAuthor Commented:
I fully agree about your first statement. I will not be allowing the server to be accessed by any public connections other than my branch office's connections. My form requires a photo upload function that is not cool over a single T-1 line.

Adding to your comments:
Double check your sql server instance tcp/ip configuration to make sure it is the ip/port that you have on your firewall for port 2214 Yes I can hit this IP from Telnet on that port from outside

I.e. Sql server\instance tcp/ip 192.168.0.5 1433 and tcp/ip is enabled.
On your firewall, you have public IP 2214 <=> 192.168.0.5:1433 No, I have two named instances on the server. One is irrelevant to this app, the other on the custom port 2214 and I can telnet to it from outside
0
hhnetworksAuthor Commented:
For my testing purposes, I have UDP 1434 open, TCP 1433 and 2214 open. I can Telnet to all of them.
0
hhnetworksAuthor Commented:
So I should assign a different private IP for the second instance, and port forward to that one?
0
arnoldCommented:
Double check the tcp/ip configuration on both instances to make sure they are not clashing such that you think 2214 goes to instance1 but actually winds up in instance2 I.e. The order of instance startup might bind the wrong instance to IP1 1433 while the other instance falls thorough not being able to bind to ip1 1433 since it is in use and only binds to localhost dynamic port.

To be certain I would recommend you go to the tcp/ip of the instance you do not need to open a firewall rule for and make sure it is not using 1433 on the LAN local IP.

When you are on the LAN are you using the same connection?
0
hhnetworksAuthor Commented:
Just an update:  I am trying an array of different tcp/ip bindings to the various instances and should have results tonight.
0
hhnetworksAuthor Commented:
I think Im lacking understanding of the TCP/IP setup for each instance in SQL Configuration Manager:

My actual server only has a single IP : 10.xx.xx.xx (private)

In the settings for each instance, I have:
IP1 = IPv6 address
IP2 = IPv4 address (the real IP of the box)
IP3 = ::1  (localhost, IPv6)
IP4 = 127.0.0.1 (localhost, IPv4)
IP5 = (some other IPv6 address...Im not using IPv6 in my network)
IPALL = Dynamic Ports clear, TCP Port either 1433, or 2214 depending on instance)

Can you help clarify what each instance should be set at?  2214 is the one I want through the firewall.

And yes, My connection string is the same in the app whether inside or outside the network.  Works fine inside.

Thanks very much!!
0
arnoldCommented:
using the sql server configuration tool, and look at the server instance, tcp/ip properties
There usually will be mulitple entries as you pointed out, the only one you are insterested in is the IPv4 10.x.x.x IP note, 10. Is a private IP space that is used by many entities and if you use 10.3.4.5 it can not be accessed from anywhere other your lan or via A VPN that you allow people to use.

Now pick a port to use 1433 if that is your preference and your firewall mapping of 2214.
On the second one use 1533 instead, and then adjust the windows advanced firewall settings to make sure both port 1433 and 1533 are allowed through. (Note sql server instances need to be restarted for the changes made take effect)
Then go onto your external router firewall and double check the rules you have for forwarding the 2214 to make sure it is going to 10.x.x.x port 1433

Double check whether the restriction if any on the source that can connect to port 2214 from the outside includes the IP/range that your external application might be seen as coming from.
0
hhnetworksAuthor Commented:
Im using a hardware firewall to map the ports from my Public IP's to my Private ones (10.x.x.x). Currently, my firewall is allowing 2214 (and UDP 1434) to the internal server address. The actual server Windows' firewall is disabled, so all ports are open internally.

So, for the individual instances on TCP/IP settings, should I disable all but the IPv4, making sure that the correct TCP Port is what I want for that instance, and everything else is cleared?  I can test this tonight, as I have a 4 hour travel ahead of me today.

Thanks for your help.
0
arnoldCommented:
You should not be allowing 1434 through the firewall, I do not believe it is necessary.
I've seen even through the windows firewall is disabled, it does have some impact.
Add the rule just in case.

No there is no need to disable anything Since you are using different ports for each instance.

The difficulty here is that the suggestions are based on your information.  Have made the mistake myself of paying attention to everything except one little thing that was the cause of the issue.

0) make sure the SQL instance you want to access is using mixed mode authentication (just to be sure).
1) make sure there sql server via 10.x.x.x port <whichever you pick for each instance> is accessible on the lan directly using LAN IPs.
2)make sure the server firewall on/off has the allow rules for the ports you selected on TCP/IP
3) double check the external firewall port forwarding for the port you want accessible from the outside is routed to the correct system which has a static IP and the correct port.
Since you are using Persist Security Info are you configuring its parameters anywhere?

Checking the firewall to see if it sees any issues.

Are you using encryption on the sql connection?....
Are you using the same system internally and externally?
0
hhnetworksAuthor Commented:
Here's where Im at on this issue, and its really puzzling:

To recap, my connection string looks like this:
Data Source=TCP:xx.xxExternalIP.xx.xx,2214\MSSQL$xxxxxxxx;Initial Catalog=xxxxxxxx;Persist Security Info=False;User ID=sa;Password=xxxxxx

This works inside the network, so my Firewall / SNAT / Port forwarding is correct

On a REMOTE computer, I run the PortQueryUI Tool and receive this successful output:
ServerName SERVER
InstanceName INSTANCE1
IsClustered No
Version 10.50.4000.0
tcp 1433
np \\SERVER\pipe\MSSQL$INSTANCE1\sql\query

ServerName SERVER
InstanceName INSTANCE2
IsClustered No
Version 10.50.4000.0
tcp 2214
np \\SERVER\pipe\MSSQL$INSTANCE2\sql\query

Also from the remote computer, I can connect to the instance via ODBC and also various SQL DB "Connection Testers" available for download.

I can only imagine that there is something in my Connection String in my Winform app that it doesnt like.  I cannot find a port blocking or firewall issue.

Any further ideas are welcome.

Thanks!
0
arnoldCommented:
I think your connection strings includes parameters that might not be needed, try without the MSSQL$ for the instance name, MSSQL$INSTANCENAME is the service name in services.msc not the SQL instance name that needs to be used to access it.



Data Source=TCP:xx.xxExternalIP.xx.xx,2214\instnaceName;Initial Catalog=xxxxxxxx;Persist Security Info=False;User ID=sa;Password=xxxxxx

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hhnetworksAuthor Commented:
That worked!!  Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.