Setting up Management Studio for remote SQL Server

Question

Setting up Management Studio for remote SQL Server

aveek22

Mr or Mrs. 500

Points: 509
More actions
January 6, 2020 at 12:00 am

#3710587

Comments posted to this topic are about the item Setting up Management Studio for remote SQL Server
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

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

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

abacrotto SSC Veteran Points: 231 More actions · Answer 1

So I have my own development computer (HP notebook with SQL Server Express 2017 installed inside a Windows 10 1909 box fully patched) and my problem is that from computers in the network I have to use the named instance to access my SQL engine, but in my own notebook it fails to connect when I use the instance name (I have to use only the computer name or IP address for connecting). I have been upgrading my SQL engine from SQL Server Express 2008 R4, to 2012, 2014 and finally to 2017 and the problem persists, so I think I have done some mistake, but I can not find out what it was nor when I've done it.

Do you have some advice as to what I can do to normalize my engine and make it connect always using the (correctly installed) instance name?

Thank you in advance for your help.

Regards.

Ariel.

aveek22 Mr or Mrs. 500 Points: 509 More actions · Answer 2

Hello,

I think you have to use the following format to connect to the named SQL instance inside your VM.

HOSTNAME\INSTANCENAME. (Hostname = VM PC Name)

I hope this helps.

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

abacrotto SSC Veteran Points: 231 More actions · Answer 3

Aveek:

It is not a virtual machine, but a notebook.

From SQL Server Management Studio (SSMS)

When I try to connect to the SQL Engine using the instance name (192.168.1.xxx\SQLxxxxxxxx in my case) I get the following error:

A network-related or instance-specific error occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server).

When I try to connect to the engine without the Instance name, it works great.

But, in another computer, both types of connections work well (with or without the Instance name).

2. From SqlCMD.exe

Strangely, from my own computer and any of the LAN I can connect to my default and named instance with no problem.

I don't understand what is the difference between connecting from SqlCMD.exe and SSMS.exe as I think they both use the same libraries (maybe I am wrong on this).

Thanks in advance.

Regards.

Ariel.

aveek22 Mr or Mrs. 500 Points: 509 More actions · Answer 4

Hello @abacrotto,

If I understand correctly what you're trying to say, you have SQL Server running on a notebook (PC #1) and you're trying to connect to it from another PC (PC #2).

You need to know the following information before debugging further.

SQL Server can be installed in two types of instances

Default Instance
Named Instance

If you have installed the default instance, then you can connect directly with the hostname. In this case, the instance name is not required. However, if you have installed SQL on a named instance, it is mandatory to provide the instance name after the hostname.

Also, please try to check if you have granted access to the user from PC #2 to connect to SQL Server in PC #1.

Please follow this video to learn more about SQL Server Instances.

Please feel free to let me know if you have doubts further.

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

abacrotto SSC Veteran Points: 231 More actions · Answer 5

Hi:

When I installed my SQL Engine (a few years ago) I did it with an instance name SQLExpress.

Now when I try to connect from another computer, I can connect with or without the instance name from SQLCMD.EXE or SSMS.EXE.

But in my own computer, I can connect with or without the instance name from SQLCMD.EXE but only without the instance name from SSMS.EXE.

I am trying to figure out two things:

Why a named instance can be connected without the instance name.
Why my local SSMS.EXE is not able to connect with the instance name.

Thank you for the pacience.

Regards.

Ariel.

aveek22 Mr or Mrs. 500 Points: 509 More actions · Answer 6

@abacrotto

Can you please find if your SQL is installed on a default or named instance?

To check, please open SQL Server 2016 Configuration Manager.

If the instance name is MSSQLSERVER, then it is the default instance. If the instance name is anything else (SQLTEST01, SQLTEST02 etc.) then it is the named instance.

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

abacrotto SSC Veteran Points: 231 More actions · Answer 7

Mine is a named instance, but I put its name incorrectly. It is SQLAoniken.

NamedInstance

Thanks.

Ariel.

aveek22 Mr or Mrs. 500 Points: 509 More actions · Answer 8

Can you please run the following query and share a screenprint?

SELECT SERVERPROPERTY('InstanceName')

If it returns NULL, then it is the Default Instance otherwise it will return the instance name.

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

abacrotto SSC Veteran Points: 231 More actions · Answer 9

As you have said, my query returns NULL to that query.

So I understand that my installation has no instance name.

The question is, then, why can I connect to it with SQLCMD.EXE using the instance name?

Remember I have said that when I installed the engine it was with an instance name, but after that I upgraded three or four times the SQL Engine.

Thanks.

Ariel.

aveek22 Mr or Mrs. 500 Points: 509 More actions · Answer 10

That's strange.

Ideally, if it is the default instance, I'd suggest you connect to it using the hostname only (from both SSMS and SQLCMD).

Hope this answers your question.

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/

abacrotto SSC Veteran Points: 231 More actions · Answer 11

I will use only the hostname or IP address.

Thank you so much.

Ariel.

AZ Pete Say Hey Kid Points: 672 More actions · Answer 12

Hello Aveek,

Thanks for the excellent, detailed article. I wanted some clarification on the network set up. In the article you configured the instance to listen on network port TCP 1434. As I understand things, the default port for SQL Server Database Engine is TCP 1433. TCP 1434 is for the DAC (Dedicated Admin connection). And UDP 1434 is used for the SQL Brower service when using dynamic ports.

Am I missing something?? Any clarification you have would be appreciated.

Thanks

Peter

Matthias.Meyer SSC-Addicted Points: 400 More actions · Answer 13

Regarding the hassle with default and named instances: This distinction is a bit arbitrary. Also the "default" instance has a name, it is "MSSQLSERVER". This is the default when installing e.g. a standard edition SQL Server. When installing an Express edition, the default name is "SQLEXPRESS". You can choose a different name for an express edition, or make it the "default" instance. You can also make a non-express edition a named instance during setup.

The other difference is that the server TCP properties for a "named" instance are usually set up differently from a "default" instance. For "named" instances, usually TCP Dynamic Ports are enabled (in the TCP/IP properties, the "TCP Dynamic Ports" setting has to be set to 0, which is a bit ... curious. The "TCP Port" setting is left blank). Connecting such an instance requires a running Browser service (and the corresponding firewall exemption). To connect, you specify "HOSTNAME\INSTANCENAME". The client will connect the browser service first and ask for the current, dynamic port of the instance.

For a "default" instance, usually a static port is specified, as described in the article (and yes, the default port is 1433, not 1434). If the port is different from 1433, you have to specify the port to connect: "HOSTNAME,PORT".

After enabling dynamic ports for a "default" instance, you can connect with "HOSTNAME\MSSQLSERVER" (active browser service required).

After specifying a static port for a "named" instance, you can connect with just "HOSTNAME" or "HOSTNAME,PORT".

Thus, the distinction between "default" and "named" instances is merely a question of network protocol setup.

haar Newbie Points: 1 More actions · Answer 14

If you have a default instance, just use port 1433 where 1434 is used in the article. After that no need to use the IP-address to connect, just use the hostname, in my case the result of the following statement: SELECT SERVERPROPERTY('MachineName')