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

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

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 25 (19 by maintainers)

Commits related to this issue

Update CI to use sql2019, not 2017 This is likely related to https://github.com/dotnet/SqlClient/issues/1395 and we obviously don't care about encryption here. — committed to progressonderwijs/ProgressOnderwijsUtils by EamonNerbonne 3 years ago
Test Microsoft.Data.SqlClient fix Test fix for dotnet/SqlClient#1395. — committed to martincostello/sqllocaldb by martincostello 2 years ago

Most upvoted comments

@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.

Rubenisme on Nov 29, 2021

@martincostello can you test with this artifact . If you do not have access use this file and change the zip extension to nupkg Microsoft.Data.SqlClient.4.0.0-pull-c0ca2ea.21348.2.zip

JRahnama on Dec 15, 2021

I am going to make PR to address the cause of this issue. The issue comes from the fact that driver checks if the connection string starts with (localdb) then it set isLocalDB to true otherwise false. However the samples provided by repros are using Instance pipe name which is in a format of np:\\.\pipe\LOCALDB#<some number>\tsql\query (MS doc) That check needs to be added to the LocalDBAPI for native and SNIProxy for managed code.

JRahnama on Nov 30, 2021

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.

martincostello on Nov 29, 2021

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.

Rubenisme on Nov 27, 2021

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 on Nov 22, 2021

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

JRahnama on Nov 20, 2021