"Network-related or Instance specific error" when connecting through SSMS

Question

"Network-related or Instance specific error" when connecting through SSMS

NewBornDBA2017

SSCarpal Tunnel

Points: 4832
More actions
July 26, 2017 at 7:59 am

#338486

This is the 1st time I came across this issue where I am unable to access the instance from SSMS which is installed on my local machine. However, I am able to login when I log into the server. TCP is enabled, firewall rule has been setup, Allow remote connection to the server is checked. I am not sure what I am missing.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99009 More actions · Answer 1

What error message are you receiving when you try to connect. What details show in the SQL Server's Logs? Can you ping the server from your machine? After enabling TCP/IP have you restarted the instance/server? Is your instance running on a different port or using a name other than the default?

You say you can't connect by SSMS, can you connect by other means then from your PC (i.e. sqlcmd)? What version of SSMS are you using?

We need more information to really troubleshoot this, as there could so many reasons why you can't connect.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 2

Thom A - Wednesday, July 26, 2017 8:09 AM
What error message are you receiving when you try to connect. What details show in the SQL Server's Logs? Can you ping the server from your machine? After enabling TCP/IP have you restarted the instance/server? Is your instance running on a different port or using a name other than the default?
We need more information to really troubleshoot this, as there could so many reasons why you can't connect.

This is the error when I try to login.

This is the only valuable information I see in the log file

2017-07-26 08:49:52.94 Server Dedicated admin connection support was established for listening locally on port 55992.
2017-07-26 08:49:52.95 spid11s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2017-07-26 08:49:52.95 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

I am able to ping the server without any issue.
After I enabled TCP/IP, Named Pipes, Created firewall rule etc, I did restart the service.
The instance is not running on the default port.

Thom A SSC Guru Points: 99009 More actions · Answer 3

Are you specifying the port in your Connection string? if not, what happens if you do? So your Server name would be: FJ-SQLDEV1\fjm,55991

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Perry Whittle SSC Guru Points: 233792 More actions · Answer 4

newdba2017 - Wednesday, July 26, 2017 8:23 AM
Thom A - Wednesday, July 26, 2017 8:09 AM
What error message are you receiving when you try to connect. What details show in the SQL Server's Logs? Can you ping the server from your machine? After enabling TCP/IP have you restarted the instance/server? Is your instance running on a different port or using a name other than the default?
We need more information to really troubleshoot this, as there could so many reasons why you can't connect.
This is the error when I try to login.
This is the only valuable information I see in the log file
2017-07-26 08:49:52.94 Server Dedicated admin connection support was established for listening locally on port 55992.
2017-07-26 08:49:52.95 spid11s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2017-07-26 08:49:52.95 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
I am able to ping the server without any issue.
After I enabled TCP/IP, Named Pipes, Created firewall rule etc, I did restart the service.
The instance is not running on the default port.

What ports and protocols have you allowed through the firewall?

For your named instance you'll need to allow the following

TCP 1433, 1434, 55991
UDP 1434

Of course if you connect using the IP or name and the port number, then just TCP port 55991 is required to be opened

FJ-SQLDEV1, 55991
someipaddress, 55991

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 5

Thom A - Wednesday, July 26, 2017 8:33 AM
Are you specifying the port in your Connection string? if not, what happens if you do? So your Server name would be: FJ-SQLDEV1\fjm,55991

This actually worked "FJ-SQLDEV1\fjm,55991" and this "FJ-SQLDEV1\fjm" didn't. So I changed the Protocol type to TCP and I still can't connect using the servername\instance name.

Perry Whittle SSC Guru Points: 233792 More actions · Answer 6

newdba2017 - Wednesday, July 26, 2017 8:50 AM
Thom A - Wednesday, July 26, 2017 8:33 AM
Are you specifying the port in your Connection string? if not, what happens if you do? So your Server name would be: FJ-SQLDEV1\fjm,55991
This actually worked "FJ-SQLDEV1\fjm,55991" and this "FJ-SQLDEV1\fjm" didn't. So I changed the Protocol type to TCP and I still can't connect using the servername\instance name.

you only need the instance name or the port name not both even though it does seem to connect.
Please confirm the ports you have opened

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 7

Perry Whittle - Wednesday, July 26, 2017 9:08 AM
newdba2017 - Wednesday, July 26, 2017 8:50 AM
Thom A - Wednesday, July 26, 2017 8:33 AM
Are you specifying the port in your Connection string? if not, what happens if you do? So your Server name would be: FJ-SQLDEV1\fjm,55991
This actually worked "FJ-SQLDEV1\fjm,55991" and this "FJ-SQLDEV1\fjm" didn't. So I changed the Protocol type to TCP and I still can't connect using the servername\instance name.
you only need the instance name or the port name not both even though it does seem to connect.
Please confirm the ports you have opened

I did create a firewall rule to specify the instance name which should work.

Sue_H SSC Guru Points: 90860 More actions · Answer 8

Is the browser service is running on the Server?

Sue

Perry Whittle SSC Guru Points: 233792 More actions · Answer 9

newdba2017 - Wednesday, July 26, 2017 9:22 AM
Perry Whittle - Wednesday, July 26, 2017 9:08 AM
newdba2017 - Wednesday, July 26, 2017 8:50 AM
Thom A - Wednesday, July 26, 2017 8:33 AM
Are you specifying the port in your Connection string? if not, what happens if you do? So your Server name would be: FJ-SQLDEV1\fjm,55991
This actually worked "FJ-SQLDEV1\fjm,55991" and this "FJ-SQLDEV1\fjm" didn't. So I changed the Protocol type to TCP and I still can't connect using the servername\instance name.
you only need the instance name or the port name not both even though it does seem to connect.
Please confirm the ports you have opened
I did create a firewall rule to specify the instance name which should work.

what about outbound rules, dont ever use windows firewall so cant say for sure if it automatically opens the required ports.
Check the ports which have been opened

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 10

Sue_H - Wednesday, July 26, 2017 10:24 AM
Is the browser service is running on the Server?
Sue

Yes

Sue_H SSC Guru Points: 90860 More actions · Answer 11

You probably need to recheck the ports as Perry mentioned. I don't use Windows firewall but I don't think that would open up the port for the browser. The executable is in a different directory, different program name than what you showed in the screenshot.

Sue

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 12

I just deleted firewall rule and ran the below query to find out which port SQL server is listening on. Perry also suggested/asked to confirm which ports have bee opened. How do I find out?

Sue_H SSC Guru Points: 90860 More actions · Answer 13

I would use PortQry. If you don't have it, it's just a free download from Microsoft that you can use from your PC to scan what ports are open on a remote machine.
There is probably a better way but I don't know much about Windows Firewall.
If you want to try PortQry and haven't used it, you can find some examples and a link to the download in this article:
PortQry - Simple Command Line Port Scanner

Sue

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 14

Let's say I find out that there are multiple ports open, what do I do next? What is the next step?