Unable to connect to server with contained DB user

[Baklap4]

I'm using Azure SQL Managed Instance and set up my databases to exist and be contained (partial/full). Within this database i've created contained SQL users. Which have the grant to connect to it. If i connect to the database by supplying the name of the db and correct credentials within SSMS all works as supposed to.

However when i ran dacpac publish within the CI with the exact same credentials and so on it somehow gets an error saying that the login wasn't correct. I'm afraid this project doesn't support contained mode such as i have set up at this moment and I'd love to see that being implemented.

SQL Server setup:

1. sa user which has a server login
2. Create a database with this sa user
3. Create a local user within the just made database. (so not on database server level but on the newly created database level)
4. Add db_owner roles to that local user so it can actually do everything
5. Grant the CONNECT permission to the local user
6. Logout the SA user
7. Login with the newly created user by ALSO supplying the database-name since you can't browse the server with a contained user.
8. Login is succesful.

[jeffrosenberg]

Fair warning here: while we provide the publish feature as a convenience for development, it's not the primary focus of this project, and most of our efforts go towards our build features. In general, we've directed users to use SqlPackage for advanced publish scenarios, so I can't guarantee that this issue will get prioritized.

That said, I believe all we'd need to do would be to add InitialCatalog to our connection string builder. Scanning through the publish code, I saw that we only use InitialCatalog in one place, along with this comment:

if (!isPreDeploy)
{
    // Only set initial catalog for post-deployment script since database might not exist yet for pre-deployment
    builder.InitialCatalog = targetDatabaseName;
}

This makes sense -- we couldn't create a contained database, since we wouldn't have a valid server login. However, we could theoretically allow the user to specify that this is a contained database and add the InitialCatalog to our connection string, we'd just have to clarify in the readme that this will fail if the database doesn't already exist.

@jmezach @ErikEJ thoughts? Do we want to add this bit of complexity to the publish code, or just recommend using SqlPackage for this?

[jeffrosenberg]

Sorry for the double post everyone, I commented the first time using the wrong Github account, I've removed the original comment and re-posted from the correct account. 😰

[ErikEJ]

I vote for SqlPackage, let's consider publish support a developer / local convenience.

[Baklap4]

The thing is SqlPackage doesn't support this either as that was my first attempt trying so i fell back to dotnet publish with this tool.

That being said for developer / local convenience i would like to replicate a production-like environment as much as possible. A plausible scenario, and even good practice, is to have contained user access instead of server logins.

Aside from the connectionstring i think there has to be a check wether or not CreateNewDatabase is set to true which cannot be the case in this scenario

[ErikEJ]

I do not understand why this is not working with SqlPackage.

Could you share your connection string/ SqlPackage script? (Anonymised)

[jeffrosenberg]

If sqlpackage doesn't support it either, then it's probably significantly more challenging than I thought and almost certainly outside our scope 🙂