Client PCs takes ages to load the SQL server base application with Firewall enabled on the SQL Server

I have completed installing SQL server as per Microsoft BPA and I handed over the server to the Software vendor to install an application on our SQL server and they have completed installing it.

We install a client program on the Client PCs to connect to our SQL database server. I opened inbound UDP port 1434 for SQL browser and TCP port  1433 for the SQL Server on the firewall. Also I applied an inbound rule by choosing the program and giving the path to the E:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn\sqlservr.exe.

I have a problem with clients connecting to the SQL server.
With the firewall enabled on the SQL Application server the clients are taking 2 minutes to load the application. If I disable the firewall on the SQL server the clients take 6-8 seconds to load application (which is really good)
I did some investigation on the server and the client.
1)      Firstly- I enable the firewall on the SQL server. I went to the client and opened the application and it opened after 2 minutes and within the client there is tab “Test server connection” when I click this it says  “server not working”
( In this scenario the clients takes 2 minutes to load the application)
 2)  Secondly- I disabled the firewall on the SQL server. I went to the client and there is tab “Test server connection” when I click this it says “Server Ok”
(In this scenario the clients takes only 6-8 seconds to load the application.
I don’t know where this bottle neck is happening when the firewall is enabled on the SQL server. Is there any third party software to find this bottle neck\Is there any other port that I need to open on the SQL server firewall?

Please suggest any help to trouble shoot much appreciated

Thanks in advance.
Who is Participating?
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.

nemws1Database AdministratorCommented:
Have you verified that SQL Server is using port 1433?  Double check to make sure TCP/IP is turned on and that the Dynamic Port setting is blank the TCP Port is set to 1433.  Look at steps 1-5 (ignore the last 2 steps) here:
there is no bottle neck. when the firewall is on,  your app can't reach the server at all. the longer loading time is the time it takes for the load-time scripts in your app to timeout and stop trying to connect to the server

like nemws said, it is very possible you picked the wrong port. if not it is probably some kind of misconfiguration
lianne143Author Commented:
I followed steps 1-5 and changed the settings under
IPALL. TCP Dynamic Ports -> Blank.
TCP Port -> 1433 .
If I make this changes the client takes more time 4 minutes to open the application.

I had a look at the settings on my old SQL 2008 on Windows 2003 , where our database was installed and the firewall on this server has been disabled, and the application was working absolutely fine on the old  server with the firewall disabled.

We have migrated the database to a SQL 2012 (Windows 2012) and this is where we have this problem.
I tried to disable the firewall on the 2012 server and I cant even RDP from a windows 7 PC and also with the Firewall disabled if I login to the SQL application server from the client it give me an error "Application Connection failed for Login Username, reason "

Sorry guys giving you all as much as information to find a solution.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

nemws1Database AdministratorCommented:
Did you restart the server after changing the TCP port settings?
lianne143Author Commented:
Yes , I restarted the server after changing the port settings.
nemws1Database AdministratorCommented:
I'd like to double-check your firewall rules.  Can you export your Inbound rules as a CSV file using the instructions here:

(remove anything/everything *not* SQL related) and post it as an attachment here?  It might be something small that you're not seeing and it's hard to get a screenshot via the GUI.
lianne143Author Commented:
On the XLS Under the name I have made it bold ,  the application which we use.

[***file removed for security at author's request***]
nemws1Database AdministratorCommented:
ARgh.  All these setting look fine to me.  Can you walk me through how these processes intercommunicate?

A client process connect to SQL on your server?  Or does it connect to Discover/SIMS and then *that* connects to SQL?  (If that is the case, you might need to make sure Named Pipes are turned on for SQL as it would have nothing to do with your TCP/IP connections).
lianne143Author Commented:
Iam not sure how the client and the server application communicate. I will get you some details about this from my application vendor on Monday. After the application is installed on the client , two ini files gets created and I edit them  for the SQL server settings
First ini file is located on the folder which is located in the programs file folder on the client.
The first ini file has server IP address and instance name.
The second ini file is copied to the windows folder on the client and it has settings for the sims data folder on server  and the MSSQL\Bin folder.
Can we turn on the named pipes for SQL server and see if that sorts the problem.
nemws1Database AdministratorCommented:
You can try turning on named pipes, but from your description, that won't help.

I don't suppose you know how to use Wireshark?  At this point, I would shut down my firewall, run Wireshark (packet sniffing program) and identify the hosts/ports involved with a transaction so I can identify the ports I need to open.

You could also use a trial-error approach.
1) Turn on the firewall
2) Add a new rule that opens ports "0-65500"
3) Test the connection?  Is it fast?
4) If so, restrict the port range in half (0-32500 or 32500-65500 - try one or the other)
- repeat 3-4 to identify a decently small range of ports you can open
5) If the answer to #3 is no... then we need to investigate further.

