Announcement

Collapse
No announcement yet.

Tracking user Logon and Logoff activity in database (secure)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Tracking user Logon and Logoff activity in database (secure)

    Although tracking logon and logoff activity by a user to a file is very easy, it is also insecure. Users can alter the log files and change them to masquerade their activities. So I created a more secure way to track a users logon and logoff activity. I managed to track these activities to a SQL database by using scripts and a group policy. This is what you need:
    • Microsoft SQL Server Express
    • A group policy
    • Some scripts
    This method prevents that users can alter the logs and do our computers harm without proper logging. It is using Windows Integrated Security for connecting to SQL.

    Let’s start by make the SQL Server environment up and running.
    1. Install Microsoft SQL Server Express edition on a server, if this is not done yet.
      Don’t forget to enable TCP/IP and Named Pipes in SQL
    2. Add the Domain Users group as a login to the SQL Server by running this query (replace <domainName> with the name of your AD Domain):
      Code:
      USE [master]
      GO
      CREATE LOGIN [<domainName>\domain users] FROM WINDOWS;
      GO
    3. Create a linked server for AD cross checking
      Code:
      EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
    4. Create a database by running this query (replace <databasename> with the name of the database you would like to create):
      Code:
      CREATE DATABASE <databasename>;
    5. Create a table by running this query (replace <databasename> with the name of the database you created in the previous step and replace <tablename> with the name of the table you would like to create):
      Code:
      USE <databasename>
      GO
      CREATE TABLE <tablename>
      (
      Id int PRIMARY KEY IDENTITY,
      Computername varchar(255) NOT NULL,
      Username varchar(255) NOT NULL,
      Timestamp datetime NOT NULL,
      Action varchar(3) NOT NULL
      )
    6. Create a login for Domain Users on your database by running this query (replace <databasename> with the name of the database you created at step 2 and replace <domainName> with the name of your AD Domain):
      Code:
      USE [<databasename>]
      GO
      CREATE USER [<domainName>\domain users] FOR LOGIN [<domainName>\domain users]
      GO
    7. Grant Domain Users only INSERT rights so regular users Logon and Logoff actions will be registered but cannot be changed or removed by running this query (replace <databasename> with the name of the database you created at step 2 , replace <tablename> with the name of the table you created at step 3 and replace <domainName> with the name of your AD Domain):
      Code:
      USE [<databasename>]
      GO
      GRANT INSERT ON <tablename> TO [<domainName>\domain users];
      GO
    8. Create a SQL trigger for Insert actions to prevent bogus inserts by running this query (replace <databasename> with the name of the database you created at step 2 , replace <tablename> with the name of the table you created at step 3, replace <domainName> with the name of your AD Domain and replace <DistinguishedNameOfDomain> (mention the 2 >> in the code below) by the Distingushed Name of your domain.):
      Code:
      USE [<databasename>]
      GO
      create trigger [dbo].[Check_Insert]
      ON [dbo].[<tablename>]
      FOR INSERT
      AS
      declare @adsiQuery NVARCHAR(1000),@sql NVARCHAR(1000), @params NVARCHAR(500), @waarde int, @ComputernameConnected varchar(255),@UsernameConnected varchar(255), @UsernameInserted varchar(255), @ComputernameInserted varchar(255), @AllOkay BIT
      BEGIN
      	Select @UsernameInserted = INSERTED.Username FROM inserted
      	Select @ComputernameInserted = INSERTED.Computername FROM inserted
      	Set @ComputernameConnected=HOST_NAME()
      	Set @UsernameConnected=replace(SUSER_SNAME(),'<domainName>\','')
      	
      	Set @AllOkay = 1
      
      	-- Check if inserted username is equal to connected username
      		if UPPER(@UsernameConnected)<>UPPER(@UsernameInserted)
      		begin
      			Set @AllOkay=0
      		end
      
      	-- Check if name of the computer from where data is inserted is in Active Directory
      		SET @adsiQuery =N'SELECT @waardeOUT=COUNT(*) FROM OPENQUERY(ADSI,''<LDAP://<DistinguishedNameOfDomain>>;(&(objectCategory=computer)(name=<compname>*));cn,ADsPath,objectCategory;subtree'')'
      		set @sql=REPLACE(@adsiQuery,'<compname>',upper(HOST_NAME()))
      		set @params = N'@waardeOUT INT OUTPUT'
      		EXEC SP_EXECUTESQL @sql, @params, @[email protected] OUTPUT
      		
      		if @waarde<=0 
      		begin
      			Set @AllOkay=0
      		end
      
      	-- Check if inserted computername is in equal to the name of the computer from where data is inserted
      		if UPPER(@ComputernameConnected)<>UPPER(@ComputernameInserted)
      		begin
      			Set @AllOkay=0
      		end	
      
      	if @AllOkay=0
      	begin
      		rollback
      	end
      END
      Just to be sure! Check that you replaced only in > character the SET @adsiQuery line. It must look something like this:
      Code:
      SET @adsiQuery =N'SELECT @waardeOUT=COUNT(*) FROM OPENQUERY(ADSI,''<LDAP://DC=domain,DC=local>;(&(objectCategory=computer)(name=<compname>*));cn,ADsPath,objectCategory;subtree'')'
    I must warn you that you cannot not run all SQL commands listed above from within one query window. Just run all code as separate queries.

    Now that SQL is up and running let’s create the scripts:

    1. Create a folder on some server
    2. Remove the automatic inheritance of permissions from the parent object on the newly created folder
    3. Remove all groups and users from the security tab except for the <Creater Owner>, <System> and <administrators> groups
    4. Add the Domain Users group an grant this group Read permissions on the folder
    5. Share the newly created folder as LogonLogoffTracking and grant Domain Admins full and Domain Users read permissions on this share
    6. Create the following VB script file in the newly created folder and name it TrackLogonLogoff.vbs:
      Code:
      Const DB_CONNECT_STRING = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=<sqlserver>;Initial Catalog=<databasename>"
       
      Dim WSHShell, WSHNetwork, objDomain, UserString
       
      Set myConn = CreateObject("ADODB.Connection")
      Set myCommand = CreateObject("ADODB.Command" )
       
      Set args = Wscript.Arguments
       
      Set WSHShell = CreateObject("WScript.Shell")
      Set WSHNetwork = CreateObject("WScript.Network")
       
      'Automatically find the domain name
      Set objDomain = getObject("LDAP://rootDse")
      DomainString = objDomain.Get("dnsHostName")
       
      'Grab the user name
      UserString = WSHNetwork.UserName
       
      'Grab the computer name for use in add-on code later
      strComputer = WSHNetwork.ComputerName
       
      myConn.Open DB_CONNECT_STRING
      Set myCommand.ActiveConnection = myConn
      myCommand.CommandText = "INSERT INTO [<tablename>] (Computername,Username,Timestamp,Action) VALUES ('" & strComputer & "','" & UserString & "', CURRENT_TIMESTAMP,'" & args(0) & "')"
      myCommand.Execute
      myConn.Close
    7. Search and replace the following in the VBS file:
      • Replace <sqlserver> by the name of your SQL server.
        If you are not using the default instance replace <sqlserver> by the full name of your instace in the syntax <servername>\<instancename>
      • Replace <databasename> by the name of your database which you have created earlier
      • Replace <tablename> by the name of your table which you have created earlier
    8. Save all changes to the VBS file
    And finally let’s create a group policy (assuming you are using Windows 200:

    1. Create a policy
    2. Go to
      Code:
      User Configuration \ Policies \ Windows Settings \ Scripts (Logon/Logoff)
    3. Double click “Logon”
    4. On the Scripts tab click on “Add”
    5. In the field “script name” enter the full path and filename of the VBS script like this (replace <servername> by the name of your server on which you have created the shared folder earlier):
      Code:
      \\<servername>\LogonLogoffTracking\TrackLogonLogoff.vbs
    6. In the field “script parameters” enter these parameters:
      Code:
      //NoLogo //B ON
      //NoLogo does not display any VBScript version info
      //B runs the script completely silently (perhaps //NoLogo is not needed?)
      ON is the argument to write the word ON in the action field of your SQL table which means that the users has Logged on to the mentioned computer
    7. Double click “Logoff”
    8. On the Scripts tab click on “Add”
    9. In the field “script name” enter the full path and filename of the VBS script like this (replace <servername> by the name of your server on which you have created the shared folder earlier):
      Code:
      \\<servername>\LogonLogoffTracking\TrackLogonLogoff.vbs
    10. In the field “script parameters” enter these parameters:
      Code:
      //NoLogo //B OFF
      OFF is the argument to write the word OFF in the action field of your SQL table which means that the users has Logged off on the mentioned computer
    11. Link the policy to one or more Organization Units in your AD
    Please, let me know if this helps you or if it gives you any troubles.
    Last edited by jasperkimmel; 26th August 2013, 12:40. Reason: Added security by using a linked server and a SQL trigger

  • #2
    Re: Tracking user Logon and Logoff activity in database (secure)

    Alternatively use auditing of logon/logoff events configured via group policy
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Tracking user Logon and Logoff activity in database (secure)

      I know about that, but that is always a hell of a job to track. I created this for a school with over 400 student computers. They wanted to track user logon and logoff activity so they could link abuse to a user.

      Comment


      • #4
        Re: Tracking user Logon and Logoff activity in database (secure)

        First of all, thanks for the detailed post and taking time to share your solution... now Ill sound like an ass for saying it and I apologize in advance but this leaves you open to a student knocking over your sql box using scripted inserts.

        Use the auditing... its made specifically for this type of activity.
        Rules of life:
        1. Never do anything that requires thinking after 2:30 PM
        2. Simplicity is godliness
        3. Scale with extreme prejudice


        I occasionally post using a savantphone, so please don't laugh too hard at the typos...

        Comment


        • #5
          Re: Tracking user Logon and Logoff activity in database (secure)

          Originally posted by userPrincipalName View Post
          First of all, thanks for the detailed post and taking time to share your solution... now Ill sound like an ass for saying it and I apologize in advance but this leaves you open to a student knocking over your sql box using scripted inserts.

          Use the auditing... its made specifically for this type of activity.
          good call.

          you're not sanitising inputs.. if they find the script (and let's face it, kids on school networks are wily) then someone will work out how to insert something.

          It might only be harmless like "INSERT INTO [<tablename>] (Computername,Username,Timestamp,Action) VALUES ('" mr Teacher "','" is wanker "', CURRENT_TIMESTAMP,'" & args(0) & "')"

          or might be worse and they inject a shell or similar....
          Please do show your appreciation to those who assist you by leaving Rep Point https://www.petri.com/forums/core/im.../icon_beer.gif

          Comment


          • #6
            Re: Tracking user Logon and Logoff activity in database (secure)

            And of course one of the students might be Bobby Tables
            Tom Jones
            MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
            PhD, MSc, FIAP, MIITT
            IT Trainer / Consultant
            Ossian Ltd
            Scotland

            ** Remember to give credit where credit is due and leave reputation points where appropriate **

            Comment


            • #7
              Re: Tracking user Logon and Logoff activity in database (secure)

              You are right userPrincipalName and you are not an ass but one of those guys/girls who are aware of these kinds of problems.

              In my opinion auditing is still not the solution.

              Can SQL Logon Triggering be of any use? An user may be prevented to access the SQL server several times in a certain time window. For example: an user will not be allowed to access the SQL server more than 2 times in the last 10 minutes.

              Comment


              • #8
                Re: Tracking user Logon and Logoff activity in database (secure)

                I'm sure you could script something that does a timebound deny on inserts.it shouldn't be that difficult but any timr you allow inserts to a human user you expose yourself to abuse...
                Rules of life:
                1. Never do anything that requires thinking after 2:30 PM
                2. Simplicity is godliness
                3. Scale with extreme prejudice


                I occasionally post using a savantphone, so please don't laugh too hard at the typos...

                Comment


                • #9
                  Re: Tracking user Logon and Logoff activity in database (secure)

                  Although it is not perfect, I added more security by adding a SQL trigger. The trigger will roll back the insertion when one of the following requirements is not met:
                  • The inserted username value must be equal to the Active Directory username which is inserting it
                  • The computer from which the insertion is made must be a member of the Active Directory
                  • The inserted computer name value must be equal to the computer from which the insertion is made

                  I added this all as code in my original post.

                  By the way. We are using Software Restriction Policies which prevent our students to run programs from other locations than the ones we have defined. Also set your NTFS and share permissions on the script folder in the right way. This combination will prevent running any insertions scripts other than the one you have specified from an AD Member Computer.

                  Please, shoot with your comments or suggestions

                  Comment

                  Working...
                  X