Troubleshooting DBM Setup for SQL Server

Docs > Database Monitoring > Setting up SQL Server > Troubleshooting DBM Setup for SQL Server

This page details common issues with setting up and using Database Monitoring with SQL Server, and how to resolve them. Datadog recommends staying on the latest stable Agent version and adhering to the latest setup documentation, as it can change with Agent version releases.

Diagnosing common connection issues

There are two ways that the agent can connect to a SQL Server instance:

Windows Authentication (only available on Windows hosts)
SQL Server Authentication

Windows authentication is the default authentication mode, and is more secure than SQL Server Auth. By using Windows Authentication, Windows groups can be created at the domain level, and a login can be created on SQL Server for the entire group. In order to use windows authentication you should:

Use the service account created at time of agent install, and make sure this account has the proper access to SQL Server.
Set connection_string: "Trusted_Connection=yes" and omit the username and password fields. The Trusted_Connection=yes connection attribute instructs the OLE DB Driver for SQL Server to use Windows Authentication for login validation.

SQL Server authentication is not based on Windows user accounts, and are instead created in the instance and stored in SQL Server itself. SQL auth requires setting the username and password in the SQL Server instance config to connect.

If you are getting a login error connecting, it is important to verify you can log into the instance as the datadog agent user first. An easy way to do this is via a cmd line utility such as sqlcmd.

For example:

# this example uses SQL Authentication
sqlcmd -S <INSTANCE_ENDPOINT> -U datadog -P <DATADOG_PASSWORD> -d master

# this example uses Windows Authentication
# Run this command in powershell via selecting the `run as user...` option to run as the ddagentuser
sqlcmd -S <INSTANCE_ENDPOINT> -d master -E

If the datadog user is unable to log into the SQL Server instance, please ensure the user has been created and given the proper permissions according the setup documentation.

Microsoft also provides a helpful doc on troubleshooting these types of errors, which can be followed here.

SQL Server TCP connection error

TCP connection issues are common when there is a setup misconfiguration with the Agent. The error messages provided by the driver are not always clear.

For example, the following error is because the TCP connection failed:

TCP-connection(ERROR: getaddrinfo failed). Exception: unable to connect: could not open database requested by login

Some common errors are:

“login failed for user”: this means the Agent succeeded in establishing a connection to the host, but the login was rejected for some reason.

To troubleshoot:

Check the agent’s login credentials
Try to login with those credentials manually using sqlcmd. For example: sqlcmd -S localhost -U datadog -P ${SQL_PASSWORD} -d master

“could not open database requested for login”: this error appears either due to network issues or due to an unknown database.

To troubleshoot:

Check the TCP connection from the agent to the host by running telnet {host} {port} to make sure there is network connectivity from the Agent to the database.
Try to login manually using sqlcmd and see if there’s an issue with the configured database. For example: sqlcmd -S localhost -U datadog -P ${SQL_PASSWORD} -d master

Due to “Invalid connection string attribute”

The following ADO Providers are supported on Windows: SQLOLEDB, MSOLEDBSQL, MSOLEDBSQL19, SQLNCLI11.

The SQLOLEDB and SQLNCLI11 providers could show the error message Invalid connection string attribute due to several issues. For example:

datadog_checks.sqlserver.connection.SQLConnectionError:
Unable to connect to SQL Server for instance foo.com,1433 - master:
OperationalError(com_error(-2147352567, 'Exception occurred.',
(0, 'Microsoft OLE DB Provider for SQL Server',
'Invalid connection string attribute', None, 0, -2147467259), None),
'Error opening connection to "ConnectRetryCount=2;Provider=SQLOLEDB;Data Source=foo.com,1433;Initial Catalog=master;User ID=datadog;Password=******;"')

This same error is shown regardless of the reason for the failure (for example, due to an unknown hostname, the TCP connection not established, invalid login credentials, or an unknown database).

Look in the error message for the HResult error codes. These are examples of known codes:

-2147217843 “login failed for user”: this means the agent succeeded in establishing a connection to the host, but the login was rejected for some reason.

-2147467259 “could not open database requested for login”: this error appears either due to network issues or due to an unknown database.

If neither step helps with the issue, or the error code you see is not listed, Datadog recommends using the MSOLEDBSQL driver or the Microsoft ODBC Driver for SQL Server. The drivers provide more detailed error messages, which can help with troubleshooting why the connection is failing.

SQL Server ‘Unable to connect: Adaptive Server is unavailable or does not exist’

This error can sometimes be the result of not properly setting the host field. For the integration, set the host field with the following syntax: host:server,port.

For example, if you’ve set host this way:

host: sqlserver-foo.cfxxae8cilce.us-east-1.rds.amazonaws.com

