Can’t access SQL Server Cluster or non-standard port or named instance after upgrading to Windows 7

After I migrated to Windows 7 we were not able to connect to the SQL Cluster using MS SQL Management Studio, Linqpad,Visual Studio, my apps, odbc, etc. The reason is that Windows firewall blocks non-default ports for SQL Server.

Determine if the Windows Firewall is the cause

The first thing you probably want to do is confirm that it is actually a Firewall issue. The easiest way (if you are lucky) is to just turn off the Windows Firewall. If you are not lucky enough to be able to turn off your firewall due to corporate policies then there is still hope. To help troubleshoot this, I created a rule in the Firewall that opened up all ports for all programs. Click here to see the details of how to do this.

Once you have determined that the firewall is the issue, you may want to open the port instead of opening it for individual programs or open all ports between your computer and the server you are trying to access.

Option 1: Open Firewall for server

This seems to be the easiest by far since you only have to do it once unless you change server ip addresses or ports.

To add a rule, go to the Start Menu | Administrative Tools | Windows Firewall with Advanced Security. Right-click the Inbound Rules on the left navigation area and then select New Rule…

Next choose Custom rule. Click the next button.

Leave all programs setting. Click the next button.

Leave Protocol type to Any. Click the next button.

Leave local IP addresses as Any. Add ALL IP addresses that you server has to the list of remote IP Addresses. I was surprised that I needed all the ip addresses for the server (use ipconfig from the command line if you don’t know them) and not just one, so to be safe add them all. Click the next button.

Make sure Allow the Connection radio button is check. Click the next button.

Choose only Domain (you may need to add private if you run into issues with vpn, etc). Click the next button.

Give the rule something descriptive.

NOTE: A slightly more secure version of this would be to specify TCP as the Protocol instead of Any. Copy the rule and edit it such that you change TCP to UDP. Then you’ll have one rule for TCP and one for UDP. This is the method I personally chose.

NOTE: You could do the same, but do it for you subnet, but that is a bit riskier from a security standpoint, though not bad on a corporate network. Just be certain to select domain (no public or private).

Option 2: Open Firewall for each program

To fix the issue, go to your Start menu | Control Panel | Windows Firewall | Allow a program or feature through Windows Firewall | Change Settings | Allow another program…

Next choose your program such as MS SQL Management Studio, Linq pad, Visual Studio, odbc, etc. Be sure to click the Network location types… button and select Home Network if you connect from home (and have selected that connection to be a home connection). FYI, to create an odbc connection using a non-standard port you will need to add the C:\Windows\System32\odbcad32.exe program.

Side note on MS Access: If you are trying to have your MS Access application access a linked server that uses ODBC to connect to the SQL Server you will need to create a new ODBC Data Source, but be sure NOT to make it a System DSN. You want to select User DSN otherwise MS Access will not be able to access it. Though, I imagine if you ran MS Access as administrator you could make it work that way with a System DSN.

I still had trouble when running in Visual Studio 2010 when debugging (worked fine if just ran without debugging). I never did figure out how to get past this. If anyone knows, please let me know.

Option 3: Hardcode server ip address

Workaround, if you can’t change your Firewall, you need admin rights on your machine I think. If you can’t do that, then you can always access the server using the ip address and port number separated by a comma. I don’t know why it works, but it does (at least on my machine).

 

Legal like warning: I am by no means a security or firewall guru. Please don’t take these as security recommendations. I simply figured out how to solve the problem while taking the security issues into consideration the best way I know how (I’m a programmer not a security expert). So, please don’t make these changes unless you understand the risks as you are messing with firewall rules that are meant to protect your computer from attack. You assume all responsibility for your changes.