EF Core automatically creates expensive database when executing migrations

[julienFlexsoft]

Hi,

The software I made executes migrations in a controlled automatic process. Sometimes there is a small bug there than can cause the migration to happen for a DB that doesn't exist. The server of the connectionstring does exist, but the DB doesn't.
This causes EF Core to automatically create the DB and apply migrations. The issue here is that we use Azure SQL Server and the default pricing plan is a General purpose Gen5, this results in a massive bill if we don't notice the new DB right away.

Is there a way to modify this behavior or change the default pricing plan?
I saw some posts about Database Initializers but those seem to only exist in Entity Framework, not EF Core.

I saw this "Specifying Azure SQL Database Options". But this isn't what I'm looking for, I'd like for the default to be "basic" but this shouldn't be applied to all DB's we have deployed.

[ErikEJ]

Maybe add a check if the database exists and use that in your further processing?

[ajcvickers]

As @ErikEJ says, you can use something like this:

if (context.GetService<IRelationalDatabaseCreator>().Exists())
{
    // Do stuff..
}

[vigouredelaruse]

apologies for harping on this but looked at another way, this feature presents an attack vector for disgruntled exployees exiting their positions, or even ungruntled employeess just doing their jobs

in more detail, (copied from my query over here #24866)

re: automatic creation of azure resources, specifically today referencing azure sql database (excluding managed instances, caveated here https://techcommunity.microsoft.com/t5/azure-sql-blog/what-azure-permissions-are-required-to-create-sql-managed/ba-p/386275)

a cursory investigation will show that database resource deployment in azure depends on an intersection of permissions

azure policy
azure arm
azure sql user permissions

EXCEPT
azure policy permits macro governance and the document here https://learn.microsoft.com/en-us/azure/azure-sql/database/policy-reference?view=azuresql has the following to say about azure policy and azure sql database

Azure Policy applicable to a Azure SQL Database creation is not enforced when using T-SQL or SSMS.

in the form of a question:
given

an entity framework core app
running against azure sql database
authenticated with a user who does not have sql drop database or create database privs
is it reasonable to expect that developers cannot 'accidentally' spin up arbitrary azure sql server instances by using .ensurecreated() or .ensuredeleted() and/or .ensuremigrated()

i suggest that no, it's not reasonable to leave unaddressed a situation whereby incomplete or buggy implementations of database generation can spin up expensive resources and in the azure case, circumvent the entire rbac stack

please advise

[julienFlexsoft]

@ajcvickers

Maybe add a check if the database exists and use that in your further processing?

Thanks, that's how I solved it, I added checks, but I'm still vulnerable to bugs and mistakes so I'd like to fix it on the azure side as well.

[ajcvickers]

@julienFlexsoft EF Core doesn't have any special permissions here. It doesn't do anything that couldn't be done without EF Core, which includes creating databases. If you think Azure shouldn't allow this, then that's probably something you should bring up with the Azure folks.

[julienFlexsoft]

@ajcvickers Ah yes, sorry, I forgot that this was the EF Core github for a minute. I'm trying to change it from the azure side as well as the EF Core side. From the Azure side I'm exploring the permissions to see if there is a way to block it there.

But for EF Core: I understand that the MigrateAsync() actually does something like Check if the DB exists → if not: create it → apply migrations. A simple parameter for MigrateAsync(bool createIfNotExist = true) could be what I'm looking for. I know this doesn't exist and I don't know if the underlying EF Core logic allows for something like this, but what is your opinion?

[roji]

@julienFlexsoft as indicated above, it's already trivial to add code which checks whether the database exists. So I'm not sure how the flag would help, given that it would default to true: anyone bitten by the issue today would continue getting bitten by the same issue tomorrow. All it would do is integrate the existence change inside MigrateAsync.

[vigouredelaruse]

further to @roji's suggestion of bringing this up with the azure entity see
https://feedback.azure.com/d365community/idea/cb36c798-3025-ec11-b6e6-000d3a4f0f84

buuut
there is a new product that seems to apply. it's in preview, called microsoft purview and claims to externally controls policies against sql server 2022 and azure sql server
as per

• https://learn.microsoft.com/en-us/azure/purview/concept-policies-data-owner
• https://techcommunity.microsoft.com/t5/azure-sql-blog/private-preview-controlling-access-to-azure-sql-at-scale-with/ba-p/2945491
• https://techcommunity.microsoft.com/t5/sql-server-blog/new-granular-permissions-for-sql-server-2022-and-azure-sql-to/ba-p/3607507

my interest in the matter is now appropriately redirected