?
Solved

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

Posted on 2014-08-07
16
Medium Priority
?
346 Views
Last Modified: 2014-08-23
Hi
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.
0
Comment
Question by:lianne143
  • 7
  • 7
  • 2
16 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1556 total points
ID: 40246785
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:

http://blogs.technet.com/b/aviraj/archive/2013/05/14/system-center-2012-sp1-configuration-manager-sccm-2012-installation-sql-server-database-error-when-using-named-instance.aspx
0
 
LVL 27

Assisted Solution

by:skullnobrains
skullnobrains earned 444 total points
ID: 40247111
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
0
 

Author Comment

by:lianne143
ID: 40248910
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.
Thanks
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1556 total points
ID: 40248934
Did you restart the server after changing the TCP port settings?
0
 

Author Comment

by:lianne143
ID: 40248985
Yes , I restarted the server after changing the port settings.
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1556 total points
ID: 40249014
I'd like to double-check your firewall rules.  Can you export your Inbound rules as a CSV file using the instructions here:

http://www.windows7library.com/blog/bkup/exporting-firewall-settings-in-windows-7/

(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.
0
 

Author Comment

by:lianne143
ID: 40249084
On the XLS Under the name I have made it bold ,  the application which we use.
Thanks

[***file removed for security at author's request***]
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1556 total points
ID: 40249145
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).
0
 

Author Comment

by:lianne143
ID: 40249294
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 Sims.net 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.
Tnx
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1556 total points
ID: 40249446
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:

http://wfn.codeplex.com/

Might want to give this a try.
0
 

Author Comment

by:lianne143
ID: 40249921
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 ?

Secondly:
"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

Thirdly:
I will runt this program http://wfn.codeplex.com/ , 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.

Thanks
0
 

Author Comment

by:lianne143
ID: 40249995
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

Thanks
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 1556 total points
ID: 40250222
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)
etc....

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.
0
 
LVL 27

Assisted Solution

by:skullnobrains
skullnobrains earned 444 total points
ID: 40250497
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
0
 

Author Comment

by:lianne143
ID: 40252075
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.
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 1556 total points
ID: 40252077
Woohoo!  :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Suggested Courses

850 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