Establishing SSL trust to SQL Server with an AWS Lambda (Linux Client)

Question

Establishing SSL trust to SQL Server with an AWS Lambda (Linux Client)

Farrell, Erik 20

Hey all.

I'm trying to connect to a SQL Server 2019 instance from an AWS lambda. I'm using a self-signed certificate for the SQL instance.

Our lambda is using Entity Framework Core 7.0.5 in .NET 6 (also tested Microsoft.Data.SqlClient 5.1 with the same results)

The connection string is like the following: "Data Source=xxxxxxx;Initial Catalog=xxxxxxx;Integrated Security=false;Encrypt=True;TrustServerCertificate=False

When I change to TrustServerCertificate=true;, everything connects fine. (I don't want to do that and override trust).

There's an official guide for Linux client connections, but it requires lower level operating system access than AWS allows in its lambdas. (https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-encrypted-connections?view=sql-server-ver16)

I typically use OpenSSL's SSL_CERT_FILE environment variable to register our certificates in lambdas, but that does not work in this case.

I've also tried ServerCertificate in the connection string (ServerCertificate=/opt/path/server.cer) as documented here, which works great in Windows, but does not seem to work in Linux: https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnectionstringbuilder.servercertificate?view=sqlclient-dotnet-standard-5.1

What alternative approaches for registering a SQL Server client certificate in Linux can I can try?

Does anyone have experience accomplishing this in an AWS lambda function?

The full error follows:

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught) ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback. at System.Net.Security.SslStream.SendAuthResetSignal(ProtocolToken message, ExceptionDispatchInfo exception) at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions) at System.Net.Security.SslStream.ForceAuthenticationAsyncTIOAdapter at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions) at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation) at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.EnableSsl(UInt32 info, SqlConnectionEncryptOption encrypt, Boolean integratedSecurity, String serverCertificateFilename) at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteTState,TResult at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

Wenbin Geng 741 Reputation points Microsoft External Staff

2023-09-28T02:23:07.91+00:00

Hi @Farrell, Erik, Sorry that our Team does not accept third-party tools for the time being, but if you are simply consulting EF Core, we are very honored to answer for you.
Farrell, Erik 20 Reputation points

2023-09-28T12:04:54.67+00:00

@Wenbin Geng I think this is a generic Microsoft.Data.SqlClient connectivity to SQL Server in Linux question at its heart.

Is there any support for Linux client trust outside of the Linux store installation guide here? https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-encrypted-connections?view=sql-server-ver16

What we're looking for is a way to specify the certificate path through an environment variable or the connection string so we don't have to move the certificate around in the filesystem or perform Linux commands we don't have access to perform.

Typical examples of this are SSL_CERT_FILE in OpenSSL and ServerCertificate=C:\mycert\cert.cer in a Windows connection string.
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2023-09-28T22:55:49.22+00:00

this is a question for AWS support.

