Page 1 of 2
MS SQL Monitoring
Posted: Mon Jan 25, 2016 5:05 am
by raamardhani7
Hi Team,
I am trying to set up MS SQL monitoring and I am facing the below issue.
Code: Select all
/usr/local/nagios/libexec/check_mssql_server.py -H server -U 'nagios' -P 'password' -I 'server' --time2connect --warning 1 --critical 5
Connection to the database failed for an unknown reason.
Could you please help me here, thanks.
Regards,
Ram.
Re: MS SQL Monitoring
Posted: Mon Jan 25, 2016 10:20 am
by lmiltchev
/usr/local/nagios/libexec/check_mssql_server.py -H server -U 'nagios' -P 'password' -I 'server' --time2connect --warning 1 --critical 5
Connection to the database failed for an unknown reason.
You should run:
/usr/local/nagios/libexec/check_mssql_server.py -H server -U 'nagios' -P 'password' -I 'Instance' --time2connect --warning 1 --critical 5
Connection to the database failed for an unknown reason.
To find out what are the instances installed on the server type "regedit" in the search bar, and hit "Enter". Then go to:
HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> MICROSOFT SQL SERVER -> INSTALLEDINSTANCES
double-click on "InstalledInstances" on the right-hand side to view them.
Instead of using an instance, you can try using a port number ("-p"). Use one or the other - can't use both. Hope this helps.
Re: MS SQL Monitoring
Posted: Mon Jan 25, 2016 11:47 am
by raamardhani7
lmiltchev wrote:/usr/local/nagios/libexec/check_mssql_server.py -H server -U 'nagios' -P 'password' -I 'server' --time2connect --warning 1 --critical 5
Connection to the database failed for an unknown reason.
You should run:
/usr/local/nagios/libexec/check_mssql_server.py -H server -U 'nagios' -P 'password' -I 'Instance' --time2connect --warning 1 --critical 5
Connection to the database failed for an unknown reason.
To find out what are the instances installed on the server type "regedit" in the search bar, and hit "Enter". Then go to:
HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> MICROSOFT SQL SERVER -> INSTALLEDINSTANCES
double-click on "InstalledInstances" on the right-hand side to view them.
Instead of using an instance, you can try using a port number ("-p"). Use one or the other - can't use both. Hope this helps.
Hi Team,
I have tried with the port.
Code: Select all
/usr/local/nagios/libexec/check_mssql_server.py -H server -U 'nagios' -P 'password' -p 1433 --time2connect --warning 1 --critical 5
SQL Server message 18456, severity 14, state 1, line 1:
Login failed for user 'nagios'.
DB-Lib error message 18456, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed
Re: MS SQL Monitoring
Posted: Mon Jan 25, 2016 12:00 pm
by lmiltchev
This Wizard/Plugin requires SQL authentication and will not work with Windows Auth. What kind of user is "nagios"? Does it have sufficient MSSQL privileges? Do you have any special characters in the password?
Re: MS SQL Monitoring
Posted: Mon Jan 25, 2016 1:19 pm
by raamardhani7
lmiltchev wrote:This Wizard/Plugin requires SQL authentication and will not work with Windows Auth. What kind of user is "nagios"? Does it have sufficient MSSQL privileges? Do you have any special characters in the password?
Password had @ in it, but earlier I tired without that too. Even then I had the same issue.
COuld you please tell me what privileges that we can grant for the user nagios, so that I can get it done ad give a try. thanks.
Re: MS SQL Monitoring
Posted: Mon Jan 25, 2016 2:46 pm
by lmiltchev
The user has to have at least database level permissions of "Connect" and role - "db_owner".
Re: MS SQL Monitoring
Posted: Thu Jan 28, 2016 1:40 pm
by raamardhani7
lmiltchev wrote:The user has to have at least database level permissions of "Connect" and role - "db_owner".
Hi Lmiltchev,
db_owner permissions are given to the user id.
Code: Select all
/usr/local/nagios/libexec/check_mssql_server.py -H USRCSQLQ05 -U 'domain\user' -P 'passwd' -p 1433 --time2connect --warning 10
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed
Could you please advice me here. thanks.
Re: MS SQL Monitoring
Posted: Thu Jan 28, 2016 1:57 pm
by tgriep
The check_mssql_server.py plugin will NOT work with Windows Authentication and requires local SQL authentication.
You will have to setup a local account on the MSSQL server, set the user rights to DBO and give that user connect rights to the master database and the check should work for you.
Re: MS SQL Monitoring
Posted: Thu Jan 28, 2016 2:33 pm
by raamardhani7
tgriep wrote:The check_mssql_server.py plugin will NOT work with Windows Authentication and requires local SQL authentication.
You will have to setup a local account on the MSSQL server, set the user rights to DBO and give that user connect rights to the master database and the check should work for you.
Hi Tom,
Code: Select all
/usr/local/nagios/libexec/check_mssql_server.py -H USRCSQLQ05 -U 'userid' -P 'password@123' -p 1433 --time2connect --warning 10
OK: Time to connect was 0.0247271060944s|time=0.0247271060944s;10;;;
Code: Select all
/usr/local/nagios/libexec/check_mssql_server.py -H USRCSQLQ05 -U 'userid' -P 'password@123' -p 1433 --averagewait --warning 20 --critical 30
SQL Server message 297, severity 16, state 1, line 1:
The user does not have permission to perform this action.
DB-Lib error message 297, severity 16:
General SQL Server error: Check messages from the SQL Server
But SQL team claims that they gave the highest privileges and whatever command we run using the user ID should work. i am going wrong somewhere with the command?
And they said when tried with the user ID and password that is shared, they are able to get the output for everything. could you please advise. thanks.
Re: MS SQL Monitoring
Posted: Thu Jan 28, 2016 3:04 pm
by jomann
Does the user only have permissions for a specified table? There are some commands that read from the internal MS SQL tables that may require special permissions.