[SOLVED] Cannot create database scoped credential with a login that is part of an AD group

Cannot create database scoped credential with a login that is part of an AD group

I'm trying to find the minimum required permission or role that would allow a user that was assigned permissiosn through an AD group in Azure SQL server to create a database scoped credential via the following syntax.

CREATE DATABASE SCOPED CREDENTIAL [SomeCredential] 
    WITH IDENTITY = N'someSqlUser', SECRET='someSqlPassword';

Based on documentation, the following permissions are needed.

GRANT CONTROL ON DATABASE::[MyDatabase] TO [SqlAdminGroup]

The error when someone from that group tries to create a database scoped credential is below:

The specified schema name "personInSqlAdminGroup@mytentant.com" either does not exist or you do not have permission to use it.

So what is interesting is that when I try this with a individual SQL login where I specifically gave the login CONTROL permission, it works. When I try this via granting to an AD group is when I get the specified schema name error. I added the group with a default schema of DBO.

I believe there is an issue with an AD group in SQL not having a default schema, so any object creation would need to fully qualify the object with [dbo].[object]. However, database scoped credentials don't seem to want a schema because specified it results in a parsing error.

CREATE DATABASE SCOPED CREDENTIAL [dbo].[SomeCredential] 
    WITH IDENTITY = N'someSqlUser', SECRET='someSqlPassword';

So if it doesn't want a schema, why does it complain about the specified schema name of the user not existing?

Solution

This is a documented issue here Use Azure Active Directory authentication. Quoting the relevant bullet from "Azure AD features and limitations": -

Azure AD users that are part of a group that has db_owner server role cannot use the CREATE DATABASE SCOPED CREDENTIAL syntax against Azure SQL Database and Azure Synapse. You will see the following error:

SQL Error [2760] [S0001]: The specified schema name 'user@mydomain.com' either does not exist or you do not have permission to use it.

Grant the db_owner role directly to the individual Azure AD user to mitigate the CREATE DATABASE SCOPED CREDENTIAL issue.

The db_owner role membership does not match your CONTROL on database scenario exactly, but it looks likely to be the same underlying issue. I don't have an explanation for this surfacing as the schema error.