Delimited (Non-Regular) Identifiers in Table or Column Names (KBA1265)
Background
MS SQL is very open to the characters that can be used in Database, Table and Column Name etc and when creating a Rule Set, the Masking Engine can successfully capture tables and columns with special characters (including country-specific encoded characters). The issue is the internal working of the Masking Engine, which is limited to only use, what is called in MS SQL, Regular Identifiers. This means that the engine cannot mask Rule Sets containing Table or Column Names with special characters or starting with numbers etc.
Regular and Delimited Identifiers - Example
Examples of Identifiers allowed in MS SQL. For more information about Identifiers: MSDN - Database Identifiers.
| Identifier Example | Classification and description |
|---|---|
| Table_1 | Regular Identifier |
| 123Tble | Identifier starting with number (Delimited Identifier) |
| "test" | Identifier with double quotes (Delimited Identifier) |
| [test] | Identifier with brackets (Delimited Identifier) |
| * | Identified using asterisks (Delimited Identifier) |
| $%^&# | Identifier using special character (Delimited Identifier) |
| 昨夜, 最高 | Identifier Japanese characters (Delimited Identifier) |
After completing this how-to you will have ...
Pre 5.2 Workaround - Fixed in 5.2
The best option is to upgrade the engine to 5.2 or later. If this is not possible the alternative is to use Views or Synonyms which will be used to substitute the tables/columns with non-regular identifiers.
There a number of different ways this can be done. Below is one of the easier methods detailed and in this step we start with the normal step of creating the Rule Set.
Steps - Creating View or Synonym
Step 1 - Create Rule Set in Masking Engine
The first step is to create the Rule Set as normal on the Masking Engine.
Step 2 - On the database, create a temporary table to mask
This step will create the structure to a temporary table that will be used to (via the View below) to mask the database.
- On the database, select the table with the issue and right-click and:
- Select "Script Table as".
- Select "CREATE To".
- Select "New Query Editor Window".
- On the database, edit the query:
- Change the Delimited Names with Regular Names.
- Change the Table name to a new name (which will be used below in the View).
- Execute the Query (this will create the table - now with Regular names only).
- On the Masking Engine -edit the Rule Set and add the new table.
- On the database, remove the table.
Step 3 - On the database, create a View or Synonym
We can now create a View or Synonym to that table and rename the Table and/or Column so it doesn't include any Delimited Identifiers.
Below is creating a View shown The steps are similar for creating a Synonym.
- On the database, open and right-click View and select New View.
- Select the required Columns.
- Name these as required using Regular names - the name can be altered.
- Save the View - use a Regular Identifier name (see above).
What's Next
This is what was achieved and what was omitted in this how-to.