So Many Databases To Monitor, So Little Time (Part 1 of 2)

So Many Databases To Monitor, So Little Time (Part 1 of 2)

Tripwire Enterprise is an industry leading tool that is commonly implemented as a File Integrity Monitoring (FIM) solution. The original software concept was developed back in 1992 as part of an academic project at Purdue, and has since been transformed into a configuration management suite that has been adopted by many.

In a recent project, West Monroe Partners implemented Tripwire Enterprise as a FIM solution intended to detect changes to critical files on systems supporting a line-of-business SaaS application. This scope included web, application, and Microsoft SQL database servers. One of the difficulties faced during this deployment was following Tripwire’s documentation when adding MS SQL database nodes to the system. The documentation dictates that (service) accounts must be manually assigned permissions and mapped to a database(s) using SQL Server Management Studio (SSMS). This process is clearly outlined in the documentation, but is not sustainable when the service account has to be mapped to many databases.

In the case of this project, a business process dictated that a separate database be created for each customer. Some database instances had over 150 databases, each requiring manual effort to map the user account that Tripwire would use to execute rule checks. With the environment containing 15+ database instances, we quickly found that some level of automation would be required. To address this, a Transact-SQL script was created.

The requirements for the script were:

  • Map the service account that Tripwire will use to the db_reader role for all databases on a given instance
  • Add the “alter trace” permission
  • Add the “view any definition” permission (to monitor all logins)

Executing the following in SSMS will achieve the above, allowing a database node to be successfully created and all of the corresponding databases to be monitored in Tripwire Enterprise. Replace <domain\user> with the domain and user account you are instructing Tripwire to leverage.

SQL

Now that the pre-work is done, the next step is to create the database instance node in Tripwire Enterprise, and add the database specific rules so that critical files, permissions, functions, etc. are monitored. As you may expect, the task of adding database-specific rules also involves much manual effort unless automation is leveraged. The second post in this series will describe how to create the needed database specific rules, leveraging the import/export rule functionality within Tripwire Enterprise and a Python script.

2 Comments

  • Inna January 19, 2017 2:28 pm

    Hi David,
    We’ve been running Tripwire in our environment for a while but are just evaluating to use it to monitor the MSSQL DBs. I’d interested to learn more about your experience and opinion on this.

    • David Wiggs January 20, 2017 8:18 am

      Hi Inna,
      Can you give me a little more information about what you’re looking for? I’d be happy to answer specific questions. Or are you looking for general information/lessons learned?

Your email address will not be published. Required fields are marked *

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons