Add support for Azure CLI authentication

[maskati]

Related to #449 which concerned sqlcmd AAD authentication in an interactive shell terminal using device code authentication. This issue deals with AAD authentication in a non-interactive script.

My scenario is that I am running sqlcmd in a GitHub Action workflow on a self-hosted runner with managed identity, but with the actions workflow authenticating against Azure using GitHub OpenID Connect with a different AAD identity (not the same managed identity). This relies on the GitHub federated authentication context being sourced from Azure CLI (through the azure/login action), even though the runner itself has a managed identity for other purposes (runner provisioning Key Vault access etc.).

The current sqlcmd authentication implementation does not support using Azure CLI in scenarios when there is also a managed identity in the execution context, as it seems there is no way to "force" DefaultAzureCredential to use Azure CLI authentication if a managed identity exists.

Consider sqlcmd support for --authentication-method of AzureCli.

[shueybubbles]

We will add more auth types in the driver and the app, but MSAL/Entra is evolving faster than we can keep up in some ways. I strongly recommend ci/cd pipeline customers use service principal auth for sqlcmd so the credential is passed directly to the app and not at risk of being trampled on by environmental factors. I believe secure variable references in most pipeline YML files are not set as environment variables on the agent, so using a service principal password as a secure secret in the script that runs sqlcmd is less likely to leak your credential to other processes on the agent. I know ADO masks out such values in the logs too.

[maskati]

@shueybubbles thanks for the reply! And i understand the challenge of keeping up with evolving identity platforms.

I am however of a different opinion regarding the relative security of secure variables vs. federated authentication. The former still requires transfer and maintenance of a secret that can leak from the pipeline itself. Leaking of federated credentials is not technically possible since it is based not on secrets but trust relationships (it can be compromised but that would be a bigger issue throughout the Azure AD or GitHub platform). The only thing that can potentially leak is the limited lifetime token provided during each workflow run.

I do hope that sqlcmd at some point includes these modern authentication methods, which are quickly becoming mainstream in terms of implementing secure external workloads authenticating against Azure resources.

[shueybubbles]

I appreciate that model for the reasons you describe. Most of the time using managed identity is great. The tradeoff is that you are basically giving every tool that runs in the pipeline equal access to resources, even ones the tools don't need. Some buggy bash or powershell script that was supposed to touch only a certain resource could now touch other resources in a bad way.

[maskati]

@shueybubbles That is definitely the case when using managed identities, for example if you configured a managed identity on a self-hosted runner and authorized it against Azure SQL. Then any workflow running on that runner could acquire that identity, which would be risky.

Azure AD identity federation when used with GitHub OIDC against Azure is however completely different. It is basically a way of authorizing specific GitHub workflow contexts against Azure. So you can do something like "allow a GitHub workflow targeting the production environment to access the production identity". If someone tries to access the production identity from a workflow targeting the testing environment authentication would fail. They are a great way of authorizing specific GitHub workflows against Azure without having to authorize the infrastructure running those workflows.

The unfortunate thing is that the GitHub OIDC Azure authorization is mostly built around authenticating Azure CLI, which does not work in the case of sqlcmd. Technically the protocol gives access to the access token (which can also be acquired from Azure CLI get-access-token), but this also does not help since sqlcmd does not provide a mechanism to provide an access token for authentication (similarly to SqlConnection.AccessToken).

[maskati]

My current workaround is to use PowerShell Invoke-SqlCmd with the -AccessToken parameter, which I can source from Azure CLI. I am tempted to create a third feature request to support access token authentication with sqlcmd 😅

[farshid3003]

Any news on this????

[shueybubbles]

Will direct support of AzureCLICredential handle the OIDC usage in Github? We tried using the AzureCLICredential for Azure Pipelines when they added federated service connections for other purposes and it didn't work; we had to implement our own TokenCredential that queried the OIDC endpoint directly (this was for a C# app).

Which is what the Azure team added to the Go SDK as AzurePipelinesCredential: https://pkg.go.dev/github.com/Azure/azure-sdk-for-go/sdk/azidentity#AzurePipelinesCredential

[shueybubbles]

Oh, have you tried the latest releases?

   --authentication-method
   Specifies the SQL authentication method to use to connect
   to Azure SQL Database. One of: ActiveDirectoryDefault,
   ActiveDirectoryIntegrated, ActiveDirectoryPassword,
   ActiveDirectoryInteractive,
   ActiveDirectoryManagedIdentity,
   ActiveDirectoryServicePrincipal, ActiveDirectoryAzCli,
   ActiveDirectoryDeviceCode, SqlPassword