Client Protocols in SQL Server Configuration Manager

  • Henry_Lee

    SSCrazy

    Points: 2714

    Good day,

    SQL Server Configuration Manager --> SQL Native Client 10.0 Configuration --> Client Protocols

    I am confused about what connections the Client Protocols apply to.

    I am on ServerA and I have disabled all 4 client protocols - (Shared Memory, TCP/IP, Named Pipes, VIA) but yet I can still connect to the local instance as well as remote instances using both SSMS and sqlcmd. Additionally, I can connect to ServerA remotely.

    Do the client protocols apply to:

    Remote clients trying to reach ServerA?

    Local client trying to reach ServerA (meaning SSMS on the same box)?

    Local client trying to reach ServerB?

    Thanks.

  • Jack Corbett

    SSC Guru

    Points: 184381

    If you want to disable access to the SQL Server for specific protocols then you want to SQL Server Network Configuration, not the SQL Server Native Client Configuration. The SQL Server Native Client Configuration is for the machine you are on. From BOL:

    The settings configured in SQL Server Native Client Configuration, are used on the computer running the client program. When configured on the computer running SQL Server, they affect only those client programs running on the server.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Henry_Lee

    SSCrazy

    Points: 2714

    Thanks for the response Jack.

    I completely understand the network settings. It's the client settings I'm confused about.

    That BOL explanation makes sense and that's how I was initially thinking about it, however, no matter what changes I make I don't see any difference.

    For example, if I'm on ServerA and I disable all the client protocols how come I can still connect to the local instance and remote instances using both SSMS and sqlcmd?

    Or another example, if I only enable say Named Pipes and then connect to a server, why does the following query return TCP?

    SELECT c.net_transport FROM sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id WHERE s.session_id=@@spid

  • R_S

    Ten Centuries

    Points: 1160

    I think you are not recycling the SQL Service. Any change in protocols require you to restart the services.

    Also, if you disable all the protocols, you won't even be able to start the services.

  • SQLALX

    SSC Eights!

    Points: 956

    I think this is an interesting issue. Since Henry_Lee is talking about client protocols, not server protocols, SQL server recylcling is not required.

    My understanding is that SSMS is trying to connect to SQL Server by using different possible protocols. If Server Network Config allows TCP protocol and not allowing Named Pipe, TCP connection is made for local server (by using SSMS in my case) even if Client Network protocols config does not allow TCP and only allows NP.

    And I don't think this is due to cached connection.

    http://www.sqlmgmt.com

  • Henry_Lee

    SSCrazy

    Points: 2714

    I think this is an interesting issue. Since Henry_Lee is talking about client protocols, not server protocols, SQL server recylcling is not required.

    Agreed.

    I'm on my home machine now - Win7 x64, SS 2008 SP2 x64 Developer Edition. I disabled all client protocols, restarted SSMS, restarted the SQL services just for kicks, yet I can still connect to my local instance using SSMS and sqlcmd. I don't see that these client protocols do anything.

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    Locally you almost always connect via shared memory, the fact that you say you disabled that one too is interesting since it is only useful for local connections. One of the thing it does is control order by which the protocols are tried, you should see an "order" column that is there for a reason. By default it is Shared Memory, TCP/IP, then Named pipes. If it isn't local it starts with TCP/IP and then named pipes.. As to why you are stil able to connect.. Not sure..

    CEWII

  • Henry_Lee

    SSCrazy

    Points: 2714

    Thanks for the response Elliot.

    I do understand the Shared Memory protocol - I disabled it in an effort to understand the affects of changing these Client Protocols.

    I am also familiar with the Order column and what it purports to do, however, again, changing it appears to have no affect. For example, the current order is Shared Memory first, Named Pipes second and TCP/IP third. I then connected to a remote instance and the following query returned TCP:

    SELECT

    c.net_transport

    FROM

    sys.dm_exec_sessions s

    LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

    WHERE

    s.session_id = @@spid

    Named Pipes is enabled on the remote server and my client protocols indicate Named Pipes should be tried prior to TCP/IP, so shouldn't I have connected over Named Pipes?

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    That depends, I stopped using named pipes as a default protocol nearly a decade ago because of problems connecting. So while it is enabled I never want to use it.

    But given network topology and network configuration you may not be able to reliably connect over named pipes so in your case it would fall back to TCP/IP.

    CEWII

  • Henry_Lee

    SSCrazy

    Points: 2714

    Interesting thought, Elliot, you're suggesting it did in fact try Named Pipes first, but failed and then went on to TCP/IP. Perhaps. However, that wouldn't explain how I connect when all protocols are disabled.

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    Perhaps it just ignores the setting when all are disabled, I'm not sure..

    CEWII

Viewing 11 posts - 1 through 11 (of 11 total)

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