Sql-docs: CREATE USER FROM EXTERNAL PROVIDER by Service Principal

[alexmbaker]

I am using an Azure Active Directory Service Principal to authenticate with an Azure SQL Database. Everything works as I would expect except when I try to add an Azure AD user to the database using a script similar to:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

I can run the script without issue however the Service Principal returns Error 33134, Severity 16, Message "Principal '[email protected]' could not be found at this time. Please try again later."

I can think of a couple of reasons for this however I think the most likely cause being the Service Principal not having been granted a required permission.

Could you include details in this document of the Azure AD permissions required to successfully execute a CREATE USER .... FROM EXTERNAL PROVIDER statement.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 65ba42b4-26ac-259a-5f2a-4c96e00f1645
• Version Independent ID: b5d8007e-38e2-b99f-7fd8-975d9bd390b7
• Content: CREATE USER (Transact-SQL) - SQL Server
• Content Source: docs/t-sql/statements/create-user-transact-sql.md
• Product: sql
• Technology: t-sql
• GitHub Login: @VanMSFT
• Microsoft Alias: vanto

[msebolt]

Hi @alexmbaker, thank you for the feedback!

This issue has been addressed on the forums and via blog post.

Please comment if either of these posts answers your questions.

[alexmbaker]

@msebolt while the links you have provided do cover a similar area of functionality they do not discuss the specific use case. Consider this script:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER

When I execute this script against my Azure SQL database it works exactly as expected.

The issues arise with this scenario:

• I create a service principal in the Azure Active Directory
• Create a group in the Azure AD - lets say 'sql-admins' and
  * Add myself to the group
  * Add the service principal to the group
• Make the group the Azure AD administrator of my Azure SQL Server

Again, if I connect to an Azure SQL database on my server I can run the script above successfully

Things go wrong when I:

• Obtain a SQL Server access token for the service principal
• Using the access token connect to the SQL Database - (so far so good I can issue DDL and DML scripts)
• When I run the script above then I encounter the issue I reported.

We have been experimenting today and have found that adding the service principal to the built in Azure AD Role Directory Readers resolves the issue. What I don't know is if this is the Microsoft recommended approach? Should we perhaps be granting the service principal an API Permission to the Microsoft Graph or Azure Active Directory API?

[VanMSFT]

Hi @alexmbaker - You should not have to add the user to the AAD role Directory Readers. Our Product Team is looking to fix this issue in the next month. I hope that helps.

[VanMSFT]

Closing this for now. Our Product Team is already working on a fix, and we'll update the documentation when it's ready. Please let us know if there are any further concerns.

[michaelsync]

@VanMSFT

Does it fix?

I have a similar issue but different error.

I can execute this script CREATE USER [AAD-UserGroup] FROM EXTERNAL PROVIDER using my AAD user but I got the following error when I execute it with the service principle.

that access between first-party applications must be handled via preauthorization

According to this page https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-aad-authentication-configure.md, it recommended using the user instead of the service principle. I am not sure if it's a limitation or a bug.

[VanMSFT]

Hi @michaelsync - We currently don't allow service principals to create users in SQL DB. We will update the documentation when this feature is available. Thanks!

[michaelsync]

@VanMSFT Thanks for the confirmation. I think it should have mentioned in the document. E.g. "Creating the AAD user in DB using the service principal is not supported."

So we don't need to spend so much time on googling and researching. :) I've been searching for the solution for 3 days now. We automated a lot of stuff using the service principal and stuck with this issue. It seems like we need to create a new user just to execute this T-SQL.

[alexmbaker]

Simply create a group in your Azure active directory, add the service principal to the group.  
Now create the database user from the group and you have what you need.

Alex Baker

On Fri, Mar 6, 2020 at 6:56 AM +1100, "Van To" [e-mail] wrote:

Hi @michaelsync - We currently don't allow service principals to create users in SQL DB. We will update the documentation when this feature is available. Thanks!

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or unsubscribe.

[Rookian]

Hi @michaelsync - We currently don't allow service principals to create users in SQL DB. We will update the documentation when this feature is available. Thanks!

Hi @VanMSFT we are also effected by this issue. Is there any news regarding this topic? Is there a schedule for beeing able to use Service Principal in order to create Managed Identity based SQL users?

[Rookian]

@msebolt while the links you have provided do cover a similar area of functionality they do not discuss the specific use case. Consider this script:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER

When I execute this script against my Azure SQL database it works exactly as expected.

The issues arise with this scenario:

• I create a service principal in the Azure Active Directory
• Create a group in the Azure AD - lets say 'sql-admins' and
  * Add myself to the group
  * Add the service principal to the group
• Make the group the Azure AD administrator of my Azure SQL Server

Again, if I connect to an Azure SQL database on my server I can run the script above successfully

Things go wrong when I:

• Obtain a SQL Server access token for the service principal
• Using the access token connect to the SQL Database - (so far so good I can issue DDL and DML scripts)
• When I run the script above then I encounter the issue I reported.

We have been experimenting today and have found that adding the service principal to the built in Azure AD Role Directory Readers resolves the issue. What I don't know is if this is the Microsoft recommended approach? Should we perhaps be granting the service principal an API Permission to the Microsoft Graph or Azure Active Directory API?

Did you use Azure.Identity or the old Microsoft.Azure.Services.AppAuthentication? I tried your solution (assigning Directory Readers) with Azure.Identity without success.

[VanMSFT]

Hi @alexmbaker and @Rookian - There will be a tutorial coming out on this in the next week or so. This is going in preview, but @alexmbaker had it right.

