Applies to
ApexSQL Audit 2016 and lower
Enterprises may have many database systems for various departments and projects. Having numerous SQL Server instances spread over multiple servers, it’s not uncommon for large enterprises to have 30 or more SQL Server instances that must be audited. Installing the ApexSQL Audit service (auditing prerequisite) manually on many different machines can be quite frustrating (even using Remote Desktop). In this article, we’ll describe a method for installing the ApexSQL Audit service remotely on one or multiple machines.
ApexSQL Audit components
The ApexSQL Audit setup program can be used to install three components:
- The main application (service, central repository database, UI)
- Web reports
- The auditing instance (service)
The main application and web reports are mandatory components.
The main application (also called the central instance) ensures auditing of local SQL Server instances, provides the central application for administering and configuring the auditing process, and communicates with a central repository database which stores the captured audit data.
The Web reports component is required for viewing and exporting the captured audit data. This component doesn’t have to be installed on the same machine as the main application. Since it’s a web-based application, it requires Internet Information Services (IIS) on the hosting machine. For additional performance, security, and reliability, most enterprises keep web servers and database servers on separate physical machines.
The above-mentioned components need to be installed only once.
The auditing instance is a Windows service which collects the SQL trace data. It doesn’t use any UI on the audited SQL Server instance, as all auditing configuration is done on the central machine where the main application is installed. Captured audit data is simply packaged and sent to the central repository database.
The ApexSQL Audit setup program supports silent installation, and in this article we will focus on silent installation of auditing instances.
Silent installation mode and arguments
In this article, we will focus on utilizing silent installation for remote installation of auditing instances. The table below provides the CLI arguments for ApexSQL Audit silent installation of the auditing instance.
| /LOG=ApexSQLAudit.Auditing.txt | Optional. Writes the setup log in a text file. |
| /SILENT | No setup pages, input prompts, or message boxes are shown. |
| /COMPONENTS=”Auditing” | Selects only the auditing instance component for installation. |
| /CentralInstanceAddress=WIN2008 | The address (network name or IP address) of the machine where the ApexSQL Audit main application and central instance are installed. The ApexSQL Audit service must be up and running. |
| /ServiceCredentialsUsername=”DomainName\Username” | Windows account under which the ApexSQL Audit service will run. |
| /ServiceCredentialsPassword=”Password” | Windows account credentials under which the ApexSQL Audit service will run. |
| /CreateSqlLoginForServiceCredentials=true | Optional. You can omit this argument, but then you need to remember to add the Windows account specified for the ApexSQL Audit service (see two rows above) as a SQL Server login to each SQL Server instance you wish to audit. |
(Note: Arguments related to the main application and web reports aren’t shown)
ApexSQLAudit.exe /LOG=ApexSQLAudit.Auditing.txt /SILENT /COMPONENTS=”Auditing” /IsSilent=true /CentralInstanceAddress=WIN2008 /ServiceCredentialsUsername=”DomainName\Username” /ServiceCredentialsPassword=”Password” /CreateSqlLoginForServiceCredentials=true
Just to be sure that this command works properly, run the command on a single remote machine and install the auditing instance. After the installation is completed, try to add that SQL Server auditing instance located on the remote machine from the main application UI.
As ApexSQL Audit doesn’t provide a direct option for installation of auditing instances remotely, the third-party tool PsExec will be used for this purpose.
PsExec
This is a free tool, which isn’t allowed to be distributed with other software products, like ApexSQL Audit. You can download it directly from Microsoft web site.
Once downloaded, copy the psexec.exe file to wherever is the most appropriate for you and run it from the command prompt, like in the following example:
psexec \\WIN2012 -u DomainName\Username -p YourPassword -c -f ApexSQLAudit.exe /LOG=ApexSQLAudit.Auditing.txt /SILENT /COMPONENTS=”Auditing” /IsSilent=true /CentralInstanceAddress=WIN2008 /ServiceCredentialsUsername=”DomainName\Username” /ServiceCredentialsPassword=”Password” /CreateSqlLoginForServiceCredentials=true
PsExec arguments define the target machine, credentials for accessing it, and the command to be executed. It’s the same ApexSQL Audit command used for silent installation, just appended to the PsExec arguments after “-f”
Example of the complete script
The previous method is good, but just simply copying the multiple commands in a single batch script would make it hard to read and locate errors. Therefore, here is a solution that uses two batch files:
The InstallSingleApexSQLAuditAuditingInstance.bat file
psexec \\%1 -u DomainName\Username -p YourPassword -c -f ApexSQLAudit.exe /LOG=ApexSQLAudit.Auditing.txt /SILENT /COMPONENTS=”Auditing” /IsSilent=true /CentralInstanceAddress=WIN2008 /ServiceCredentialsUsername=”DomainName\Username” /ServiceCredentialsPassword=”Password” /CreateSqlLoginForServiceCredentials=true
The InstallMultipleApexSQLAuditAuditingInstances.bat file
Call InstallSingleApexSQLAuditAuditingInstance.bat WIN2008_1
Call InstallSingleApexSQLAuditAuditingInstance.bat WIN2008_2
Call InstallSingleApexSQLAuditAuditingInstance.bat WIN2008_3
In this example, we assume that target machines are part of a single Windows domain, and that a domain account(s) “DomainName\Username” on those target machines has local administrator privileges. Alternatively, non-domain local administrator accounts can be used as well, in PsExec arguments if the target machine is not under the Windows domain (e.g. “MachineName\Username”) and these scripts must be modified accordingly.
If the Domain Administrator account used or the local administrator account is the same for all target machines, the complete installation can be done using the single PsExec command by listing the target computers in the command.
Detecting and resolving installation failures
Sometimes, things can go wrong due to various reasons like incorrect arguments or missing privileges. If all required arguments are supplied and the installer encounters an error in the final stage of the installation process, it will be logged in a file on the target machine in the Local Application Data folder:
%USERPROFILE%\AppData\Local\ApexSQL\ApexSQLAudit2015
If, however, the setup fails due to invalid arguments and doesn’t even get to the main installation phase, the error will not be logged at all. In such case, you must rely on the console output or main setup log, which is stored as a local file (the filename is specified as command line argument). On a 64-bit system, the log is generated in the %windir%\SysWOW64 directory. Setting an absolute path for the log file (e.g. “/LOG=C:\ApexSQLAudit.Auditing.log”) can make it easier to locate.
Debugging and fixing PsExec related issues is beyond the scope of this article.
For any assistance on installing ApexSQL Audit using PsExec, please contact ApexSQL Support
Useful link
