September 19, 2014 at 11:37 am
Hello folks
I have a server with a default instance, but outside this server I cannot connect to this instance without specifying the static port it has assigned. Inside the server I can connect without port number. Following is the configuration I have for this Server/Instance:
ServerName: Server1
O.S. Windows Server 2008 R2 Datacenter
SQL Server 2008R2 SP1
Windows Firewall off
SQL Server Browser running (automatic):
SQL Server (MSSQLSERVER) running (automatic)
Client Protocols:
Shared Memory
Named Pipes
TCP/IP (static port 1757)
Maximum number of concurrent connections: Unlimited
Allow remote connections: True
Remote Admin connections: True
When I try to connect using SSMS from other servers in the same subnet only with instance name/FQDN/Ip Addres/ (Windows or SQL Authentication) I get the following error:
A network-related or instance specific error ocurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 -Could not open a connection to SQL Server) (Microsoft SQL Server, Error:5)
--> Access is denied
From the servers I'm trying to connect, Windows Firewall is off, they have same version of SQL Server and they are able to connect to other sql instances with no issues. The only way I can connect to Server1 is adding port number, same way using an ODBC connection.
Please, I really appreciate if you could give some advise/suggestion. There aren't also any logs related to this in SQL Server logs (client/destination) or in Event viewer.
September 20, 2014 at 6:56 am
[font="Verdana"]Well, that's the expected behavior of SQL Server. Did I fail to understand your problem.. I wonder??
If you modify the default 1433 port to some other number, then it becomes a mandate to specify that new port number while connecting to the server like "ServerName,NewPort".
It's absolutely expected to throw an error by your SSMS that it can't connect to the specified server because the target server is waiting for connections at a different rendezvous point(a.k.a port).
Hope I suggested, as you expected:-D[/font]
--In 'thoughts'...
Lonely Rogue
September 20, 2014 at 8:25 am
Quick thought, looks like the browser service is not working.
😎
September 20, 2014 at 10:18 am
Well, I have other servers/Instances with same configuration and they do connect without specifiyng port number. I guess maybe SQL Server Browser is not working on my Server1, but how could I prove that?
September 20, 2014 at 7:42 pm
Start>Run>Services.Msc
locate the SQL Browser service in that list.
And a quote on BOL says that if Browser service isn't running then, connections without port number doesn't work. So how are your other connections happening??
Using SQL Server Browser
If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.
However, if the SQL Server Browser service is not running, the following connections do not work:
Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).
Any component that generates or passes server\instance information that could later be used by other components to reconnect.
Connecting to a named instance without providing the port number or pipe.
DAC to a named instance or the default instance if not using TCP/IP port 1433.
The OLAP redirector service.
Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.
--In 'thoughts'...
Lonely Rogue
September 21, 2014 at 7:39 pm
Hi, SQL Server Browser is running in Server1, as well as in other environments we have but the behavior of Server1 is like if it wasn't running.
September 21, 2014 at 10:35 pm
hiram.osiris (9/21/2014)
Hi, SQL Server Browser is running in Server1, as well as in other environments we have but the behavior of Server1 is like if it wasn't running.
The browser service's default port is UDP 1434, is there anything (firewall) blocking that port or UDP in general?
😎
September 22, 2014 at 2:34 am
hiram.osiris (9/21/2014)
Hi, SQL Server Browser is running in Server1, as well as in other environments we have but the behavior of Server1 is like if it wasn't running.
Hi.
Maybe where a successful connection, you use aliases for the instance name? And in this connection is set up aliases for the port number already?
September 25, 2014 at 8:50 am
Hello everybody
We finally got it working... we just installed the latest patches for SQL Server 2008R2 as well as latest windows updates, the reboot and everything worked fine. Maybe there was a bug that required a patch, anyway, now we are able to connect to Server1 from outside the server just specifying Instance name, without port number.
Thanks for your patience 🙂
September 25, 2014 at 9:41 am
Good stuff!
Out of curiosity, what is the output of
select @@version
after the fix?
😎
September 25, 2014 at 9:51 am
Hi, we had SP1 so we installed first SP2 and then the latest CU released last August, 10.50.4331 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply