Mysql 5.7 using TLS connections
"Edmunds, Grant (Contractor)" <[email protected]> Tue, 20 Feb 2018 21:23:39 +0000
| Newsgroups | gmane.network.nagios.plugins |
|---|---|
| Message-ID | <1a239a28db14404da55acb56e0b5f709__42966.2447343164$1519162230$gmane$org@PACDCEX42.cable.comcast.com> |
--_000_1a239a28db14404da55acb56e0b5f709PACDCEX42cablecomcastco_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I'm trying to use this plugin 'check_mysql_avs' with a mysql server (vd= ecmdb-as-01.sys.comcast.net) that is using TLS connections. With newer ver= sions of mysql (I'm using Percona 5.7.19-log) there's a setting called 'req= uire_secure_transport' when set, prevents users to login unless they can co= nnect using TLS. The user I'm testing with nagios@'op5%sys.comcast.net' ha= s 'require ssl' in its configuration. I've been trying to get this check = working (see below). I think that the certificate files need to be on the = OP5 servers for it to work. Is there a way to register them with the OP5 s= ervers? The mysql server does have the certificate registered. Here's something that is odd and you'll find blogs on this out there that d= iscuss this (see link https://www.percona.com/blog/2017/06/27/ssl-connectio= ns-in-mysql-5-7/). I can connect from another host with this command witho= ut providing the certificate information and a connection will be created a= nd will be encrypted. I'm wondering if you were to update the mysql client = software on the OP5 servers that might solve the issue? I'm guessing that = the script is written in Perl or Python so perhaps updating those RPM(s) ma= y solve it too? I'd be willing to test with you if you'd like. -sh-4.2$ hostname vdecmdbwst-ho-a2p.sys.comcast.net -sh-4.2$ mysql -h vdecmdb-as-01.sys.comcast.net -u nagios -pN3gi0spswd\) OP5 check definition nagios!N3gi0spswd\)!streamer -l --ca-cert=3D/db/data/CA_NSO_2010.crt --cert= =3D/db/data/vde_cmdb_east.crt --key=3D/db/data/vde_cmdb_east.key output on _USER1_/check_mysql -H 10.146.0.137 -u nagios -p N3gi0spswd\) -d streamer -= l --ca-cert=3D/db/data/CA_NSO_2010.crt --cert=3D/db/data/vde_cmdb_east.crt = --key=3D/db/data/vde_cmdb_east.key Result code: CRITICAL SSL connection error Mysql server mysql> show global variables like "%ssl%"; +---------------+----------------------------+ | Variable_name | Value | +---------------+----------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /db/data/CA_NSO_2010.crt | | ssl_capath | | | ssl_cert | /db/data/vde_cmdb_east.crt | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /db/data/vde_cmdb_east.key | +---------------+----------------------------+ 9 rows in set (0.00 sec) Grant Edmunds MySQL DBA CVPI - Core Video Platform Integration 303 712-3239 [email protected] --_000_1a239a28db14404da55acb56e0b5f709PACDCEX42cablecomcastco_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr= osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:= //www.w3.org/TR/REC-html40"> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"= > <meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)"> <!--[if !mso]><style>v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style><![endif]--><style><!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;} pre {mso-style-priority:99; mso-style-link:"HTML Preformatted Char"; margin:0in; margin-bottom:.0001pt; font-size:10.0pt; font-family:"Courier New";} span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;} span.extratest {mso-style-name:extra_test;} span.HTMLPreformattedChar {mso-style-name:"HTML Preformatted Char"; mso-style-priority:99; mso-style-link:"HTML Preformatted"; font-family:"Courier New";} .MsoChpDefault {mso-style-type:export-only;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext=3D"edit"> <o:idmap v:ext=3D"edit" data=3D"1" /> </o:shapelayout></xml><![endif]--> </head> <body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72"> <div class=3D"WordSection1"> <p class=3D"MsoNormal">Hi, I’m trying to use this plugin ‘check= _mysql_avs’ with a mysql server (vdecmdb-as-01.sys.comcast.net) that = is using TLS connections. With newer versions of mysql (I’m usi= ng Percona 5.7.19-log) there’s a setting called ‘require_secure= _transport’ when set, prevents users to login unless they can connect using TLS. = The user I’m testing with nagios@’op5%sys.comcast.net' has = 216;require ssl’ in its configuration. I’ve been tr= ying to get this check working (see below). I think that the certific= ate files need to be on the OP5 servers for it to work. Is there a way to regi= ster them with the OP5 servers? The mysql server does have the certif= icate registered. <o:p></o:p></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal">Here’s something that is odd and you’ll = find blogs on this out there that discuss this (see link <a href=3D"https://www.percona.com/blog/2017/06/27/ssl-connections-in-mysql= -5-7/">https://www.percona.com/blog/2017/06/27/ssl-connections-in-mysql-5-7= /</a>). I can connect from another host with this command without pro= viding the certificate information and a connection will be created and will be encrypted. I’m wondering if= you were to update the mysql client software on the OP5 servers that might= solve the issue? I’m guessing that the script is written in Pe= rl or Python so perhaps updating those RPM(s) may solve it too? I’d be willing to test with you if you’d l= ike.<o:p></o:p></p> <p class=3D"MsoNormal">-sh-4.2$ hostname<o:p></o:p></p> <p class=3D"MsoNormal">vdecmdbwst-ho-a2p.sys.comcast.net<o:p></o:p></p> <p class=3D"MsoNormal">-sh-4.2$ <b>mysql -h vdecmdb-as-01.sys.comcast.net -= u nagios -pN3gi0spswd\)</b><o:p></o:p></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal"><b>OP5 check definition<o:p></o:p></b></p> <p class=3D"MsoNormal">nagios!N3gi0spswd\)!streamer -l --ca-cert=3D/db/data= /CA_NSO_2010.crt --cert=3D/db/data/vde_cmdb_east.crt --key=3D/db/data/vde_c= mdb_east.key<o:p></o:p></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal"><b>output<o:p></o:p></b></p> <p class=3D"MsoNormal"><span style=3D"font-size:8.5pt;font-family:"Ari= al",sans-serif;color:#414141;background:#F7F5EF"> </span><s= pan style=3D"font-size:8.5pt;font-family:"Arial",sans-serif;color= :#414141">on </span><span style=3D"font-size:12.0pt;font-family:"= Times New Roman",serif"><o:p></o:p></span></p> <p class=3D"MsoNormal" style=3D"background:#F2F2F2"><span style=3D"font-siz= e:10.0pt;font-family:"Courier New";color:#414141">_USER1_/check_m= ysql -H 10.146.0.137 -u nagios -p N3gi0spswd\) -d streamer -l --ca-cert=3D/= db/data/CA_NSO_2010.crt --cert=3D/db/data/vde_cmdb_east.crt --key=3D/db/data/vde_cmdb_east.key<o:p></o:p></span></p> <p class=3D"MsoNormal" style=3D"background:#F2F2F2"><span style=3D"font-siz= e:10.0pt;font-family:"Courier New";color:#414141">Result code: CR= ITICAL<o:p></o:p></span></p> <p class=3D"MsoNormal" style=3D"background:#F2F2F2"><span style=3D"font-siz= e:10.0pt;font-family:"Courier New";color:#414141">SSL connection = error<o:p></o:p></span></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal"><b>Mysql server<o:p></o:p></b></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= mysql> show global variables like "%ssl%";<o:p></o:p></span></= p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= +---------------+----------------------------+<o:p></o:p></span= ></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | Variable_name | Value &nbs= p; = |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= +---------------+----------------------------+<o:p></o:p></span= ></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | have_openssl | YES &= nbsp; &nbs= p; |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | have_ssl | YES  = ; &n= bsp; |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_ca | /db/data/CA_NSO_2010.c= rt |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_capath | &= nbsp; &nbs= p; |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_cert | /db/data/vde_cmdb_east.crt |<o:p= ></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_cipher | &= nbsp; &nbs= p; |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_crl | &nbs= p; = |<o:p></o:p></s= pan></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_crlpath | = &nb= sp; |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= | ssl_key | /db/data/vde_cmdb_east.key = |<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= +---------------+----------------------------+<o:p></o:p></span= ></p> <p class=3D"MsoNormal"><span style=3D"font-family:"Courier New"">= 9 rows in set (0.00 sec)<o:p></o:p></span></p> <p class=3D"MsoNormal"><o:p> </o:p></p> <p class=3D"MsoNormal"><b><span style=3D"color:black">Grant Edmunds</span><= /b><span style=3D"color:black"> <br> MySQL DBA<o:p></o:p></span></p> <p class=3D"MsoNormal"><span style=3D"color:black">CVPI – Core Video = Platform Integration<br> </span><span style=3D"font-size:12.0pt;color:black">303 712-3239</span><spa= n style=3D"color:black"><br> </span><b><u><span style=3D"font-size:10.0pt;color:blue">grant_edmunds@comc= ast.com</span></u></b><span style=3D"color:#833C0B"><o:p></o:p></span></p> <p class=3D"MsoNormal"><o:p> </o:p></p> </div> </body> </html> --_000_1a239a28db14404da55acb56e0b5f709PACDCEX42cablecomcastco_--