Does Management Studio uses SQL native client library to connect to SQL instance ?

Question

Post reply

Does Management Studio uses SQL native client library to connect to SQL instance ?

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
March 7, 2016 at 9:58 am

#325601

Hi all,
I am using SQL server 2014.
I have a ServerA (192.168.1.174) running SQL default instance.
I have a ClientB with management studio installed and is able to reach the instance on Server A via the IP above using sa account.
On ServerA SQL server configuration management -> I have change the default port of the SQLserver for TCP/IP to 1521 (under SQL Server Network Configuration) and restarted the SQLServer service.
I am able to telnet to 192.168.1.174 1521 from ClientB.
Firewall is turned off in Server A.
In ClientB, I have also changed the "Client Protocols" for TCP/IP to 1521 in SQL Native Client 11.0 Configuration (32bit) under SQL Server configuration manager.
But i am not able to connect the ServerA SQLserver instance, unless i specify the connector as 192.168.1.174,1521
q1) How do i get ClientB's management studio to connect with port 1521 as default "without specifying ,1521" ?
Have i downloaded a wrong copy of the client ( i am not sure why it is showing as "SQL Native Client 11.0 Configuration (32bit)" -- why is it 32bit ?
ServerA is running Window Server 2012 R2
ClientB is running Window professional 8.1
Regards,
Noob

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

The SQL Browser service has to be running on the server in order to do that;

that service keeps track of what port SQL is running on, and forwards requests to that port.

when the service is not running, you have to explicitly state which port, which is what you are seeing now.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 2

Lowell (3/7/2016)
The SQL Browser service has to be running on the server in order to do that;
that service keeps track of what port SQL is running on, and forwards requests to that port.
when the service is not running, you have to explicitly state which port, which is what you are seeing now.

Hi Lowell,

Thanks for the reply.

q1) But does the above apply to a default instance (with a non default port) as well ?

q2) Does it also means that the Management Studio is not using the SQL native client (to do connections to the SQL server) ? ; for I have change the default port to 1521 already.

Regards,

Noob

Lowell SSC Guru Points: 323518 More actions · Answer 3

szejiekoh (3/7/2016)
Lowell (3/7/2016)
The SQL Browser service has to be running on the server in order to do that;
that service keeps track of what port SQL is running on, and forwards requests to that port.
when the service is not running, you have to explicitly state which port, which is what you are seeing now.
Hi Lowell,
Thanks for the reply.
q1) But does the above apply to a default instance (with a non default port) as well ?
q2) Does it also means that the Management Studio is not using the SQL native client (to do connections to the SQL server) ? ; for I have change the default port to 1521 already.
Regards,
Noob

The above specifically applies to any instance of any type(i.e. default or named) that does not listen on the default port of 1433;

The Browser service answers UDP port 1434, and provides the actual port for the servername or Server\Instance that was requested. It also redirects Dedicated Admin Connections to the right port, if used.

you didn't change the default port, though, you changed one service(the default instance) to listen to a non-standard port.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 4

Lowell (3/8/2016)
szejiekoh (3/7/2016)
Lowell (3/7/2016)
The SQL Browser service has to be running on the server in order to do that;
that service keeps track of what port SQL is running on, and forwards requests to that port.
when the service is not running, you have to explicitly state which port, which is what you are seeing now.
Hi Lowell,
Thanks for the reply.
q1) But does the above apply to a default instance (with a non default port) as well ?
q2) Does it also means that the Management Studio is not using the SQL native client (to do connections to the SQL server) ? ; for I have change the default port to 1521 already.
Regards,
Noob
The above specifically applies to any instance of any type(i.e. default or named) that does not listen on the default port of 1433;
The Browser service answers UDP port 1434, and provides the actual port for the servername or Server\Instance that was requested. It also redirects Dedicated Admin Connections to the right port, if used.
you didn't change the default port, though, you changed one service(the default instance) to listen to a non-standard port.

Hi Lowell,

Thanks for your reply. I would like to highlight that for the client side, I am using SSMS to do the connection to the SQL server.

I have been reading around, and it is mentioned in quite some places that to connect to default instance with non-default port. Below are serveral methods to be used

1) specifying the port in the connector (e.g. IP,port)

2) using alias

3) by changing the global default port on the client/ that the client will use when connecting to default instance.

What I am having issue is that -> am I changing the global default port on the client side ? Using SQL server configuration manager and configuring the SQL Native client (both 32 and 64 bit) under TCP/IP have literally no effect at all when using SSMS to connect to the SQL server.

How do i use method 3) using SSMS ?

It's seem like also that when using SSMS, if the connection is specified by just using IP or Computername , without the instance name (as i am connecting to a default instance), SSMS will still attempt to connect using 1433 instead of 1434.

Hence having a SQL browser service, will not help.

Regards,

Noob