Documentation

Configuring MS SQL Server database monitoring

On the page

Do you need help?

MS SQL Server user creation

Create a user to be used for monitoring the MS SQL Server database. This user has the minimum rights to access system information on the instance and the databases.
The user can be set up as an MS SQL type monitoring account in the ServiceNav application.
A domain user is preferred to an SA user.
Run the following script on theMS SQL Server instance that you wish to monitor, or alternatively create the user in the Microsoft SQL Server Management Studio console.
The script performs the following configuration tasks:
  • user creation
  • user access permission to the system status information of the Microsoft SQL Server instance
  • on each database of the instance, except master, tempdb, msdb:
    • creation of the specified role
    • the following rights granted to the role: run on the database, define the database, collect the system status information of the database
    • create user and add role for user
Before running it, the lines in red must be edited with the user's login information.
DECLARE @dbname VARCHAR(255)
DECLARE @check_mssql_health_USER VARCHAR(255)
DECLARE @check_mssql_health_PASS VARCHAR(255)
DECLARE @check_mssql_health_ROLE VARCHAR(255)
DECLARE @source VARCHAR(255)
DECLARE @options VARCHAR(255)
DECLARE @backslash INT

/*******************************************************************/
SET @check_mssql_health_USER = '"[Servername OR Domainname]\Username"'
SET @check_mssql_health_PASS = 'Password'
SET @check_mssql_health_ROLE = 'ServiceNavMonitoring'
/*******************************************************************

Please modify the values above with your information

- Example for Windows authentication:
SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
Do not omit the ".
SET @check_mssql_health_ROLE = 'Rolename'

- Example for SQLServer authentication:
SET @check_mssql_health_USER = 'Username'
SET @check_mssql_health_PASS = 'Password
SET @check_mssql_health_ROLE = 'Rolename

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
It is strongly recommended to use a windows authentication
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*********** NO NEED TO CHANGE ANYTHING BELOW THIS LINE *************/

