October 19, 2011 at 8:08 am
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.
October 19, 2011 at 1:06 pm
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
October 19, 2011 at 1:26 pm
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
October 19, 2011 at 2:24 pm
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.
October 19, 2011 at 5:38 pm
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.
October 19, 2011 at 6:24 pm
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.
October 19, 2011 at 7:36 pm
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
October 20, 2011 at 5:50 am
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?
October 20, 2011 at 9:18 am
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
October 20, 2011 at 9:27 am
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.
October 20, 2011 at 10:45 am
Perhaps it just ignores the setting when all are disabled, I'm not sure..
CEWII
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply