Applies to
ApexSQL Clean, ApexSQL Data Diff, ApexSQL Data Diff for MySQL, ApexSQL Diff, ApexSQL Diff for MySQL, ApexSQL Doc, ApexSQL Generate, ApexSQL Log, ApexSQL Restore, ApexSQL Script, ApexSQL Trigger, ApexSQL Unit Test
Summary
This article explains how to automate the execution of ApexSQL tools via the CLI while handling the passing of database credentials or using Windows authentication.
Option 1 – Directly supplying credentials as to relevant switches in CLI
Most ApexSQL tools use a command line interface aka CLI to allow for automatic and even unattended automation. We will use ApexSQL Diff for example. To use the CLI, navigate to the installation folder of ApexSQL Diff, enter the path of ApexSQLDiff.com file, the console application, and define the desired switches needed for execution. We’ll show an example below, including how to pass in the database credentials
ApexSQLDiff.com /server1:(local) /database1:AdventureWorks2016 /server2:DestinationServer /database2:AdventureWorks2014 /user2:destinationlogin /password2:destinationpassword /f /v
The problem with this approach is that the credentials are in plain text and would compromise database security if they were left in this state.
Option 2. Creating a PowerShell prompt to accept credentials and pass them to CLI
The second option for automating the execution of ApexSQL applications is to create a prompt in a script which calls upon a combination of specified CLI switches. This option is useful if you don’t want to leave the credentials in plain text in the script, or worry about forgetting to remove them after execution.
Learn more about this from the article, How to create a login form dialog using PowerShell, accept user input and pass it to an ApexSQL console application.
Option 3 – Using encrypted database credentials in the project file
Another approach will be to use the project file itself to pass the stored and encrypted database credentials, to the console application directly.
In this example, let’s set up ApexSQL Doc to be automated via the CLI using a project file.
To set up the project file, choose the required objects and set up the desired documentation options, and then save them all in a project file. To save a project file, click the Save or Save as button in the main ribbon bar.
If the decision is to save all necessary login credentials into a project file, then, before saving the project file, select the option Remember password, in the Add SQL server menu and the credentials will be remembered in the project file itself and they will be encrypted.
The encrypted password in the project file will be stored in an encrypted text format and it looks similar to this:
TrustedConnection=”False” StoreEncryptedPassword=”True” Password=”jYf3O2nO6/MmtwXm8a9Z4g==” Login=”Bravo”
When the project file is saved, it can be used in the CLI. Now open Windows PowerShell specify the location of ApexSQLDoc.com file which starts the CLI of ApexSQL Doc and it’s located in the installation folder of ApexSQL Doc. Right after that use the project file switch (/pr) to specify the previously saved project file and press enter.
‘ApexSQLDoc.com’ /pr:”C:\Users\PC\DBProject.axdp” /f /v
Option 4 – Using Windows authentication with the project file
Running the script using Windows authentication is more secure because since it uses a certificate-based security mechanism and the execution will not fail and it puts the user in charge if some changes are made in database credentials. Windows authentication logins pass access tokens instead of a name and password when connecting to SQL Server. When the user connects to a database, he’s is not required to enter a user name and password which makes this option the best solution for running the process unattended.
To use this option, first, the project file needs to be saved with the login information where windows authentication is used. That means, you need to log on to SQL Server using Windows authentication mode.
After that, you can continue setting up the project options as mentioned before and save the project file once completed.
When using this option, the project file will be mapped with the following setting:
” TrustedConnection=”True”
When the project file is saved, it can be used in the CLI. Now open Windows PowerShell specify the location of ApexSQLDoc.com file which starts the CLI of ApexSQL Doc and it’s located in the installation folder of ApexSQL Doc. Right after that use the project file switch (/pr) to specify the previously saved project file and press enter.
‘ApexSQLDoc.com’ /pr:” DBProject.axdp” /f /v
Once the executed process is complete, the console will display a “return code 0” which means that the operation is successful.
Once the executed process is complete, the console will display a “return code 0” which means that the operation is successful.
FAQ’s
Q: Can I run the executed process silently without any output progress or information?
A: Yes. To run any ApexSQL application process silently, specify the /silent switch in the script or console.
Q: Can I override any setting saved in the project file?
A: Yes. Any setting in a saved in the project file can be overridden by specifying the desired switch after the project file switch.
Q: Can I see the user name and password in the project file?
A: The user name is visible in the project file, but the password is stored as encrypted text.
Q: Can I export the CLI progress and results into a text file?
A: To export every detail of the documentation process into a text file, specify the /out switch followed by the targeted location
Q: Can I create a prompt for more than one server connection for ApexSQL tools?
A: Yes, for that you need to create a form for each server connection where credentials are required but not saved, and specify the user and password variables.
Q: What are the main advantages of using Windows authentication?
A: With Windows authentication you can run the process completely unattended and it guarantees a non-failing mechanism when it comes to connection if the database credentials are changed.
