Logon trigger -- Password did not match that for the login provided. [CLIENT: <named pipe>].

Question

Greetings . SQL2K8.

I had this trigger created in a test region and it worked perfectly for weeks. What the trigger does it look for any application login attempted connections --with the "ap%' prefix, and validates that they are coming in from application servers. It does this by comparing the attempted host connection IP address to a pre-defined list of allowed IP addresses in a table in the Master DB.

Today I attempted to move into Prod, and it blew up. As soon as I created it, I attempted to connect with a login named apTest, and it blew uo with this message.

Date  4/25/2012 8:06:48 AM
Log  SQL Server (Current - 4/25/2012 11:14:00 AM)

Source  Logon

Message
Login failed for user 'logonTriggerExecutor'. Reason: Password did not match that for the login provided. [CLIENT: <named pipe>]

One would think that Named Pipes is not enabled, but it is. It should also be noted that the trigger never needs to supply a password, so I'm not sure how it could be supplied wrong? This is NOT the usual error message seen when a connection is attempted from a non-allowed IP address.

Below is all the code to make this go. Do NOT attempt to reproduce in a Prod environment. All you should need to do is put your IP address into the table, and test it out.

All ideas are appreciated!

use master 
go
CREATE LOGIN [logonTriggerExecutor] WITH PASSWORD=N'Monday01', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [logonTriggerExecutor] FOR LOGIN [logonTriggerExecutor] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', N'logonTriggerExecutor'
GO
CREATE TABLE [dbo].[allowedIPAddresses](
	[myPK] [int] IDENTITY(1,1) NOT NULL,
	[allowedIP] [varchar](50) NULL,
	[serverName] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  DdlTrigger [admin_KillUnauthorizedApLogins]    Script Date: 04/25/2012 10:32:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [admin_KillUnauthorizedApLogins]
ON ALL SERVER with execute as 'logonTriggerExecutor'
FOR  logon
AS 
	SET CONCAT_NULL_YIELDS_NULL ON
	declare @data xml
	declare @ClientHost nvarchar(100)
	declare @LoginName nvarchar(100)
	SET @data = EVENTDATA()
	set @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
	set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
	
	if @LoginName like 'ap%'
	begin
			if @clientHost not in (select allowedIP from dbo.allowedIPAddresses where allowedIP = @ClientHost)
			rollback
					
	end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--disable TRIGGER [admin_KillUnauthorizedApLogins] ON ALL SERVER
GO

---

TIA, ChrisRDBA

Answer 1

I also just noticed this message right after the last message:

Login failed for user 'apTest'. Reason: Could not find a login matching the name provided. [CLIENT: <named pipe>]

This is also NOT a message typically found when a valid IP address is missing from the table. Those are:

Logon failed for login 'apTest' due to trigger execution. [CLIENT: 10.223.14.116]

---

TIA, ChrisRDBA

Answer 2

I attempted to reproduce in a Test region by disabling Named Pipes. Even with it disabled, the trigger still executes as expected.

---

TIA, ChrisRDBA

Answer 3

Try taking up a profiler trace to analyze your login failure and if there is an issue in executing the login trigger.

http://sqltroubles.wordpress.com

Please mark as Answered if helped

---

Kris

Answer 4

Try taking up a profiler trace to analyze your login failure and if there is an issue in executing the login trigger.

http://sqltroubles.wordpress.com

Please mark as Answered if helped

---

Kris

The trace doesn't reveal anything I don't already know, thanks. Think I'll reboot every the weekend and see if it helps. Will advise.

---

TIA, ChrisRDBA

Answer 5

Try taking up a profiler trace to analyze your login failure and if there is an issue in executing the login trigger.

http://sqltroubles.wordpress.com

Please mark as Answered if helped

---

Kris

The trace doesn't reveal anything I don't already know, thanks. Think I'll reboot every the weekend and see if it helps. Will advise.

---

TIA, ChrisRDBA

Didn't help at all. Any ideas?

---

TIA, ChrisRDBA

Answer 6

A coworker of mine discovered the resolution. In the test region, we use port 1433, in Prod we don't. All of these attempted connections were using Mgmt Studio. By default, it usues named pipes. However, if the port is specified in the Mgmt Studio, the connection is made via TCPIP as expected.

---

TIA, ChrisRDBA

Answer 7

Hey Mr. SQLMaster,

did you see that this thread is from May 2012?
Seems to be spam / advertisements, isn't it.

And - with respect - if i have to use a third Party solution to avoid brute force attacks i wouldn't call me "MSSQLMaster" :)

Configure your firewall and change the port of your default instance - nothing else is needed!

---

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)