{"id":48,"date":"2007-06-11T00:00:00","date_gmt":"2007-06-11T00:00:00","guid":{"rendered":"http:\/\/netexpertise\/en\/?p=48"},"modified":"2021-10-28T08:30:58","modified_gmt":"2021-10-28T06:30:58","slug":"mysql-traffic-encryption-with-openssl","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html","title":{"rendered":"Mysql Traffic Encryption with OpenSSL"},"content":{"rendered":"<div style=\"float:left; margin-right:15px; margin-top:0px;\">\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<!-- 200x200, Netexpertise --><br \/>\n<ins class=\"adsbygoogle\" style=\"display:inline-block;width:200px;height:200px\" data-ad-client=\"ca-pub-6495750100906580\" data-ad-slot=\"1946825373\"><\/ins><br \/>\n<script><br \/>\n(adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/div>\n<p>This is related to Freeradius software but can be applied to any application that needs to encrypt Mysql traffic. <a href=\"http:\/\/www.freeradius.org\">Freeradius<\/a> is compliant to Radius protocol characteristics, which give ability to accomplish various actions, such as authenticate users. Number of caveats have been found, not related to the software but to the protocol. Joshua Hill from the laboratory <a href=\"http:\/\/www.infogard.com\">Infogard<\/a> has realised a very good analyse available on the web at <a href=\"http:\/\/www.untruth.org\/~josh\/security\/radius\/\">http:\/\/www.untruth.org\/~josh\/security\/radius\/<\/a>. This document relates possible attacks while sending login and password for authentication.<br \/>\nWe won&#8217;t detail Radius protocol issues, but try to avoid some security problems when using a database system as a backend. Access to the Radius database must be secure of course, but the data transport from the Freeradius server also. This is really convenient when both servers are distant, which can be frequent with networks of big size. We propose to encrypt Mysql traffic with OpenSSL, the wildly used SSL engine, in these conditions.<\/p>\n<h3>OpenSSL Installation<\/h3>\n<p><a href=\"http:\/\/www.openssl.org\">OpenSSL<\/a> can be downloaded from the <a href=\"http:\/\/www.openssl.org\">official site<\/a>. This test has been realised with the last stable version available today, 0.9.8e. It can be installed as follow:<\/p>\n<p><i>.\/config &#8211;prefix=\/usr\/local\/openssl shared zlib<br \/>\nmake<br \/>\nmake test<br \/>\nmake install<\/i><\/p>\n<p>&#8220;zlib&#8221; activates support for compression\/decompression and &#8220;shared&#8221; for shared libraries. It&#8217;s important to note compiling Mysql will fail on a Linux plateform without the &#8220;shared&#8221; option.<\/p>\n<p>New libraries must be included in the path then. Add path <i>\/usr\/local\/openssl\/lib<\/i> (or any other where you&#8217;ve installed OpenSSL) in the <i>\/etc\/ld.so.conf<\/i> file under Linux. Run command line <i>ldconfig<\/i> to make the new path active.<\/p>\n<h3>OpenSSL activation on the server<\/h3>\n<p><a href=\"http:\/\/www.mysql.com\">Mysql<\/a> libraries are usually provided with YaSSL, to replace OpenSSL, limited by its license. (Re)compilation is then necessary:<\/p>\n<p><i>.\/configure &#8211;enable-openssl=\/usr\/local\/openssl<br \/>\nmake<br \/>\nmake install<\/i><\/p>\n<p>To check wether Mysql server supports SSL after service restart, we need to examine the value of the system variable <i>have_openssl<\/i> like this:<\/p>\n<pre>mysql&gt; SHOW VARIABLES LIKE 'have_openssl';\n+---------------+----------+\n| Variable_name | Value    |\n+---------------+----------+\n| have_openssl  | DISABLED |\n+---------------+----------+\n<\/pre>\n<p>If the value is &#8220;DISABLED&#8221;, the server supports SSL connections but wasn&#8217;t started with the correct options. Certificates needed for encryption must be created.<\/p>\n<p><i>cd \/usr\/local\/mysql<br \/>\nmkdir openssl &amp;&amp; cd openssl<br \/>\nmkdir certs &amp;&amp; cd certs<\/i><\/p>\n<p>CA certificate generation<br \/>\n<i>openssl genrsa -out ca-key.pem 2048<br \/>\nopenssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem<\/i><br \/>\nAnswer questions with appropriate data.<br \/>\nOpenssl commands generate a 2048 bit key and a certificate valid for a thousand day period.<\/p>\n<p>Server certificate generation<br \/>\n<i>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.pem<br \/>\nopenssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem<\/i><\/p>\n<p>Client certificate generation<br \/>\n<i>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem -out client-req.pem<br \/>\nopenssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem<\/i><\/p>\n<p>Certificates related data must be given as parameters while launching the service. They can also be specified in Mysql configuration file <i>\/etc\/my.cnf<\/i> in section [mysqld].<\/p>\n<p><i>ssl-ca=\/usr\/local\/mysql\/openssl\/certs\/cacert.pem<br \/>\nssl-cert=\/usr\/local\/mysql\/openssl\/certs\/server-cert.pem<br \/>\nssl-key=\/usr\/local\/mysql\/openssl\/certs\/server-key.pem<\/i><\/p>\n<p>Having restarted Mysql, SSL encryption should be available:<\/p>\n<pre>mysql&gt; SHOW VARIABLES LIKE 'have_openssl';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| have_openssl  | YES   |\n+---------------+-------+\n<\/pre>\n<p>As for now, the server accepts secure connections. It&#8217;s still possible to connect in clear mode as the choice is made on a per connection basis. It is possible to force a user to connect only in secure mode. This option resides in the user&#8217;s parameters and can be changed with the GRANT command.<\/p>\n<h3>OpenSSL activation on the client<\/h3>\n<p>There are several ways to connect to the server in SSL mode. Certificates are either given in option while launching Mysql client, or can be read from the configuration file <i>\/etc\/my.cnf<\/i> (or any other as we can  modify the default file like this: mysql &#8211;defaults-file=my.cnf). Consequently, add these lines in the [client] section:<\/p>\n<p><i>ssl-ca=\/usr\/local\/mysql\/openssl\/certs\/cacert.pem<br \/>\nssl-cert=\/usr\/local\/mysql\/openssl\/certs\/client-cert.pem<br \/>\nssl-key=\/usr\/local\/mysql\/openssl\/certs\/client-key.pem<\/i><\/p>\n<p>After being connected, the cipher used to encrypt data can be displayed:<\/p>\n<pre>SHOW STATUS LIKE 'Ssl_cipher';\n+---------------+--------------------+\n| Variable_name | Value              |\n+---------------+--------------------+\n| Ssl_cipher    | DHE-RSA-AES256-SHA |\n+---------------+--------------------+\n<\/pre>\n<p>In the case where Freeradius is the client, the number of SSL connections should rise with the same number of connections created in the pool, as defined in file <i>sql.conf<\/i>.<\/p>\n<pre>show status like 'Ssl_accepts';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| Ssl_accepts   | 52    |\n+---------------+-------+\n<\/pre>\n<h3>Performance Impact<\/h3>\n<p>Data encryption has of course an impact on performance as the operation is greedy in CPU and network usage. Here is a graphical visualisation of the  number of transactions processed per second with and without encryption<\/p>\n<div style=\"text-align:center\"><img decoding=\"async\" src=\"..\/..\/images\/MysqlSSLPerformance.png\" style=\"border:0\" alt=\"Impact SSL sur les performances Mysql\"><\/div>\n<p>These tests have been realised with a machine on which were running both Radius and Mysql services. They give however a good appreciation of encryption impact on performance that is around 30% slower.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is related to Freeradius software but can be applied to any application that needs to encrypt Mysql traffic. Freeradius is compliant to Radius protocol characteristics, which give ability to accomplish various actions, such as authenticate users. Number of caveats have been found, not related to the software but to the protocol. Joshua Hill from [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[3,4],"tags":[5,29,384,385,34,27,10,31],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.8.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Netexpertise - Mysql Traffic Encryption with OpenSSL<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Mysql Traffic Encryption with OpenSSL\" \/>\n<meta property=\"og:description\" content=\"This is related to Freeradius software but can be applied to any application that needs to encrypt Mysql traffic. Freeradius is compliant to Radius protocol characteristics, which give ability to accomplish various actions, such as authenticate users. Number of caveats have been found, not related to the software but to the protocol. Joshua Hill from [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2007-06-11T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-28T06:30:58+00:00\" \/>\n<meta name=\"author\" content=\"dave\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@netexpertise\" \/>\n<meta name=\"twitter:site\" content=\"@netexpertise\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html\",\"url\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html\",\"name\":\"Netexpertise - Mysql Traffic Encryption with OpenSSL\",\"isPartOf\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\"},\"datePublished\":\"2007-06-11T00:00:00+00:00\",\"dateModified\":\"2021-10-28T06:30:58+00:00\",\"author\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/www.netexpertise.eu\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mysql Traffic Encryption with OpenSSL\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/\",\"name\":\"Netexpertise\",\"description\":\"Systems \/ Networks \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.netexpertise.eu\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"caption\":\"dave\"},\"sameAs\":[\"http:\/\/www.netexpertise.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Netexpertise - Mysql Traffic Encryption with OpenSSL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html","og_locale":"en_US","og_type":"article","og_title":"Netexpertise - Mysql Traffic Encryption with OpenSSL","og_description":"This is related to Freeradius software but can be applied to any application that needs to encrypt Mysql traffic. Freeradius is compliant to Radius protocol characteristics, which give ability to accomplish various actions, such as authenticate users. Number of caveats have been found, not related to the software but to the protocol. Joshua Hill from [&hellip;]","og_url":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html","og_site_name":"Netexpertise","article_published_time":"2007-06-11T00:00:00+00:00","article_modified_time":"2021-10-28T06:30:58+00:00","author":"dave","twitter_card":"summary_large_image","twitter_creator":"@netexpertise","twitter_site":"@netexpertise","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html","url":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html","name":"Netexpertise - Mysql Traffic Encryption with OpenSSL","isPartOf":{"@id":"http:\/\/www.netexpertise.eu\/en\/#website"},"datePublished":"2007-06-11T00:00:00+00:00","dateModified":"2021-10-28T06:30:58+00:00","author":{"@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa"},"breadcrumb":{"@id":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-traffic-encryption-with-openssl.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.netexpertise.eu\/en"},{"@type":"ListItem","position":2,"name":"Mysql Traffic Encryption with OpenSSL"}]},{"@type":"WebSite","@id":"http:\/\/www.netexpertise.eu\/en\/#website","url":"http:\/\/www.netexpertise.eu\/en\/","name":"Netexpertise","description":"Systems \/ Networks \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.netexpertise.eu\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa","name":"dave","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","caption":"dave"},"sameAs":["http:\/\/www.netexpertise.eu"]}]}},"_links":{"self":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/48"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/comments?post=48"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/48\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/media?parent=48"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/categories?post=48"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/tags?post=48"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}