March 5, 2013 at 6:15 pm
I installed a single named instance of SQL Server in my lab, and I am having trouble connecting from a remote machine, although local connections to the instance work fine. The server hosting SQL Server is a brand new VMware Workstation virtual machine that does not have any other SQL Server versions or instances installed.
On the database server, I used SQL Server Configuration Manager to change the TCP port to static 1433.
On the database server, I opened the firewall with the following commands:
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
netsh advfirewall firewall add rule name = SQLPort_1434 dir = out protocol = UDP action = allow localport = 1434
PortQry.exe shows 1433 open when I run it from the remote machine that I am using to connect to SQL Server.
Scenario 1: Surprisingly, this command (which does not specify the instance name) works on the remote machine:
sqlcmd.exe -S sql_netbios_name -U sa -P password -Q "select getdate()"
Scenario 2: This command (which specifies the instance name) gets an error error (which I am attempting to fix with this web posting) on the remote machine:
sqlcmd.exe -S sql_netbios_name\instance_name -U sa -P password -Q "select getdate()"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
Scenario 3: But this command (which specifies the instance name *AND* the port number, with the connection string encapsulated in double quotes) works on the remote machine:
sqlcmd.exe -S "sql_netbios_name\instance_name,1433" -U sa -P password -Q "select getdate()"
Any ideas why Scenario 2 fails? This has to be simple. I just don't see it.
March 5, 2013 at 6:30 pm
You only need to specify instance name OR port number, not both.
Can you ping the SQL server box from the remote server?
Can you telnet to the servername,port from the remote server?
March 5, 2013 at 6:39 pm
Thanks for the response. Yes, ping works. As I mentioned, PortQry.exe shows that port 1433 (which is the static port on which SQL Server is listening) is open.
Scenario 1 and Scenario 3 show that I have a path through the firewall. All of the scenarios took place on the same remote machine.
I just cannot figure out why I need to specify the port in Scenario 2 (which is what makes Scenario 3 successful). If Scenario 1 can find the instance without specifying the port, Scenario 2 should be able to do so as well.
March 5, 2013 at 6:43 pm
Unless you configure the named instance to run (listen) on port 1433, it listens on a dynamically assigned port. This is why port 1434 needs to open so that the SQL Browser server can listen for for requests for a named instance and can help connect the requester to the proper port. You have 1434 open for out going but not incoming traffic.
March 5, 2013 at 6:44 pm
Lynn Pettis (3/5/2013)
Unless you configure the named instance to run (listen) on port 1433, it listens on a dynamically assigned port. This is why port 1434 needs to open so that the SQL Browser server can listen for for requests for a named instance and can help connect the requester to the proper port. You have 1434 open for out going but not incoming traffic.
>>On the database server, I used SQL Server Configuration Manager to change the TCP port to static 1433.
Yup, I totally agree.
March 5, 2013 at 6:48 pm
Just a guess, but by configuring the named instance to use port 1433, looks like you don't need the name of the instance. I'd be interesting knowing the why on that one as well.
March 5, 2013 at 6:48 pm
shew (3/5/2013)
Lynn Pettis (3/5/2013)
You have 1434 open for out going but not incoming traffic.
That's it!!!!!!!!!!!! Good eye. I snagged the netsh commands from a web page, which had UDP 1434 set to out, but not in.
Thanks!
March 9, 2013 at 8:24 am
I rebuilt this environment again to clean up my notes because I am planning to build similar lab environments in the future. (I had tried numerous firewall configuration attempts prior to making my original post, and I wanted to be sure that I had documented only the required firewall steps.) No surprise... I am having firewall issues on the rebuild. Sigh...
So far, on this round, I:
- Installed SQL Server 2012
- Used SQL Server Configuration Manager to change SQL Server to listen on static port 1433, instead of dynamic port 49202
- Bounced SQL Server for the port change to take effect
At this point, SQL Server is listening on port 1433 on the local server--here is the test I used on the local server:
.\PortQry.exe -e 1433 -n server_name
Querying target system called:
server_name
Attempting to resolve name to IP address...
Name resolved to n.n.n.n <-- This IP address matches the server IP
querying...
TCP port 1433 (ms-sql-s service): LISTENING
Next, on the local server, I used PowerShell, opened as Administrator, to configure the firewall, using commands similar to what we discussed in this post:
# The following command opens the standard SQL Server port on a standalone machine
netsh advfirewall firewall add rule name = SQLPort_1433 dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
# The following command opens the port for SQL Server Browser so that remote systems can connect
netsh advfirewall firewall add rule name = SQLPort_1434 dir = in protocol = UDP action = allow localport = 1434
I retested the local server to make sure that it is still listening on port 1433:
.\PortQry.exe -e 1433 -n server_name
Querying target system called:
server_name
Attempting to resolve name to IP address...
Name resolved to n.n.n.n <-- This IP address matches the server IP
querying...
TCP port 1433 (ms-sql-s service): LISTENING
Next, I cut and pasted the working (i.e., "LISTENING") PortQry.exe command from the server into a remote machine to test the port from the remote machine, and the remote machine is getting blocked by the firewall:
.\PortQry.exe -e 1433 -n server_name
Querying target system called:
server_name
Attempting to resolve name to IP address...
Name resolved to n.n.n.n <-- This IP address matches the server IP
querying...
TCP port 1433 (ms-sql-s service): FILTERED
I am obviously missing a step somewhere. Thanks for your patience.
March 9, 2013 at 11:33 am
This command (from my previous post) must have something wrong in it:
netsh advfirewall firewall add rule name = SQLPort_1433 dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
This deprecated command opens the port:
netsh firewall set portopening TCP 1433 "SQLServer"
Can anyone help me correct the non-deprecated command?
March 9, 2013 at 12:01 pm
This non-deprecated command appears to work:
netsh advfirewall firewall add rule name=SQLPort_1433 dir=in action=allow protocol=TCP localport=1433
After the above command is executed on the local server, the following connections work from a remote machine:
sqlcmd.exe -S server_name -U sql_login -P sql_password -Q "select getdate()"
sqlcmd.exe -S server_name\instance_name -U sql_login -P sql_password -Q "select getdate()"
sqlcmd.exe -S "server_name\instance_name,1433" -U sql_login -P sql_password -Q "select getdate()"
However, based on my web searches, you also need:
netsh advfirewall firewall add rule name=SQLPort_1434 dir=in protocol=UDP action=allow localport=1434
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply