Data Masker for SQL Server 6

Running Data Masker from the command line

The Data Masker application can be run in batch mode from a DOS command line or Windows based scheduler in order to automate the masking of data in a target database. Automated masking and exit is controlled by providing the appropriate command line options when the application starts.

Two Command Line Modes

There are two modes in which the Data Masker software can execute a masking set in batch. The operation of the masking set in the database is identical in both modes – the basic difference is whether the Graphical User Interface (GUI) is started. The command line options are not identical in both modes.

The GUI version which is started from the DataMasker.exe binary is normally started via the usual windows methods (double click on an icon etc.), however, it also has a command line. This version will always start the Data Masker user interface while it executes - although there is an option to close the application down when the masking set completes. This mode is useful in situations in which the progress of the masking set run is to be inspected as the run progresses. Any masking set started from within the DataMasker.exe binary (whether by command line or not) has no ability to run without the GUI.

The non-GUI version of Data Masker is started from by running the DataMaskerCmdLine.exe binary. This executable file is installed with the Data Masker software and can be found in the same installation directory as the DataMaskerCmdLine.exe binary. The DataMaskerCmdLine.exe file is a true “headless” command line executable - it has no GUI to start and must always be run from a command line.

For the purposes of nomenclature in the discussion below, the DataMasker.exe binary will be referred to as the GUI version and the DataMaskerCmdLine.exe binary will be referred to as the Headless version.

Masking Sets in the GUI and Headless Versions

A Masking Set which will run in the GUI version of Data Masker will always run identically in the Headless version and vice-versa. The only caveat is that it is possible to supply additional command line options on the GUI version (the flag to close it when the masking run completes for example) that do not exist in the headless version.

The Headless Data Masker Command Line

The GUI version of Data Masker has a number of command line options. In the interests of simplicity, when the Headless version was introduced in Data Masker for SQL Server V6, all but one of these command line options were removed.

There is only one command line option for the Headless version. This is the PARFILE option. A PARFILE (parameter file) is a simple text file which contains a list of the options required to run the masking set. All configuration information for the masking set run is located inside the parameter file as lines of text. It should be noted that the GUI version also has a PARFILE option but some of the information can be located both on the command line as an option and/or within the PARFILE.

The Parameter File

The PARFILE option specifies the name of the parameter file to read. This should be a full path including the drive and directory information. The Headless Data Masker software will not use default directories in command line mode. If the parameter file filename contains spaces, remember to enclose it in double quotes when placing it on the command line otherwise the Windows operating system will present it to the Data Masker software as multiple command line options.

The name of the parameter file always follows the PARFILE command line option and the PARFILE option must be specified with an equals sign as in the example below:

PARFILE=C:\MaskingSets\SamplePARFILE.txt

The Parameter File Contents

Each line of the parameter file specifies a configuration option.  Note that it is not necessary to enclose file names which contain space characters in double quotes inside the parameter file. The use of double quotes is only required (by Windows) when the file name is specified directly on the command line. Below is an example of a parameter file:

-- Sample Parameter file for Data Masker Command Line
-- usage: DataMaskerCmdLine.exe PARFILE=C:\MaskingSets\SamplePARFILE.txt 
-- 

MASKINGSET=C:\Program Files\Red Gate\Data Masker for SQL Server 6\Demo.DMSMaskSet
LOGFILEDIR=C:\Dump\DMSV6Logs
DATASETSDIR=C:\Program Files\Red Gate\Data Masker for SQL Server 6\DataSets
REPORTSDIR=C:\Dump\DMSV6Logs
INTERIM_REPORTS=true
--LOGINSUB=DummyLogin@DummyServer[DummyDatabase]||DataMasker/DataMasker@DEMODB[DMTest]
--LOGINSUB=XXXLogin@XXXServer[XXXDatabase]||DBSSourceUser/DBSSourceUser@N2KBUILD-PC\N2KTEST1[DBSSource]DMSSUBVALUE00=DM_INVOICE_LINE_HISTORY
DMSSUBVALUE05=DM_INVOICE_LINE_HISTORY
--Please take note that substitution value placeholder in Oracle version has a different keyword DMOSUBVALUExx:

Each line in the parameter file is a keyword immediately followed by the data for that keyword. Note the use of the equals (“=”) sign immediately after the keyword and also that no spaces are used. The keywords are always uppercase and are case sensitive. The keywords for a PARFILE are listed below.

MASKINGSET

The name of the masking set to load. This must be a full path including the drive and directory information. The Data Masker software will not assume default directories in the parameter file. This keyword is required.

LOGFILEDIR

The name of the directory in which to place the log. Data Masker creates extensive logs and you will want to review them if there are errors. This keyword is required.

DATASETDIR

