Document when PersistSecurityInfo=True is needed

[umbarov]

File a bug

When I pass to UseSqlServer() connection string, it connects to the docker db and local db. But when I pass to UseSqlServer() new Microsoft.Data.SqlClient.SqlConnection(connString), it fails to connect to the docker db but connects to the local db. Simple repo https://github.com/umbarov/dotnet-sqlserver-gh-actions with Github Actions.

Connects to the docker db and local sql server:

var context = new AppDbContext(new DbContextOptionsBuilder<AppDbContext>().UseSqlServer(connString).Options);

Fails to connect to the docker db, but connects to the local sql server:

var context = new AppDbContext(new DbContextOptionsBuilder<AppDbContext>().UseSqlServer(new SqlConnection(connString)).Options);

Include stack traces

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'sa'.
   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
)

Include provider and version information

EF Core version: 5.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10 Pro , Version: 20H2, OS build: 19042.630

[smitpatel]

You need PersistSecurityInfo=True in you connection string when you pass us SqlConnection because by default it will strip out the password. So the connection EF Core has does not have password information in it to connect correctly.
This does not happen for localdb because you are using Trusted_Connection which effectively uses your user login.

[umbarov]

@smitpatel thanks. With PersistSecurityInfo=True it works.

[ajcvickers]

Moved to docs to document when this is needed. (Basically when the application passes a SqlConnection to us, but we then need to connect to the master database. This means we have to clone the connection, and we lose the password.)

[umbarov]

@ajcvickers I saw examples on this page https://docs.microsoft.com/en-us/ef/core/testing/sharing-databases#the-fixture when was writing unit tests. Someone else might step on the rake.