Configuring Custom SQL Ports for Multiple SQL Instances with Remote SSMS Accessibility
We have a client here at SherWeb that had SQL 2000 Express instance installed on their Windows 2003 Standard Edition x86 server. They recently installed SQL 2008 R2 Express Edition on this server and wanted to be able to access this new instance remotely “SQLEXPRESS”.
To accomplish this, we launched the SQL Server Configuration Manager for the SQL Server 2008 R2 Express installation. We navigated to SQL Server Network Configuration > Protocols section and edited the properties of the TCP/IP Protocol. On the Protocol tab, we set the Enabled to Yes, Keep Alive remains default 30000, and Listen All to No. We only wanted this to be available on the primary IP address.
Next, on the IP Addresses Tab we made sure that the Primary IP was set to Active Yes, Enabled Yes, we removed the TCP Dynamic Ports 0, and set the custom TCP Port 1435. We also set the IP All to 1435 static as well.
(IP addresses and hostnames are removed to protect client confidentiality)
In order for these changes to take effect, we restarted the SQL 2008 R2 Express instance Service and the SQL Browser Service as prompted.
On our gateway firewall we opened up the SQL Browser port (1434) and the new custom port (1435) to the server from any source. This would allow the client to access the SQL instance by, “IP\SQLEXPRESS”. An external port scan was completed and verified that the ports 1434, and 1435 were remotely accessible to the host.
Next, we launched SQL Server Management Studio on an off-domain remote test host and attempted to make a connection, “IP\SQLEXPRESS, but it failed:
After a couple of tries were unsuccessful, we moved on to troubleshooting. We installed Microsoft’s Network Monitor utility on the server. Then on our test machine we found its current public IP address (a quick Google search on “what is my ip” or www.whatismyip.com).
Then we launched Network Monitor on the server, selected New Capture, then selected Capture Settings. In the text field we entered the public IP address of our test machine: “IPv4.Address == (Public IP)”, so that only traffic from that server would be captured. Then click close.
We also applied the filter RDP traffic filter in the Display Filter; this will make it much easier to find the traffic we need:
Then we started the capture of packets on the server. From the test machine, we tried to make the connection to the server once more. Once again we received the Error 26, indicating a network or connection issue; however, this time we had some data in Network Monitor that we could use to track down the failure.
We selected one of the frames and inspected the Frame Details section first:
What we found is that the request from the test machine successfully reached the target server, details on the source and destination IP addresses, and a key clue in this case, the source port 1434 SQL Browser.
Next, look at the Hex Details section. You will see how the SQL Browser Service is attempting to serve up the connection to the test machine’s request. The Named Pipe section is key here:
If you attempt to connect with “Server\SQLEXPRESS” from the test host once more it will fail. Notice that even though the original connection was by IP address, SQL Browser has changed that to “ServerName”. Since we’re trying to connect from outside of the domain, DNS is unable to resolve this hostname externally without specifying the Fully Qualified Domain Name (FQDN); however, with the FQDN we can resolve the hostname.
This can be confirmed with the nslookup utility off of the domain.
Access the command prompt and type in “nslookup”. Then type in your server name without the domain. You’ll see that your external DNS provider is unable to resolve this. Then type in the Server name in this format, servername.domain.com.
In this case we used server.orcsweb.com and it was resolved with the primary IP of the server.
If you are unable to resolve your server’s FQDN externally using the nslookup utility, you’ll need to ensure that your external DNS server is configured with an appropriate “A record” in the correct domain.
Next we tried once more to connect with SSMS, using “server.orcsweb.com\SQLEXPRESS”
Success! Now we are able to connect to this SQL instance from anywhere by server.orcsweb.com\SQLEXPRESS.
So the solution is simply to use the FQDN rather than the IP address. When using the FQDN the SQL Server Management Studio (SSMS) will take the hint and it will use the FQDN even though SQL Browser will still return just the server name.
The reason we ran into this is because of the custom port and the SQLEXPRESS instance which relies on the SQL Browser service. In this case we used servername.orcsweb.com and it was resolved with the primary IP of the server.