SQL 2008 Express-Firewall Issue

[MattSTech]

I just recently installed SQL Server 2008 Express. I set up the accounts and tried to connect to the server from another computer in the workgroup. I can only connect if I have the windows firewall off.
I am using Mixed mode. Logging in as sa with the appropriate password.
I changed the TCP port to 1322 from the default 1433 and restarted the services. When I telnet, it can't connect when the firewall is on. It connects just fine with the firewall off.
I added port 1322 as an exception. No luck. I added sqlserver.exe, and every other executable in binn for that matter as exceptions. No luck.
Any other ideas? It is definately a firewall issue, I just dont' know what else to allow for exceptions.

Matt

 

[ramam1]

Do you have more than one firewall? Network gear can be setup to enforce access rules whether you run a firewall on your server box or not.

 

[MattSTech]

I have a router to the outside world and just windows firewall. There has to be an easy way to simply allow access from local pc's. There was another thread on this subject not more than a week ago, but the issue was "solved" by reinstalling? I think this has to be simply a allow access to all SQL stuff button I need to find in the Windows firewall.

 

[ramam1]

1) can you ping the server from the workstation?
2) are you targeting your internal IP address?
3) is the source IP an internal IP address?
4) what does the windows firewall log say?
5) Do you allow TCP and UDP traffic through?

If when the firewall is off you can reach it but not when it's on then you are probably not allowing the traffic through even though you think you are.

 

[MattSTech]

1) yes
2) I am using SQL Management Studio to connect servername\sqlexpress (I think this is named instance vs. IP)
3) IP's are assigned dynamically by the router. Internal.
4) I am not at home right now to test. Will report back in a few hours
5) What specifically do I need to do to allow the traffic. I added TCP port 1322 as an exception. I added UDP port 1434 as an exception. Are there more that I need to allow?

 

[ramam1]

>3) IP's are assigned dynamically by the router. Internal.
I meant for you to confirm that when you are telneting (or otherwise connecting) that the IP address being used by the workstation is in fact the internal IP address you expect (with dual NICs or some firewall configs this could surprise you).... In any case the windows firewall log should reveal if this is the case.

For now focus on establishing a TCP connection. You need to check your MSSQL config to see what port it is listening on, add that port to the firewall allow list, and confirm this worked by telnetting from the remote workstation to that port on the server. If that does nto work you need to check firewall log on the server to see if the server firewall is seeing the traffic and if so it should tell you why it is rejecting it (presumably an error configuring the firewall to allow it).
Once you can get past the firewall(s) you can worry about which ports you need to open; In general it depends on which services you run that you want to connect to and on which ports those services are configured to listen.

 

[MattSTech]

I am an idiot. I am so sorry for wasting time. I had UDP port 1433 as an exception. not 1434. All is well.

 

[ramam1]

Enjoy