Protecting DBA’s secret
One common challenge for a migration project is for a DBA to give password to access a database that he/she owns. This article shows an approach where DBA can keep the password while allowing a separate migration team to run assessment and migration.
This is done through SSMA console application which allows you to store password for database connection in an encrypted form in the machine where SSMA is installed. Using this feature, a DBA can use SSMA console application to encrypt and store the password, then any user (from the same machine) can use SSMA console application to perform database migration assessment and conversion. Note that the encrypted password can only be used to retrieved information for SSMA (not for connection using tool such as Oracle client tool or for any other purposes) and it can't be retrieved in clear text to anyone (including the machine administrator).
The following are the steps for DBA to encrypt and store Oracle password (the example use Oracle but the same steps apply for SSMA for Sybase, MySql and Access):
- Open SSMA console application
Program > Microsoft SQL Server Migration Assistant > Microsoft SQL Server Migration Assistant Command Prompt
- Execute SSMAForOracleConsole.exe with the following parameter:
-p|-securepassword
-a|add {"<server_id>[,.n]"|all : map the password to a specific server_id in the SSMA console script file or for all servers
-s|script<script-file>]: map the password to a specific SSMA console script file
-c|-serverconnection <server-connection-file>] : map the password to a specific SSMA console server connection file
[-v|variable<variable-value-file>] : map the password to a specific SSMA console variable file (optional)
[-o|overwrite] : overwrite existing encrypted password (optional)
Note that you need to specify either script file or serverconnection file . The encrypted password will be associated to the specified file.
For Example:
SSMAforOracleConsole.exe -securepassword -add all -s "C:\SSMA\ScriptFile.xml"
or
SSMAforOracleConsole.exe -securepassword -add all -c "C:\SSMA\ConnectionFile.xml"
- Enter the password
Additional Commands
To remove the password:
–p|securepassword –r|remove {<server_id> [, …n] | all}
For example:
ssmaforOracleConsole.exe -securepassword -remove all
To display list of Server IDs for which the password is encrypted:
–p|securepassword –l|list
Once the DBA encrypt and store the password on the machine, you can then set up the script file and/or variable files for console application. If you are using a script file without additional variable file in your console application, then you can remove password entry in the script file.
If you are using a script file with variable file, then you leave password entry in the script file but use empty string in the password xml element in the variable file. You may also need to set encrypted-password override option in the output provides section in the script file:
<output-providers>
<encrypted-password override="false"/>
</output-providers>
When set to false(default), the script file will use the encrypted password stored on the machine first. The order for this mode is: Protected storage->Script File->Server Connection File-> Prompt User.
When set to true, the script file will override the encrypted password stored on the machine with what specified by the user: The order for this mode is: Script File->Server Connection File->Prompt User