SqlException thrown related to encryption not being supported for SQL LocalDB named pipes

[martincostello]

Describe the bug

Using Microsoft.Data.SqlClient 4.0.0 with SQL LocalDB and named pipes does not work when attempting to connect to the instance.

I maintain a library that provides interop with the SQL LocalDB APIs, and upgrading its tests to version 4.0.0 is causing a number of its tests to fail.

An example of one of these failing tests is here.

Exception message: The instance of SQL Server you attempted to connect to does not support encryption.
Stack trace:

[xUnit.net 00:01:35.98]     Can_Manage_SqlLocalDB_Instances [FAIL]
  Failed Can_Manage_SqlLocalDB_Instances [8 s]
  Error Message:
   Microsoft.Data.SqlClient.SqlException : The instance of SQL Server you attempted to connect to does not support encryption.
  Stack Trace:
     at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   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.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at MartinCostello.SqlLocalDb.SqlLocalDbApiTests.Can_Manage_SqlLocalDB_Instances() in D:\a\sqllocaldb\sqllocaldb\tests\SqlLocalDb.Tests\SqlLocalDbApiTests.cs:line 287
   at MartinCostello.SqlLocalDb.SqlLocalDbApiTests.Can_Manage_SqlLocalDB_Instances() in D:\a\sqllocaldb\sqllocaldb\tests\SqlLocalDb.Tests\SqlLocalDbApiTests.cs:line 288
--- End of stack trace from previous location ---

To reproduce

Clone the martincostello/sqllocaldb repo on a Windows machine and checkout the dependabot/nuget/Microsoft.Data.SqlClient-4.0.0 branch and run build.ps1 to run the library's build and test script.

Expected behavior

The tests pass.

Further technical details

Microsoft.Data.SqlClient version: 4.0.0
.NET target: net6.0
SQL Server version: SQL LocalDB 13
Operating system: Windows 2019

[martincostello]

Likely related to #1312.

[JRahnama]

@martincostello I am looking at the issue and will update yo soon.

[JRahnama]

@martincostello as I see you have build the application for net 6. Are you using VS2022?

On Friday when I tested your repro I got the same error message as you mentioned, but today it worked fine. The only thing I did extra was stopping localdb and start it again.

This is my localdb version:

I also made a fresh clone this morning in case you have changed anything.

[martincostello]

I use VS 2022 locally now, but I think the GitHub Actions images still use VS 2019 in CI for windows-latest.

I haven't actually run the PR locally myself, I can check it tomorrow on my development machine to see if it fails there too.

[martincostello]

This is failing for me locally. My version appears to be 15.0 from the library path, but 13.1 from the sqllocaldb tool.

[xUnit.net 00:01:02.00]     CreateConnection_Creates_A_Sql_Connection [FAIL]
  Failed CreateConnection_Creates_A_Sql_Connection [12 s]
  Error Message:
   Microsoft.Data.SqlClient.SqlException : The instance of SQL Server you attempted to connect to does not support encryption.
  Stack Trace:
     at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   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.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at MartinCostello.SqlLocalDb.ISqlLocalDbInstanceManagerExtensionsTests.CreateConnection_Creates_A_Sql_Connection() in C:\Coding\martincostello\sqllocaldb\tests\SqlLocalDb.Tests\ISqlLocalDbInstanceManagerExtensionsTests.cs:line 49
