[checkmk-commits] Check_MK Git: check_mk: #2472 MSSQL Agent Plugin: Can now be configured to auth as database user

Module: check_mk Branch: master Commit: dc768d1a43216a6b680d061b7c5ea4fb09a81a14 URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=dc768d1a43216a… Author: Lars Michelsen &lt;lm(a)mathias-kettner.de&gt; Date: Tue Jul 21 15:41:39 2015 +0200 #2472 MSSQL Agent Plugin: Can now be configured to auth as database user The mssql.vbs script can now be configured to authenticate as database user using a configured username / password combination. Previously it was only possible to authenticate using the system privileges of the user the agent is running with. This is still the default. If you need to authenticate as database user, you need to create a file named <tt>mssql.ini</tt>, or if you need it instance specific, <tt>mssql_[instance-id].ini</tt>. You need to write the following content into this file: F+:mssql.ini [auth] type = db username = monitoring-user password = mysecretpw F-: --- .werks/2472 | 24 +++++++++++ ChangeLog | 1 + agents/windows/plugins/mssql.vbs | 81 +++++++++++++++++++++++++++++++++----- 3 files changed, 96 insertions(+), 10 deletions(-) diff --git a/.werks/2472 b/.werks/2472 new file mode 100644 index 0000000..c693ea3 --- /dev/null +++ b/.werks/2472 @@ -0,0 +1,24 @@ +Title: MSSQL Agent Plugin: Can now be configured to auth as database user +Level: 1 +Component: checks +Compatible: compat +Version: 1.2.7i3 +Date: 1437485882 +Class: feature + +The mssql.vbs script can now be configured to authenticate as database user +using a configured username / password combination. Previously it was only +possible to authenticate using the system privileges of the user the agent +is running with. This is still the default. + +If you need to authenticate as database user, you need to create a file +named <tt>mssql.ini</tt>, or if you need it instance specific, +<tt>mssql_[instance-id].ini</tt>. You need to write the following content +into this file: + +F+:mssql.ini +[auth] +type = db +username = monitoring-user +password = mysecretpw +F-: diff --git a/ChangeLog b/ChangeLog index 2cc3802..5b75aad 100644 --- a/ChangeLog +++ b/ChangeLog @@ -23,6 +23,7 @@ * 2405 New checks for UCS bladecenter: ucs_bladecenter_topsystem, ucs_bladecenter_faulinst... * 2451 wut_webtherm.humidity, wut_webtherm.pressure: Two new checks for humidity and air pressure sensors for WuT devices... NOTE: Please refer to the migration notes! + * 2472 MSSQL Agent Plugin: Can now be configured to auth as database user... * 2315 FIX: windows agent: BOM replacement, fixed incorrect byte offset... * 2316 FIX: windows agent: fix garbled output of cached agent plugins... * 2358 FIX: check_mk_agent.solaris: more correct computation of zfs used space... diff --git a/agents/windows/plugins/mssql.vbs b/agents/windows/plugins/mssql.vbs index 834eb7c..20892fe 100644 --- a/agents/windows/plugins/mssql.vbs +++ b/agents/windows/plugins/mssql.vbs @@ -5,9 +5,16 @@ ' on the local system. ' ' The current implementation of the check uses the "trusted authentication" -' where no user/password needs to be created in the MSSQL server instance. It -' is only needed to grant the user as which the Check_MK windows agent service -' is running access to the MSSQL database. +' where no user/password needs to be created in the MSSQL server instance by +' default. It is only needed to grant the user as which the Check_MK windows +' agent service is running access to the MSSQL database. +' +' Another option is to create a mssql.ini file in MK_CONFDIR and write the +' credentials of a database user to it which shal be used for monitoring: +' +' [auth] +' username = monitoring +' password = secret-pw ' ' The following sources are asked: ' 1. WMI - to gather a list of local MSSQL-Server instances @@ -21,19 +28,49 @@ Option Explicit -Dim WMI, prop, instId, instIdx, instVersion, instIds, instName, output, WMIservice, colRunningServices, objService +Dim WMI, FSO, SHO, prop, instId, instIdx, instVersion, instIds, instName, output +Dim WMIservice, colRunningServices, objService, cfg_dir, cfg_file, hostname WScript.Timeout = 10 ' Directory of all database instance names Set instIds = CreateObject("Scripting.Dictionary") +Set FSO = CreateObject("Scripting.FileSystemObject") +Set SHO = CreateObject("WScript.Shell") +hostname = SHO.ExpandEnvironmentStrings("%COMPUTERNAME%") +cfg_dir = "C:\check_mk_agent" 'SHO.ExpandEnvironmentStrings("%MK_CONFDIR%") output = "" Sub addOutput(text) output = output & text & vbLf End Sub +Function readIniFile(path) + Dim parsed : Set parsed = CreateObject("Scripting.Dictionary") + If path <> "" Then + Dim FH + Set FH = FSO.OpenTextFile(path) + Dim line, sec, pair + Do Until FH.AtEndOfStream + line = Trim(FH.ReadLine()) + If Left(line, 1) = "[" Then + sec = Mid(line, 2, Len(line) - 2) + Set parsed(sec) = CreateObject("Scripting.Dictionary") + Else + If line <> "" Then + pair = Split(line, "=") + If 1 = UBound(pair) Then + parsed(sec)(Trim(pair(0))) = Trim(pair(1)) + End If + End If + End If + Loop + FH.Close + End If + Set readIniFile = parsed +End Function + ' Dummy empty output. ' Contains timeout error if this scripts runtime exceeds the timeout WScript.echo "<<<mssql_versions>>>" @@ -91,9 +128,7 @@ Next Set WMI = nothing -Dim CONN, RS, hostname - -hostname = WScript.CreateObject("WScript.Shell").ExpandEnvironmentStrings("%COMPUTERNAME%") +Dim CONN, RS, CFG, AUTH ' Initialize connection objects Set CONN = CreateObject("ADODB.Connection") @@ -106,8 +141,31 @@ CONN.Provider = "sqloledb" ' Loop all found server instances and connect to them ' In my tests only the connect using the "named instance" string worked For Each instId In instIds.Keys + ' Use either an instance specific config file named mssql_<instance-id>.ini + ' or the default mysql.ini file. + cfg_file = cfg_dir & "\mssql_" & instId & ".ini" + If Not FSO.FileExists(cfg_file) Then + cfg_file = cfg_dir & "\mssql.ini" + If Not FSO.FileExists(cfg_file) Then + cfg_file = "" + End If + End If + + Set CFG = readIniFile(cfg_file) + If Not CFG.Exists("auth") Then + Set AUTH = CreateObject("Scripting.Dictionary") + Else + Set AUTH = CFG("auth") + End If + ' At this place one could implement to use other authentication mechanism - CONN.Properties("Integrated Security").Value = "SSPI" + If Not AUTH.Exists("type") or AUTH("type") = "system" Then + CONN.Properties("Integrated Security").Value = "SSPI" + Else + CONN.Properties("User ID").Value = AUTH("username") + CONN.Properties("Password").Value = AUTH("password") + End If + wscript.echo instId If InStr(instId, "__") <> 0 Then instName = Split(instId, "__")(1) @@ -115,6 +173,7 @@ For Each instId In instIds.Keys Else instName = instId End If + wscript.echo instId ' In case of instance name "MSSQLSERVER" always use (local) as connect string If instName = "MSSQLSERVER" Then @@ -122,10 +181,10 @@ For Each instId In instIds.Keys Else CONN.Properties("Data Source").Value = hostname & "\" & instName End If - 'WScript.echo (CONN) + WScript.echo (CONN) CONN.Open - + ' Get counter data for the whole instance RS.Open "SELECT counter_name, object_name, instance_name, cntr_value " & _ "FROM sys.dm_os_performance_counters " & _ @@ -219,6 +278,8 @@ Next Set RS = nothing Set CONN = nothing +Set FSO = nothing +Set SHO = nothing ' finally output collected data WScript.echo output