why contained database user needs Persist Security Info=True

Question

I have a database where I created a contained user and I needed to connect to my web app using that user. I have always been able to connect to the web app with a standard user having Persist Security Info=False.

However, the only way I was able to connect with the contained user was changing my connection string to Persist Security Info=True, otherwise I'd get a login failed sql exception even though I was able to connect using SSMS. I'm not sure why it worked, does anybody know why a contained user needs the property set to True?

Answer 1

For you web app, are you using Entity Framework ? And for your DbContext are you using IdentityDbContext ?

If so, I had the same problem. I was able to connect directly with SqlConnection but encountered an "Access Deny" error when connecting with Entity Framework. When I gave enough permissions to my user, all queries were very slow.

When instantiating the Context (with IdentityDbContext) you should set the second parameter to false.

public AdeleDbContext(string connectionString) : base(connectionString, false)
{
}

The second parameter is throwIfV1Schema and when set to true (which is the default value), it will validate schema against the database by calling SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@Table for many columns.

That was the reason why the connection was slow and user needed more permissions when connecting to DB with Entity Framework and IdentityDbContext.