User profile for PlantBasedSQL

Hi @rpcasey001 - I have just verified that SQL Data Generator can connect to and insert data into an Azure SQL DB - though of course the two types of supported authentication in the tool are Windows and SQL Server Auth, so obviously I had to connect via SQL Auth and it worked fine! I would imagine this is true for MI as well (but unfortunately don't have access to one to test!), however I don't believe Azure SQL Data Warehouse is supported. As for IaaS VMs running SQL Server, this should function absolutely fine, as the tool supports up to SQL Server 2019: https://documentation.red-gate.com/sdg/getting-started/requirements Let me know if I can help further! [image] / comments

Hi @rpcasey001 - I have just verified that SQL Data Generator can connect to and insert data into an Azure SQL DB - though of course the two types of supported authentication in the tool are Window...

0 votes

Hi @kevinumass thank you for your post! I would say the best way to achieve what you're looking for would be something like a Row-Internal Sync rule with a CASE statement. Target the column you want to mask (in this example I'll use real_num), set both DMSPARAM1 and DMSPARAM2 to be Numbers, Integer (Random) with high and low bounds as 16 and 0 respectively. Then in the Column Replacement Values Clause go for something like "CASE WHEN DMSPARAM1 = real_num THEN DMSPARAM2 ELSE DMSPARAM1 END)" You could even go for an additional layer of logic by adding a third DMSPARAM or doing additional WHEN/THEN to say if DMSPARAM2 also = real_num and is greater than 0, then just minus 1 from the number, else add 1. The kicker here is that after the operation nobody will know which row got a DMSPARAM and which just had 1 minused/added to it. There's more info on Row-Internal rules here: https://www.red-gate.com/hub/university/sql-provision/sql-provision under "Synchronizing Masked Information" I hope this helps? Let me know if you have any additional questions! / comments

Hi @kevinumass thank you for your post!I would say the best way to achieve what you're looking for would be something like a Row-Internal Sync rule with a CASE statement. Target the column you want...

0 votes

Hi @kevinumass did you get this requirement working at all after the above thread? I've been thinking about this a bit more recently and have a couple of other ideas that might be able to help out, but will leave off if it's not longer required! Thank you very much. / comments

Hi @kevinumass did you get this requirement working at all after the above thread?I've been thinking about this a bit more recently and have a couple of other ideas that might be able to help out, ...

0 votes

Hi @ben_b thank you for your message - glad to hear the Webinar was helpful! Typically we see the 2 main use cases for SQL Data Catalog being 1) The masking of lower environments as you stated and 2) Informing other business decisions about data. This could be anything from putting auditors minds to rest that we know where all of our sensitive data is to deciding whether or not to give people access to certain systems - it could also be useful in some kind of impact assessment if we were to evaluate our readiness for data breaches and our response times. I've also heard of companies both using this to a) Catch new columns/Tables that are added by developers and ensure they stay on top of the classification workflow and b) Use it for reporting purposes to data governance teams i.e. who owns the data, where is it being used etc. I guess the point is that whilst the initial Cataloging requirement can be satisfied, it's actually the ongoing consumption of that Catalog that drives the real value! As for the differentiation between Confidential and Confidential GDPR, this is primarily the Microsoft specific sensitivity labels that come out of the box with SQL Server 2016+, but like I mentioned in the webinar, with Data Catalog these can be extended to reflect your own business-agreed taxonomy so someone could use "Confidential - POPI" if they're in South Africa, or can add their own Categories and tags like "Treatment Intent" or "Retention Period", but the point is it really has to be agreed up front so that everyone is tackling the classification from the same angle. We actually package up an example taxonomy out of the box with Data Catalog, which represents an amalgamation of categories and tags that we have seen many of our Early Access Users adopt into their own taxonomies. I hope some of this information helps? Let me know if you have any additional questions! Thank you very much! Chris / comments

Hi @ben_b thank you for your message - glad to hear the Webinar was helpful!Typically we see the 2 main use cases for SQL Data Catalog being 1) The masking of lower environments as you stated and 2...

0 votes

Hi @Camromere thank you for your post. Data Masker can be called via the command line by a SQL Server Agent Job with a PARFILE argument, the syntax is available here: https://documentation.red-gate.com/dms/data-masker-help/general-topics/about-command-line-automation You should use the headless runner datamaskercmdline.exe for this. I hope this helps? Let me know if you have any additional questions on this! Thank you very much. / comments

