1

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?

Ray
  • 11
  • 3
  • 1
    You can use the code-first approach with Entity Framework, but specify the existing database to use instead of trying to create a new one. You can do this by using the "Database.Exists" method in your Application_Start method to check if the database exists, and if so, use "Database.SetInitializer(null)" to prevent Entity Framework from trying to create the database. – Oyvind Andersson Feb 08 '23 at 13:07
  • 1
    @OyvindAndersson Alright the SetInitializer(null) works, the Database.Exists doesn't. It performs a query on sys.databases (IF db_id(N'databasename') IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=N'databasename') this is a permission the user will not have anymore after initial setup. But I can write my own select query and execute it to verify if the database exists! So I thank you for your response you have guided me towards a solution! – Ray Feb 08 '23 at 13:39

1 Answers1

1

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

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • While I agree that nothing should be left up to "auto-voodoo", we rely on the automatic creation/updating of our database. Our application runs on premise for a lot of customers, and it provides an easy and reliable way of making sure the database is fully up-to-date. Not executing our migrations on a production environment would require additional steps or automation. So unfortunately I cannot accept your question. Due to the reason that it doesn't fix my problem. – Ray Feb 08 '23 at 14:20
  • Couldn't you do a "IsEnvironment" suppression "CreateDatabaseIfNotExists"...but keep MigrateDatabaseToLatestVersion for every environment? – granadaCoder Feb 08 '23 at 14:38
  • I see. You have to "install on premise"... many times/different-places. Gotcha. I've leave the answer for future reader..but understand how it does not apply in your situation. – granadaCoder Feb 08 '23 at 14:46
  • Yeah we could, but I have learned that MigrateDatabaseToLatestVersion executes the same query as CreateDatabaseIfNotExists to see if the database is there, before doing the migrations. So both methods will result in a error when the user does not have permissions to execute queries outside of its database. – Ray Feb 08 '23 at 14:47