SQL Server Logon Trigger Problems
-
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 @LoginNameVARCHAR(128),
@DBNameVARCHAR(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 @SessionIDINT,
@LoginTimeDATETIME,
@LoginNameVARCHAR(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…
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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.
-
PS:
Lowell, wouldn't the EXECUTE AS control the access to the various tables?
Thanks.
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
OK, I'll give your responses a try!
Thanks...
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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?
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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...
-
also, take a look at this, where you should change the WarningsReturnAsErrors flag in php.ini:
http://msdn.microsoft.com/en-us/library/cc793139(v=sql.90).aspx
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It looks to me like you're checking all connections to the database. If there are many such connections for different users, that could be a drag on the trigger.
So, I suggest using EXISTS() instead of COUNT and, if possible, using @@SPID instead of db name to use rows from sysprocesses. I'm sure you know that sysprocesses is deprecated and that sys.dm_exec_sessions is recommened now instead.
IF EXISTS(SELECT 1
FROM DatabaseLogonAudit.dbo.AuditDatabase ad
INNER JOIN master.sys.sysprocesses sp on sp.spid = @@SPID
INNER JOIN master.sys.dm_exec_connections ec ON ec.session_id = @@SPID
WHERE sp.loginame = @LoginName
AND ad.DatabaseName = @DBName
AND ad.DBLogonAuditRequired = 1
AND ec.Encrypt_option <> 'TRUE')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
The application developer cannot provide me with a copy of a PHP .ini file. The claim is that none exist. I'm waiting for verification of that. This is based on what is addressed at:
In the meantime, I changed the trigger to SET the various options inside the create trigger statement. This was based on what was returned in the SQL Server Error Log:
CREATE TRIGGER [CaptureLogonInfo]
ON ALL SERVER WITH EXECUTE AS 'DatabaseLogonAuditUser'
AFTER LOGON
AS
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
BEGIN
...
This got me past the failure of the application.
I added RAISERROR code to check if I was even getting into the trigger from PHP:
DECLARE @SessionIDINT,
@LoginTimeDATETIME,
@LoginNameVARCHAR(128),
@DBNamevarchar(128),
@ErrorTextvarchar(1000)
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)'),
@DBname = ORIGINAL_DB_NAME ()
SET @ErrorText = 'Outside IF statement for DatabaseLogonAuditUse trigger ' + @DBName
RAISERROR (@ErrorText, -- Message text.
10, -- Severity,
1, -- State,
7, -- First argument used for width.
3, -- Second argument used for precision.
N'SEL') -- Third argument supplies the string.
WITH LOG
For all other applications, except the one PHP we have, this works fine and I log the message/db name in the SQL Server logs.
Since I feel this is a PHP issue, I'm going to concentrate from that end.
Thanks, everyone!
-
maleitzel 96965 (3/21/2013)
I thought maybe somebody had a magic bullet for this!The magic bullet for me was putting the SET options within the trigger to bypass any inconsistencies in 3rd party connection drivers.
Try the below and see if it works.
CREATE TRIGGER [DenyUnencryptedConnection]
ON ALL SERVER WITH EXECUTE AS 'DatabaseLogonAuditUser'
FOR LOGON
AS
SET CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING ON
BEGIN
DECLARE @LoginNameVARCHAR(128),
@DBNameVARCHAR(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
Regards
Chirag
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply