Creating New Azure SQL Server From Daemon

Question

How can I use the Azure SDK to create a new Azure SQL Server within a resource group from a daemon (technically a queue processor)? The problem I'm encountering is an authorization error when attempting to do the create. Here's the exception:

Hyak.Common.CloudException AuthorizationFailed: The client 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' with object id 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' does not have authorization to perform action 'Microsoft.Sql/servers/write' over scope '/subscriptions/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/resourceGroups/{myResourceGroupName}/providers/Microsoft.Sql/servers/{newServerName}'.

(The actual exception has real values in each of the placeholders.)

I'm using the Microsoft.Azure.Management.Sql NuGet package, version 0.38.0-prerelease (latest currently available).

I previously attempted to use CertificateCloudCredentials to make the change -- however it yielded the following exception:

Hyak.Common.CloudException AuthenticationFailed: Authentication failed. The 'Authorization' header is not present or provided in an invalid format.

Now I'm using TokenCloudCredentials, obtained in the following way:

AuthenticationContext aContext = new AuthenticationContext(string.Format(
            anApiConfiguration.LoginUriFormat,
            anApiConfiguration.TenantId));
ClientCredential aClientCredential = new ClientCredential(
            anApiConfiguration.ClientId, 
            anApiConfiguration.ClientSecretKey);
AuthenticationResult aTokenResponse = await aContext.AcquireTokenAsync(
            anApiConfiguration.BaseResourceUrl, 
            aClientCredential);

From this I successfully obtain a token. I then use that token as follows:

SqlManagementClient sql = new SqlManagementClient(anAuthToken);
ServerCreateOrUpdateProperties someProperties = 
    new ServerCreateOrUpdateProperties {
        AdministratorLogin = someSettings.AdminAccountName,
        AdministratorLoginPassword = someSettings.AdminPassword,
        Version = "12.0"
};
ServerGetResponse aCreateResponse = 
    await sql.Servers.CreateOrUpdateAsync(
        someSettings.GetResourceGroup(aRegionType),
        aServerName, 
        new ServerCreateOrUpdateParameters(someProperties, aRegion));

That final line, the CreateOrUpdateAsync call, is what yields the exception at the top of this post.

Within Azure Active Directory the client application is granted the Access Azure Service Management (preview) delegated permission (the only one permission which exists for that application) within the Windows Azure Service Management API application. (If it matters, I've also granted the client application all available permissions for the Windows Azure Active Directory application.)

Why are you not using this package Microsoft.WindowsAzure.Management.Sql? — Shaun Luttin
Right now the code above is being manually executed from an integration test. I just added that detail to indicate that this will be run from a web job, processing requests from an Azure queue, once deployed. — Gnosian
I'm not using Microsoft.WindowsAzure.Management.Sql because it does not support creating the server within a resource group. — Gnosian
Where did you find Microsoft.Azure.Management.Sql? It is not in my NuGet feed. No worries. Found it with -IncludePrerelease flag. — Shaun Luttin

Adam Kromm Adam Kromm · Accepted Answer · 2015-09-11T21:37:52

I think I have figured out what the problem is and how to fix it (I was also able to reproduce this on my work computer).

Cause

It looks like in the sample app the default method for getting the authentication token is using the wrong account. For example my Subscription and Application are under my example@outlook.com email account, but when I run the console application from my work computer the application gets a token for username@company.com and tries to perform the actions under that account. It seems like the code for getting the authentication token is using some cached account information from the computer (IE?).

The error message you see relates to RBAC (Role-Based Access Control) restricting an authenticated but unauthorized account from doing actions to your resources (https://azure.microsoft.com/en-us/documentation/articles/role-based-access-control-configure/).

Solution

You can change the code around a bit to tell it which account to use when performing the actions. Change the "GetAccessTokenUsingUserCredentials" method in the sample app to look like this:

    private static AuthenticationResult GetAccessTokenUsingUserCredentials(UserCredential userCredential)
    {
        AuthenticationContext authContext = new AuthenticationContext
            ("https://login.windows.net/" /* AAD URI */
            + "YOU.onmicrosoft.com" /* Tenant ID or AAD domain */);

        AuthenticationResult token = authContext.AcquireToken(
            "https://management.azure.com/"/* the Azure Resource Management endpoint */,
            "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX" /* application client ID from AAD*/,
            new Uri("XXXX" /* redirect URI */,
            PromptBehavior.Auto,
            new UserIdentifier(userCredential.UserName, UserIdentifierType.RequiredDisplayableId));

        return token;
    }

and in the main function change the line that gets the token to be:

        var token = GetAccessTokenUsingUserCredentials(new UserCredential("<desired_account_email>"));

You should be able to modify it a bit to also take in a password so the AAD login box doesn't pop-up.

I hope that works for you!

EDIT: Here is how to get it to work for a Web-App:

Get your Client ID and Key from the Configure page for the web app (You need to select a duration for the key and then click save for it to be generated)

Change the authentication code in the app to look like this:

private static AuthenticationResult GetAccessTokenForWebApp()
{
    AuthenticationContext authContext = new AuthenticationContext
        ("https://login.windows.net/" /* AAD URI */
        + "YOU.onmicrosoft.com" /* Tenant ID or AAD domain */);

    ClientCredential cc = new ClientCredential("<client_id>", "<key>");

    AuthenticationResult token = authContext.AcquireToken(
        "https://management.azure.com/"/* the Azure Resource Management endpoint */,
        cc);

    return token;
}

At this point if you try to run the app you will get an authentication error that looks something like this:

Hyak.Common.CloudException AuthorizationFailed: The client 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' with object id 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' does not have authorization to perform action 'Microsoft.Sql/servers/write' over scope '/subscriptions/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/resourceGroups/{myResourceGroupName}/providers/Microsoft.Sql/servers/{newServerName}'.

This is because you need to add a RBAC role for the application/service principal. From the error message you need to copy the "Object ID", this is the AD object ID that will be used for the role assignment.

Get the latest powershell cmdlets for Windows Azure: http://azure.microsoft.com/en-us/downloads/

Once powershell is installed run the following:

Switch-AzureMode AzureResourceManager #_this will be deprecated soon_
Add-AzureAccount # you will be prompted for your credentials
New-AzureRoleAssignment -ObjectId <your_object_id> -RoleDefinitionName <role_name> 
# You can use "Contributor" to give the web app access to basically everything.
# You can look in portal.azure.com for the roles that are available

When the above runs you should see output that the object id was added to the role.

Your Web-App should now work!

Some bonus reading:

For service principal / app login: http://azure.microsoft.com/en-us/documentation/articles/resource-group-authenticate-service-principal/

For on-behalf-of login: https://msdn.microsoft.com/en-us/library/azure/dn790557.aspx

Creating New Azure SQL Server From Daemon

2 Answers

EDIT: Here is how to get it to work for a Web-App:

Initial Results

Program.cs

packages.config