I am getting the following error for some of my queries. The database I am connecting to is an Amazon Aurora DB. The Hosting OS is Windows Server 2016. The MySqlConnector is being used as a dependency of Pomelo.EntityFrameworkCore.MySql 2.2.0. The Version of MySQLConnector is 0.49.2.
MySql.Data.MySqlClient.MySqlException (0x80004005): SSL Authentication Error ---> System.Security.Authentication.AuthenticationException: A call to SSPI failed, see inner exception. ---> System.ComponentModel.Win32Exception: The message or signature supplied for verification has been altered
--- End of inner exception stack trace ---
at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
at MySqlConnector.Core.ServerSession.<InitSslAsync>d__80.MoveNext()
at MySqlConnector.Core.ServerSession.<InitSslAsync>d__80.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
at MySqlConnector.Core.ServerSession.<ConnectAsync>d__64.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
at MySqlConnector.Core.ConnectionPool.<GetSessionAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at MySqlConnector.Core.ConnectionPool.<GetSessionAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
at MySql.Data.MySqlClient.MySqlConnection.<CreateSessionAsync>d__96.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
at MySql.Data.MySqlClient.MySqlConnection.<OpenAsync>d__27.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySql.Data.MySqlClient.MySqlConnection.Open()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
After creating a self-signed SSL certificate, I have configured my remote MySQL server to use them (and SSL is enabled)
I ssh into my remote server, and try connecting to its own mysqld using SSL (MySQL server is 5.5.25)..
mysql -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key --ssl-ca=ca.cert
Enter password:
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
Ok, I remember reading theres some problem with connecting to the same server via SSL.
So I download the client keys down to my local box, and test from there…
mysql -h <server> -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key --ssl-ca=ca.cert
Enter password:
ERROR 2026 (HY000): SSL connection error
Its unclear what this «SSL connection error» error refers to, but if I omit the -ssl-ca, then I am able to connect using SSL..
mysql -h <server> -u <user> -p --ssl=1 --ssl-cert=client.cert --ssl-key=client.key
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 37
Server version: 5.5.25 MySQL Community Server (GPL)
However, I believe that this is only encrypting the connection, and not actually verifying the validity of the cert (meaning I would be potentially vulnerable to man-in-middle attack)
The SSL certs are valid (albeit self signed), and do not have a passphrase on them. So my question is, what am I doing wrong? How can I connect via SSL, using a self signed certificate?
MySQL Server version is 5.5.25 and the server and clients are CentOS 5.
Thanks for any advice
Edit: Note that in all cases, the command is being issued from the same directory where the ssl keys reside (hence no absolute path)
Edit (in response to mgorven):
ca.cert is the Certificate Authority certificate, which is supposed to tell mysql that my certificate authority is trusted.
The config from my.cnf is
[mysqld]
ssl-ca=/etc/ssl/mysql/ca.cert
ssl-cert=/etc/ssl/mysql/server.cert
ssl-key=/etc/ssl/mysql/server.key
I also tried adding ssl-cipher=DHE-RSA-AES256-SHA but have since removed it as it didn’t help.
-
-
July 27 2014, 17:19
- IT
- Cancel
Есть несколько разных способов установки MySQL с SSL, но иногда сложно её закончить, из-за того что все эти источники не достаточно качественны и просты. Обычно, установка MySQL+SSL не так проста из-за весьма непрозрачных факторов типа “сегодня не мой день”, что-то не взлетит либо врёт документация
Я дам инструкции по установке MySQL с SSL, репликации SSL и установлению скьюрного коннекта из консоли и скриптами покажу что всё работает.
1. Генерирую SSL-сертификат в соответствии с примером . Использую разные имена для Use клиентского и серверного сертификатов.
2. Для ясности, сохраню сгенерированные файлы сертификатов сюда — /etc/mysql-ssl/
3. Добавлю следующее в /etc/my.cnf в секцию [mysqld]:
# SSL
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem
4. Рестартую MySQL.
5. Создаю юзера для разрешения ему только SSL-encrypted коннектов:
GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;
Установка секьюрного коннекта из консоли
1. Если клинет на другом ноде, копирую /etc/mysql-ssl c другого сервера на этот.
2. Добавлю следующее в /etc/my.cnf в секцию [client]:
# SSL
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem
3. Тестирую секьюр коннект:
[root@тазик ~]# mysql -u ssluser -p -sss -e ‘s’ | grep SSL
SSL: Cipher in use is DHE-RSA-AES256-SHA
Устанавливаю репликацию SSL
1. Установлю скьюрный коннект из консоли к слейву (Slave) как описано выше, для того чтобы удостовериться что SSL работает.
2. На мастере (Master) добавлю “REQUIRE SSL” для репликации изера:
GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;
3. Меняю опции мастера и рестартую слейва:
STOP SLAVE;
CHANGE MASTER MASTER_SSL=1,
MASTER_SSL_CA=’/etc/mysql-ssl/ca-cert.pem’,
MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’,
MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem’;SHOW SLAVE STATUSG
START SLAVE;
SHOW SLAVE STATUSG
Установка секьюрного коннекта из PHP
1. Устанавливаюl пакеты php и php-mysql. Я использовал версию >=5.3.3 (в других случаях может не работать).
2. Создаю скрипт:
[root@тазик ~]# cat mysqli-ssl.php
$conn=mysqli_init();
mysqli_ssl_set($conn, ‘/etc/mysql-ssl/client-key.pem’, ‘/etc/mysql-ssl/client-cert.pem’, NULL, NULL, NULL);
if (!mysqli_real_connect($conn, ’127.0.0.1′, ‘ssluser’, ‘pass’)) { die(); }
$res = mysqli_query($conn, ‘SHOW STATUS like “Ssl_cipher”‘);
print_r(mysqli_fetch_row($res));
mysqli_close($conn);
3. Тестирую:
[root@тазик ~]# php mysqli-ssl.php
Array
(
[0] => Ssl_cipher
[1] => DHE-RSA-AES256-SHA
)
Установка секьюрного коннекта из Python
1. Устанавливаю пакет MySQL-python.
2. Создаю скрипт:
[root@тазик ~]# cat mysql-ssl.py
#!/usr/bin/env python
import MySQLdb
ssl = {‘cert’: ‘/etc/mysql-ssl/client-cert.pem’, ‘key’: ‘/etc/mysql-ssl/client-key.pem’}
conn = MySQLdb.connect(host=’127.0.0.1′, user=’ssluser’, passwd=’pass’, ssl=ssl)
cursor = conn.cursor()
cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
print cursor.fetchone()
3. Тестирую:
[root@тазик ~]# python mysql-ssl.py
(‘Ssl_cipher’, ‘DHE-RSA-AES256-SHA’)
Примечания
Альтернативный сетап локального коннекта по SSL
Если Вы коннектитесь к серверу локально с включенным SSL, можно так же устанавливать секьюрные соединения так::
1. Создаём ca.pem:
cd /etc/mysql-ssl/
cat server-cert.pem client-cert.pem > ca.pem
2. Имеем только следующие строки по ssl- в /etc/my.cnf в секции [client]:
# SSL
ssl-ca=/etc/mysql-ssl/ca.pem
Error “ssl-ca” при локальных коннектах
Если Вы оставили строку “ssl-ca=/etc/mysql-ssl/ca-cert.pem” в разделе [client] файла /etc/my.cnf сервера, чтобы включить SSL и при этом пытаетесь установить локальное подключение через SSL, получите “ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)”.
Описание в документации
http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html гласит “A client can connect securely like this: shell> mysql –ssl-ca=ca-cert.pem” which does not work with “REQUIRE SSL”. Тоесть, либо поддерживаетя клиентский сертификат и ключ для всех. либо комбинированный сертификат client+server для локального секеьюрного коннекта.