Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Log & Event Manager (LEM) > Configure MSSQL Auditor on a LEM Agent

Configure MSSQL Auditor on a LEM Agent

Overview

Auditing a Microsoft SQL Server to LEM has 4 parts:

  • Installing the MSSQL Profiler on the SQL Server – The profiler defines the auditing needed from the SQL database. The Profiler is included with Microsoft SQL, and is specific to each version of SQL.
  • Installing the MSSQL Auditor – The Auditor performs the connection to the SQL database and places the logs to a file. The Auditor can be downloaded from the SolarWinds Customer Portal.
  • Installing the LEM Agent on the SQL Server – The LEM Agent delivers the log data from the Connector to LEM. The LEM Agent can be downloaded from the SolarWinds Customer Portal.
  • Configuring the MSSQL Connector – The SQL Connector fetches data from the file location set by the Auditor. The MSSQL Connector is configured under the Agent.
     

Note: Depending on Active Directory group policies, local permissions and rights, software already installed on the SQL server, and firewall settings, there may be issues why the SQL Auditor cannot be installed.

Depending upon the permissions and rights of the SQL server, you may need to designate a separate server (does not need to be a SQL server), to be the SQL management server.

This server would have the SQL Profiler, SQL Auditor, LEM agent, and SQL Auditor Connector installed and configured.

