Connect to database using a workload identity

[flexwie]

I'm currently in the process of setting up and configuring an AKS cluster with workload identities enabled. I followed this example code to get access tokens using the MSAL library for Go. Is there a similar strategy or way to use this access token with go-mssqldb? From what I've seen in the source code, there is no case for handling federated identities. Sorry if this is a redundant question, I'm just getting started with federated identities.

[shueybubbles]

@flexwie have you tried including the azuread package and specifying azuresql as the driver name with an appropriate connection string?

https://github.com/microsoft/go-mssqldb#azure-active-directory-authentication

[flexwie]

I tried all options listed there (just to make sure), but they all failed with my connection string: "server=%s;user id=%s; database=%s; access token=%s; fedauth=ActiveDirectoryMSI" Only fedauth=ActiveDirectoryManagedIdentity and fedauth=ActiveDirectoryMSI did not fail immediately but kind of did nothing when trying to ping the database.

[shueybubbles]

I may not be aware of what "federated identity" means. ActiveDirectoryManagedIdentity refers to Azure Managed Identity, which can be either system-assigned or user-assigned. In either case you don't need to provide an access token, the driver will acquire it for you when running on a VM that has the Azure managed identity service running. If the identity is user-assigned you just need to provide the name as the user id.

[shueybubbles]

One app that uses the driver for AAD auth is sqlcmd. Perhaps you can test with that app to find the right combination of inputs/auth method. https://github.com/microsoft/go-sqlcmd/releases/tag/v0.10.0

[shueybubbles]

if your token acquisition method is completely custom and not provided by any of the methods exposed by azuread you can provide your own token function by using this connector. https://github.com/microsoft/go-mssqldb/blob/main/accesstokenconnector.go

[flexwie]

I may not be aware of what "federated identity" means. ActiveDirectoryManagedIdentity refers to Azure Managed Identity, which can be either system-assigned or user-assigned. In either case you don't need to provide an access token, the driver will acquire it for you when running on a VM that has the Azure managed identity service running. If the identity is user-assigned you just need to provide the name as the user id.

I haven't understood the concept 100% myself, but these identities that are used in my case are not the same as the Instance Metadata Service that is available in Azure VMs (and that is used with managed identities). A service in Kubernetes creates a token issued by the cluster, which is then mounted in the containers and needs to be exchanged with Azure AD before you can use it (see more).

I think the easiest way right now for me would be integrating my solution with the connector you pointed out. I'll report back if that solves it for me, thank you for your help so far!

[flexwie]

func main() {
    clientID := os.Getenv("AZURE_CLIENT_ID")
    tenantID := os.Getenv("AZURE_TENANT_ID")
    tokenFilePath := os.Getenv("AZURE_FEDERATED_TOKEN_FILE")

    var connectionString = fmt.Sprintf("server=%s;user id=%s; database=%s", "url", "client id", "db")

    connection, err := mssql.NewConnectorWithAccessTokenProvider(connectionString, func(ctx context.Context) (string, error) {
        credential, err := azidentity.NewClientAssertionCredential(tenantID, clientID, func(ctx context.Context) (string, error) {
            content, err := os.ReadFile(tokenFilePath)
            if err != nil {
                return "", err
            }

            return string(content), nil
        }, nil)
        if err != nil {
            log.Fatal(err)
        }

        token, err := credential.GetToken(ctx, policy.TokenRequestOptions{
            Scopes: []string{"https://database.windows.net/.default"},
        })
        if err != nil {
            return "", err
        }

        return token.Token, nil
    })

    if err != nil {
        log.Fatalf("could not create access token provider: %v", err)
    }
    db := sql.OpenDB(connection)

    if err = db.PingContext(context.Background()); err != nil {
        log.Fatalf("could not ping database: %v", err)
    }
    log.Println("connected")
}

The connector works like a charm, even using the azidentity library instead if MSAL! Is that something you would consider for a pull request? It doesn't seem like this use case is covered by existing strategies in azuread.

[shueybubbles]

Given the newness of this architecture I'm inclined to wait and see what the Azure SDK for Go comes up with to support it directly. One concern I have is how this path can discover the right Azure resource identifier for Azure SQL Database. What if the app is running in a national cloud such that database.windows.net isn't the right resource? The SQL DB instance provides the resource identifier during the login TDS process in the FEDAUTHINFO token, and I don't know if this connector leverages that information.

[flexwie]

Do you have reason to believe that the behaviour of client assertions in azidentity will change in the near future? I guess you could add some options that change the cloud that is used. I'll leave it up to you if that's something that could be included in the library. If not, them I'm more than happy to just have it documented in this issue :) Anyways, thanks for your help and time!

[shueybubbles]

The MSAL folks have implemented the AKS identity in their managed SDK. I don't know if/when it will make it into the Go version. I'll poke around.

[sblackstone]

As of now (April 2023), if you set your azidentity version in go.mod to:

github.com/Azure/azure-sdk-for-go/sdk/azidentity v1.3.0-beta.5

and use the following as a connection string..

fmt.Sprintf("server=%s;port=%d;fedauth=ActiveDirectoryDefault;user id=%s;database=%s;connection timeout=%d", dbc.Hostname, dbc.Port, dbc.Username, dbc.Database, dbc.TCPConnectionTimeout)

workload identity works - I'm using this inside an AKS cluster.