Connecting with Managed Authentication

Database Monitoring is not supported for this site.

This guide assumes that you have configured Database Monitoring.

Datadog Database Monitoring (DBM) allows you to view explain plans and query samples running on your database hosts. This guide shows you how to use cloud managed authentication features, such as IAM, to connect the agent to your database. This provides a more secure way to authenticate and saves you from having to manage database credentials across your agent hosts.

Before you begin

Supported databases
Postgres, SQL Server

Supported authentication types and Agent versions :

Authentication TypeAgent VersionPostgresSQL Server
IAM
7.46
Azure Managed Identity
7.48

Configure IAM authentication

AWS supports IAM authentication to RDS and Aurora databases. In order to configure the Agent to connect using IAM, do the following:

  1. Turn on IAM authentication on your RDS or Aurora instance.
  2. Create an IAM role, and then attach the following policy, replacing <YOUR_IAM_ROLE> with the IAM role information:
{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Effect": "Allow",
           "Action": [
               "rds-db:connect"
           ],
           "Resource": [
               "arn:aws:rds-db:REGION:ACCOUNT:dbuser:RESOURCE_ID/<YOUR_IAM_ROLE>"
           ]
       }
   ]
}
  1. Log in to your database instance as the root user, and grant the rds_iam role to the new user:
GRANT rds_iam TO <YOUR_IAM_ROLE>;

  1. Complete the Agent setup steps for your RDS or Aurora instance.

  2. Attach the role with each EC2 instance that is running the agent. Note, this can be done at EC2 launch time.

  3. Update your Postgres instance config with an aws block specifying the region of the RDS instance:

instances:
- dbm: true
 host: example-endpoint.us-east-2.rds.amazonaws.com
 port: 5432
 username: datadog
 aws:
   instance_endpoint: example-endpoint.us-east-2.rds.amazonaws.com
   region: us-east-2

Configure Azure managed identity authentication

Azure allows users to configure managed identity authentication for any resource that can access Azure AD. The Datadog Agent supports both user and system assigned managed identity authentication to your cloud databases.

Connect to PostgreSQL

In order to configure authentication to your PostgreSQL Flexible or Single Server instance, do the following:

  1. Create your managed identity in the Azure portal, and assign it to your Azure Virtual Machine where the agent is deployed.
  2. Configure an Azure AD admin user on your PostgreSQL instance.
  3. Connect to your PostgreSQL instance as the Azure AD admin user, and run the following command:
select * from pgaadauth_create_principal('<IDENTITY_NAME>', false, false);
  1. Proceed with the normal Agent setup steps for Azure. For example:

Create the following schema in every database:

CREATE SCHEMA datadog;
GRANT USAGE ON SCHEMA datadog TO "<IDENTITY_NAME>";
GRANT USAGE ON SCHEMA public TO "<IDENTITY_NAME>";
GRANT pg_monitor TO datadog;

Create the function in every database to enable the Agent to collect explain plans.

CREATE OR REPLACE FUNCTION datadog.explain_statement(
  l_query TEXT,
  OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;


BEGIN
  OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
  FETCH curs INTO plan;
  CLOSE curs;
  RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
  1. Configure your instance config with the managed_identity YAML block, where the CLIENT_ID is the Client ID of the Managed Identity:
instances:
 - host: example-flex-server.postgres.database.azure.com
   dbm: true
   username: "<IDENTITY_NAME>"
   ssl: "require"
   managed_identity:
     client_id: "<CLIENT_ID>"
     # Optionally set the scope from where to request the identity token
     identity_scope: "https://ossrdbms-aad.database.windows.net/.default"
   azure:
     deployment_type: flexible_server
     fully_qualified_domain_name: example-flex-server.postgres.database.azure.com

Connect to SQL Server

In order to configure authentication to your Azure SQL DB or Azure Managed Instance, do the following:

  1. Create your managed identity in the Azure portal, and assign it to your Azure Virtual Machine where the agent is deployed.
  2. Configure an Azure AD admin user on your SQL Server instance.
  3. Connect to your PostgreSQL instance as the Azure AD admin user, and run the following command in the master database:
CREATE LOGIN <MANAGED_IDENTITY_NAME> FROM EXTERNAL PROVIDER;
  1. Proceed with the normal Agent setup steps, for Azure. For example, for Azure Managed Instance:
CREATE USER <MANAGED_IDENTITY_NAME> FOR LOGIN <MANAGED_IDENTITY_NAME>;
GRANT CONNECT ANY DATABASE to <MANAGED_IDENTITY_NAME>;
GRANT VIEW SERVER STATE to <MANAGED_IDENTITY_NAME>;
GRANT VIEW ANY DEFINITION to <MANAGED_IDENTITY_NAME>;
GO

If you are using Azure SQL DB, run the following from the master database:

CREATE LOGIN <MANAGED_IDENTITY_NAME> FROM EXTERNAL PROVIDER;
CREATE USER datadog FOR LOGIN <MANAGED_IDENTITY_NAME>;
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER <MANAGED_IDENTITY_NAME>;
ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER <MANAGED_IDENTITY_NAME>;

And then create the user in every database:

CREATE USER <DBM_DATADOG_TEST_IDENTITY> FOR LOGIN <DBM_DATADOG_TEST_IDENTITY>;
  1. Update your instance config with the managed_identity config block:

Note: ODBC Driver 17 for SQL Server or greater is required to use this feature.

instances:
 - host: "example.cfcc2366ab90.database.windows.net,1433"
   connector: "odbc"
   driver: "{ODBC Driver 18 for SQL Server}"
   dbm: true
   connection_string: "TrustServerCertificate=no;Encrypt=yes;"
   managed_identity:
     client_id: "<CLIENT_ID>"
   azure:
     deployment_type: managed_instance
     fully_qualified_domain_name: example.cfcc2366ab90.database.windows.net