November 18, 2011 2 Comments
Every once in a while you encounter strange, but interesting issues. This time a colleague asked me to fix the problem that another colleague could not connect to his SQL Engine. After some research we found out that the machine he was working on ran 2 versions of SQL: SQL 2005 Express, and SQL 2008 Enterprise.
The first thing that came to mind was conflicting installations (shared DLL’s for example). That wasn’t it… Second thought: Both instances are default named instances, so I stopped the 2005 Express service. Still nothing…
Then it came to mind. I’ve seen this before! Both instances use the same default TCP/IP port 1433, and this causes issues! So I opened the configuration tool, and changed some IP addresses and port numbers and (already there, so why not) started the SQL Browser Service (why it was stopped? I don’t know!). Result: still nothing…
Now I’m getting pissed! This is impossible! After some searching I tried (as a last resort) to turn of the Windows Firewall. BAM! Works! Damn it!!! In order to make sure it isn’t a coincidence. Restart SQL Service: still works. Turn Firewall on: no connection possible. This is it! Finally, after 30 minutes of trial and error, I get a result.
So I ended up adding a custom rule to the Firewall to allow SQL connections through. This can be done by following these steps:
1) Open Firewall Window:
2) Click on “Inbound Rules”, and then “New Rule…”
3) Choose Port:
4) Add TCP Port 1433
5) You need to allow the connection:
6) You can choose whatever you want in this screen. I personally only allow Domain connections to my SQL Server:
7) Name the Firewall Rule:
When you click Finish, there’s no need to restart SQL Service or your workstation. The changes take effect instantly.
From now on you will be able to connect to your SQL Server Instance from another machine over the internet or your network.