Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Logon Trigger Problems Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 8, Visits: 150
Hello,
I have two business needs to deny logon to any connection that is accessing SQL Server with a session that is not encrypted (we use SSL and the force encryption option cannot be set due to various third party app problems) and I need to capture certain audit data for each successful logon to SQL Server.

I created two logon triggers to do this.

The problem is that the two triggers work fine and as expected when testing through SQL Server Management Studio.

The check for encryption trigger fails with the infamous ‘login fails due to trigger execution’ error when using any .Net app (SSIS, SSRS, VB program, etc.).

The audit trigger works fine from all our applications except one written in PHP.

I created a table that stores the dbid, dbname, an encrypted required indicator and an audit required indicator. I use this to control if the connections are denied or if auditing of the database connection is required.

We use SQL Server 2008 R2 SP2 (build 10.50.4000).

Example of the deny logon trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DenyUnencryptedConnection]
ON ALL SERVER WITH EXECUTE AS 'DatabaseLogonAuditUser'
FOR LOGON
AS
BEGIN
DECLARE @LoginName VARCHAR(128),
@DBName VARCHAR(128)
SELECT @LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
@DBName = ORIGINAL_DB_NAME ()
IF (SELECT COUNT(ad.DatabaseName)
FROM DatabaseLogonAudit.dbo.AuditDatabase ad
INNER JOIN master.sys.sysprocesses sp on sp.dbid = ad.DatabaseId
INNER JOIN master.sys.dm_exec_connections ec ON ec.session_id= sp.spid
WHERE sp.loginame = @LoginName
AND ad.DatabaseName = @DBName
AND ad.DBLogonAuditRequired = 1
AND ec.Encrypt_option <> 'TRUE') > 0
ROLLBACK;
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Example of the audit trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [CaptureLogonInfo]
ON ALL SERVER WITH EXECUTE AS 'DatabaseLogonAuditUser'
AFTER LOGON
AS
BEGIN
DECLARE @SessionID INT,
@LoginTime DATETIME,
@LoginName VARCHAR(128)
SELECT @SessionID = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
@LoginTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar (128)'),
@LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')
IF (SELECT COUNT(AuditDatabaseIdn) FROM databaseLogonAudit.dbo.AuditDatabase
WHERE DatabaseName = ORIGINAL_DB_NAME ()
AND DBLogonAuditRequired = 1) > 0
INSERT INTO DatabaseLogonAudit.dbo.AuditLogin
(ServerName,
LoginTime,
LoginName,
DatabaseName,
Session_id,
HostName
)
VALUES
(
@@Servername,
@LoginTime,
@LoginName,
ORIGINAL_DB_NAME (),
@SessionID,
HOST_NAME()
);
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


Has anyone had unpredictable results using logon triggers and are there any steps/processes that I may be missing in implementing these?

Thanks…
Post #1433412
Posted Wednesday, March 20, 2013 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
this error:
The check for encryption trigger fails with the infamous ‘login fails due to trigger execution’ error when using any .Net app (SSIS, SSRS, VB program, etc.).

is normal for a login trigger;

the connecting user cannot get any custom error message raised by the trigger...that error goes into the SQL log, but not to the connecting user.
they get stopped, but it might not be obvious what the error was.

some issues might be raised because you are using tables the connecting user has no access to at all: ie the specific table (DatabaseLogonAudit.dbo.AuditDatabase) ; and maybe some of the sys views the end user might not have access to: you might need to grant SELECT ON DatabaseLogonAudit.dbo.AuditDatabase to public to fix that issue.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433426
Posted Wednesday, March 20, 2013 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 8, Visits: 150
Lowell, thanks for the quick reply. However, the error message occurs due to the trigger failing to execute properly. I failed to mention that.

If I test the trigger through a .Net application where a db does not require encryption and the connection is not encrypted, the same message is displayed.

So, for both the true and false condition you get the same results.

I know that the message appears for the dbs that we require encryption for when testing through SSMS and I'm fairly confident that the trigger works fine in SSMS.

Thanks, again.
Post #1433434
Posted Wednesday, March 20, 2013 1:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 8, Visits: 150
PS:

Lowell, wouldn't the EXECUTE AS control the access to the various tables?

Thanks.
Post #1433439
Posted Wednesday, March 20, 2013 1:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
again, no matter what the REAL ERROR is inside the trigger (ie Invalid object name 'DatabaseLogonAudit.dbo.AuditDatabase '. or user does not have access,
the generic error message is all they get.


pretty sure the issue is permissions on the table DatabaseLogonAudit.dbo.AuditDatabase

that's probably a database created by a sysadmin with no users in it at all..hence the trigger fails for anyone not a sysadmin (like when you test it yourself in SSMS..our an admin!)

its super easy to test this yourself, with SSMS:
create a brand new login,and use SSMS to login with it

create login ClarkKent with password = 'NotARealpassword'


that login will fail with the generic error message.
if you look in the SQL log, however, you will see something like this:



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433445
Posted Wednesday, March 20, 2013 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 8, Visits: 150
OK, I'll give your responses a try!

Thanks...
Post #1433446
Posted Wednesday, March 20, 2013 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
fixed the link for the screenshot, so you can see an example of how other errors occur in the trigger, but only the generic error gets sent to the user

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433459
Posted Thursday, March 21, 2013 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 8, Visits: 150
I thought of this last night and here are some conclusions and test results for using the audit logon trigger. At this point I am not debugging the deny logon trigger.

Database A has DBAUser and requires auditing of logons to the database. The application is using VB.

Database B has DBBUser, requires auditing and the application is written in PHP.

The audit trigger uses an EXECUTE AS statement using a SQL Server login with elevated SQL Server permissions.

DBAUser and DBBUser are not explicitly users in the Audit database and the public role does not have select permissions to the Audit db.

When connecting to SQL Server using SSMS and either the DBAUser or DBBUser credentials, the logon trigger fires and I capture the info I need.

My first conclusion is that running the trigger under the EXECUTE AS context works exactly as planned. I do not need to give any db users or public permissions to the Audit database.

When using the VB application and DBAUser credentials, the logon trigger fires and works fine.

When using the PHP application and DBBUser the trigger fails to even run and the application aborts and a trigger error is logged to the SQL Server logs:

Logon failed for login 'DBBUser' due to trigger execution

The conclusion here is that PHP has something different in the connection to SQL Server that causes a logon trigger to behave differently than VB.

Any additional thoughts on this?
Post #1433746
Posted Thursday, March 21, 2013 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
gotcha, so it works correctly most of the time, but not correctly when accessing via PHP.
When using the PHP application and DBBUser the trigger fails to even run and the application aborts and a trigger error is logged to the SQL Server logs:
Logon failed for login 'DBBUser' due to trigger execution

PHP my be setting different ANSI Settings/ variables than you would normally have, from say, and SSMS connection.

definitely look at the SQL logs; if you find the failed login for PHP, you will find the real, exact error that occurred inside the trigger, and can diagnose from there a lot better.
my first guess is the SET options like you see here:




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433749
Posted Thursday, March 21, 2013 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 8, Visits: 150
Yeah, I spun my wheels on the SET options earlier and will need to take a big look at this again...

I thought maybe somebody had a magic bullet for this!

Thanks...
Post #1433751
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse