Topic
12 replies Latest Post - ‏2013-03-25T13:29:08Z by SystemAdmin
SystemAdmin
SystemAdmin
483 Posts
ACCEPTED ANSWER

Pinned topic MSSQL problem getting DB user activities

‏2013-03-13T10:21:30Z |
Dears,

When I tried to do quality test for Guadium on our environment I found that when DBA login through SQL server Guardium detect auto generated procedure from MSSQL server but when he tries to execute a command I'm not able to see it on Guardium console.

What would be cause of the problem? I checked inspection engine status and configuration and every thing is fine.
Updated on 2013-03-25T13:29:08Z at 2013-03-25T13:29:08Z by SystemAdmin
  • Muris
    Muris
    45 Posts
    ACCEPTED ANSWER

    Re: MSSQL problem getting DB user activities

    ‏2013-03-13T15:58:35Z  in response to SystemAdmin
    Hi,

    Have you restarted instances after STAP installation on DB server?

    Regards,
    Muris
  • SystemAdmin
    SystemAdmin
    483 Posts
    ACCEPTED ANSWER

    Re: MSSQL problem getting DB user activities

    ‏2013-03-14T04:40:23Z  in response to SystemAdmin
    I don't have S-TAP installed because I don't look for local traffic on the server, I just care about traffic over the network "inspection engine".
    • SystemAdmin
      SystemAdmin
      483 Posts
      ACCEPTED ANSWER

      Re: MSSQL problem getting DB user activities

      ‏2013-03-14T15:40:47Z  in response to SystemAdmin
      Do you mean you are monitor the SQL transaction over the network SPAN port?

      How did the user connect to the DB server and using what software?
      • SystemAdmin
        SystemAdmin
        483 Posts
        ACCEPTED ANSWER

        Re: MSSQL problem getting DB user activities

        ‏2013-03-17T05:10:14Z  in response to SystemAdmin
        Hi 1XWY_TS_Teh,

        Yeah, I'm monitoring SQL transactions over spanning port, and users for MSSQL use Management Studio.
        The problem that while they are login; management studio invokes some stored procedures when admin got connected to on of the DBs I can log that, the last command I can capture "user dbo.Xtable" after that if he execute "select * from sky" I can't log it.
        • SystemAdmin
          SystemAdmin
          483 Posts
          ACCEPTED ANSWER

          Re: MSSQL problem getting DB user activities

          ‏2013-03-17T15:18:43Z  in response to SystemAdmin
          May I know under which Query you can not see the example "Select * from Sky"? This is strange, if you can see all those background commands from Management Studio, you should be able to see other as well.
  • zbychfish
    zbychfish
    8 Posts
    ACCEPTED ANSWER

    Re: MSSQL problem getting DB user activities

    ‏2013-03-14T11:14:18Z  in response to SystemAdmin
    It looks, that your policy doesn't store events for particular sessions.
    Could you provide the list of rules from your policy?
    • SystemAdmin
      SystemAdmin
      483 Posts
      ACCEPTED ANSWER

      Re: MSSQL problem getting DB user activities

      ‏2013-03-17T04:59:17Z  in response to zbychfish
      in the policy rules I ignored session for two system users and for all users that are not DBA's "Application users", please find the attached screenshot
      • SystemAdmin
        SystemAdmin
        483 Posts
        ACCEPTED ANSWER

        Re: MSSQL problem getting DB user activities

        ‏2013-03-17T15:14:44Z  in response to SystemAdmin
        Hi, do you mean the first two rules you are using the "Ignore S-TAP session" action to ignore the non-privilege user activities? But, I thought you are using Network Monitoring and not using S-TAP? I hope that is just you described in the description only.

        If you are using Network Monitoring, you should used Ignore Session instead of Ignore S-TAP session.

        So, for rule 3, you want to capture the SQL Commands and Value from NBAD DBAs as well as send you alert in real-time, right?
        • SystemAdmin
          SystemAdmin
          483 Posts
          ACCEPTED ANSWER

          Re: MSSQL problem getting DB user activities

          ‏2013-03-19T05:26:55Z  in response to SystemAdmin
          Hi,
          this ignore S-TAP session because I don't want to log sys account events on the SQL server localhost because it's created huge amount of traffic and that's causes performance issue before, and regarding rule 3 I alert all logs to Arcsigh since it's our logs repository.
          regards.
          • SystemAdmin
            SystemAdmin
            483 Posts
            ACCEPTED ANSWER

            Re: MSSQL problem getting DB user activities

            ‏2013-03-19T15:35:43Z  in response to SystemAdmin
            OK. I'm sorry, I'm a big confuse here. Did I remember wrong that you said you are not using STAP but only network SPAN port monitoring, right?

            If you do not using S-TAP, how to ignore those session by using the "Ignore S-TAP session" action?

            The 3rd rules is Log Full Details than Alert to syslog. I think those traffics might be appear in the Policy Violation query which you can find it in Exception tab.

            How about from ArcSight, do you manage to see those traffics you claimed not in Guardium?
            • SystemAdmin
              SystemAdmin
              483 Posts
              ACCEPTED ANSWER

              Re: MSSQL problem getting DB user activities

              ‏2013-03-20T05:50:34Z  in response to SystemAdmin
              yeah,but what I tried to say that I don't have DBAs login to the server and do changes, all changes through Management Studio. that's why only system activities I exclude from monitoring.
              The traffic shown on the as an incident "policy violation" and I saw all the traffic on arcsight but when I tried to log specific activity for one user I couldn't see anything after he type "use dbo.table"
              • SystemAdmin
                SystemAdmin
                483 Posts
                ACCEPTED ANSWER

                Re: MSSQL problem getting DB user activities

                ‏2013-03-25T13:29:08Z  in response to SystemAdmin
                Can you run the TCPDUMP from Guardium CLI via the DIAG command? Than see whether you can observe the traffics that mentioned it is missing from the console.