Create an MS-SQL Server user
A domain user is preferred to an 'sa' user.
- user creation
- authorised user access to system state information of the Microsoft SQL Server instance
- on each database of the instance except master, tempdb, msdb:
- creation of the role indicated in the parameter
- following rights granted to the role: execution on the database, definition view, database state view
- creation of the user and addition of the role for the user
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 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
Configuring access to the database from the ServiceNav Box
This is done by Coservit (or the partner) for each SNB in production.
Edit the file /etc/freetds.conf.
CautionThe 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 [LOGIN_NAME] host = fqdn or @IP port = TCP/IP port of the instance instance = proceeding name tds version = 7.0
LOGIN_NAME is important: this is the name that will be specified in services.
ServiceNav configuration
Example: Configuring the User Service "MS-SQL database-backup-age" with the objective to measure 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 WARNING, the number of hours above which the status changes to CRITICAL. The instance must be defined on the SNB. The user must have the serveradmin role on the instance.
Deleting the user
The following script is used to delete the user created earlier.
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 (in the case of boxes installed directly in version 3.13.1)
On the SNB, execute the following command line. (The box must have internet access)
apt-get install freetds-bin
For all the commands below, if yours is an Ubuntu 12 version SNB, you must add "/usr/local/freetds/bin/" to 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 Host IP or name with the IP or name of the host:
tsql -H-L
Validate the access account with a database instance
To validate that the account created (by you or the script above is functional).
On the box, execute the following command line, replacing
tsql -S-U \\
Enter the password at the system's request (Password associated with the account
If the connection is OK, 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) fromLine 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 message received.
Get the list of instance databases
On the box, execute the following command line, replacing
tsql -S-U \\
Then enter the following command lines
select name from master.dbo.sysdatabases ; Go
The expected result should be similar to this: (The list is variable according to your database)
master tempdb model msdb BEDB
Validate database access account
On the box, execute the following command line, replacing
Then enter the following command lines (replacing
use DATABASE_NAME ; Go
In event of an access error you'll get a message similar to
"The server principal "DomaineLogin" is not able to access the database "Database_Name" under the current security context."