Issues with SSL Communication to Amazon RDS SQL Server

Question

I am encountering difficulties in establishing secure communication with an Amazon RDS SQL Server instance using SSL. Here are the details of my setup and troubleshooting steps:

Environment Details
RDS CA Certificate Used: rds-ca-rsa2048-g1
Certificates Tried:
ap-southeast-1-bundle.pem
global-bundle.pem
Parameter Group Configuration:
rds.force_ssl is set to 1 (enabled), but it remains in the state:

```
"ApplyMethod": "pending-reboot"
```
The RDS instance has been rebooted multiple times.

OpenSSL Testing:

```
openssl s_client -connect :1433 -CAfile global-bundle.pem -tls1_2
Result:

CONNECTED(00000005)
407881F301000000:error:0A000126:SSL routines::unexpected eof while reading:ssl/record/rec_layer_s3.c:693:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 268 bytes
Verification: OK
---
```

Connection works without SSL:

```
sqlcmd -S  -U  -P 
```
Connection also works with SSL flags (-N -C), but I’m unsure if SSL is being enforced:

```
sqlcmd -S  -U  -P  -N -C
```

Running the following query on the RDS instance:

```
SELECT ENCRYPT_OPTION FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID;
```
Returns TRUE, indicating encryption is enabled for the current connection.

Questions
1. Why does the OpenSSL handshake fail with the error and no peer certificate available?
2. Why does the rds.force_ssl parameter remain in pending-reboot state after multiple reboots?

Is there a way to confirm that SQLCMD is enforcing SSL for connections made with -N -C?

Are there additional steps needed to ensure proper SSL enforcement on this RDS instance?

Additional Information:
Engine Version: SQL Server 2022
Port: Default SQL Server port is open, and connections without SSL work as expected.
Thank you for any guidance or insights!

MikeLim · Accepted Answer

SQL Server uses STARTTLS to support both unencrypted and encrypted connections on port 1433. OpenSSL s_client will not work.

While it support `-starttls` switch, OpenSSL does not support STARTTLS with SQL.
For SMTP mail server, the syntax is as follows
```
openssl s_client -starttls smtp -connect MAIL-SERVER:25
```

*Below commands are on Amazon Linux 20223 with Microsoft ODBC driver for SQL Server (Linux) installed*

You can install nmap and use it to retrieve TLS cert as illustrated below
```
[ec2-user@ip ~]$ nmap --script=ssl-cert -p 1433 database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com -Pn
Starting Nmap 7.93 ( https://nmap.org ) at 2025-01-19 06:01 UTC
Nmap scan report for database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com (172.31.48.4)
Host is up (0.00034s latency).
rDNS record for 172.31.48.4: ip-172-31-48-4.ap-southeast-1.compute.internal

PORT     STATE SERVICE
1433/tcp open  ms-sql-s
| ssl-cert: Subject: commonName=database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com/organizationName=Amazon.com/stateOrProvinceName=Washington/countryName=US
| Subject Alternative Name: DNS:database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com, DNS:listener.database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com
| Issuer: commonName=Amazon RDS ap-southeast-1 Subordinate CA RSA2048 G1.A.3/organizationName=Amazon Web Services, Inc./stateOrProvinceName=WA/countryName=US
| Public Key type: rsa
| Public Key bits: 2048
| Signature Algorithm: sha256WithRSAEncryption
| Not valid before: 2025-01-19T05:48:02
| Not valid after:  2028-01-19T05:48:02
| MD5:   6f6a3308392c51c42f0315543e132964
|_SHA-1: f98fe421a544cc766f22673e1ca76e5d84dde72f

Nmap done: 1 IP address (1 host up) scanned in 0.29 seconds
```

With regards to your error, you may want to create a new parameter group and attach it to your RDS SQL server

```
sqlcmd -S database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com -U  -P  -Nm
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000086:SSL routines::certificate verify failed:unable to get local issuer certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.
```

Reboot RDS SQL.

On your client, add CA bundle to `/usr/share/pki/ca-trust-source/anchors` and running `sudo update-ca-trust`

With `force_ssl` enabled... 
```
[ec2-user@ip ~]$ sqlcmd -S database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com -U  -P  -Ns
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection.

[ec2-user@ip ~]$ sqlcmd -S database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com -U  -P  -Nm
1> select ENCRYPT_OPTION from SYS.DM_EXEC_CONNECTIONS where SESSION_ID = @@SPID
2> go
ENCRYPT_OPTION
----------------------------------------
TRUE

(1 rows affected)
1> quit

[ec2-user@ip anchors]$ sqlcmd -S database-1.cxvgib1qvhwr.ap-southeast-1.rds.amazonaws.com -U  -P 
1> select ENCRYPT_OPTION from SYS.DM_EXEC_CONNECTIONS where SESSION_ID = @@SPID
2> GO
ENCRYPT_OPTION
----------------------------------------
TRUE

(1 rows affected)
```

re:Post Agent · Answer

Based on the information provided, it appears you're experiencing several issues with SSL communication to your Amazon RDS SQL Server instance. Let's address your questions and provide some insights:

1. The OpenSSL handshake failure could be due to a mismatch between the SSL/TLS versions supported by the server and the one you're trying to use. The error "unexpected eof while reading" often indicates that the server closed the connection prematurely, possibly because it doesn't support the SSL/TLS version you're attempting to use.

2. The rds.force_ssl parameter remaining in "pending-reboot" state after multiple reboots is unusual. This could indicate an issue with the parameter group application or a problem with the RDS instance itself. You may need to contact AWS support if this persists after ensuring you're modifying the correct parameter group and that it's associated with your DB instance.

Regarding SQLCMD enforcing SSL:
When you use the -N and -C flags with SQLCMD, you are requesting an encrypted connection. The fact that your query on SYS.DM_EXEC_CONNECTIONS returns TRUE for ENCRYPT_OPTION suggests that the connection is indeed encrypted. However, to be absolutely certain, you can try the following:

1. Use SQL Server Management Studio (SSMS) to connect, and in the connection properties, set "Encrypt connection" to "Yes" and uncheck "Trust server certificate". If the connection succeeds, it confirms SSL is working correctly.

2. Check the SQL Server error log for entries related to SSL connections.

To ensure proper SSL enforcement:

1. Verify that the rds.force_ssl parameter is actually applied after a reboot. You can check this using the AWS CLI or RDS console.

2. Make sure you're using the correct and up-to-date SSL certificate. The rds-ca-rsa2048-g1 is a newer certificate, so ensure your client trusts this certificate.

3. Import the RDS certificate into your client's trusted root certification authorities store.

4. If using SSMS version 19.3, 20.0, or 20.2, you may need to enable "Trust Server Certificate" and import the certificate into your system.

5. Check if there are any security group or network ACL rules that might be interfering with SSL connections.

If issues persist after trying these steps, you may need to engage AWS support for further investigation, especially regarding the rds.force_ssl parameter not applying correctly.  
**Sources**  
[Sql Server: Certificate Authority not trusted with new rds-ca-rsa2048-g1 certificate | AWS re:Post](https://repost.aws/questions/QU-zSx0GPySHWERoRSm_DXBg/sql-server-certificate-authority-not-trusted-with-new-rds-ca-rsa2048-g1-certificate)  
[Using SSL with a Microsoft SQL Server DB instance - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html)  
[Troubleshooting connections to your SQL Server DB instance - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.Troubleshooting.html)

Issues with SSL Communication to Amazon RDS SQL Server

Add your answer

Relevant content