1. Assign a server identity to the Azure SQL logical server
   
   
   
   • Set-AzSqlServer -ResourceGroupName <resource group> -ServerName <Server name> -AssignIdentity
   
   
2. Grant the Directory Readers permissions to the server identity.

[Rookian]

Hi @VanMSFT!
We have continued to research the topic and propose the following alternative solution.
We ask Microsoft kindly to verify the solution described below and to inform whether this is an approperiate solution.

Proposed solution
A service principal is configured to create various resources in a subscription.
This service principal has to be assigned to the Directory Readers role in the AAD in order to determine the Application Id for a Managed Identity in the context of the deployment process.

The configured “Managed Identity” of the Azure App Service can be registered on the respective Azure SQL database with the following command.

CREATE USER [{managedIdentityName}] WITH SID={applicationSid}, TYPE=E

The SID (applicationSid) passed in the SQL statement is now resolved by the deployment process and is accepted by the SQL Server without verification. You can use the .NET Core library Microsoft.Azure.Management.Graph.RBAC.Fluent to obtain the Application Id for an AAD registered application (Managed Identity).

var principal = await graphRbacManager.ServicePrincipals.GetByNameAsync(managedIdentityName);
var applicationId = Guid.Parse(principal.ApplicationId);
var applicationSid = FormatSqlByteLiteral(applicationId.ToByteArray());

private static string FormatSqlByteLiteral(byte[] bytes)
{
    var stringBuilder = new StringBuilder();
    stringBuilder.Append("0x");
    foreach (var @byte in bytes)
    {
        if (@byte < 16)
        {
            stringBuilder.Append("0");
        }
        stringBuilder.Append(Convert.ToString(@byte, 16));
    }
    return stringBuilder.ToString();
}

After that, authorization roles (e.g. db_datareader, db_datawriter, db_ddladmin) can now be assigned to the assigned Managed Identity.

ALTER ROLE {role} ADD MEMBER [{managedIdentityName}];

Conclusion
The Azure App Service can now access the previously authorized Azure SQL database in the context of its Managed Identity.

Regarding your proposed solution of using SQL Server Managed Identity and assign it the Directory Readers role
Please keep in mind that a lot of companies out there have very strict regulations when it comes to Azure AD administration. Your proposed solution has two possible approaches

1 Grant Service Principal permission to assign Azure AD roles in order to assign Directory Readers role to SQL Server Managed Identity. This is a security nightmare, because now the Service Principal could assign e.g. Global administrator or other roles.

2 Manually assigning Directory Readers role by an Azure AD admin after SQL Server is provisioned. (error-prone and cumbersome).

[valx]

Hi all,
I am working with a very similar scenario as @Rookian (Thanks for posting it!), and his solution seems to work for us.

I am working with user groups (instead of individual users) and programming in Python, so I used the following code:
1) Get user SID using MS Graph REST API:

    prefix = "user-group-name" # user group name (or prefix of the name)
    resource = "https://graph.microsoft.com/"
    authority_url = (parameters['authorityHostUrl'] + '/' +
                    parameters['tenant'])
    context = adal.AuthenticationContext(
        authority_url, api_version=None
        )
    token = context.acquire_token_with_client_credentials(
        resource,
        parameters['clientId'],
        parameters['clientSecret'])
    accessToken = token["accessToken"]

    result = requests.get(
        (f'{resource}v1.0/groups/?$filter=startswith(displayName,\'{prefix}\')'
         f'&$select=id,securityIdentifier,displayName'),
        headers={'Authorization': accessToken})

The SID can be extracted from the result json.

2) convert the SID from the format returned from MS Graph into the one used in SQL Servers.
I wrote the following function for the conversion (I couldn't find any documentation, so I have reverse engineered this function).

def convert_graphSid_to_sqlSid(g_sid):
    """
    converts a string representing a users/groups Security ID (SID) in Azure Graphs 
    into a string representing SID for Azure SQL server users/groups.
    The output is a 16 bytes Hex representation of the last 4 10-digits-blocks of the graph SID

    Example:
    input g_sid: 'S-2-24-2-1232761456-456137123-1234432456-1235122456'
    output s_sid: '0x70727A49A319301BC8F1934918799E49'
    """

    # split the g_sid by '-' and take the last 4 elements
    l_g_sid = g_sid.split('-')[4:]

    # for each 10 digit string block: convert string to int, convert int to byte,
    # convert the bytes to their hexadecimal representation.
    # Byteorder is "little": the most significant byte is at the end of the byte array
    s_sid = ''.join(map(lambda x: int(x).to_bytes(4,'little').hex().upper(), l_g_sid))
    s_sid = '0x' + s_sid

    return s_sid

3) Finally the SQL code to create SQL user associated to the AD user groups:

CREATE USER [user-group-name]  WITH SID=0x70727A49A319301BC8F1934918799E49, TYPE=X;

[TroyWitthoeft]

Hi @alexmbaker and @Rookian - There will be a tutorial coming out on this in the next week or so. This is going in preview, but @alexmbaker had it right.

1. Assign a server identity to the Azure SQL logical server

   • Set-AzSqlServer -ResourceGroupName <resource group> -ServerName <Server name> -AssignIdentity
2. Grant the Directory Readers permissions to the server identity.

@VanMSFT Do we have a link to the tutorial?
Update: Is this it? https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial

[VanMSFT]

@TroyWitthoeft - Yes, that is the new tutorial.

[GithubMirek]

Please be aware that:
SID and TYPE represent an undocumented, unsupported and unsecured commands and will be removed in the near future.
For proper solution please review the main docs indicated below
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-directory-readers-role
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-directory-readers-role-tutorial
Mirek Sztajno, Senior PM, Azure SQL