Documentations

Configuring the supervision of an MS SQL Server database

On the page

Need some help?

MS SQL Server user creation

Create the user that will be used for monitoring on the MS SQL Server database that you want to monitor. This user has the minimum rights to access system information about the instance and the databases.
This user can then be set up as an MS SQL supervision account in the ServiceNav application.
Prefer a domain user rather than an SA user.
Run the following script on theMS SQL Server instance that you want to monitor, or alternatively perform the configuration with the Microsoft SQL Server Management Studio console.
The script performs the following configuration tasks:
  • user creation
  • user access authorization to the state system information of the Microsoft SQL Server instance
  • on each of the bases of the instance except master, tempdb, msdb:
    • creation of the role indicated in parameter
    • following rights granted to the role: execution on the base, definition of the base, collection of information base report system
    • creation of the user and addition of the role for the user
Before starting it, the following lines must be edited with the user's login information.
DECLARE @dbname VARCHAR(255)
DECLARES @check_mssql_health_USER VARCHAR(255)
DECLARES @check_mssql_health_PASS VARCHAR(255)
DECLARES @check_mssql_health_ROLE VARCHAR(255)
DECLARE @source VARCHAR(255)
DECLARES @options VARCHAR(255)
DECLARE @backslash INT

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

Please modify the above values 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
dblist deallocate

Configuration of the access to the base on the supervision box

This operation is carried out by Coservit on each supervision box in production, or by the reseller partner on the offer.

Edit the file /etc/freetds.conf.

AttentionThe 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 = 8.0

[LOGIN_NAME]
    host = fqdn or @IP
    port = TCP/IP port of the instance
    instance = proceeding name
    tds version = 8.0

CONNECTION_NAME is important: this is the name that will be used to set up the unit service.

ServiceNav configuration

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

The number of hours that have elapsed since the last backup of the MS SQL Server database instance. Configuration: the database instance, the account and password of the user with access to the database, the number of hours above which the status changes to ALERT, the number of hours above which the status changes to CRITICAL. The instance must be defined on the supervision server. The user must have the role serveradmin on the instance.

 

ServiceNav - MS SQL database monitoring

Deleting the user

The following script is used to delete the user created in step 1.

DECLARE @dbname VARCHAR(255)
 DECLARES @check_mssql_health_USER VARCHAR(255)
 DECLARES @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
 dblist deallocate
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 tools are not installed (case of boxes installed directly in version 3.13.1)

On the box, execute the following command line. (The box must have internet access)

apt-get install freetds-bin

 

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

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

Get the list of database instances

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

tsql -H  -L

 

Validate the access account to a database instance

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

On the box, execute the following command line, replacing by the name of the instance (in the example CONNECTION_NAME) and \ by the identifier (be careful to keep the double)

tsql -S  -U \

Enter the password at the system request (Password associated with the account \)

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

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

If the connection does not work, you get a response like :

locale is "fr_EN.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

It is then necessary to check your connection identifiers and/or your freetds file according to the indicated message.

Get the list of the bases of an instance

On the box, execute the following command line, replacing by the name of the instance (in the example CONNECTION_NAME) and \ by the identifier (be careful to keep the double)

tsql -S  -U \

Then enter the following command lines

select name from master.dbo.sysdatabases ;

Go

The expected result should be of the following type: (The list is variable according to your bases)

master
tempdb
model
msdb
BEDB

 

Validate database access account

On the box, execute the following command line, replacing by the name of the instance (in the example CONNECTION_NAME) and \ by the identifier (be careful to keep the double)

Then enter the following command lines (replacing with the database name)

use  ;

Go

In case of an access error you get a message of the type

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

This may also be of interest to you

2 - How to supervise an Azure environment

3 - Supervise Azure metrics via APIs

1 - Prerequisites Azure and Office 365 plugins

en_USEnglish
fr_FRFrench en_USEnglish

Welcome to ServiceNav!

Need help? More information about our products? Write to us!
You have taken note of our privacy policy.

[COVID - 19 ] - TELEWORKING, TARGET AVAILABILITY 100% !

While the epidemic lasts, ensure the availability and performance of your IT services for teleworking, with ServiceNav!

Following the government's call to mobilize to help businesses overcome the current health and economic context, we help you, free of charge, to ensure the complete monitoring of your teleworking environments: VPN, VDI, Teams, Skype Enterprise, Citrix... Objectives: collection, availability and usage indicators, dashboards to support your communication.
We use cookies to ensure that you have the best possible experience on our site, and 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.