Solved

Can't connect SQL2008 when firewall sbs2011 is active

Posted on 2011-09-16
3
2,073 Views
Last Modified: 2012-05-12
I’m using  a Windows SBS2001 with SQL express 2008r2.

When a client tries to connect the SQL server it doesn’t work. The connection is probably blocked by the SBS2001 firewall. If I turn the firewall off, then the client can connect to the SQL database.

I tried many thing, but they didn’t help.

-      Allow incoming tcp port 1433 in the server firewall
-      In the SQL server configuration manager under “Protocols for SQL Express “
I configured the tcpip protocol: tcp port: 1433, dynamic port – (and also tried different combinations)

So basically, when I turn off the server 2011 firewall, I can connect to the SQL database. But I want to make it work when firewall’s active.


0
Comment
Question by:sitpro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 14

Accepted Solution

by:
setasoujiro earned 500 total points
ID: 36552114
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36552117
add  exception programs to server firewalls since the SQL server and Sql browser will sometimes use dynamic ports with this way you will cover all dynamic ports since you are allowing the program to pass throu the firewall.


From my experience you need to add the SQL server and Sql Server Browser

Take a look here

http://blog.sqlauthority.com/2007/04/23/sql-server-fix-error-40-could-not-open-a-connection-to-sql-server/

http://blog.sqlauthority.com/2008/08/09/sql-server-fix-error-1326-cannot-connect-to-database-server-error-40-could-not-open-a-connection-to-sql-server/ 
0
 

Author Closing Comment

by:sitpro
ID: 36554305
You have to add ALL below ports in the Windows firewall to make it work.

Type in the searchbox firewall and select Windows Firewall with advanced security. Go to Inbound Rules and add the ports one by one:

New Rule > TCP > Specific Local Ports > Next > Domain > Next > Finish

Use these TCP Port:
 135 – Transact SQL Debugger
 1433 – SQL Traffic
 2383 – SQL Analytics Traffic
 4022 – SQL Broker Traffic

Use these UDP Port:
1434 – SQL Browser Traffic
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 57
Remove () 9 36
Create a Calendar table 29 40
Any benefit to adding a Clustered index here? 4 35
A procedure for exporting installed hotfix details of remote computers using powershell
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

738 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