英文:
C# Entity Framework error when MSSQL server has View any database = denied
问题
我们在C# .NET Framework 4.8应用程序中使用MSSQL，使用Entity Framework进行与数据库相关的操作。
但是在我们的生产环境中，SQL服务器具有Securable: View any database on Deny权限。
应用程序的数据库存在，但Entity Framework无法看到数据库并尝试创建它，导致出现“CREATE DATABASE权限在数据库'master'中被拒绝”的错误。
我在Application_Start()中使用CreateDatabaseIfNotExists和MigrateDatabaseToLatestVersion。
现在问题（我认为）出现在CreateDatabaseIfNotExists上。
对于第一次运行，我们为数据库用户提供足够的权限来创建和填充数据库，这没有问题。
但在初始设置之后，我们撤销了这些权限，问题就开始出现了。
它尝试创建数据库，但它已经存在。
我希望有一种方法可以同时实现自动数据库创建/迁移和拒绝查看任何数据库的安全性。
有人知道如何解决这个问题吗？
是否有一种选项我可以启用以阻止这种行为？
英文:
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?
答案1
得分: 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.
for asp.net-core.
....
Then you will use (probably an existing)
"Is" method:
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
英文:
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.
for asp.net-core.
....
Then you will use (probably an existing)
"Is" method:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论