Any way to block sensitive data (GDPR) from showing in SQL Monitor for certain users?
and1
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?
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?
Answers
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
Have you visited our Help Center?
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).
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).
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:
- Navigate to
- When it starts click on "Load configuration file" button in the middle, choose the
- 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):
- Then click the Disk icon at the top right to save the configuration change
- 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).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).RedGate.SqlMonitor.Engine.Alerting.Base.Service.exe.settings.config
file and click open.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]
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
Have you visited our Help Center?
Thank you for raising that- I have internally mentioned this forum post in relation to that request now.
Kind regards,
Alex
Have you visited our Help Center?