Configurer la supervision d’une base MS SQL Server

Création utilisateur MS SQL Server

Créer l’utilisateur qui sera utilisé pour la supervision sur la base MS SQL Server que vous souhaitez superviser. Cet utilisateur dispose des droits minimaux lui permettant d’accéder aux informations système sur l’instance et les bases.
Cet utilisateur pourra ensuite être paramétré en tant que compte de supervision de type MS SQL dans l’application ServiceNav.
Préférer un utilisateur de domaine plutôt qu’un utilisateur de type SA.
Exécuter le script suivant sur l’instance MS SQL Server que vous voulez superviser, ou alternativement effectuer la configuration avec la console Microsoft SQL Server Management Studio.
Le script effectue les tâches de configuration suivantes:
  • création de l’utilisateur
  • autorisation d’accès de l’utilisateur aux informations système d’état de l’instance Microsoft SQL Server
  • sur chacune des bases de l’instance à l’exception de master, tempdb, msdb:
    • création du rôle indiqué en paramètre
    • droits suivants octroyés au rôle : exécution sur la base, définition de la base, collecte des informations système d’état de la base
    • création de l’utilisateur et ajout du rôle pour l’utilisateur
Avant de le lancer, il faut éditer les lignes suivantes avec les informations de connexion de l’utilisateur.
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|Domainname]\Username"'
SET @check_mssql_health_PASS = 'Password'
SET @check_mssql_health_ROLE = 'ServiceNavMonitoring'
/*******************************************************************

Merci de modifier les valeurs ci dessus avec vos informations

- Exemple pour authentification Windows:
SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
Ne pas omettre les " .
SET @check_mssql_health_ROLE = 'Rolename'

- Exemple pour authentification SQLServer:
SET @check_mssql_health_USER = 'Username'
SET @check_mssql_health_PASS = 'Password'
SET @check_mssql_health_ROLE = 'Rolename'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Il est fortement recommande d utiliser une authentification windows
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*********** 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 de l’accès à la base sur le boitier de supervision

Cette opération est effectuée par Coservit sur chaque boitier de supervision en production, ou par le partenaire revendeur sur l’offre.

Editer le fichier /etc/freetds.conf.

Attention: Le fichier freetds.conf  est un lien vers le fichier suivant :  -> /usr/local/freetds/etc/freetds.conf

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

[NOM_CONNEXION]
    host = fqdn ou @IP
    port  = port TCP/IP de l’instance
    instance = nom de l’instance
    tds version = 8.0

NOM_CONNEXION est important : c’est ce nom qui sera utilisé pour paramétrer le service unitaire.

Configuration ServiceNav

Exemple : configuration du service unitaire MS-MSSQL-database-backup-age ayant l’objectif suivant :

Le nombre d’heures écoulées depuis la dernière sauvegarde des bases de l’instance de base de données MS SQL Server. Configuration: l’instance de la base, le compte et le mot de passe de l’utilisateur ayant un accès à la base, le nombre d’heures au-dessus duquel l’état passe à ALERTE, le nombre d’heures au-dessus duquel l’état passe à CRITIQUE. L’instance doit être définie sur le serveur de supervision. L’utilisateur doit disposer du rôle serveradmin sur l’instance.

 

ServiceNav - MS SQL database monitoring

Suppression de l’utilisateur

Le script suivant permet de supprimer l’utilisateur créé en étape 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.'

Aide

Obtenir la liste des instances de base de données

Sur la box, exécuter la ligne de commande suivante, en remplaçant <Host IP or name> par l’IP ou le nom de l’host:

/usr/local/freetds/bin/tsql -H <Host IP or name> -L

 

Valider le compte d’accès à une instance de base de données

Afin de valider que le compte créé (par vos soins ou le script ci dessus est bien fonctionnel).

Sur la box, exécuter la ligne de commande suivante, en remplaçant <tag defined in freetds.conf> par le nom de l’instance (dans l’exemple NOM_CONNEXION) et <Windows Domain>\\<Login Name> par l’identifiant (attention à bien conserver le double \)

/usr/local/freetds/bin/tsql -S <tag defined in freetds.conf> -U <Windows Domain>\\<Login Name>

Saisir le mot de passe à la demande du système (Mot de passe associé au compte <Windows Domain>\\<Login Name>)

Si la connexion est fonctionnelle, vous devez obtenir une réponse du type :

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

Si la connexion n’est pas fonctionnelle, vous obtenez une réponse du type :

locale is "fr_FR.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18452 (severity 14, state 1) from <Serveur>\<instance> 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

Il convient alors de vérifier vos identifiants de connexion et/ou votre fichier freetds en fonction du message indiqué.

Obtenir la liste des bases d’une instance

Sur la box, exécuter la ligne de commande suivante, en remplaçant <tag defined in freetds.conf> par le nom de l’instance (dans l’exemple NOM_CONNEXION) et <Windows Domain>\\<Login Name> par l’identifiant (attention à bien conserver le double \)

/usr/local/freetds/bin/tsql -S <tag defined in freetds.conf> -U <Windows Domain>\\<Login Name>

Saisir ensuite les lignes de commande suivante

select name from master.dbo.sysdatabases ;

Go

Le résultat attendu doit être du type : (La liste est variable selon vos bases)

master
tempdb
model
msdb
BEDB

 

Valider le compte d’accès à une base de données

Sur la box, exécuter la ligne de commande suivante, en remplaçant <tag defined in freetds.conf> par le nom de l’instance (dans l’exemple NOM_CONNEXION) et <Windows Domain>\\<Login Name> par l’identifiant (attention à bien conserver le double \)

Saisir ensuite les lignes de commande suivante  (en remplaçant <DATABASE_NAME> par le nom de la base de données)

use <DATABASE_NAME> ;

Go

En cas d’erreur d’accès vous obtenez un message du type

 "The server principal "Domaine\Login" is not able to access the database "<DATABASE_NAME>" under the current security context."
Facebook
Google+
Google+
http://servicenav.coservit.fr/documentation/configurer-la-supervision-pour-une-base-ms-sql-server/
Youtube
Youtube
LinkedIn