Generate New Self-Signed Certificate Files For MySQL

By default, MySQL has its own SSL certificate files in the '/var/lib/mysql' directory.
If required: generate New Self-signed SSL Certificate Files.
Identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically.
Generate SSL Certificate files:

1. We need 3 certificates:
1. CA Certificate
2. Server Certificate and Key
3. Client Certificate and Key.
2. We will create them with OpenSSL.
3. After creation, we will configure the files with MySQL

Generate SSL Certificate files
output files: ca-key.pem, ca.pem

1. Create a new directory for the certificate files

mkdir -p /etc/certs

cd /etc/certs

2. Generate new CA certificate ca.pem file

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

Output files: ca-key.pem, ca.pem

3. Generate the server-side certificates

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Output files: server-req.pem, server-key.pem, server-cert.pem

4. Generate certificate files for the client

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Output files: client-req.pem, client-key.pem, client-cert.pem

5. Verify certificate files

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

The CA certificate info must be different from the client and server info.

Common name should be equal in client and server files (steps 3, 4).

For VERIFY_IDENTITY ssl-mode: Common name like the host name for the server and the client (steps 3, 4).

Configure the Certificate files with MySQL

1. Change the owner of the certs directory to mysql user :

chown -R mysql:mysql /etc/certs/

2. Change permissions of all key files

chmod 600 client-key.pem server-key.pem ca-key.pem

SSL Configuration in MySQL

Default SSL Configuration in MySQL

By default, MySQL server always installs and enables SSL configuration.
However, it is not enforced that clients connect using SSL.

Configuration metric	MySQL 5.6	MySQL 5.7	MySQL 8.0	cnf section	Purpose
have_openssl	DISABLED	YES	YES	Set to on when the SSL files are declared	Enable SSL
have_ssl	DISABLED	YES	YES	Set to on when the SSL files are declared
ssl_ca		ca.pem	ca.pem	mysqld
ssl_cert		server-cert.pem	server-cert.pem	mysqld
ssl_key		server-key.pem	server-key.pem	mysqld
require_secure_transport	(not exists)	OFF	OFF	mysqld	Force SSL
ssl_capath
ssl_cipher
ssl_crl
ssl_crlpath
ssl_fips_mode	(not exists)	(not exists)	OFF
admin_ssl_ca	(not exists)	(not exists)
admin_ssl_capath	(not exists)	(not exists)
admin_ssl_cert	(not exists)	(not exists)
admin_ssl_cipher	(not exists)	(not exists)
admin_ssl_crl	(not exists)	(not exists)
admin_ssl_crlpath	(not exists)	(not exists)
admin_ssl_key	(not exists)	(not exists)
mysqlx_ssl_ca	(not exists)	(not exists)
mysqlx_ssl_capath	(not exists)	(not exists)
mysqlx_ssl_cert	(not exists)	(not exists)
mysqlx_ssl_cipher	(not exists)	(not exists)
mysqlx_ssl_crl	(not exists)	(not exists)
mysqlx_ssl_crlpath	(not exists)	(not exists)
mysqlx_ssl_key	(not exists)	(not exists)
performance_schema_show_processlist	(not exists)	(not exists)	OFF

How to check SSL Configuration

show global variables like '%ssl%';

show global variables like '%require_secure_transport%';

status

SSL enabling for MySQL

1. SSL Certificate Files

By default, MySQL has its own SSL certificate files in the '/var/lib/mysql' directory.

If required: Generate New Self-Signed Certificate Files For MySQL .

Identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically.

2. Enable SSL for MySQL

Edit Cnf file:

[mysql]

ssl-ca=/var/lib/mysql/ca.pem

ssl-cert=/var/lib/mysql/server-cert.pem

ssl-key=/var/lib/mysql/server-key.pem

 

[client]

ssl-ca=/var/lib/mysql/ca.pem

ssl-cert=/var/lib/mysql/client-cert.pem

ssl-key=/var/lib/mysql/client-key.pem

• These changes in cnf file require a restart of the MySQL instance.
• Pay attention that the files paths are correct.
• If new certificate files were created – update names and paths

In order to Force SSL login:

Edit Cnf file:

[mysqld]

require_secure_transport = ON

• These changes in cnf file require a restart of the MySQL instance.
• if require_secure_transport is set as OFF (as the default value) - it means that a user can use certificates and also can login without SSL certificates.

3. Enable Remote Connection

Edit Cnf file:

[mysqld]

#bind-address = 0.0.0.0

bind-address = *

• These changes in cnf file require a restart of the MySQL instance.

Login to an SSL instance from a client

1. Copy and save the certificate files in the client's server.
2. Add SSL properties to the connection (mysql or other) command.

No	-	-	No SSL and other certificate is required	mysql -h 10.240.86.5 -P 3307 -u root -p
Yes	No SSL	DISABLED	Connecting without SSL	mysql -h 10.240.86.5 -P 3306 -u root --ssl-mode=DISABLED -p	When the instance requires SSL, it is not possible to connect without SSL
	If available	PREFERRED (the default if --ssl-mode is not specified)	Establish an encrypted connection if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established.	mysql -h 10.240.86.5 -P 3307 -u root -p	Same as without any SSL definitions.
	Require	REQUIRED	Establish an encrypted connection if the server supports encrypted connections. The connection attempt fails if an encrypted connection cannot be established.	mysql -h 10.240.86.5 -P 3307 -u root \ --ssl-cipher=ECDHE-RSA-AES128-GCM-SHA256 -p
	Require and verify CA	VERIFY_CA	Like REQUIRED, but additionally verify the server Certificate Authority (CA) certificate against the configured CA certificates. The connection attempt fails if no valid matching CA certificates are found.	mysql -h 10.240.86.5 -P 3307 -u root \ --ssl-mode=VERIFY_CA \ --ssl-ca=/etc/certs/ca.pem \ --ssl-cert=/etc/certs/client-cert.pem \ --ssl-key=/etc/certs/client-key.pem -p
	Require and verify Identity	VERIFY_IDENTITY	Like VERIFY_CA, but additionally perform host name identity verification by checking the host name the client uses for connecting to the server against the identity in the certificate that the server sends to the client:	mysql -h MySQL -P 3306 -u root \ --ssl-mode=VERIFY_IDENTITY \ --ssl-ca=/etc/certs3306/ca.pem \ --ssl-cert=/etc/certs3306/client-cert.pem \ --ssl-key=/etc/certs3306/client-key.pem -p	Host name identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically by the server or manually using mysql_ssl_rsa_setup. Such self-signed certificates do not contain the server name as the Common Name value. In the connection – use host name and not an IP.

This is how it looks at MySQL Workbench:

SAP RS - Sybase SSL error: Open Server error: Error: 16029, State: 0, Severity 20 -- 'Failed to start any network listeners'

Error:

F. 2020/08/02 08:25:22. FATAL ERROR #1030 GLOBAL RS(GLOBAL RS) - eneric/err/errhand.c(1262)

    Open Server error: Error: 16029, State: 0, Severity 20 -- 'Failed to start any network listeners'.

T. 2020/08/02 08:25:22. (-1): Exiting due to a fatal error.

Solution:

- see if the RS (or other service) run on the port that set to RS in the sql.ini/interfaces file:

sybase16@asazrlnsap16:/sybvol01/sap16rs/REP-16_0/install$ netstat -a | grep 11753

tcp        0      0 asazrlnsap16.inte:11753 0.0.0.0:*               LISTEN

- find the Process ID of the service:

sybase16@asazrlnsap16:~$ netstat -lntp | grep 11753

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 10.1.1.4:11753          0.0.0.0:*               LISTEN      61270/repserver

- kill the service:

sybase16@asazrlnsap16:~$ kill 61270

- check that the services were killed:

sybase16@asazrlnsap16:~$ ps -ef | grep 61270

