Provision Grafana dashboards in your pipelines

by Jan de Vries in  , , ,

For an evaluation tool that measures regressions and improvements in agent responses, I need to visualize the output. At first, I figured it would be nice to create a web application with lots of charts and grids. Then it occurred to me that we also have Azure Managed Grafana nowadays. This software is built for data visualization and supports a ton of data sources, one of them being SQL Server.
As I had never worked with this before, and because we need this visualization to analyze our measurements, I had a good excuse to start working with it.

Deploy the resource

The Managed Grafana offering is a regular Azure resource and is deployed as such. It is important to assign an identity to this resource, either a system-assigned identity or a user-assigned managed identity (UAMI). For demo purposes, I’m using a system-assigned identity, but for production, a UAMI is the way to go in my opinion.

resource grafana 'Microsoft.Dashboard/grafana@2024-10-01' = {
  name: name
  location: location
  tags: allTags
  identity: {
      type: 'SystemAssigned'
    }
  sku: {
    name: skuName
  }
  properties: {
    apiKey: enableApiKey ? 'Enabled' : 'Disabled'
    autoGeneratedDomainNameLabelScope: 'TenantReuse'
    deterministicOutboundIP: deterministicOutboundIp ? 'Enabled' : 'Disabled'
    publicNetworkAccess: publicNetworkAccess ? 'Enabled' : 'Disabled'
    zoneRedundancy: zoneRedundancy ? 'Enabled' : 'Disabled'
  }
}

Assigning permissions to Grafana’s data plane

I’m running the entire deployment via my deployment pipelines, including the dashboards in the Grafana resource. Because of this, I also need to make sure the deployment principal has enough permissions to create resources on the Grafana data plane. Choose the Grafana Editor (a79a5197-3a5c-4973-a920-486035ffd60f) or Grafana Admin (22926164-76b3-42b3-bc55-97df8dab3e41) role for this purpose. From a least-privilege standpoint, the Grafana Editor role is preferred.

module evaluationRbacGrafanaAdminForDeploymentPrincipal 'modules/rbac-assignment.bicep' =  {
  name: '${environment}EvaluationRbacGrafanaAdminForDeploymentPrincipal'
  scope: resourceGroup(evaluationResourceGroupName)
  params: {
    principalId: deploymentPrincipalObjectId
    roleDefinitionId: subscriptionResourceId(
      'Microsoft.Authorization/roleDefinitions',
      '22926164-76b3-42b3-bc55-97df8dab3e41'
    ) // Grafana Admin
    principalType: 'ServicePrincipal'
    assignmentDescription: 'Allow deployment principal to provision Managed Grafana assets'
    scopeId: grafana.outputs.resourceId
    targetScopeType: 'managedGrafana'
    targetResourceName: grafana.outputs.name
  }
}

// rbac-assignment.bicep
resource roleAssignmentAtManagedGrafana 'Microsoft.Authorization/roleAssignments@2022-04-01' = if (targetScopeType == 'managedGrafana') {
  name: roleAssignmentName
  scope: managedGrafana
  properties: {
    principalId: principalId
    roleDefinitionId: effectiveRoleDefinitionId
    principalType: principalType
    description: !empty(assignmentDescription) ? assignmentDescription : null
  }
}

With this role assigned, you should be good to deploy the dashboards later on.

Set up the datasource

A dashboard is nothing without any data to show. For this, you’ll need one or more data sources. In my solution, I only need to connect to an Azure SQL database. The default SQL Server data source can be used for this purpose.

Assigning permissions to the SQL database

Before I can read and write data in the deployed SQL database, my identities need access to the database. I’m using groups and service principals for this.

The sample object I’m sending to the script looks like this:

principals = [
  {
    principalName: "my-app-identity",
    objectId:      "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
    principalType: "ServicePrincipal",
    roles:         ["db_datareader", "db_datawriter"]
  }
]

This object gets deconstructed into variables in my deployment script.

Below is a sample query that creates a user in the database.

-- Small excerpt of creating a user and assigning roles

-- Example: Service Principal "my-app-identity" with Object ID a1b2c3d4-e5f6-7890-abcd-ef1234567890
-- TYPE = E  → External user (service principal / managed identity)
-- TYPE = X  → External group (Entra ID group)

DECLARE @principalObjectId UNIQUEIDENTIFIER = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';
DECLARE @principalSid VARBINARY(16) = CONVERT(VARBINARY(16), @principalObjectId);

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE sid = @principalSid
)
BEGIN
    DECLARE @principalSidHex NVARCHAR(34) = CONVERT(VARCHAR(34), @principalSid, 1);

    -- Rendered dynamic SQL (TYPE=E for service principal, TYPE=X for group):
    -- CREATE USER [my-app-identity] WITH SID = 0xD4C3B2A1F6E5907890ABCDEF1234567890, TYPE = E;
    DECLARE @createUserSql NVARCHAR(MAX) =
        N'CREATE USER [my-app-identity] WITH SID = ' + @principalSidHex + N', TYPE = E;';

    EXEC(@createUserSql);
END

-- ...
-- Omitted for brevity...
-- ...

-- Sample for assigning roles to an identity
$escapedRoleName = $roleName.Replace(']', ']]')
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_role_members drm
    INNER JOIN sys.database_principals role_principal ON drm.role_principal_id = role_principal.principal_id
    INNER JOIN sys.database_principals member_principal ON drm.member_principal_id = member_principal.principal_id
    WHERE role_principal.name = '$escapedRoleNameLiteral'
      AND member_principal.name = '$escapedPrincipalNameLiteral'
)
BEGIN
  ALTER ROLE [$escapedRoleName] ADD MEMBER [$escapedPrincipalName];
END

I’m using a Deployment Script to assign the identities in my environment. However, this might not be possible in locked-down environments, as it will spin up a Container Instance without any networking capabilities. In that case, you might want to use a different approach to update the database and execute the scripts.
With the roles in place for all the identities that need access (Grafana identity, pipeline identity, etc.), you should be able to set up a connector and write data to the SQL database too.

Create the datasource

A data source is defined as a JSON object with a name, type, uid, and a couple of connector-specific properties. You can create one yourself in Grafana and inspect the definition. I’ve created a small script that I can use in my pipeline to create a connector based on the resources I have deployed.

DATASOURCE_NAME="sample-evaluation-sql"
DATASOURCE_UID="smpl-eval-sql"
SQL_SERVER_URL="my-evaluation-sql.database.windows.net:1433"
SQL_DATABASE="EvaluationDB"

jq -n \
  --arg name    "$DATASOURCE_NAME" \
  --arg uid     "$DATASOURCE_UID" \
  --arg url     "$SQL_SERVER_URL" \
  --arg database "$SQL_DATABASE" \
  '{
    name: $name,
    uid:  $uid,
    type: "mssql",
    access: "proxy",
    url:  $url,
    database: $database,
    jsonData: {
      database: $database,
      authenticationType: "Azure AD Authentication",
      azureCredentials: { authType: "msi" },
      encrypt: "true"
    }
  }' > /tmp/datasource.json

cat /tmp/datasource.json

The above script creates a datasource.json file with all the details of the created SQL Server and database and specifies that the managed identity of the Grafana resource should be used. This is why it’s important to grant the Grafana resource identity read access to the database.

The next step is to deploy the data source. The easiest way to do this is via the Azure CLI Grafana commands, again using many of the same variables as in the other scripts.

EVAL_RG="rg-evaluation"
GRAFANA_NAME="grafana-sample-eval"
DATASOURCE_NAME="sample-evaluation-sql"
DATASOURCE_UID="smpl-eval-sql"
DEFINITION_FILE="/tmp/datasource.json"

# 1. Try to find by UID first (most stable identifier)
EXISTING_UID="$(az grafana data-source list \
  --resource-group "$EVAL_RG" \
  --name "$GRAFANA_NAME" \
  --query "[?uid=='$DATASOURCE_UID'].uid | [0]" -o tsv)"

# 2. Fall back to name lookup (handles UID changes)
if [ -z "$EXISTING_UID" ]; then
  EXISTING_UID="$(az grafana data-source list \
    --resource-group "$EVAL_RG" \
    --name "$GRAFANA_NAME" \
    --query "[?name=='$DATASOURCE_NAME'].uid | [0]" -o tsv)"
fi

if [ -z "$EXISTING_UID" ]; then
  echo "Creating datasource..."
  DATASOURCE_UID="$(az grafana data-source create \
    --resource-group "$EVAL_RG" \
    --name "$GRAFANA_NAME" \
    --definition "@$DEFINITION_FILE" \
    --query uid -o tsv)"
else
  echo "Updating existing datasource ($EXISTING_UID)..."
  DATASOURCE_UID="$(az grafana data-source update \
    --resource-group "$EVAL_RG" \
    --name "$GRAFANA_NAME" \
    --data-source "$EXISTING_UID" \
    --definition "@$DEFINITION_FILE" \
    --query uid -o tsv)"
fi

echo "Active datasource UID: $DATASOURCE_UID"

The above script will add or update the SQL Server data connector based on the details described in datasource.json.
This step should be relatively fast, so you can proceed to creating and publishing the dashboards.

Building dashboards

As you might know from building dashboards in other environments, such as Power BI or Azure Dashboards, they are often saved as JSON or XML documents. These contain properties like title, description, location on the dashboard, data sources, identifiers, and many more.
Building the dashboards manually in the Grafana portal is fine to start with and, if you’re unfamiliar with the dashboards, maybe even a good idea.
Do remember to export the dashboards when you’re done and make sure they are added to the repository. The generated JSON output of a Grafana dashboard is quite readable and easy to use.
An alternative approach is to use a coding agent to create dashboards. It turns out they are quite capable of creating queries and the correct JSON structure for a Grafana dashboard. You do need to make sure the coding agent is aware of the database schema to make the correct queries. Quite a time-saver, for me at least.
I did ask my coding agent to use a data source placeholder and put __DATASOURCE_UID__ in the uid property. This means I can replace it with the actual uid of the connector in the pipeline.

Deploy the dashboards

EVAL_RG="rg-evaluation"
GRAFANA_NAME="grafana-sample-eval"
FOLDER_UID="abc123"
DATASOURCE_UID="smpl-eval-sql"
DASHBOARD_FILE="./dashboards/evaluation-overview.json"

WORK_DIR="$(mktemp -d)"
trap 'rm -rf "$WORK_DIR"' EXIT

RENDERED="$WORK_DIR/$(basename "$DASHBOARD_FILE")"

# jq reads the file as raw text (-R -s), does a global find-and-replace,
# then parses back to JSON to validate it.
jq --arg datasourceUid "$DATASOURCE_UID" \
   -R -s 'gsub("__DATASOURCE_UID__"; $datasourceUid) | fromjson' \
   "$DASHBOARD_FILE" > "$RENDERED"

az grafana dashboard import \
  --resource-group "$EVAL_RG" \
  --name "$GRAFANA_NAME" \
  --folder "$FOLDER_UID" \
  --overwrite true \
  --definition "$RENDERED" >/dev/null

echo "Dashboard imported successfully."

The above script takes the dashboard file and adds the SQL Server data connector to the different types of queries where it’s required, making the deployment dynamic and easy to roll out across multiple environments. You can apply this practice to any dashboard you have and to any data source you need.

What’s important

The key takeaway for me is that Grafana is a great tool for creating visualizations of any kind. I can’t think of a reason not to use it, although I’m sure I’ll think of a couple in the future.

When setting this up, you need to:

  • Make sure the Grafana resource has an identity
  • This identity has read access to the data sources
  • Make sure the resource is able to connect, from a networking perspective, to the data sources
  • Add the dashboards to source control
  • Apply placeholders for the data sources in the dashboards that get dynamically replaced

Doing all of the above will give you a nicely scalable solution for visualizing data.