You must add the port, and instead set it as the following:

host: sqlserver-foo.cfxxae8cilce.us-east-1.rds.amazonaws.com,1433

SSL Provider: The certificate chain was issued by an authority that is not trusted

Microsoft OLE DB Driver 2019

This error is common after upgrading to the MSOLEDBSQL 2019 driver due to breaking changes that were introduced. In the latest version of the driver, all connections to the SQL instance are encrypted by default.

If you are using the latest version of the Microsoft OLE DB Driver for SQL Server, and trying to connect to a SQL Server instance which requires encrypted connections, you can use one of the following workarounds:

If you are using a self-signed certificate and the Force Encryption setting on the server (rds.force_ssl=1 on AWS) to ensure clients connect with encryption:
- Change to a certificate that is trusted as part of the client’s trust chain.
- Add the self-signed certificate as a trusted certificate on the client.
- Add Trust Server Certificate=True; to the connection string.

This is described in more detail in the Microsoft documentation

If your SQL Server instance does not require encryption to connect (rds.force_ssl=0 on AWS), then update the connection string to include Use Encryption for Data=False;. For example:

# example uses windows authentication
instances:
  - host: <INSTANCE_ENDPOINT>,<PORT>
    connection_string: "Trust Server Certificate=True;Use Encryption for Data=False;"
    connector: adodbapi
    adoprovider: MSOLEDBSQL19

Install the 2018 version of the MSOLEDBSQL driver, which does not use encryption by default. After installing the driver, update the adoprovider to MSOLEDBSQL. For example:

# example uses windows authentication
instances:
  - host: <INSTANCE_ENDPOINT>,<PORT>
    connection_string: "Trusted_Connection=yes;"
    connector: adodbapi
    adoprovider: MSOLEDBSQL

Other Microsoft OLE DB and ODBC driver versions

If you are using an OLE DB driver other than MSOLEDBSQL 2019 or ODBC drivers, this error can be resolved by setting TrustServerCertificate=yes in the connection string. For example, for the ODBC driver:

# this example uses SQL Server authentication
instances:
  - host: <INSTANCE_ENDPOINT>,<PORT>
    username: datadog
    password: <DD_AGENT_PASSWORD>
    connection_string: "TrustServerCertificate=yes;"
    connector: odbc
    driver: '{ODBC Driver 18 for SQL Server}'

SQL Server unable to connect ‘SSL Security error (18)’

This is a known issue for older versions of the SQL Server ODBC driver. You can check which version of the driver is being used by the agent by looking at the connection string in the error message.

For example, if you see Provider=SQL Server in the connection string of the error message, upgrading to a newer version of the ODBC driver will fix the error.

This issue is described in more detail in this Microsoft blog post

Empty connection string

Datadog’s SQL Server check relies on the adodbapi Python library, which has some limitations in the characters that it is able to use in making a connection string to a SQL Server. If your Agent experiences trouble connecting to your SQL Server, and if you find errors similar to the following in your Agent’s collector.logs, your sqlserver.yaml may include some characters that cause issues with adodbapi.

OperationalError: (KeyError('Python string format error in connection string->',), 'Error opening connection to ""')

At the moment, the only character known to cause this specific connectivity issue is the % character. If you need to use the % character in your sqlserver.yaml file, (for example, if your Datadog SQL Server user password includes a %), you must escape that character by including a double %% in place of each single %.

Diagnosing common SQL Server driver issues

Data source name not found, and no default driver specified

This is a common error seen on Linux when using the default setting for the ODBC driver. This can happen due the DSN, which is set for your driver in the /etc/odbcinst.ini file, not matching the name of the driver that is set in your agent config.

For example, if you wanted to use the default ODBC driver for the Agent ({ODBC Driver 18 for SQL Server}), your instance config should contain the following:

  connector: odbc

When the Agent starts and tries to establish a connection to your SQL Server instance, it looks for the /etc/odbcinst.ini file to find the path to the driver binaries.

For example, this /etc/odbcinst.ini file sets the driver:

```text
$ cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.7.0
UsageCount=1
```

The DSN in the above example is [ODBC Driver 18 for SQL Server], which matches the default driver name the Agent is using. If the DSN for your driver does not match the name of driver the Agent is using, you will get the Data source not found error.

It is possible to set the dsn in your instance config to match what is set in your /etc/odbcinst.ini file. For example:

```text
$ cat /etc/odbcinst.ini
[Custom]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.7.0
UsageCount=1
```

In your instance config, you would then set the dsn field:

  connector: odbc
  dsn: "Custom"

Provider or driver not found

This error message can vary in wording across the different drivers, but typically it looks like the following for ODBC:

