0

Using Stack Exchange’s Opserver Step 2: SQL Configuration

Opserver Login ScreenOpserver is…

an open source server monitoring project from those innovative folks at StackExchange. I plan to use it to monitor SQL and web servers. In my last post, I built the project. Now I will configure monitoring access to my SQL servers.

A warning before we proceed:

I am new to Opserver and not affiliated with the project. This is a note about What Worked For Me™. Please do not rely on these notes when working with heavy machinery or robot armies.

On First Try

On first login, Opserver greets me with a warning because I did not enable any of the settings files in the project \Config folder.

Opserver No Config Warning

If you visit the /about page (manually enter the URL), you can see the default server configuration. Under “Sections”, you can see that all are disabled.

Opserver Default About Page

Adding SQL Configuration

Edit the “SQLSettings.json.example” in the project’s \Config folder. We will make changes and enable it by saving the file without the .example suffix.
Opserver Project Config Folder

Pat Pack has a useful post covering many of the configuration files.

I am not monitoring a cluster, so I omit that section.

My “Seattle-SQL” server has a unique configuration, so I provide both a name and connectionString.

KC-SQL and Denver-SQL have a similar connection string, so I setup the defaultConnectionString and provide the instance names below. Save this file as “SQLSettings.json”.

{
    "defaultConnectionString": "Data Source=$ServerName$;Initial Catalog=master;Integrated Security=SSPI;",
        "instances": [
            {
                "name": "Seattle-SQL",
                "connectionString": "Data Source=Seattle-SQL;Initial Catalog=master;UID=monitoruser;PWD=monitorpassword",
            },
            { "name": "KC-SQL" },
            { "name": "Denver-SQL" }
    ]
}

Something is still wrong…

When I restart, I am still getting the “Uh Oh! No Configuration!” error.

Opserver No Config Warning

When I view the /About page I can see that the SQL configuration is enabled with 3 nodes.

Opserver Config SQL Enabled

Access Denied?

…but when I click “(click to view)” then a “JSON” action link on the next page, I find the real issue:

Opserver Access Denied

Fix Access to Database Servers

So what is happening is that I am authenticated to visit the IIS dashboard, but not authorized based on my current Active Directory credentials to view the SQL Server data.

To resolve this, you can:

  • Login to Opserver with an Active Directory account with SQL server access.
  • Use the “alladmin” security provider rather than “AD” in SecuritySettings.config.

Choose an option that works best for your security needs. After a reboot of Opserver, the dashboard displays correctly.

Opserver Monitoring Dashboard

And now, I can drill down into Seattle-SQL to find out what is wrong.

Leave a Reply