Post reply

Named Pipes connections

  • 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?

  • January 13, 2016 at 9:14 am

    #1851620

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

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

  • January 13, 2016 at 9:30 am

    #1851632

    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?

  • January 13, 2016 at 1:57 pm

    #1851729

    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.

  • January 14, 2016 at 5:46 am

    #1851863

    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'

  • January 14, 2016 at 9:50 am

    #1851987

    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 = ??;

  • January 14, 2016 at 10:01 am

    #1851993

    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!?

  • January 14, 2016 at 10:25 am

    #1852002

    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.

  • January 15, 2016 at 7:24 am

    #1852248

    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?

  • January 19, 2016 at 3:28 am

    #1852680

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

    I'd file this on Connect.

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

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