sybase16  66053  65873  0 08:51 pts/2    00:00:00 grep --color=auto 61270

sybase16@asazrlnsap16:~$ netstat -lntp | grep 11753

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

Start RS

SAP ASE - Sybase SSL error: kernel ninit: bind, Address already in use

Error:
00:0002:00000:00009:2020/07/21 13:10:26.85 kernel  ninit: bind, Address already in use
00:0002:00000:00009:2020/07/21 13:10:26.85 kernel  Cannot allocate resources for listener with protocol ssltcp, host asazrlnsap16, port 5000.

Solution:
Delete duplicate definitions for the server with the same port (edit the ini file).

SAP ASE - Sybase SSL error: Configuration parameter 'enable ssl' can not be enabled without license 'ASE_ASM'.

Error:

Configuration parameter 'enable ssl' can not be enabled without license 'ASE_ASM'.

(from the error log file):

Solution:

Create a license of type "SAP Sybase ASE Enterprise Edition, Security & Direct".

1. Go to Launchpad: https://launchpad.support.sap.com/
2. Click tile "License keys"
3. Click your current server id, i.e. L16
4. Right bottom click "Edit"
5. The info should already there, so "Continue"
6. In this page search "security"
From my side I see below, but you might have different license number, for an example:
Item Description
7011762 SAP Sybase ASE Enterprise Edition Security & Directo
 
-> If you can find such "security" license, then click the "Generate" and step by step fill out the system info, to download.
-> If you can NOT find such license, call SAP hotline(560499) for help, ask them to help involve the SAP sales for license purchase.
 
7. Put the downloaded license in folder:
/sybvol01/sap16/SYSAM-2_0/licenses/
8. Restart ASE server.
9. Check ASE error log see if SSL is enabled, if still license error raised please send me back the complete ASE error log.

SSL Configuration - SAP RS (Sybase RS)

How to setup self-signed SSL with SAP RS

On the server-side:

1. Create an ASE certificate.
2. Enable SSL in ASE (if required).
3. Edit the interfaces/sql.ini file - add SSL definition.
4. Enable SSL in RS.
5. Restart RS.

A detailed description of the process to activate SSL for ASE

On the server-side:

If required: Installing OpenSSL on the machine.

1. Create an ASE certificate

1-7. Steps 1-7 like SSL for ASE.

If already done for your server - skip it. 
The certificates for the ASE on the server can be used also by the RS on this server.

8. Create/Copy the crt file to RS certificates folder
cd /sybvol01/sap16/OCS-16_0/bin/
cat ASAZRLNSAP16.public ASAZRLNSAP16.key > /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.crt
File created: /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.crt

9. Create  client certificate

9.a create ASAZRLNSAP16.txt in the RS certificates folder
cp root.crt /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.txt

9.b add certificates content to the exists trusted.txt in the RS config folder (.../sap16rs/config/trusted.txt)

9.c copy more files to the RS certificates folder:
cp ASAZRLNSAP16.csr /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.csr
cp ASAZRLNSAP16.key /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.key
cp ASAZRLNSAP16.public /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.public
cp root.crt /sybvol01/sap16rs/REP-16_0/certificates/root.crt
cp root.csr /sybvol01/sap16rs/REP-16_0/certificates/root.csr
cp root.key /sybvol01/sap16rs/REP-16_0/certificates/root.key

2. Enable SSL in ASE  (if required)

10. Enable ssl in ASE
→ if already done for the ASE (step 10) - skip it.
sp_configure "enable ssl", 1

11. Add ssl certificate into ASE (already done for ASE, step 11)

3. Edit the interfaces/sql.ini file - add SSL definition

12. Edit the interfaces/sql.ini file to create an ssl port:
ASAZRLNSAP16
    master tcp ether ASAZRLNSAP16 5000 ssl="CN=ASAZRLNSAP16"
    query tcp ether ASAZRLNSAP16 5000 ssl="CN=ASAZRLNSAP16"
 