The name of the directory in which the datasets are located. Datasets are the replacement values used by some masking rules such as the Substitution rule. These datasets are installed with the Data Masker software and the directory for them can be found in the installation folder. This keyword is required.

REPORTSDIR

The name of the directory in which to place the reports. Data Masker will emit a text-based report detailing the results of the data masking operations. This is very useful for auditing purposes since it is not possible to review the rule progress via a GUI when in Headless mode. This keyword is required.

INTERIMREPORTS

A flag which indicates if interim reports are produced during the masking run. If enabled, you will see a report of the current masking rule and table statistics emitted roughly once per minute. This enables you to track the progress of the masking run as it is running even though there is no GUI. The value for this keyword can be either of true or false and is case sensitive. This keyword is optional – Interim Reports are disabled by default.

LOGINSUB <param>

The login substitution parameter. The masking set can be configured with dummy login information in the Rule Controllers and have that information substituted by the parameter file. This functionality enables the target of masking set to be determined at runtime. The format of the login substitution parameter looks like 

For SQL Server:

<DummyLogin>@<DummyServerName>[DummyDatabase]||<RealLogin>/<RealPassword>@<RealServerName>[RealDatabase]

For Oracle:

with usage of TNSNames:

<DummyLogin>@<TNSSpecificName>||<RealLogin>/<RealPassword>@<OtherTNSSpecifName>

with connection via TCP

<DummyLogin>@[{Server}:{Port}:{DB or Service}]||<RealLogin>/<RealPassword>@[{Server}:{Port}:{DB or Service}]

For example, a Rule Controller might be configured with a login of FOO, a server name of BAR, and a database name of ABCDB. In that case, a parameter file line with the option…

LOGINSUB=FOO@BAR[ABCDB]||DataMasker/ItIsSecret@XANTHE\MSSDMTEST[DMTest]

would check each Rule Controller in the masking set and substitute the real login information of DataMasker/ItIsSecret@XANTHEMSSDMTEST in place of FOO@BAR and the database name DMTest would be substituted in place of the dummy value ABCDB. The dummy values of serve as place holders for the real information and allow the appropriate Rule Controller to be updated at run time.

A masking set can have multiple Rule Controllers. In that circumstance, multiple substitution parameters can be configured as shown below:

LOGINSUB=FOO@BAR[ABCDB]||DataMasker/ItIsSecret@XANTHE\MSSDMTEST[DMTest] LOGINSUB=AAA@BBB[ABCDB]||DataMasker/SecretPassWord@CHRYSE\SQLSERVERTEST[Pubs]

If more than one login substitution is required by the masking set, simply use multiple LOGINSUB lines in the parameter file as illustrated in the example above.

The Data Masker Command Line Processor uses delimiter characters ('@', '[', ']' and '=') to determine the various components of the login name, password, database and instance in the Login and Database substitution parameters. If the contents of a login substitution parameter requires the use of any of these characters (for example a password is "myp@ss") the presence of the delimiters will disrupt the interpretation and processing of the command line.

Delimiter characters used in the text of a substitution value must be marked with a preceding backslash character (escaped). For example, a Login Substitution value implementing a password of myp@ss=1 would have its password written as myp@ss=1. The full Login Substitution value might look like:

FOO@BAR[ABCDB]=DataMasker/myp\@ss\=1@XANTHE\MSSDMTEST[DMTest]

The command line processor will see the "@" and "=" and will interpret them as part of the substitution value text and not as the '@' and '=' delimiters. Similarly, the '[' and ']' characters can be escaped as "[" and "]". A single backslash can be represented in the text as "\" if necessary.

This keyword is optional.

The login substitution parameter. The masking set can be configured with dummy login information in the Rule Controllers and have that information substituted by the parameter file. This functionality enables the target of masking set to be determined at runtime. The format of the login substitution parameter is always

<DummyLogin>@<DummyServerName>[DummyDatabase]||<RealLogin>/<RealPassword>@<RealServerName>[RealDatabase]

For example, a Rule Controller might be configured with a login of FOO, a server name of BAR, and a database name of ABCDB. In that case, a parameter file line with the option…

LOGINSUB=FOO@BAR[ABCDB]||DataMasker/ItIsSecret@XANTHE\MSSDMTEST[DMTest]

would check each Rule Controller in the masking set and substitute the real login information of DataMasker/ItIsSecret@XANTHE\MSSDMTEST in place of FOO@BAR and the database name DMTest would be substituted in place of the dummy value ABCDB. The dummy values of serve as place holders for the real information and allow the appropriate Rule Controller to be updated at run time.

DMSSUBVALUExx (SQL Server version)/ DMOSUBVALUExx (Oracle version)

Up to twenty replacement strings can be specified in the parameter file. The xx is a two-digit numeric value. These runtime substitution values are designed to permit generic SQL statements to be dynamically adjusted at runtime for specific targets during batch operations.

