Any way to block sensitive data (GDPR) from showing in SQL Monitor for certain users?

and1and1 Posts: 1 New member
Hi! 

We have a bunch of developers who would like to start using SQL Monitor for our production databases, but since SQL Monitor may show sensitive data like when you click on a Deadlock and view SQL Query fragment - there is a possibility that sensitive data is shown.

Is there a way to block users (in this case they only have Read only access) from viewing actual data in the databases?
Tagged:

Answers

  • Alex BAlex B Posts: 1,157 Diamond 4
    Hi @and1

    If the queries are all parameterized, then the only user role that can see the query parameters are Admin role users.  All other user roles are not able to see the parameters used.

    If the queries are hardcoded with sensitive data values, then these would be viewable to anyone who has access to SQL Monitor and the specific entity that the query was run on.  In this case restricting the user to be able to view only entities they need to would help limit this, but they would still be visible to the users with access and there isn't a built in way to prevent this (other than disabling the sampling of the SQL Processes and Top queries.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • scubakubascubakuba Posts: 3 New member
    Hi @Alex B
    Could you give me some more information on how to "disable the sampling of the SQL Process and top queries" ?
    We tried disable the 1222 traceflag but found out that it was Red-Gate monitor who enabled the trace on the sql server (sql monitor quickly enabled the flag just after we disabled it).

  • TiborKarasziTiborKaraszi Posts: 4 New member
    We have the same situation.
    SQL Monitor can be very valuable for developers, but we are not allowed to expose sensitive information. Disabling 1222, feels like a hack (limiting security for SQL Monitor). I haven't tried replacing it with XE, but assume that the XE data has the same issue with exposing sensitive information.

    IMO we need a security feature with a role for which clear-text data is anonymized by SQL Monitor (be it in top queries, deadlock or somewhere else).
  • Alex BAlex B Posts: 1,157 Diamond 4
    Hi @scubakuba and @TiborKaraszi

    The traceflag 1222 is for the older Deadlock sampler which is not used by default now, the newer iteration (Deadlock (extended events)) uses extended events.

    The steps to disable samplers are as follows using the ConfigurationFileEditor.exe:

    1. Navigate to C:\Program Files\Red Gate\SQL Monitor\BaseMonitor\Tools (by default, or wherever you have installed it) and start the ConfigurationFileEditor.exe as an administrator (right click > run as administrator).
    2. When it starts click on "Load configuration file" button in the middle, choose the RedGate.SqlMonitor.Engine.Alerting.Base.Service.exe.settings.config​ file and click open.
    3. Then on the screen that appears, scroll down to the appropriate entry click the dropdown box that says Interval and choose Disabled e.g. here for the DeadlockTraceFlag sampler (you can disable this one and I believe it will prevent SQL Monitor from enabling the trace flag):
      For the SQL Process sampler it is [Cluster].[SqlServer].[SqlProcess]
      For the Top Queries sampler it is [Cluster].[SqlServer].[TopQueries]
      For the Deadlock (extended event) sampler it is [Cluster].[SqlServer].[ExtendedEvents]
    4. Then click the Disk icon at the top right to save the configuration change 
    5. And lastly restart the SQL Monitor Base Monitor service.

    This will disable the samplers you have chosen to disable.  To note, this will remove a large portion of functionality from SQL Monitor as the Top X queries section (Top queries sampler) on the server overview will no longer have data nor will there be SQL User process data or long running queries or blocking process alerts (SQL Process sampler), or deadlock alerts (Deadlock XE sampler).

    For your request about anonymizing clear text data @tibor@TiborKaraszi please raise a suggestion on the SQL Monitor Uservoice forum https://sqlmonitor.uservoice.com/forums/91743-suggestions/filters/top

    Kind regards,
    Alex

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • TiborKarasziTiborKaraszi Posts: 4 New member
    Thanks Alex. That info is more important to us, so that means we have to exclude developers from SQL Monitor. FWIW I filed such a request a few days ago at https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/47151862-mask-sensitive-data-from-deadlock-and-top-queries 
  • Alex BAlex B Posts: 1,157 Diamond 4
    Hi @TiborKaraszi,

    Thank you for raising that- I have internally mentioned this forum post in relation to that request now.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.