Unable to bypass Windows Firewall with SQL Server 2008 R2

Question

Hi

I am attempting to set up a test environment on a Windows 7 laptop using SQL server 2008. After install, I have entered the relevant ports into Windows Firewall but I am still unable to connect to the server via Visual Studio. I can say for certain that WF is causing the issue because as soon as I disable it, I can connect fine. I have opened the following TCP ports...

135 – Transact SQL Debugger

1433 – SQL Traffic

1434 – SQL Browser Traffic

2383 – SQL Analytics Traffic

4022 – SQL Broker Traffic

I am aware that there are many existing threds on this, but they all seem to point to the above ports as the solution. Am I missing something?

Thanks

Steve

 

Answer 1

I should also mention that I used the following method to open the ports...

http://support.microsoft.com/kb/968872

The article is titled Server 2008, but I was referred there by an article that stated that this method also works fine for Win7

Answer 2

From where are you trying to connect: a local subnet or from the Internet?  If you are trying to connect from the Internet, you may need to open the port to the WAN in Windows Firewall.

Answer 3

Over a local network

Answer 4

You can enable the firewall log by following the instructions here: http://technet.microsoft.com/en-us/library/cc787462(v=ws.10).aspx.  You can then try again and see what port your version of VS is trying to connect on and open that port.  Make sure you select "Log dropped packets"

Answer 5

Ok, I gave this a go and retreived the following from the log...

#Fields: date time action protocol src-ip dst-ip src-port dst-port size tcpflags tcpsyn tcpack tcpwin icmptype icmpcode info path

                                                                                                                                                                                                                2012-03-15 15:49:58 DROP TCP 192.168.20.54 192.168.20.82 52677 49173 52 S 1129056448 0 8192 - - - RECEIVE
2012-03-15 15:50:01 DROP TCP 192.168.20.54 192.168.20.82 52677 49173 52 S 1129056448 0 8192 - - - RECEIVE

This seems to be pointing to TCP port 49173, which after after a Google search doesn't seem related to SQL Server. Does this seem strange or would it be ok to just open this port?

Thanks

Steve

Answer 6

Visual Studio 11 Beta includes SQL Server 2012 LocalDB, which is supposed to be a local-only database engine for development purposes.  If you are using that, then it might be defaulting to this port.  In any event, you should be able to tell Visual Studio to use the right port (1433) by typing 192.168.20.82:1433 as the hostname when you try to connect.

Answer 7

I'm using VS2010 Professional. Using this hostname doesn't work as I am running in debug mode for testing (http://localhost:1075/Home.aspx). However, I opened the port and it fixed the issue, so at least it's working.

Thanks for your help

Steve

Answer 8

is your sql server install a named instance?  that would explain the port number

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 9

is your sql server install a named instance?  that would explain the port number

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Hi Andrew

Yes, I am using a named instance. Will the server always use this port number or will the port be dynamic?

Thanks

Steve

Answer 10

It is a dynamically allocated port, but I think you'd be unlucky for the port number to ever change.  SQL Server will continue to re-use it until the day that you install something that explicitly uses that port whilst SQL Server's off... then when SQL Server tries to grab that port when it starts up and sees it no longer available, will get a new one from the OS.  Our production servers are named instances and we've not had any problems because of it..

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 11

I'll bear this in mind, thanks.

Answer 12

For the SQL Browser service, you must be sure that you have opened the UDP port 1434 and not the TCP port 1434.  The 1434 TCP port and the 1434 UDP port are two different things.  Look at the end of the listing of KB968872 for opening the UDP port 1434 and enable it to be multicast.

Unless you use their services, the other ports don't need to be opened.