KB: Configuration Settings

Overview

Idera SQL Column Search is a free tool designed to make it easy to search SQL Server tables to find column names that match a user-defined set of strings. By default, the application comes pre-configured with 45 search strings to help you find potentially sensitive data by identifying common sensitive data column name definitions. Users can select, edit, and even delete the pre-configured strings and create new strings depending upon their search needs.

In order to perform a search, a search profile needs to be defined and set as the active search profile. The application does not limit the number of search profiles that can be created or the number of strings that can be defined. All search strings that have been defined, including the pre-configured strings are shown in the list.

Creating a Search Profile

To create a search profile the user simply selects the checkboxes next to the search strings in the list and then saves that profile. If no profile has been created yet, the application only allows for the Save New Profile option. After at least one profile is created you can use the Save Profile to update a profile or the Save New Profile to create a new one.

  • When saving a profile the name you define is not case sensitive and will be in all caps regardless of the case you enter the text as.
  • A Select All/Deselect All option enables for selection or deselection of all the items in the list.

Active Search Profile

Once a profile is created and saved it is set as the Active Search Profile and is what the application will use when the Perform Search is selected from the main screen. There is a text box on the main screen that also notes what the currently active search profile is.

To change the active search profile you return to the search settings and either Open a different profile, update the existing profile, or create and save a new profile.

Creating a New String

New search strings can be created at any time by selecting New String. Selecting this opens the configuration screen where you can define a Category, Search String Name, and Definition for the string. All fields must be completed before being able to save the string.

  • In the Category field, you can select from a list of categories that have already been defined from the drop down, or enter a new category manually.
  • The Search String Name can be anything of your choosing.
  • All string searches are NOT case sensitive and to help remind you of this only lower case text is displayed regardless of the case you enter the text as.

Search String Syntax

The search syntax conforms to the commonly used SQL Server LIKE syntax. That is, the percent sign % refers to zero or more characters, the underscore _ refers to a single character, the square brackets [ ] refer to any single character within the specified range, and the square brackets with a circumflex [^ ] refer to any single character not within the specified range.

For example:

  • date% matches DateOfBirth and DatesReceived but not BirthDate.
  • %name matches FirstName and FamilyName but not NameLast.
  • %number% matches SocialSecurityNumber, numbers and SerialNumbersNew.
  • s_op matches Stop and Shop but not SysOp.
  • b[a-z]ts and b[aeiou]ts matche Bits and Bots but not Boats.
  • cell[^5-9]number and cell[^02468]number matche Cell1Number and Cell3Number but not Cell8Number.

Refer also to Microsoft technet note.

Editing and Deleting Strings

To edit a string select it in the list and select Edit String. Make the desired edits and save. To delete a string select it in the list and select Delete String.

  • Edits made to a string in one profile apply to all profiles that also have that string selected. Create a new string and then unselect the existing string from that profile to keep the original string definition in use in other profiles.
  • Deleting a string deletes it entirely from the list and from being associated with any previously associated search profiles.

Restore Default Settings

To restore the application to the settings when it was first installed select Delete Profile, then Restore Default Settings option. This will delete all profiles and return the search string list to just include the pre-configured search strings with none of the items selected.