Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
martincostello opened this issue Nov 19, 2021 · 25 comments · Fixed by #1433
Labels
🐛 Bug! Issues that are bugs in the drivers we maintain.

Comments

@martincostello
Copy link
Member

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
Copy link
Member Author

Likely related to #1312.

EamonNerbonne added a commit to progressonderwijs/ProgressOnderwijsUtils that referenced this issue Nov 19, 2021
This is likely related to dotnet/SqlClient#1395 and we obviously don't care about encryption here.
@JRahnama
Copy link
Contributor

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

@JRahnama
Copy link
Contributor

JRahnama commented Nov 22, 2021

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

This is my localdb version:

image

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

@martincostello
Copy link
Member Author

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
Copy link
Member Author

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
Copy link
Contributor

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

@martincostello
Copy link
Member Author

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
Copy link
Contributor

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

@martincostello
Copy link
Member Author

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
Copy link

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
Copy link
Contributor

@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
Copy link
Member Author

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
Copy link
Contributor

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

@Rubenisme
Copy link

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

@JRahnama
Copy link
Contributor

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
Copy link
Contributor

as a work around for now, till the fix is out, if there is a way to add Encrypt=false to your connection string that would solve the issue. In the provided sample repro by @Rubenisme it happens at await EnsureDatabaseCreated(); inside test class IntegrationTestBaseLocalDb

@Rubenisme
Copy link

@JRahnama thank you for finding the issue so quickly, and creating a PR to fix it. Sorry I didn’t have a more minimal repo but thank you and for pinpointing the place it happens exactly.

@martincostello will you update the library in the meantime or let users know about the workaround and wait for the fix on the SqlClient library?

@martincostello
Copy link
Member Author

I won't be updating the library for this, especially with a fix coming, as users can work around it themselves.

I'll point any users that file issues with the SqlLocalDb library to this issue if I get any reports.

@KoalaBear84
Copy link

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

Got the same error message as mentioned above after updating from 3.x to 4.0.0. SQL Server is on my local machine, not sure if that if the same issue. I've added Encrypt=no to my connection string and it works again. Might not be the best solution, but one that works.

@Rubenisme
Copy link

Rubenisme commented Dec 3, 2021

@KoalaBear84 Above @JRahnama says that you should use Encrypt=false, probably your no also works as you said.

On another note:

The issue comes from the fact that driver checks if the connection string starts with (localdb) then it set isLocalDB to true otherwise false.

@JRahnama if you have this implemented, can you also expose the IsLocalDb property/method somewhere or have the provider name be different from normal SqlServer?
For an InMemory database you can do: context.Database.IsInMemory(). I would like to do the same for a localDb, so either context.Database.IsLocalDb() method or context.Database.IsLocalDb get-only property would be great.

Update: Nevermind, I realize this is an EF core thingy, and although you work for the same company and probably work somewhat close together with that team, they are still 2 different worlds. If you can expose it to them that would probably help down the road. ;)

@ErikEJ
Copy link
Contributor

ErikEJ commented Dec 3, 2021

@Rubenisme All localdb server names have a unique pattern, that you can detect.

@Rubenisme
Copy link

@ErikEJ Well yes, I now use: context.Database.GetConnectionString().ToLowerInvariant().Contains("localdb") Not super nice, but it works.

@JRahnama
Copy link
Contributor

JRahnama commented Dec 3, 2021

@Rubenisme you can access that through SELECT SERVERPROPERTY ('IsLocalDb'). Read here.

@JRahnama JRahnama added the 🐛 Bug! Issues that are bugs in the drivers we maintain. label Dec 15, 2021
@JRahnama
Copy link
Contributor

JRahnama commented Dec 15, 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

@martincostello
Copy link
Member Author

@JRahnama Validated the fix with martincostello/sqllocaldb#466.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
🐛 Bug! Issues that are bugs in the drivers we maintain.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants