CODEWITHSUNDEEP
c#sql-serverentity-framework
Ray
Ray

Reputation: 11

C# Entity Framework error when MSSQL server has View any database = denied

We use MSSQL for our C# .NET Framework 4.8 Application using Entity Framework for database related activities. But on our production environment the SQL server has the Securable: View any database on Deny.

The database for the application exists but Entity Framework cannot see the database and tries to create it, this results in the CREATE DATABASE permission denied in database 'master' error.

I am using CreateDatabaseIfNotExists and MigrateDatabaseToLatestVersion in my Application_Start(). Now the issue (I think) lies with CreateDatabaseIfNotExists.

For the first run we give the db user enough rights to create and fill the database, it does this without problem.

But after the initial setup we remove those rights and the issue starts.

It tries to create the database, But it already exists.

And I am hoping there is a way to have both Automatic database creation/migration, and the View any database on deny securable.

Does anyone have a idea on how to solve this issue?

Is there some sort of option I could enable to stop this behaviour?

Upvotes: 1

Views: 132

Answers (1)

granadaCoder
granadaCoder

Reputation: 27904

You should "wire in" IHostingEnvironment and make sure you run

CreateDatabaseIfNotExists and MigrateDatabaseToLatestVersion

only in certain environments.

===========

For DotNet-Core (NON asp.net-core) apps.

https://learn.microsoft.com/en-us/dotnet/api/microsoft.extensions.hosting.internal.hostingenvironment?view=dotnet-plat-ext-7.0

for asp.net-core.

https://learn.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.hosting.iwebhostenvironment?view=aspnetcore-6.0

....

Then you will use (probably an existing)

"Is" method:

https://learn.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.hosting.hostingenvironmentextensions.isdevelopment?view=aspnetcore-7.0

IsDevelopment IsProduction IsStaging

or you have the ability to "add your own environment".. with

IsEnvironment(string)

I would NEVER leave to "auto-voodoo" what might happen to the production database.

You can see this approach:

https://stackoverflow.com/a/60399887/214977

Upvotes: 1

Related Questions