Post reply

Audit Trail

  • May 11, 2006 at 7:03 am

    #114636

    All,

    A while back, an article appeared here (http://www.sqlservercentral.com/columnists/spustovit/easyauditingasharedaccount.asp&nbsp that talked about how to implement an auditing scheme.  I modified the stored procedure to meet our needs and implemented for testing on a couple of our databases (one full-blown SQL and the other was MDAC 2.8).  The CONTEXT_INFO field is being properly updated in the master.dbo.sysprocesses table to the GUID of the user's record. 

    On the tables that I want to track I have added the following trigger:

                            UPDATE MyTable

                            SET SQLLastUpdated = GetDate(),

                            SQLEmployeeID = dbo.fnGetContextLogin(),

                            SQLIPAddress = dbo.fnGetMACAddress()

                            FROM Inserted i

                            WHERE i.SQLGUID = MyTable.SQLGUID

    where the fnGetContextLogin() function does a reverse lookup to the password table based on the CONTEXT_INFO and the fnGetMACAddress() function gets the MAC Address of the user's computer from the sysprocesses table.  This is then used to do some synchronization of tables between the master database and some laptops.

    This works fine on the laptops, which are running MDAC 2.8.  However, when

    running it on the full-blown SQL, I am receiving an error when

    attempting to add or update a record.  On an insert, I get an error about

    returning more than one record from a sub-query and it then adds three

    records to the table.  On an update, it just gives the error message and

    won't update the record.  Both errors reference the probable cause as the

    Vehicle record.  I did some experimenting with the trigger and have been

    able to isolate the problem to the fnGetContextLogin function.

    This is the function:

    CREATE  FUNCTION dbo.fnGetContextLogin()

    RETURNS varchar(50)

    AS

    BEGIN

        DECLARE @login varchar(50)

        SELECT @login = EmpNo FROM PwdMast WHERE convert(varbinary(128),SQLGUID)

    =

        (SELECT context_info FROM master..sysprocesses(Nolock) WHERE

    context_info <> 0x00000 and sid =

        (SELECT sid FROM master..sysprocesses(Nolock)WHERE spid = @@SPID))

        IF @login is NULL SET @login = SUSER_SNAME()

        RETURN(@Login)

    END

    Under normal circumstances, this SELECT statement would look like this:

        SELECT @login = EmpNo FROM PwdMast WHERE convert(varbinary(128),SQLGUID

    =

        (SELECT CONTEXT_INFO FROM Master..sysprocesses(Nolock) WHERE spid ==

    @@SPID)

    however, due to the front-end language's penchant for creating and destroying threads into the database all the time, the above format is required.

    I've tried to think of other ways to do this, but I'm running out of ideas.

    Any ideas?

    Thanks, in advance,

    Steve Shurts

  • May 11, 2006 at 10:15 am

    #637326

    Maybe a TOP 1 from sysprocesses?

    I'd think you could use suser_sname() for the user name. For the MAC I think you are stuck with sysprocesses.

  • May 11, 2006 at 10:24 am

    #637332

    Thanks for your input.

    Unfortunately, we are not using Windows Authentication; therefore, the suser_sname is not unique.  This methodology overcomes the limitation of having a bunch of users that are logged on as DBUser, for example.  I tried the TOP 1 approach earlier, but it didn't help, so I reverted back to not having it.

    One thing I have discovered since I posted this: it is very specific to servers that have SP4 installed.  If the server is SP3 or below, it works fine.

     

     

  • May 12, 2006 at 2:31 am

    #637466

    The reason for the error is the fact that you have multiple sysprocesses records added via the "join" with context_info, which you control. You are effectively asking for all sysprocess records that still exist with the same contect_info as the current SPID's . 

    What I do not understand is why @@SPID is not enough, as it is the connection that initiated the trigger, right?

    I am convinced, but have no real proof, that SP4 does contribute by allowing OLE DB Resource Pooled SPIDs to stay alive longer than SP3.

    You might try changing the connection string to control OLE DB Resource Pooling:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

    Table 4. Setting OLE DB Services by Using ADO Connection String Attributes

    Services enabled Value in connection string

    All services (the default) "OLE DB Services = -1;"

    All services except pooling  "OLE DB Services = -2;"

    All services except pooling and auto-enlistment "OLE DB Services = -4;"

    All services except client cursor "OLE DB Services = -5;"

    All services except client cursor and pooling "OLE DB Services = -6;"

    No services "OLE DB Services = 0;"

    I swear that some Dec / Jan patch also is contributing as I finally turned off Login failure notification as failed "login" or initial connections were retried for 6 hours. However uninstalling these patches did not cure the issue.

    Andy

  • May 12, 2006 at 6:00 am

    #637505

    Andy,

    Thanks for your response.  I am going to pursue your connection string idea as it seems promising.  As far as the @@SPID goes, if use just the @@SPID, it gives me the thread that is performing the update.  While that's unique right now, over a period of several logins and several days, my spid today may not be my spid tomorrow.  If I use the loginame for the @@SPID, I will get one of three values: generically dbadmin, dbuser, or dbreader.  That's because we are using mixed mode authentication and have generic user names (I know - not a good idea, but I lost that argument...). 

    In order to set the value of the update field to the "real" user identity, we are using the process indicated.  The CONTEXT_INFO field is then updated to the SQLGUID of the user's record in the password table, which is maintained by our program.  Worked great until someone decided to throw SP4 into the mix... 

    BTW, as far as adding the three records, I discovered that is being done programmatically.  There is some auto-generated code that makes three attempts to add the new record before failing.  Because the trigger is returning an error, the program thinks the insert has failed and keeps trying to add the new record -- so that's a red herring. 

    Thanks, again,

    Steve

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply