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 Thursday, March 21, 2013 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 13,620, Visits: 34,779
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

--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 #1433752
Posted Thursday, March 21, 2013 12:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:23 PM
Points: 2,966, Visits: 4,529
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1433954
Posted Monday, March 25, 2013 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 4, 2015 11:55 AM
Points: 8, Visits: 179
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:

http://social.technet.microsoft.com/wiki/contents/articles/1258.accessing-sql-server-databases-from-php.aspx

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 @SessionID INT,
@LoginTime DATETIME,
@LoginName VARCHAR(128),
@DBName varchar(128),
@ErrorText varchar(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!
Post #1434886
Posted Thursday, February 26, 2015 10:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 326, Visits: 346
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 @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

Regards
Chirag
Post #1663887
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse