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 Foglight Cloud or version 8.0.0.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. ActiveDirectoryDefault authentication mode from Agent Properties is mapped to "Microsoft Entra Default" if you add the agent using the Wizard. 

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.