Note: The keywords for these replacement strings are DMSSUBVALUE00 to DMSSUBVALUE19. If the text %DMSSUBVALUExx% is used in a Command rule or in the Where Clause of a masking rule, the value associated with that keyword will be replaced at runtime before the rule is executed. For example, if a Command rule included a statement like

truncate table %DMSSUBVALUE05%_TEMP;

and the parameter file contained a line stating DMSSUBVALUE05=TESTUSER then the actual statement that would be executed would be

truncate table TESTUSER_TEMP;

The value TESTUSER would be substituted for the %DMSSUBVALUE05% text in the SQL statement immediately before the statement was executed.

This keyword is optional.

Exit and Shutdown

The Headless version of Data Masker automatically exits when the masking set run is complete. This is true regardless of whether the masking set concluded successfully or was terminated with error. If an error did occur, the Exit Code and text (see below) should be examined for a general error message and the logfile should be reviewed to determine a more specific cause of the problem.

Exit Codes

When run from a command line, the Headless version of the Data Masker software will return an exit code via the standard DOS mechanism. If the run of the masking rules was successful, then this exit code will always be zero. Any non-zero exit code implies that an error occurred while opening the masking set or while running the masking rules. If a non-zero exit code is returned, the log file should always be examined to determine the error that occurred. The defined exit codes are:

200 - EXITCODE_RULERUN_ERROR

There was an error executing one or more masking rules. See the log file for more information.

110 - EXITCODE_PARAMFILESUB_ERROR

There was an error processing the LOGINSUB substitution keyword in the parameter file.

109 - EXITCODE_MASKSETOPEN_ERROR

There was an error opening the masking set. See the log file for more information.

108 - EXITCODE_MASKSETMISSING_ERROR

The masking set specified in the parameter file could not be found. See the log file for more information.

107 - EXITCODE_REPORTSDIR_ERROR

The specified report directory could not be found or there were permissions issues. See the log file for more information.

106 - EXITCODE_DATASETBUILD_ERROR

There was an error reading the datasets. See the log file for more information.

105 - EXITCODE_DATASETSDIR_ERROR

The specified dataset directory could not be found or there were permissions issues. See the log file for more information.

104 - EXITCODE_LOGFILEDIR_ERROR

The specified log file directory could not be found or there were permissions issues. See the log file for more information.

103 - EXITCODE_CMDLINEARGS_ERROR

There was an error processing the command line. See the log file for more information.

102 - EXITCODE_LOGCREATE_ERROR

There was an error creating the log file.

101 - EXITCODE_LOGINIT_ERROR

There was an error initializing the logging sub-system.

100 - EXITCODE_UNSET

Unspecified error. See the log file for more information.

99 - EXITCODE_UNHANDLED_EXCEPTION

An exception occurred. See the log file for more information.

0 - EXITCODE_SUCCESS

The run of the masking set completed successfully. The log file and reports will have full details.

The exit code value is returned using the Windows standard mechanisms and can be determined via any of the available Windows methods. The most common way to view the return code is by using the DOS `echo %errorlevel%` command. The script output below shows such an operation. The 200 exit code means the masking set execution failed with an error.

C:\Program Files\Data Masker (Sql Server)>start /wait "" DMS_CmdLine.exe PARFILE=C:\workspace\data-masking\DMV6\DMS_TestSets\DebugPARFILE.txt C:\Program Files\Data Masker (Sql Server)>echo %errorlevel% 
200 
C:\Program Files\Data Masker>

The Start /Wait Command

There is an important consideration which must be noted when launching Windows applications from a command line. The Command application will, by default, launch a Windows application as a separate process. Once started, the Command application will not monitor the running application process. In effect, this behavior means that immediately after launching the Windows application, the Command application will return to the DOS prompt and continue with the next command statement. It is not possible to retrieve the exit code in such circumstances and the next operation in the Command application will begin to process long before the masking set has completed.

If it is necessary for the Command line application to wait until the masking set has executed (and the Data Masker software has shutdown) before it executes the next operation, then the Data Masker software must be launched with the DOS start /wait command. The example below illustrates these concepts:

Note: For some reason, the first parameter of the Start /Wait Command needs to be the Title of the Window To Open. Nobody knows quite why such an optional parameter would be required let alone be specified first in the list - however that is just the way it is. In the examples below the window title is entered on the command line as two double quote characters "". You can use whatever title you wish but the title parameter does need to be present or the Start /Wait command will improperly interpret the remaining command line parameters.

Run a Masking Set with the Start/Wait Command

start /wait "" DMS_CmdLine.exe PARFILE=C:\MaskingSets\SamplePARFILE.txt



Didn't find what you were looking for?