Typically, the profiler, auditor, agent and connector are installed and configured on the same server as SQL (SQL 2005, SQL 2008, SQL 2012, SQL 2016, 2000-SQL-MSDE, 2005-SQL-Express, 2008-SQL-Express.

 

It is very important that you install both SQL auditor and the LEM Agent using the Runas Administrator option. To do so, copy the installer files to the local hard drive, right-click the installer file, and choose "Runas administrator." This step in required even if your user account is an administrator account.

Environment

All versions of LEM

Requirements

Install the following components on your database server prior to installing MSSQL Auditor.

  • Microsoft SQL 2005 Profiler or 2008 Profiler or 2012 Profiler (matches SQL version)
  • Microsoft .NET 2.0 Framework
  • SolarWinds LEM Agent for Windows

Note:  Multiple SQL Servers can be monitored from a 'SQL mgmt' server, and procedure is at the bottom of this article. The SQL mgmt server does not require SQL to be installed. When you have decided to monitor your SQL servers with MSSQL Auditor, select a system that has network access to the SQL Servers to be monitored, and that are on the same domain. Install SQL System Profiler, SolarWinds LEM MSSQL Auditor, and the LEM agent on that same server. This allows you to monitor multiple SQL servers from one system, and reduces the workload on the SQL server itself. However the version of SQL must match the installed Profiler version. If 2008 SQL you must install 2008 Profiler.


Procedure

I. Install MSSQL Profiler:

Refer to Microsoft documentation.

Note: When auditing and monitoring multiple SQL Servers, add all servers to the Profiler configuration.

 

II. Install MSSQL Auditor on the SQL server:

Note: You must install SQL Auditor using the Runas Administrator option.

  1. Extract the contents of SolarWinds-LEM-v6.1.0-MSSQLAuditor.zip in your LEM software package to a local hard drive on the SQL server.

    Get the latest version of MSSQLAuditor, which connects with the newer versions of SQL Server.

  2. From the extracted folder “SolarWinds Log & Event Manager\MSSQL Auditor“, right-click mssqlaudsetup.exe > and select Runas Administrator, which only works if the installer is located on the local hard drive.
  3. Click Next to start the wizard.
  4. If you agree, acceptthe End User License Agreement, and then click Next.
  5. Click Change to specify an installation folder, or accept the default, and then click Next.
  6. Click Install.
  7. When the installation is finished, select Launch SolarWinds MSSQL Auditor, and click Finish.
     
    Note:  SQL Auditor cannot be un-installed from Programs & Features.
    A Microsoft Un-install utility is needed to remove the SQL Auditor, and the registry entry is a manual entry.
    Here is the un-installer:  http://support.microsoft.com/mats/Pr...and_Uninstall/
    Here is the registry key that would need to be removed (not required if re-installing):  
         HKEY_CLASSES_ROOT\Installer\Products\5E257EDD08916A14BAD710D9947A9705
     

III. Launch SQL Profiler to import and save the template

Note: The template defines the fields to be audited by the SQL Auditor.

  1. Launch SQL Profiler, and then select Import from the File > Templates > Import menu.
  2. Import the "SQL specific" tdfs file from C:\program files (x86)\Solarwinds Log and Event Manager SQL Auditor\tdfs\
  3. Use the drop-down menus in the middle to select the Solarwinds/Trigeo template. Specify the appropriate version of SQL Server.
  4. Choose File > Templates > Edit Template.
    In the Template Properties dialog, select the “Use as a default template for selected server type” option, and then click Save.  (For context, click here to view a screen capture of the Properties dialog.)
  5. Verify that Profiler saved the template to the same directory that it was imported from. If Profiler saved the template to the user profile, move it to the correct location.
  6. Locate and copy the saved template to: C:\Users\<username>\AppData\Roaming\Microsoft\SQL Profiler\SQL\<10>\*.tdfs.
  7. Open Windows Explorer and navigate to C:\program files (x86)\Solarwinds Log and Event Manager SQL Auditor\tdfs\
  8. Rename the original Solarwinds2008.tdfs file (and add .old to the filename). This is the file that we imported to Profiler.
  9. Paste (save) the copied file (copy of template saved by Profiler) to this directory.
  10. Go to the next section to configure the SQL Auditor.

 

IV. Configure MSSQL Auditor for use with your servers:

  • If you did not select Launch SolarWinds MSSQL Auditor from previous steps above, you can launch it from the SolarWinds Log and Event Manager Program group from the Start menu.
  • SolarWinds recommends using a domain-based account that has sysadmin privileges on the database, although the account only needs to have Execute permissions for any stored procedures that have the xp_trace prefix.

 

  1. Enter the name of the SQL Server instance that you want to monitor in the SQL Server\Instance field (the server name is preferred, and the DB instance must be known), and then click Add Server. Do the same for each SQL Server.
    In the "SQL Server Configuration Manager" example below, the database instance is "MSSQLSERVER", which is also the default instance for SQL Server.



    SQL Server Management Studio will show the Database names. Open Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance ri view databases.
     
  2. If you want to use an account other than the Local System Account to run MSSQL Auditor on your database server, select This Account from the Run Service As section, and provide the appropriate credentials.
    This account must have the "runas a service" right, and have SA privileges.
     
  3. Click Start Auditor Service (green Play icon) from the Manage Auditor Service section, and then click OK.
    You can do the test (only works before starting the auditor), this is a basic test for functionality.
     
  4. SQL Auditor should now grab the new configuration template file, and SQL Auditor should be logging the data. to the logs directory:  C:\program files (x86)\Solarwinds Log and Event Manager SQL Auditor\logs\

 

V. Start the MSSQL Auditor connector on your LEM Agent:

Note: Repeat these steps for the MSSQL 2000 Application Log connector, if SQL Logging appears in the Microsoft Application event log.

  1. Open your LEM Console and log into your LEM Manager as an administrator.
  2. Click Manage, and then select Nodes.
  3. Locate the LEM Agent for your database server and verify it is connected to your LEM Manager.
  4. Click the gear icon next to the LEM Agent, and select Tools.
  5. Enter MSSQL in the search box at the top of the Refine Results pane.
  6. Click the gear icon next to the SolarWinds Log and Event Manager MSSQL Auditor 2000, 2005, 2008, 2012, 2014 connector, and then select New.
  7. Give the new tool a custom Alias, or accept the default.
  8. Verify that the value in the Log File field matches the folder on your database server, and then click Save.
  9. Click the gear icon next to the new tool (denoted by an icon in the Status column), and then select Start.
  10. Click Close to exit the Connector Configuration window.

 

Note: You can verify your setup by creating a filter for all Alerts with MSSQL in theToolAlias field. The exact conditions would state, Any Alert.ToolAlias = *MSSQL*, provided you accepted the default Alias values above.


If you are seeing the data in the LEM GUI-console, it should also be going into the database.
If you have trouble with this, contact SolarWinds Support for assistance.

-----

Optional:

Deploying SQL Auditor for multiple SQL servers (different versions of Windows and different versions of SQL).

Microsoft SQL Profiler comes with each version of SQL, and it needs to match the SQL version.
If the SQL Profiler does not match the SQL version, the Auditor may not collect logs, and it may not run.

The Solarwinds SQL Auditor is a universal version, but during install the executables used (within SQL auditor) are specific to a particular version of SQL.
While you can deploy SQL Profiler, SQL Auditor, and the LEM agent (with associated SQL connectors) on each SQL server, it is not necessary.

You can install the Profiler, Auditor, and Agent on a designated "SQL management" server(s). These are just Windows server(s) with Profiler, Auditor, and Agent installed.

 

For explanation here, assume the following:

SQL management servers

server A = windows 2012-R1, no SQL installed
server B = windows 2008-R2, SQL 2012 installed
server C = windows 2016-R1, no SQL installed

 

SQL servers (servers with SQL installeded, and SQL servers to be monitored.)

server1 = windows 2008-R2, SQL 2008
server2 = windows 2012-R1, SQL 2008

server3 = windows 2008-R1, SQL 2012
server4 = windows 2012-R2, SQL 2012

server5 = windows 2008-R2, SQL 2016
server6 = windows 2016-R1, SQL 2016

-----

Now configure the servers that collect the logs. (Note: The SQL Server version is important, not the version of Windows.)
 - In each example, all 3 installs will need to be configured to get the logs.
 - The Auditor will need to be pointed at the SQL database (on the SQL server) to read the database logs.
     (be sure you have the SQL server hostname, SQL database name, and SQL instance name.)
 - The LEM Agent has the LEM hostname or IP contained in the config file (C:\windows\syswow64\ContegoSPOP\spop.conf) when the agent is installed.

Server A --> Install SQL 2008 Profiler, install (latest) SQL Auditor, and point each to get SQL from server1 and server2. (Note SQL version 2008)
          Install the LEM 6.3.1 Agent, and enable/config the SQL connectors on the Agent.

 

Server B --> Install SQL 2012 Profiler, and then install (the latest) SQL Auditor. Point each to get SQL from server3 and server4. (Note SQL version 2012)
          Install LEM 6.3.1 Agent, and enable/config the SQL connectors on the agent.

 

Server C --> Install SQL 2016 Profiler, install (latest) SQL Auditor, and point each to get SQL from server5 and server6. (Note SQL version 2016)
          Install LEM 6.3.1 Agent, and enable/config the SQL connectors on the agent. (SQL Auditor for SQL 2016 uses the same executable for gathering logs as SQL 2012.)

 

Look for receipt of SQL logs from all 3 "management" servers.

 

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any. 

Last modified
10:49, 26 Jul 2017

Tags

Classifications

Public