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

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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-13T15:58:35Z  
    Hi,

    Have you restarted instances after STAP installation on DB server?

    Regards,
    Muris
  • SystemAdmin
    SystemAdmin
    483 Posts

    Re: MSSQL problem getting DB user activities

    ‏2013-03-14T04:40:23Z  
    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".
  • zbychfish
    zbychfish
    8 Posts

    Re: MSSQL problem getting DB user activities

    ‏2013-03-14T11:14:18Z  
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-14T15:40:47Z  
    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".
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-17T04:59:17Z  
    • zbychfish
    • ‏2013-03-14T11:14:18Z
    It looks, that your policy doesn't store events for particular sessions.
    Could you provide the list of rules from your policy?
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-17T05:10:14Z  
    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?
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-17T15:14:44Z  
    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
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-17T15:18:43Z  
    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.
    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.
  • SystemAdmin
    SystemAdmin
    483 Posts

    Re: MSSQL problem getting DB user activities

    ‏2013-03-19T05:26:55Z  
    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?
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-19T15:35:43Z  
    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.
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-20T05:50:34Z  
    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?
    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

    Re: MSSQL problem getting DB user activities

    ‏2013-03-25T13:29:08Z  
    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"
    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.