1. Preface
This article is currently under construction and is being expanded (almost) daily. With this type of publication, we would like to get the existing chapters out to readers and users as quickly as possible. Feedback on the article and the way it is published is always welcome. Either by e-mail to feedback@checkmk.com or, for example, as an issue in Github (see link above).
2. Introduction
Checkmk enables you to comprehensively monitor Microsoft SQL Server (abbreviated to "MSSQL" in the following). You can find a complete list of all available check plug-ins in our Catalog of Check Plug-ins.
In order to be able to monitor the databases, you just need the agent plug-in mk-sql 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 either on the Checkmk server or on the database server.
The only mandatory requirement for the use of mk-sql is that the TCP/IP protocol is activated in the SQL Server network configuration.
If TCP/IP is not permitted or allowed in your MSSQL server environment, you will have to fall back on the legacy plug-in mssql.vbs until further notice.
3. Set up login for monitoring
The agent plugin 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 exclusively the Windows Authentication mode or the mixed mode SQL Server and Windows Authentication mode. In the following two sections, we explain what to look out for in the respective procedures.
3.1. Use system user (Windows only)
The Checkmk agent is executed by the system user (NT AUTHORITY\SYSTEM) on Windows systems.
If you can also use this system user (NT AUTHORITY\SYSTEM) for monitoring MSSQL, you only need to make sure that this login has at least permissions for the following securable elements (Securables):
View server state
Connect SQL
Depending on which databases of your instances you want to monitor and depending on which server roles and mappings the system user (NT AUTHORITY\SYSTEM) already has, it may also be necessary to grant authorization for Connect any database.
3.2. Use a dedicated account for monitoring
There are also good reasons for not running MSSQL monitoring via the Windows system user. The most banal: There is no Windows system user on Linux systems. It continues with security requirements in the company or simply the desire to have dedicated and clearly named logins, the meaning and purpose of which can already be seen from the name.
Of course, the agent plugin also works with such 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 system user, this requirement should already be met, but it should not go unmentioned.
If there is no corresponding user on your MSSQL server or in your instance, you can create one using the Microsoft SQL Server Management Studio:
This login requires the following authorizations:
View server state
Connect SQL
Connect any database
Grant these authorizations in the Object Explorer via Security > Logins. Open the properties of the login and click on Securables. Under Explicit you will find the three entries mentioned above. Set the corresponding checkmarks in the Grant column and confirm with OK.
Alternatively, you can also create the user and the authorizations via the command line.
In the following example, this is done for the user checkmk:
mssql> USE master;
CREATE USER checkmk WITH PASSWORD = 'MyPassword123';
GOund danach:
mssql> USE master;
GRANT CONNECT SQL TO checkmk;
GRANT CONNECT ANY DATABASE TO checkmk;
GRANT VIEW ANY DATABASE TO checkmk;
GO4. Setting up the connection manually
If you use a 
Checkmk Raw Edition or do not want to use the agent bakery of the commercial editions, set up the communication manually.
Note: The chapter on manual setup will be written and published soon.
5. Configuration using the Agent Bakery
Setup is greatly simplified in the commercial editions using the agent bakery, as syntax errors in the configuration files are avoided and adjustments to changing environments can be made easily.
The main difference to a manual installation is that you only have to work on the MSSQL host on 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.
You can then search for mssql in the search field:
Depending on how the agent plugin is allowed to log in to your SQL server (see Set up login for monitoring), select the appropriate option under Authentication.
If you use the system user (NT AUTHORITY\SYSTEM), leave the selection at Local Integrated Authentication (Windows).
5.1. 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 the standard case. However, if you have a more complex MSSQL world in your company, there are further options, both for monitoring the database on the same host and for monitoring it on a remote host. These are basically the same for both connection types.
| Option | Function |
|---|---|
Connection |
If you need more specific connection data for the general connection to the MS SQL server, you can enter it here. |
Data to collect (Sections) |
Here you can restrict section by section which data should be collected or whether it should be collected synchronously or asynchronously. |
Cache age for asynchronous checks |
You can change the caching 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 databases |
Settings for the instance search. |
Custom instances |
In addition to the general settings for accessing your MS SQL Server, you can make 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
Failed to gather SQL Server instances
Check whether the TCP/IP protocoll is configured and working.
7. Files and directories
7.1. On the MSSQL host (Windows)
| Pfad | Verwendung |
|---|---|
|
Plug-in directory |
|
Configuration file for the agent plug-in |
7.2. On the MSSQL host (Linux)
| Pfad | Verwendung |
|---|---|
|
Plug-in directory |
|
Configuration file for the agent plug-in |
7.3. On the Checkmk server
| Pfad | Verwendung |
|---|---|
|
The plug-in you have to copy to your Windows hosts, in order to monitor MS SQL there. |
|
The plug-in you have to copy to your Linux hosts, in order to monitor MS SQL there. |