--- End of stack trace from previous location ---
  Standard Output Messages:
 [2021-11-23 12:41:29Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[30]
       Obtaining versions for SQL LocalDB.
 [2021-11-23 12:41:29Z] dbug: MartinCostello.SqlLocalDb.Interop.LocalDbInstanceApi[1]
       Loaded SQL LocalDB API from C:\Program Files\Microsoft SQL Server\150\LocalDB\Binn\SqlUserInstance.dll.
 [2021-11-23 12:41:29Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[32]
       Obtained 3 versions for SQL LocalDB.
 [2021-11-23 12:41:29Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[10]
       Creating named instance c867b24b-0108-4289-8859-9e92aace5dab of SQL LocalDB with version 15.0.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[12]
       Created named instance c867b24b-0108-4289-8859-9e92aace5dab of SQL LocalDB with version 15.0.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[21]
       Obtaining information for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[23]
       Obtained information for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[36]
       Starting SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[38]
       Started SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab using named pipe np:\\.\pipe\LOCALDB#47F58D7D\tsql\query.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[21]
       Obtaining information for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[23]
       Obtained information for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[21]
       Obtaining information for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[23]
       Obtained information for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:31Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[39]
       Stopping SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab with timeout: 00:01:00 and option(s) None.
 [2021-11-23 12:41:42Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[41]
       Stopped SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab after 00:00:10.0808585.
 [2021-11-23 12:41:42Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[13]
       Deleting named instance c867b24b-0108-4289-8859-9e92aace5dab of SQL LocalDB.
 [2021-11-23 12:41:42Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[18]
       Deleting file(s) for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab from C:\Users\martin.costello\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:42Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[20]
       Deleted file(s) for SQL LocalDB instance c867b24b-0108-4289-8859-9e92aace5dab from C:\Users\martin.costello\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\c867b24b-0108-4289-8859-9e92aace5dab.
 [2021-11-23 12:41:42Z] dbug: MartinCostello.SqlLocalDb.SqlLocalDbApi[17]
       Deleted named instance c867b24b-0108-4289-8859-9e92aace5dab of SQL LocalDB.
 [2021-11-23 12:41:42Z] dbug: MartinCostello.SqlLocalDb.Interop.LocalDbInstanceApi[55]
       Unloaded SQL LocalDB Instance API library C:\Program Files\Microsoft SQL Server\150\LocalDB\Binn\SqlUserInstance.dll.

C:\Coding\martincostello\sqllocaldb [dependabot/nuget/Microsoft.Data.SqlClient-4.0.0 ≡]> sqllocaldb info MSSQLLocalDB
Name:               MSSQLLocalDB
Version:            13.1.4001.0
Shared name:
Owner:              MyDomain\Martin.Costello
Auto-create:        Yes
State:              Running
Last start time:    23/11/2021 12:41:19
Instance pipe name: np:\\.\pipe\LOCALDB#B794E64A\tsql\query

[JRahnama]

@martincostello any progress on this issue? I am not able to repro it anymore !!!

[martincostello]

Just retried the linked job to update from GitHub Actions, and it's still failing: https://github.com/martincostello/sqllocaldb/runs/4328686462?check_suite_focus=true

[JRahnama]

out of curiosity, may this warning have anything to do with the issue?

[martincostello]

I don't think so. That's just a warning from MSBuild about file locks when copying artifacts around during the compilation for different TFMs in parallel.

[Rubenisme]

I'm having the same issue, I use Martin's library and had System.Data.SqlClient installed in a project reference, tests worked fine, but then switched to Microsoft.Data.SqlClient 4.0 and then got 2 different errors:

System.InvalidOperationException : An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseSqlServer' call.
----> Microsoft.Data.SqlClient.SqlException : The instance of SQL Server you attempted to connect to does not support encryption.

And in another test:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Switching back to either System.Data.SqlClient (4.8.3) or Microsoft.Data.SqlClient 3.0.1 (previous stable before 4.0) fixes it.

[JRahnama]

@Rubenisme thank you for updating this thread. One last question before Have you or @martincostello tried with net 5 to get connection from localdb?

@Rubenisme can you provide your connection string and the sample repro?

[martincostello]

Have you or @martincostello tried with net 5 to get connection from localdb?

With version 4.0.0, no. The library's tests were targeting .NET 5 until recently, but moved to .NET 6 not long after GA.

[JRahnama]

one more test, if you can add Encrypt=false to your connection string and see the result.

[Rubenisme]

@JRahnama It took me a little to reduce to a somewhat not-way-too-big way but have it reproduced: here.

Just run the unit test (nunit), then see it works, then update the Microsoft.Data.SqlClient nuget package to 4.0.0 and see it fail.