Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Logon trigger -- Password did not match that for the login provided. [CLIENT: <named pipe>].

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

  • Wednesday, April 25, 2012 6:50 PM
     
      Has Code
    Sign In to Vote
    0

    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

     

All Replies

  • Wednesday, April 25, 2012 7:07 PM
     
     
    Sign In to Vote
    0

    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

     
  • Wednesday, April 25, 2012 9:08 PM
     
     
    Sign In to Vote
    0
    I attempted to reproduce in a Test region by disabling Named Pipes. Even with it disabled, the trigger still executes as expected.

    TIA, ChrisRDBA

     
  • Wednesday, April 25, 2012 11:38 PM
     
     
    Sign In to Vote
    0

    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

     
  • Friday, April 27, 2012 7:21 PM
     
     
    Sign In to Vote
    0

    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

     
  • Wednesday, May 02, 2012 4:23 PM
     
     
    Sign In to Vote
    0

    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

     
  • Wednesday, May 09, 2012 5:57 PM
     
     Answered
    Sign In to Vote
    0
    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


    • Edited by ChrisRDBA Wednesday, May 09, 2012 5:58 PM
    • Marked As Answer by ChrisRDBA Wednesday, May 09, 2012 5:58 PM
    •  
     
  • Saturday, April 20, 2013 10:43 AM
     
     
    Sign In to Vote
    0

    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)