SET @options = 'DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=English'
SET @backslash = (SELECT CHARINDEX('\', @check_mssql_health_USER))
IF @backslash > 0
BEGIN
SET @source = ' FROM WINDOWS'
SET @options = ' WITH ' + @options
END
ELSE
BEGIN
SET @source = ''
SET @options = ' WITH PASSWORD=''' + @check_mssql_health_PASS + ''',' + @options
END

PRINT 'CREATE Nagios plugin USER ' + @check_mssql_health_USER
EXEC ('CREATE LOGIN ' + @check_mssql_health_USER + @source + @options)
EXEC ('USE MASTER GRANT VIEW SERVER STATE TO ' + @check_mssql_health_USER)
PRINT 'USER ' + @check_mssql_health_USER + ' created.'
PRINT ''

declare dblist cursor for
select name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
fetch next from dblist into @dbname
while @@fetch_status = 0 begin
EXEC ('USE [' + @dbname + '] print ''GRANT permissions IN the db '' + ''"'' + DB_NAME() + ''"''')
EXEC ('USE [' + @dbname + '] CREATE ROLE ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] GRANT EXECUTE TO ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] GRANT VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] GRANT VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] CREATE USER ' + @check_mssql_health_USER + ' FOR LOGIN ' + @check_mssql_health_USER)
EXEC ('USE [' + @dbname + '] EXEC sp_addrolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
EXEC ('USE [' + @dbname + '] print ''Permissions IN the db '' + ''"'' + DB_NAME() + ''" GRANTED.''')
fetch next from dblist into @dbname
end
close dblist
deallocate dblist

Configuration database access on the ServiceNav box

This operation is performed by Coservit on each ServiceNav box in production, or by the reseller partner.

Edit the /etc/freetds.conf file.

NoteThe freetds.conf file is a link to the following file: -> /usr/local/freetds/etc/freetds.conf

[global]
# TDS protocol version
# tds version = 4.2
tds version = 7.0

[CONNECTION_NAME]
    host = fqdn or @IP
    port = TCP/IP port of the instance
    instance = name of the instance
    tds version = 7.0

CONNECTION_NAME is important: it is this name that will be specified in the service configuration.

ServiceNav configuration

Example: configuration of the MS-MSSQL-database-backup-age unit service template with the following goal:

To monitor the number of hours since the last database backup of the MS SQL Server database instance. Configuration: the database instance, the name and password of the user with access to the database, the number of hours above which the status changes to WARNING, the number of hours above which the status changes to CRITICAL. The instance must be defined on the ServiceNav box. The user must have the serveradmin role on the instance.

ServiceNav - MS SQL database monitoring

Deleting the user

The following script deletes the user created in step 1.

DECLARE @dbname VARCHAR(255)
 DECLARE @check_mssql_health_USER VARCHAR(255)
 DECLARE @check_mssql_health_ROLE VARCHAR(255)
 SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
 SET @check_mssql_health_ROLE = 'ServiceNavMonitoring'
 DECLARE dblist cursor FOR
 SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'msdb') OPEN dblist
 fetch NEXT FROM dblist INTO @dbname
 while @@fetch_status = 0 BEGIN
 EXEC ('USE [' + @dbname + '] print ''Revoke permissions in the db '' + ''"'' + DB_NAME() + ''"''')
 EXEC ('USE [' + @dbname + '] EXEC sp_droprolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
 EXEC ('USE [' + @dbname + '] DROP USER ' + @check_mssql_health_USER)
 EXEC ('USE [' + @dbname + '] REVOKE VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
 EXEC ('USE [' + @dbname + '] REVOKE VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
 EXEC ('USE [' + @dbname + '] REVOKE EXECUTE TO ' + @check_mssql_health_ROLE)
 EXEC ('USE [' + @dbname + '] DROP ROLE ' + @check_mssql_health_ROLE)
 EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" revoked.''')
 fetch NEXT FROM dblist INTO @dbname
 END
 close dblist
 deallocate dblist
PRINT ''
PRINT 'drop Nagios plugin user ' + @check_mssql_health_USER
EXEC ('USE MASTER REVOKE VIEW SERVER STATE TO ' + @check_mssql_health_USER)
EXEC ('DROP LOGIN ' + @check_mssql_health_USER)
PRINT 'User ' + @check_mssql_health_USER + ' dropped.'

Help

If the necessary tools are not installed (eg ServiceNav boxes installed directly in version 3.13.1)

Execute the following command on the ServiceNav box (The box must have access to the internet)

apt-get install freetds-bin

For all the commands below, if your box is on a Ubuntu version 12 , you must add "/usr/local/freetds/bin/" to the beginning of the command:

tsql -H ... becomes /usr/local/freetds/bin/tsql -H

Get list of database instances

On the ServiceNav box, execute the following command, replacing with the IP or name of the database host:

tsql -H  -L

Validate the access account with a database instance

In order to validate that the account created (by you or the script above) is working.

On the ServiceNavbox, execute the following command, replacing the instance name (in the example NAME_CONNECTION) and Windows Domain\\ Login Name by the credential (be careful to keep the double \)

tsql -S  -U \\

Enter the password when prompted by the system (Password belonging to the account)

If the connection is OK, you should get a response like this:

locale is "en_FR.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8

If the connection is not OK, you get a response like :

locale is "en_FR.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18452 (severity 14, state 1) from  Line 1:
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server

You should check your login details and/or your freetds file depending on the message shown.

Get list of instance databases

On ServiceNav box, execute the following command, replacing the instance name (in the example NAME_CONNECTION) and Windows Domain\\Login Name with the credential (be careful to keep the double \)

tsql -S  -U \\

Then enter the following commands

select name from master.dbo.sysdatabases ;

Go

The expected result should be similar to this: (The list is variable depending on your databases)

master
tempdb
model
msdb
BEDB

Validate a database access account

On the box, execute the following command, replacing with the instance name (in the example NAME_CONNECTION) and Windows Domain\\Login Name with the credential (be careful to keep the double \)

Then execute the following commands (including the name of your preferred database)

use  ;

Go

In case of an access problem you will get a message like this:

 "The server principal "DomaineLogin" is not able to access the database "" under the current security context."

You may also be interested in

Configure IBM AS/400 monitoring requirements

en_GB

Welcome to ServiceNav!

Do you need some help? More information about our products? Write to us!
You have taken note of our privacy policy.
We use cookies to ensure the best experience on our site. If you continue to use this site, we will assume that you are satisfied with it.

Reserve your place

You have taken note of our privacy policy.