Named Pipes connections

Question

Post reply

Named Pipes connections

simon.letts

Hall of Fame

Points: 3876
More actions
January 13, 2016 at 8:59 am

#399564

So I have a SQL2012 server where I have used SQL Configuration Manager to disable Named Pipes as usual.
However I noticed in the errorlog shortly afterwards that there were some connection\access (infrastructure) errors, probably from our monitoring software (SCOM) that checks DB health every 15 minutes.
So I gave the relevant account 'connect' on the TSQL Named Pipes endpoint (which it allowed even though it’s disabled)
And after that the login errors disappeared. I can also see these successful logins now using profile trace, as:
Audit login -- network protocol: Named Pipes
However Named pipes is still disabled in SQL Config Mgr so how come I can grant the endpoint, and it allows named pipes connections?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

There are cases where applications will fall back to TCP/IP, which might be happening.

http://www.sqlservercentral.com/Forums/Topic988886-1550-1.aspx

simon.letts Hall of Fame Points: 3876 More actions · Answer 2

Thanks Steve, but shouldn't I expect to see the connections as TCP/IP if that happens? I still see it as 'Named Pipes'

I also can't see anything in the link you mentioned that suggests it will fall back to TCP/IP if Named Pipes is disabled. The thread actually talks about Apps\Conns failing when one or the other is turned off?

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 3

Sorry, I thought I read a link that showed connections falling back, or switching to TCP/IP. I think I had the wrong link there. My apologies.

I've been looking around and I don't see a place where clients should be able to connect with named pipes disabled (using that protocol).

Has the server been restarted? Meaning, these aren't old connections. If you kill the connection, does it reconnect with NP? If you look in the errorlog for startup, is the server listening on NP?

I might try changing the pipe name (/query1 or something) and see if this causes errors for clients.

simon.letts Hall of Fame Points: 3876 More actions · Answer 4

Hey Steve, no problem, v grateful for the response!

I've had a further look and yes the server has been restarted a few times. What's interesing is that although Named Pipes is disabled in Config Mgr for both 'Client protocols', when I query sys.endpoints the state = 0 (enabled, like all others) and state_desc is started, so there doesn't appear to be a correlation.

I then ran ALTER ENDPOINT [TSQL Named Pipes] STATE = STOPPED, I can now see in sys.endpoints that the state and description has changed to stopped, I've bounced SQL, but it still comes up in the log as listening for it ie...

Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ]...or is this something else and not related to named pipes?? In the properties for named pipes in Config Mgr it's set to 'sql\query'

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 5

This is a bit strange. I have NP disabled, but I see it started with this

select * from sys.endpoints

Can you run this:

alter endpoint [TSQL Named Pipes] state = STOPPED

Can someone still connect?

If you look at the connections with this, do you see np?

select net_transport, endpoint_id, connect_time,client_net_address from sys.dm_exec_connections

where session_id = ??;

simon.letts Hall of Fame Points: 3876 More actions · Answer 6

Hi, yes the first two queries are what I mentioned I'd run already. It is still stopped in sys.endpoints

The last query doesn't show any named pipes connections at the moment, but I guess they could randomly come in. I'll set up something to check, or maybe look for an extended event to trace it. I suspect they come in though as it was the endpoint access on Named Pipes I had to grant initially to allow the monitor to run, and it's not failing even though everything is disabled!?

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 7

This is strange. If I stop the endpoint, I get an error (attached).

If I start the endpoint, I can connect with this in SSMS:

Server: np:\\.\pipe\MSSQL$SQL2016\sql\query

Windows Auth credentials

I'd check this for you, get the pipe from the log or Config Mgr. Then I might set up a trace looking for connections with named pipes.

simon.letts Hall of Fame Points: 3876 More actions · Answer 8

Hi Steve, yes I get the same thing. If the endpoint is stopped I can't connect using np:.... but if it's started I can.

However all this time

a) the Named Pipes Protocol in SQL Config is set to disabled

b) the SQL errorlog displays 'Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ]' whatever the state of the protocol and whether the Endpoint using ALTER ENDPOINT is set to stopped or started.

So there seesm no point in setting a) as it makes no difference, and the errorlog entry in b) also appears to be misleading as whatever combination of the endpoint being stopped\started and\or the protocol being disabled\enabled, this message says it's ready for connections!??! Is that one for Microsoft?

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 9

Sounds like a bug of some sort. I wonder if there's some restart bug that enables NP?

I'd file this on Connect.