1. Introduction
Checkmk enables you to comprehensively monitor Microsoft SQL Servers (abbreviated to 'MSSQL' in the following). A complete list of all available check plug-ins can be found in our Catalog of check plug-ins.
To be able to monitor the databases, you will also need the mk-sql
agent plug-in in addition to the Checkmk agent.
As of Checkmk 2.3.0, this agent plug-in is also able to monitor databases on remote systems.
No additional software is required on either the Checkmk or the database server.
The only mandatory requirement for using mk-sql
is that the TCP/IP protocol is activated in the SQL server network configuration.
If TCP/IP is not permitted or otherwise allowed in your MSSQL server environment, you must use the legacy agent plug-in mssql.vbs
until further notice.
2. Setting up an account for monitoring (Windows)
The agent plug-in requires access to your MSSQL instances. As a rule, it is clear — long before the monitoring team arrives — what such access can or may look like. In the case of MSSQL, there are two options. Either you use only the Windows Authentication mode or the mixed mode SQL Server and Windows Authentication mode.
2.1. Using the LocalSystem account
The Checkmk agent is executed on Windows systems under the LocalSystem account (NT AUTHORITY\SYSTEM
).
If you can also use this LocalSystem account for monitoring MSSQL, you will only need to ensure that this account (login) has as a minimum the permissions for the following securable elements (Securables):
View server state
Connect SQL
Connect any database (optional)
The permission Connect any database (optional) is optional, but is required in almost all MSSQL monitoring scenarios.
2.2. Using a dedicated account
There are good reasons for not running MSSQL monitoring via the Windows LocalSystem account. Potential reasons include security requirements in the organization or simply the desire to have dedicated and clearly named accounts, the purpose of which is already clear from the name.
The plug-in of course also works with such accounts (logins).
The basic requirement in MSSQL is that the Server authentication is set to SQL Server and Windows Authentication mode. If you do not want to or cannot use the Windows LocalSystem account, this requirement should have already been met, but it should not go unmentioned here.
If there is no corresponding user on your MSSQL server or in your instance, you can create one on your Windows system via the Microsoft SQL Server Management Studio (with any username):

This login requires the following permissions:
Connect SQL
Connect any database
View server state
Grant these permissions in the Object Explorer via Security > Logins. Open the properties of the account (login) and click on Securables. Under Explicit you will find the three entries mentioned above. Set the corresponding check marks in the Grant column and confirm with OK.

Alternatively, you can also create the user and the permissions via the command line.
In the following example, this is done for the user checkmk
:
mssql> USE master;
CREATE USER checkmk WITH PASSWORD = 'MyPassword123';
GO
and after that:
mssql> USE master;
GRANT CONNECT SQL TO checkmk;
GRANT CONNECT ANY DATABASE TO checkmk;
GRANT VIEW SERVER STATE TO checkmk;
GO
2.3. Setting up the connection manually
If you use Checkmk Raw or do not want to use the Agent Bakery of the commercial editions, set up the communication manually.
For the rest of this description, we assume that the agent for Windows monitoring has already been installed on the host.
Creating a configuration file
On the Windows host create an mk-sql.yml
file in the directory specified below:
When using the LocalSystem account, this is all that is required:
---
mssql:
main:
authentication:
username: ''
type: integrated
If you use a dedicated account for monitoring, in its place, you will need the following content:
---
mssql:
main:
authentication:
username: checkmk
password: MyPassword123
type: sql_server
If you are running multiple MSSQL instances with different usernames and passwords on the host, add the details of the instances to the yml
file accordingly.
Your yml
file could then look like this, for example:
---
mssql:
main:
authentication:
username: ""
type: integrated
instances:
- sid: myInstance 1
auth:
username: myUser1
password: "123456"
type: sql_server
- sid: myInstance 2
auth:
username: myUser2
password: "987654"
type: sql_server
Installing the agent plug-in
The mk-sql.exe
plug-in can be found
in Checkmk Raw under Setup > Agents > Windows and in the commercial editions
under Setup > Agents > Windows, Linux, Solaris, AIX > Related > Windows files:

Download the mk-sql.exe
file and save it on the Windows host in the directory C:\ProgramData\checkmk\agent\plugins\
.
Configuring services
Now that you have installed and configured the agent plug-in, you can perform a service discovery for this host. The following screenshot shows only a selection of the services that can be found:

2.4. Extended configuration
Asynchronous execution of the agent plug-in
The agent plug-in for monitoring MSSQL can — like so many others — be executed asynchronously, for example to take account of the longer runtimes of large MSSQL instances.
To run the agent plug-in asynchronously under Windows, edit the configuration of the plug-in and add a cache_age
line to the main
section:
mssql:
main:
authentication:
username: myuser
password: mypassword
type: sql_server
cache_age: 300
For detailed instructions on the asynchronous execution of agent plug-ins on a Windows host, see the article that explains the Windows agent.
3. Setting up an account for monitoring (Linux)
3.1. Creating a user for the monitoring
You will need a user who can connect to your MSSQL instance. This user requires at least the following permissions:
Connect SQL
Connect any database
View server state
Apart from these permissions, it is up to you or your organization how such a user can or must be created.
You can use a local user for testing purposes. If there is no corresponding user on your MSSQL server or in your instance, you can create one on your Linux system via the command line:
In the following example, this is being done for the user checkmk
:
user@host:~$ sudo adduser --system checkmk --ingroup sudo
Follow the prompts to assign a password, etc.
Then:
mssql> USE master;
GRANT CONNECT SQL TO checkmk;
GRANT CONNECT ANY DATABASE TO checkmk;
GRANT VIEW SERVER STATE TO checkmk;
GO
3.2. Manual setup of the connection
If you are using Checkmk Raw or do not want to use the Agent Bakery of the commercial editions, set up the communication manually.
For the rest of this description, we will assume that the agent for Linux monitoring has already been installed on the host.
Create a configuration file
On the Linux host, create an mk-sql.yml
file in the directory specified below:
---
mssql:
main:
authentication:
username: checkmk
password: checkmkPW
type: sql_server
If you are running multiple MSSQL instances with different usernames and passwords on the host, add the details of the instances to the yml
file accordingly.
Your yml
file could then look like this, for example:
---
mssql:
main:
authentication:
username: checkmk
password: checkmkPW
type: sql_server
instances:
- sid: myInstance1
auth:
username: myUser1
password: 123456
type: sql_server
- sid: myInstance2
auth:
username: myUser2
password: 987654
type: sql_server
Installing the agent plug-in
The mk-sql
plug-in can be found
in Checkmk Raw under Setup > Agents > Linux and in the commercial editions
under Setup > Agents > Windows, Linux, Solaris, AIX > Related > Linux, Solaris, AIX files:

Download the mk-sql
file and place it on the Linux host in the /usr/lib/check_mk_agent/plugins/
directory.
Setting up services
Now that you have installed and configured the agent plug-in, you can perform a service discovery for this host. The following screenshot shows only a selection of the services that can be found:

3.3. Extended configuration
Asynchronous execution of the agent plug-in
The plug-in for monitoring MSSQL can — like so many others — be executed asynchronously, for example to take account of longer runtimes for large MSSQL instances.
To execute the agent plug-in asynchronously under Linux, edit the configuration of the plug-in and add a cache_age
line to the main
section:
mssql:
main:
authentication:
username: myuser
password: mypassword
type: sql_server
cache_age: 300
For detailed instructions on how to run agent plug-ins asynchronously on a Linux host, see the article on the Linux agent.
4. Further configuration options in Checkmk
4.1. Configuring threshold values
Monitoring connections
You can specify your own threshold values for the utilization of the maximum number of simultaneous connections specified by MSSQL, as these are much more dependent on the structure of your MSSQL instance than with other services. You can set up the corresponding threshold values in no time at all using a rule from the MSSQL Connections rule set.

Monitoring file sizes
You can also define threshold values for the sizes of individual files in MSSQL. To do this, use the MSSQL datafile sizes rule set.

5. Configuration via the Agent Bakery
The setup has been very simplified in the commercial editions
using the Agent Bakery, as syntax errors in the configuration files are avoided and customizations to suit changing environments can be made easily.
The main difference to a manual installation is that you only have to work on the MSSQL host via the command line if you want to make special MSSQL-specific configurations.
For the initial setup it is sufficient to create a corresponding rule in the Agent Bakery.
You can find the rule set under Setup > Agents > Windows, Linux, Solaris, AIX > Agent rules.
There search for mssql
in the search field:

5.1. Setting up the agent rule (Windows)
Depending on how the agent plug-in is allowed to log in to your MSSQL server (see Setting up an account for monitoring), select the appropriate option under Authentication.
If you are using the LocalSystem account (NT AUTHORITY\SYSTEM
), leave the selection at Local Integrated Authentication (Windows).

If you use SQL Server Authentication instead, select the SQL database user credentials option, and also enter the Login name (see Using a dedicated account) in the User field and add the corresponding Password.
If you are running multiple MSSQL instances with different usernames and passwords on the host, select the Custom instances option. Use Add new element to access the details for the first — and then each additional — instance to be monitored. Enter the access data for each instance here, as described above.

5.2. Setting up the agent rule (Linux)
Under Authentication select the SQL database user credentials option.

Then enter the Login name in the User field and add the corresponding Password. For the Connection, add the Host name.
If you are running multiple MSSQL instances with different usernames and passwords on the host, select the Custom instances option. Use Add new element to access the details for the first — and then each additional — instance to be monitored. Enter the access data for each instance here, as described above.

5.3. Further options
With the settings you have just made, you first create a simple agent for monitoring your MSSQL instance, which is located directly on the host. All available data is evaluated directly in Checkmk and this should be sufficient for a typical case scenario. However, if your organization has a more complex MSSQL environment, there are further options, both for monitoring the database on the same host and for monitoring it on a remote host. These options are basically the same for both connection types.
Option | Description |
---|---|
Connection |
If you need more specific connection data for the general connection to the MSSQL server, you can specify this here. |
Data to collect (Sections) |
Here you can restrict by section which data should be collected and whether it should be collected synchronously or asynchronously. |
Cache age for asynchronous checks |
You can change the cache period in seconds for the previously defined asynchronous checks. |
Map data to specific host (Piggyback) |
Piggyback data can be assigned to a host, regardless of the actual source. For example, the SQL information can be separated from the data of the underlying Windows server. |
Discovery mode of database instances |
Settings for the search in the instance. |
Custom instances |
In addition to the general settings for accessing your MSSQL server, you can also define your own settings for specific instances here. |
Options |
The maximum number of parallel SQL server connections can be set here. |
6. Diagnostic options
6.1. Possible errors and error messages
Error message: Failed to gather SQL Server instances
Check whether the TCP/IP connection has been configured and is working properly.
7. Files and directories
7.1. On the MSSQL host (Windows)
File path | Description |
---|---|
|
Directory for storing the agent plug-ins. |
|
Configuration file for the agent plug-in. |
7.2. On the MSSQL host (Linux)
File path | Description |
---|---|
|
Directory for storing the agent plug-ins. |
|
Configuration file for the agent plug-in. |
7.3. On the Checkmk server
File path | Description |
---|---|
|
The agent plug-in to be copied to your Windows hosts in order to monitor MSSQL there. |
|
The agent plug-in to be copied to your Linux hosts to monitor MSSQL there. |