Can't open lib .* file not found
Data source name not found.* and no default driver specified

And for MSOLEDBSQL providers the error message looks like:

Provider cannot be found. It may not be properly installed.

This means that the driver or provider is not properly installed on the host where the Agent is running. You should ensure that you have followed all the installation directions for the driver you have chosen to use.

It’s possible that the Agent is not finding the driver. This is more common with ODBC drivers on Linux. See the connecting to SQL Server on a Linux host section for more instructions on how to install the ODBC driver on Linux.

For help choosing a driver, see the picking a SQL Server driver section on how to properly configure your driver with the agent.

Connecting to SQL Server on a Linux host

To connect SQL Server (either hosted on Linux or Windows) to a Linux host:

Install the Microsoft ODBC Driver for your Linux distribution. If you are unsure of the driver name to use, you can find it enclosed in brackets at the top of /etc/odbcinst.ini.

$ cat /etc/odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.7.0
UsageCount=1

Copy the odbc.ini and odbcinst.ini files into the /opt/datadog-agent/embedded/etc folder.
If needed, install the pyodbc module. This can be done by running pip install pyodbc within your Agent’s python environment. For example:
```
$ sudo /opt/datadog-agent/embedded/bin/pip install pyodbc
```

Configure your SQL Server conf.yaml to use the odbc connector and specify the proper driver as indicated in the odbcinst.ini file.

init_config:

instances:
  - host: <HOST>,<PORT>
    # enable the odbc connector
    connector: odbc
    # enable the ODBC driver
    driver: '{ODBC Driver 13 for SQL Server}'
    username: <USERNAME>
    password: <PASSWORD>

Picking a SQL Server driver

In order for the agent to connect to the SQL Server instance, you must install either the Microsoft ODBC driver or the OLE DB driver.

The driver you choose, determines what you set for the connector field in your instance config.

For example, for the Microsoft ODBC driver:

connector: odbc
driver: '{ODBC Driver 18 for SQL Server}'

For the OLE DB driver:

connector: adodbapi
adoprovider: MSOLEDBSQL

These values will be used to map to the Provider part of the connection string.

So for example, if you set adoprovider: MSOLEDBSQL, then the connection string would include Provider=MSOLEDBSQL. This should match the name of the driver version you have installed.

In the latest version of the Microsoft OLE DB driver, the driver name was changed from MSOLEDBSQL to MSOLEDBSQL19, which means this should appear in your instance config like so:

connector: adodbapi
adoprovider: MSOLEDBSQL19

It is recommended to stay up to date with the latest available version of the driver you select.

Other common issues

SQL Server user tag is missing on the Query Metrics and Plan Samples

The user tag is no longer supported for Query Metrics and Plan Samples due to technical limitations in SQL Server which prevent the collection of the correct user running queries.

The user tag is available for Query Activity events and Database Load metrics.

Why are there so many “CREATE PROCEDURE” queries?

In versions of the agent older than 7.40.0, there exists a bug where PROCEDURE statistics are over counted. This leads to seeing many executions of CREATE PROCEDURE... in the database-monitoring Query Metrics UI. In order to fix this issue, please upgrade to the latest version of the Datadog agent.

SQL Server Agent Jobs are not being collected with error “The SELECT permission was denied on the object ‘sysjobs’”

The SQL Server Agent Jobs check requires the SELECT permission on the msdb database. If you are seeing the error The SELECT permission was denied on the object 'sysjobs', you should grant the SELECT permission to the user that the Agent is using to connect to the SQL Server instance.

USE msdb;
CREATE USER datadog FOR LOGIN datadog;
GRANT SELECT to datadog;

Known limitations

SQL Server 2012

The following metrics are not available for SQL Server 2012:

sqlserver.files.read_io_stall_queued
sqlserver.files.write_io_stall_queued
sqlserver.ao.quorum_type
sqlserver.ao.quorum_state
sqlserver.ao.member.type
sqlserver.ao.member.state
sqlserver.ao.member.number_of_quorum_votes
sqlserver.ao.log_send_queue_size
sqlserver.ao.log_send_rate
sqlserver.ao.redo_queue_size
sqlserver.ao.redo_rate
sqlserver.ao.low_water_mark_for_ghosts
sqlserver.ao.filestream_send_rate
sqlserver.ao.replica_status
sqlserver.ao.secondary_lag_seconds
sqlserver.fci.status
sqlserver.fci.is_current_owner
sqlserver.latches.latch_wait_time

SQL Server 2014

The following metrics are not available for SQL Server 2014:

sqlserver.ao.secondary_lag_seconds
sqlserver.latches.latch_wait_time