with an azure function hosted on linux (the equivalent of AWS lambda's), the azure portal allows installing the certificate in the store
Farrell, Erik 20 Reputation points

2023-09-29T04:11:23.9233333+00:00

I'm still hoping for clarity from the Microsoft camp around options.

For instance, there's no mention of Windows-only support around ServerCertificate (https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnectionstringbuilder.servercertificate?view=sqlclient-dotnet-standard-5.1) yet I can't seem to get it to work with Linux.
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2023-09-29T16:10:47.1866667+00:00

Be sure you are using Microsoft.Data,SqlClient 5.1

here is the code:

https://github.com/dotnet/SqlClient/blob/9e0d8b7095e051a6146904ed34738fb3497ceb62/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNITcpHandle.cs

the important line is:

clientCertificate = new X509Certificate(_serverCertificateFilename);

which should work on linux. are you sure the path is correct?
Farrell, Erik 20 Reputation points

2023-10-12T19:05:29.6166667+00:00

That line does work - it seemed like the issue was in the remote validation callback.

To kind of close this thread out, we ended up using our enterprise support with Microsoft to kind of troubleshoot, it looks like that ServerCertificate was a bit of a known issue in Linux behind the scenes - I've added it as a github issue in SqlClient to kind of add some weight and hopefully priority: https://github.com/dotnet/SqlClient/issues/2178

Thanks for your feedback all!

Accepted answer

0 additional answers

Your answer

Wenbin Geng 741 Reputation points Microsoft External Staff

2023-09-28T02:23:07.91+00:00

Hi @Farrell, Erik, Sorry that our Team does not accept third-party tools for the time being, but if you are simply consulting EF Core, we are very honored to answer for you.
Farrell, Erik 20 Reputation points

2023-09-28T12:04:54.67+00:00

@Wenbin Geng I think this is a generic Microsoft.Data.SqlClient connectivity to SQL Server in Linux question at its heart.

Is there any support for Linux client trust outside of the Linux store installation guide here? https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-encrypted-connections?view=sql-server-ver16

What we're looking for is a way to specify the certificate path through an environment variable or the connection string so we don't have to move the certificate around in the filesystem or perform Linux commands we don't have access to perform.

Typical examples of this are SSL_CERT_FILE in OpenSSL and ServerCertificate=C:\mycert\cert.cer in a Windows connection string.
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2023-09-28T22:55:49.22+00:00

this is a question for AWS support.

with an azure function hosted on linux (the equivalent of AWS lambda's), the azure portal allows installing the certificate in the store
Farrell, Erik 20 Reputation points

2023-09-29T04:11:23.9233333+00:00

I'm still hoping for clarity from the Microsoft camp around options.

For instance, there's no mention of Windows-only support around ServerCertificate (https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnectionstringbuilder.servercertificate?view=sqlclient-dotnet-standard-5.1) yet I can't seem to get it to work with Linux.
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2023-09-29T16:10:47.1866667+00:00

Be sure you are using Microsoft.Data,SqlClient 5.1

here is the code:

https://github.com/dotnet/SqlClient/blob/9e0d8b7095e051a6146904ed34738fb3497ceb62/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNITcpHandle.cs

the important line is:

clientCertificate = new X509Certificate(_serverCertificateFilename);

which should work on linux. are you sure the path is correct?
Farrell, Erik 20 Reputation points

2023-10-12T19:05:29.6166667+00:00

That line does work - it seemed like the issue was in the remote validation callback.

To kind of close this thread out, we ended up using our enterprise support with Microsoft to kind of troubleshoot, it looks like that ServerCertificate was a bit of a known issue in Linux behind the scenes - I've added it as a github issue in SqlClient to kind of add some weight and hopefully priority: https://github.com/dotnet/SqlClient/issues/2178

Thanks for your feedback all!

Answer 1

Takahito Iwasa 4,851 MVP Volunteer Moderator

Hi.

I understand that you want to use SSL for SQL connections with AWS Lambda, but are unable to do so due to limitations of the AWS Lambda managed runtime.

In addition to the .NET client-side approach, I would like to comment on the AWS Lambda layer approach.

Another option for AWS Lambda is to run it with a custom Docker runtime.

https://docs.aws.amazon.com/lambda/latest/dg/images-create.html

If you run Lambda in this way without using a managed runtime, I think you can intervene in the Amazon Linux middleware layer.

Farrell, Erik 20 Reputation points

2023-10-23T11:23:40.81+00:00

This is one approach we're potentially looking into - I think it would work fine if you installed the certificate per the guide (https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-encrypted-connections?view=sql-server-ver16) - (AWS Linux is RHEL-ish, the last time I installed a cert to RHEL I had to append to /etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem directly)

Another option if people like it is to get a certificate from a common public trust and avoid the problem entirely, like LetsEncrypt or DigiCert - AWS does have their CA installed into all lambdas, so you can use ACM as well. Not always cost effective though.

Share via

Establishing SSL trust to SQL Server with an AWS Lambda (Linux Client)

0 additional answers

Your answer