Hi @Camromere thank you for your post.Data Masker can be called via the command line by a SQL Server Agent Job with a PARFILE argument, the syntax is available here: https://documentation.red-gate....

0 votes

Hi @Camromere thank you for your post. Data Masker can be called via the command line by a SQL Server Agent Job with a PARFILE argument, the syntax is available here: https://documentation.red-gate.com/dms/data-masker-help/general-topics/about-command-line-automation You should use the headless runner datamaskercmdline.exe for this. I hope this helps? Let me know if you have any additional questions on this! Thank you very much. / comments

Hi @Camromere thank you for your post.Data Masker can be called via the command line by a SQL Server Agent Job with a PARFILE argument, the syntax is available here: https://documentation.red-gate....

0 votes

Hi @rob_wingate - long time no speak - I hope you're well! What you're describing is deterministic masking. The first thing I would ask before describing this is why you would need to have the names be masked to the same value every time? Is there an intended workflow for this? The reason I ask is that implementing this functionality requires a bit of perseverance as the tool was not intended to handle this initially. If it is a workflow you must have to ensure you have viable Images and Clones every time then you have 2 options (as far as I know): Pre-Requisite for both approaches: Have a database on the staging server you're using for the imaging process, to which you can add a mapping table (i went for dbo.MaskingMapper) this table can be something like: ID int, MFName nvarchar(100), MLName nvarchar(50) -- With a primary key on ID. Options: 1) Restore the Database to your staging server first, in your masking set you will need a second rule controller that links to the MaskingMapper DB. As part of the process: - Mask all of the names in the DB - Use a Table-To-Table Sync Rule to copy across values that may already be present in the mapping table - Write a command rule to insert distinct IDs, and the now masked first and last names into the mapping table This process then will run each time adding new masked values to the table and copying across the ones that already existed - but you will need to call data masker via the command line after the restore THEN kick off the imaging process from your now live masked DB, and then afterwards you can drop the temporary copy. 2) If you want to ensure this process runs as part of the image creation process and you don't want to restore the DB then you will just need to use Command rules instead of the Table-to-Table Sync Rule. The command rules would, after you've masked the names, then go off and Update the table based on what is in MaskingMapper and then write any new values to MaskingMapper. Because this mapping table is on the staging server but not included as part of the backing up / restore or Imaging process it will always be preserved, and because the table is ID and then any masked fields, you're never storing additional sensitive data (unless the ID is in itself sensitive, in which case this process won't work and I'd have to have a think about how you could adapt it to work!) Does that make sense? Let me know if you need any clarification! Thank you very much :-) / comments

Hi @rob_wingate - long time no speak - I hope you're well!What you're describing is deterministic masking.The first thing I would ask before describing this is why you would need to have the names ...

0 votes

Hi @arungk Thank you for that - yes that is the correct email address - I look forward to receiving your masking set. / comments

Hi @arungk Thank you for that - yes that is the correct email address - I look forward to receiving your masking set.

0 votes

Hi @arungk Thank you for coming back on this - the Masking Set is just XML and is configuration only and does not store any sensitive information (so that in cases like this they can be shared). If though you would still not be happy sharing these are you able to at least email me the logs from the tool so I can see where it's going wrong, please? My best guess without looking at this is that you have "Unique Values Only" set on your substitution rule and this means that there aren't enough values to mask all of your fields. Whether you wish to have this box ticked though can vary on what column specifically you're masking. If it's something like First Names, for which the set has around 4,000 values, then there's no reason to have this checked as many people share the same first name. Are you able to provide some more information on this please, so that I may better assist? Thank you very much. / comments

Hi @arungk Thank you for coming back on this - the Masking Set is just XML and is configuration only and does not store any sensitive information (so that in cases like this they can be shared).If ...

0 votes

Hi @arungk thank you for your post - I know we've spoken before about getting Masking Rules to work and we exchanged some emails around this too. I've seen you've also posted about a substitution rule on the forums, are these rules in the same Masking Set? Would you be able to email the Masking Set and Logs from the failed run to me please so that I can take a look for you? Thank you very much. / comments

Hi @arungk thank you for your post - I know we've spoken before about getting Masking Rules to work and we exchanged some emails around this too.I've seen you've also posted about a substitution ru...

0 votes

How can we help you today?

PlantBasedSQL

Comments