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: Today @ 11:31 AM
Points: 12,901, Visits: 32,136
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: Today @ 12:03 PM
Points: 2,121, Visits: 3,206
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: Tuesday, September 9, 2014 1:23 PM
Points: 8, Visits: 139
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse