MFA connection support in SQL Server and Azure SQL agents (4379882)

Starting with version 7.3.1.10, the Azure SQL and SQL Server cartridges support Multi-Factor Authentication (MFA) for connections to Azure SQL Databases and Azure Managed Instances using ActiveDirectoryDefault authentication. This enhancement allows secure, token-based authentication through the Azure CLI.

Prerequisites

1. Foglight Cartridge Version: Ensure you are using version 7.3.1.10 or higher of the Azure SQL or SQL Server cartridge.
2. FglAM Configuration:
   • Run FglAM as a process under an administrator user account.
   • Do not run FglAM as a service, as token refresh fails in service mode, causing MFA login to break.
3. Azure CLI:
   • Install the Azure CLI on the FglAM host machine.
   • How to install the Azure CLI: How to install the Azure CLI

Authentication Setup

1. Login to Azure CLI: Open a terminal on the FglAM machine and run:

1. This will open a browser window prompting the user to authenticate.

2. Successful Login Message: After authentication, the CLI will display:

   

3. Token Generation:

   • The az login command generates:
     • Access Token: Valid for 75 minutes
     • Refresh Token: Valid for 90 days
       • Note: Each time a refresh token is used, it is replaced with a new one.

Monitoring with MFA

Once logged in, the user can monitor Azure SQL or Managed Instances using the ActiveDirectoryDefault authentication method in Foglight.

Important: If the user needs to connect to a different database or tenant not previously accessed, they must re-authenticate by running az login again to acquire a new token.

Limitations

• Token Expiry: If FglAM is restarted or the token expires without refresh, the user must re-run az login.
• Service Mode Restriction: Running FglAM as a service prevents token refresh and breaks MFA login.
• Token Lifetime Policies: Azure AD policies may override default token lifetimes. Consult your Azure administrator for custom configurations.

Troubleshooting

• MFA Login Fails:

  • Ensure FglAM is launched manually under an administrator used account and not as a Windows service.
  • Re-run az login to refresh tokens.
  • Verify Azure CLI is installed and up to date.
  • User Azure CLI or SQL tools to confirm that the connection works independently or use a tool like Quest Toad, Azure Data Studio, or SQL Server Management Studio (SSMS) with Azure Active Directory - Universal with MFA authentication to confirm access. 
    

    az sql db show --name <database-name> --server <server-name> --resource-group <resource-group>

  • Check the Token Expity by using the following command to inspect the token details
    

    az account get-access-token

• Access Denied Errors:

  • Confirm the user has appropriate permissions on the target database.
  • Re-authenticate if switching between tenants or subscriptions.