It would be nice if Windows logged blocked incoming connections.  We could find out right away then.

I just searched and found this app, which I have not ever tried, but looks like it'll notify us of incoming/blocked connections:

Might want to give this a try.
lianne143Author Commented:
Thanks for your post  and  I have not used Wireshark in the past , but I will run now. Sorry if I am silly , want to be more clear Firstly:
1) Do I need to shutdown the firewall on the client and run the Wireshark on my client ?
2) Do I need to shutdown the firewall on the client and run the Wireshark on the server ?
3) Do I need to shutdown the firewall on the server and run the Wireshark on the server ?

"It would be nice if Windows logged blocked incoming connections.  We could find out right away then."
How do I enable this , is this on the Client \Server

I will runt this program , Do I need to run on the server or client ?

I will try the trial error approach of opening range of  ports on the firewall and post you the results.

lianne143Author Commented:
On the server inbound policy I gave arrange of (0-32500) TCP and  I opened the application on the client and now  it opens in 10 seconds ( which is good) :)
So do I need to leave with this range or again minimise the range of ports.

Do I still need to run the Wireshark\Windows firewall notifier from CodePlex

Please suggest

nemws1Database AdministratorCommented:
you don't want to leave ports 0-32,500 open - that's basically like running without a firewall.  Repeat the steps narrowing down by the port rang in half.

In other words something like this happens:
1) Tr 0-16,125 (result: still 10 seconds)
2) Try 0-8,000 (result: still 10 seconds)
3) Try 0-4,000 (result: long/2 minutes)
4) try 4000-8000 (result: 10 seconds)
5) try 4000-6000 (result: 10 seconds)
6) Try 4000-5000 (result: long/slow)
7) Try 5000-5500 (result: long/slow)
8) Try 5500-6000 (result: 10 seconds)
9) Try 5700-6000 (result: 10 seconds)
10) Try 5800-6000 (result: long/slow)
11) Try 5700-5750 (result: 10 seconds)

I know this sounds arduous, but it *is* a solution.  Of the other solutions I provided, I would try the 3rd one, WFN first, as I think that has the most promise.  Turn your firewall back on/remove the 0-3200 ALLOW rule before you run it, though.

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
you can run procexp on either side. select your application if you are on the client, or the sql server if you are on the server, and look at the tcp pane which will display all tcp connections.

in case there is some weird stuff happening while the application loads, you probably should load it a few times while looking. it will be easier to debug on the client side if the sql server has other clients.

do the above when you are in a good (10s) situation as you will only see working connections, restrict ports depending on observation, and check it still loads fine.

note that using wireshark will give better informations, logging, and will also see blocked connections. if you can afford to run it on the client, and filter so you see only connections to the sql server, you'll have a much better debug but it's more complex to use.


named pipes will permit connection between apps on a specific host but are not applicable for remote connections
lianne143Author Commented:
Thanks you so much for all your suggestions, your posts really  helped me to sort this problem.
I narrowed down the ports and finally found that port 8080 was required :)
Now the client loads up quickly.
nemws1Database AdministratorCommented:
Woohoo!  :-)
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
MySQL Server

From novice to tech pro — start learning today.