I have an Azure SQL Edge running in Docker, but I'm not able to create new database users. CREATE USER gives following error:
"You can only create a user with a password in a contained database."
If I try to change containment using ALTER DATABASE TestDB set containment=PARTIAL; there's no success either. Error says: "The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use. ALTER DATABASE statement failed."
I've also tried EXEC sp_configure 'contained database authentication', 1;, but it is giving: "The specified option 'contained database authentication' is not supported by this edition of SQL Server and cannot be changed using sp_configure."
Is there something else, I could try, or is this what it should be, and I should just use database as sa? Any help would be much appreciated.
This is probably not relevant for you anymore, but anyone else coming across this issue. I had the same problem using testcontainers.
I solved it going for the traditional login model: https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver16#syntax-differences
-- set username:testUserLogin and password:yourPassword for your database connection
create login [testUserLogin] with password = 'yourPassword';
create user [TESTUSER] for login [testUserLogin] with default_schema =[YOUR_SCHEMA];
However, if you really need or want the contained user model, then it wouldn't help you.