RSFOG2 
   master tcp ether ASAZRLNSAP16 11753 ssl="CN=ASAZRLNSAP16"
   query tcp ether ASAZRLNSAP16 11753 ssl="CN=ASAZRLNSAP16"

4. Enable SSL in RS

13. Grant execute permissions for sp_serverinfo (CR# 814027)
use sybsystemprocs
go
grant execute on sp_serverinfo to public
go

14. Enable SSL on RS
1> configure replication server set use_ssl to 'on'
2> go
Config parameter 'use_ssl' is modified. This change will not take effect until the Replication Server is restarted.

5. Restart and do checks

15. Restart RS
Stop RS:
1> shutdown
2> go
Start RS:
cd /sybvol01/sap16rs/REP-16_0/install/
startserver -f RUN_RSFOG2

• Windows: stop and start the service in Services.

16. Check that SSL is enabled:

1> use RSFOG2_RSSD
2> go
1> select * from rs_config where optionname like "%ssl%"
2> go
optionname                     objid              charvalue         status comments                                                                                                                                             
 ------------------------------ ------------------ -------------
 ssl_protocol                   0x0000000000000000 TLSv1        0 Indicated the SSL protocol value of Replication Server.                                                                                                                     
 use_ssl                        0x0000000000000000 on   
should be:
1. ssl_protocol is not null
2. ssl_protocol is on

1> select @@ssl_ciphersuite
2> go
 ------------------------------
 TLS_RSA_WITH_AES_256_CBC_SHA

SSL Configuration - SAP ASE (Sybase)

How to setup self-signed SSL with ASE

On the server-side:

1. Create an ASE certificate.
2. Enable SSL in ASE.
3. Edit the interfaces/sql.ini file - add SSL definition.
4. Restart ASE.

On the clients:

1. Copy the certificate files to C:\SAP\ini in the client.
2. Edit sql.ini / interface file - add SSL definition.
3. Restart ASE

The process based on https://launchpad.support.sap.com/#/notes/0001899365

A detailed description of the process to activate SSL for ASE

On the server-side:

If required: Installing OpenSSL on the machine.

1. Create an ASE certificate

1. cd /sybvol01/sap16/OCS-16_0/bin/

2. Create root certificate
openssl genrsa -passout pass:dba4ever -out root.key 4096
output:
Generating RSA private key, 4096 bit long modulus (2 primes)
.............................................................................++++
.................................++++
e is 65537 (0x010001)
File created: root.key

3. Create root certificate request to be signed:
openssl req -new -key root.key -passin pass:dba4ever -out root.csr -subj "/C=XX/ST=XX/L=city/O=Org/CN=root"
File created root.csr

4. Self-Sign root certificate
openssl x509 -req -days 3650 -in root.csr -signkey root.key -passin pass:dba4ever -out root.crt
    output:
    Signature ok
    subject=C = XX, ST = XX, L = city, O = Org, CN = root
    Getting Private key

File created: root.crt

5. Create ASE private key (Note: Add your servername/"select @@servername" in for ASEname):
openssl genrsa -des3 -passout pass:dba4ever -out ASAZRLNSAP16.key 2048
File created: ASAZRLNSAP16.key

6. Create ASE certificate request to be signed:
openssl req -new -key ASAZRLNSAP16.key -passin pass:dba4ever -out ASAZRLNSAP16.csr -subj "/C=XX/ST=XX/L=city/O=Orig/CN=ASAZRLNSAP16"
File created: ASAZRLNSAP16.csr

7. Sign ASE request with root:
openssl x509 -req -days 3650 -in ASAZRLNSAP16.csr -CA root.crt -CAkey root.key -passin pass:dba4ever -set_serial 1 -out ASAZRLNSAP16.public
    output:
    Signature ok
    subject=C = XX, ST = XX, L = city, O = Orig, CN = ASAZRLNSAP16
    Getting CA Private Key
File created: ASAZRLNSAP16.public

8. Combine ASE certificates together:
cat ASAZRLNSAP16.public ASAZRLNSAP16.key > $SYBASE/$SYBASE_ASE/certificates/ASAZRLNSAP16.crt
File created: ASAZRLNSAP16.crt

9. Create ASE client certificate.
This is the root public certificate created in step 3. This is for all client side connections.

cp root.crt $SYBASE/$SYBASE_ASE/certificates/ASAZRLNSAP16.txt

cp root.crt $SYBASE/config/trusted.txt
File created:
    $SYBASE/$SYBASE_ASE/certificates/ASAZRLNSAP16.txt
    $SYBASE/config/trusted.txt

content of file:
-----BEGIN CERTIFICATE-----
MIIFEzCCAvsCFCi6ELH5OlZt96P56Zkz3r+DUeWJMA0GCSqGSIb3DQEBCwUAMEYx
CzAJBgNVBAYTAlhYMQswCQYDVQQIDAJYWDENMAsGA1UEBwwEY2l0eTEMMAoGA1UE
CgwDT3JnMQ0wCwYDVQQDDARyb290MB4XDTIwMDYxNjE0NTEwNFoXDTMwMDYxNDE0
NTEwNFowRjELMAkGA1UEBhMCWFgxCzAJBgNVBAgMAlhYMQ0wCwYDVQQHDARjaXR5
MQwwCgYDVQQKDANPcmcxDTALBgNVBAMMBHJvb3QwggIiMA0GCSqGSIb3DQEBAQUA
A4ICDwAwggIKAoICAQDD7h2H+PPYXlt+E9mvxFTnayo2S/2+TNLwpXYlrnasbbzG
hT9c2y6FD/NMo7C7ncrLHr+BUM5cBNRfDyijeb8Tm1ASQiSCj/CJQYDbxJ/VBt4N
aESN8POOMbNqQezZISWk8dPX4cHOh8d5oZqAUo/D+Y69VScAOKIGq1PmT1a/StXz
TIiw+xmibmvBClRC9oH9vujVFDTOG8fwUm38yaV9iz30upriPL9Ly17/klzauaRY
yJ4N6HRC79jZwgbMTbHP3j5fPsETstCD9S7LZxUNmlyXW14bndU/EYjeKGHUzvtr
7mIINQTc7u1/dRZGQVGl7NtaP5t0MIH7I5e1H8y56wQHd8umaE+Uwv+P9Mn2J9bb
zRIVcK/6vVz5KdU3XahgEr8U0mbA93Kn/8Xijx1wbzTFYcvqLpNd2MGSWEjy08N9
kxN13tC/aTm8vzkygnUvrfzjeYXXsji8fRDMYCanYzutA2FWpm+L0im8ob8uqwoL
egB+ZGOkuIwKrqP0gPJXr2GFTqgRyOWbfTHC31mv0Qo3Rg5bfsPXUkIbSBXUrz6i
pKYsMjFSi3npmuw/CCli2RIQMtCMMv8kn2Q0WqlWvePXNBcEiwVs1f/8J3h/cXAN
fjU2iaqT2aANc40hrmmJPVR/L5AladwGRESzphFEacVoKz/c+RZP/0wm2EQzMQID
AQABMA0GCSqGSIb3DQEBCwUAA4ICAQATVj1EazHcTonBVcthL2mgQaluSJaMYnxi
tEy8UKWBJj4Bu+THed7FCwJy9MDw5ReZgB1yh+PQvclg19LbGrVX+x9W/3cuQ9Cy
A7PMl9r3B5WnNwzCvxlltDenzzszcOGXSP+oUAql8nYp6wM9FbLnjW3aZ036d5+V
QEx8xsbEZ+9Qwb7SFWkIJky8sEVjueeXb9u0WP84bYrSp+T+YOESWUwZvwvJGCwc
YsRM1nCpLgFQlQNZjjtPcx/lKSB0+gKGmlrePoyaa8MYlswRanzYdnk487dAk3u9
X9JYO2yyNL7drGBf0VVnT8b7X2nOlDnw0wYs4mgIuLwBWhSbYpNHYcPFCxXjEoh1
jjdGnZGsQSGMoPKfxpNq8sZUyhzEroJHpV7OxLikvpRSz/IUhLpyNWoHVKaAehkJ
Iwc9pDja4Fz2ArO5l5g7P60rwq2cCTS0zKXrRqaPGm11WIX6ovCpobOZwBDvRGiv
6KiTF2y5Ib9xL+9Q6E8R0kPWOsPzOLaeyN5pz4IHLbRPppCIAurWxD4bozJ5CWVP
W9l+LF3IIxoLLtK2+8lxHiqMiV9o9PJTBd0I83YjPJ+QrB6ARMIg2fJWAaVQH8CN
Mdlw0aVwiVo9X06Lb6FW4AC9a2b5iBVF+ncUPeVIZYyrHNmoA65sEy/hb2rwdS+w
FoueokT6Zg==
-----END CERTIFICATE-----

2. Enable SSL in ASE

10. Enable ssl in ASE:
sp_configure "enable ssl", 1;

11. Add ssl certificate into ASE:
Note: Use fully qualified path

sp_ssladmin addcert, "/sybvol01/sap16/ASE-16_0/certificates/ASAZRLNSAP16.crt", "dba4ever"

3. Edit the interfaces/sql.ini file - add SSL definition

12. Edit the interfaces/sql.ini file to create an ssl port:
   interfaces (Unix)
   -------------
    ASEname
           master tcp ether myhost myport  ssl
           query tcp ether myhost myport ssl        
    sql.ini  (Windows)
    -------
    [ASEname]
    master=tcp,myhost,myport,ssl
    query=tcp,myhost,myport,ssl

for example:
ASAZRLNSAP16
  master tcp ether asazrlnsap16 5000 ssl="CN=ASAZRLNSAP16"
  query tcp ether asazrlnsap16 5000 ssl="CN=ASAZRLNSAP16"

4. Restart and do checks

13. Restart your ASE.

14. Checks

14.1 Check the log to make sure everything loaded:
16.0:
kernel  Common Crypto Library SSL symbols loaded.
kernel  Common Crypto Library SSL startup succeeded.
...
kernel network name host, interface IPv4, address ipaddress, type ssltcp, port port, filter ssl
15.7:
kernel Certificate load from file `$SYBASE/$SYBASE_ASE/certificates/ASEname.crt`: succeeded.
kernel Trusted root certificates loaded from file '$SYBASE/$SYBASE_ASE/certificates/ASEname.txt': succeeded.
For all versions:
kernel network name host, interface IPv4, address ipaddress, type ssltcp, port port, filter ssl

14.2 sp_ssladmin lscert
1> sp_ssladmin lscert
2> go
 certificate_path
 ---------------------------------------------------------
 /sybvol01/sap16/ASE-16_0/certificates/ASAZRLNSAP16.crt 

14.3 Check that "select @@ssl_ciphersuite" return value
1> select @@ssl_ciphersuite
2> go
 ----------------------------------
 TLS_RSA_WITH_AES_256_CBC_SHA 


On the clients:

1. Copy the certificate files to C:\SAP\ini in the client.

1. copy ASAZRLNSAP16.crt and ASAZRLNSAP16.txt to C:\SAP\ini in the client.

2. Edit sql.ini / interface file - add SSL definition.

2. Edit sql.ini / interface file - add SSL definition

3. Restart ASE

3. Restart ASE

Linux - ubuntu - 'apt-get' is ubuntu 'yum'

 Run: 

sudo yum install openssl

Error:

There are no enabled repos.

 Run "yum repolist all" to see the repos you have.

 You can enable repos with yum-config-manager --enable <repo>

Run successfully:

sudo apt-get install openssl

The usual way to install packages on the command line in Ubuntu is with apt-get.

The usual way to install packages on the command line in Red Hat is with yum.