Connection Issues with the SQL Server Integration

Common SQL Server Connection Troubles:

You can configure the Datadog Agent to collect metrics from SQL Server by following the instructions in the SQL Server integration tile in your account. This integration offers a number of basic SQL Server metrics, which you can expand to your own liking

But there is a common connection error that users run into while they're setting up this integration, one that can be especially frustrating to troubleshoot since there are many variables that can cause it. In full, the error looks like this:

'Unable to connect to SQL Server for instance 127.0.0.1,1433 - None. \n Traceback (most recent call last):\n File "C:\\Program Files (x86)\\Datadog\\Datadog Agent\\files\\..\\checks.d\\sqlserver.py", line 219, in get_cursor\n File "adodbapi\\adodbapi.pyc", line 116, in connect\nOperationalError: (com_error(-2147352567, \'Exception occurred.\', (0, u\'Microsoft OLE DB Provider for SQL Server\', u\'[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.\', None, 0, -2147467259), None), \'Error opening connection to "Provider=SQLOLEDB;Data Source=127.0.0.1,1433;Initial Catalog=master;User ID=datadog;Password=******;"\')\n'

As you'll gather from SQL Server does not exist or access denied, this error indicates that the agent was unable to connect to your SQL Server to complete its data collection. This could be caused by any of the following:

  1.  A typo in your sqlserver.yaml host, port, username, or password (it's all worth triple-checking)
  2. Your SQL Server's TCP/IP connection has not been enabled
  3. Your SQL Server's IPv4 address is incorrect or does not match what you've provided in your sqlserver.yaml
  4. Your SQL Server's TCP/IP port is incorrect or does not match what you've provided in your sqlserver.yaml
  5. The authentication mode of your SQL Server is not set to the appropriate option between "SQL Server and Windows Authentication mode" vs. simply "Windows Authentication mode"

If you are unsure of how to set up your server to listen on the correct TCP/IP address/port, this page from Microsoft should give you some direction (IPv4 and IPALL are the specifically relevant parts; there, you may set your port either as a "Dynamic" or as a "Static" port, but whichever you aren't using should be left blank). If the agent is installed on the same host as your SQL Server, it may be appropriate to set your sqlserver.yaml's host option to "127.0.0.1", even if the host is not a localhost from your perspective as a user. The standard port for connections to SQL Server is 1433. 

If you are unsure how to set your SQL Server's authentication mode, you may find this page from Microsoft useful.

Do note that any of the above changes that you make to your SQL Server will require that you restart your SQL Server before the changes take effect. 

Here's an example of some SQL Server IP/TCP settings that have worked just fine on one of our testing environments (Windows 2012 R2, SQL Server 2014 Express):

 

Empty Connection String?

Our 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 probably includes some character that causes issues with adodbapi. 

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

At the moment, the only character we know of that causes this specific connectivity issue is the "%" character. If you must use the "%" character in your sqlserver.yaml (e.g, if your datadog SQL Server user password includes a "%"), you will have to escape that character by including a double "%%" in place of each single "%".

 

Connecting to Sql Server on a Linux Host?

Our agent's SQL Server check doesn't currently support connection from agents hosted on operating systems other than Windows. If you try connecting from a linux-hosted agent, you will run into an error similar to the following:

instance #0 [ERROR]: 'Unable to connect to SQL Server for instance _host_,_port_ - master. \n Traceback (most recent call last):\n  File "/opt/datadog-agent/agent/checks.d/sqlserver.py", line 316, in open_db_connections\n    timeout=timeout)\n  File "/opt/datadog-agent/embedded/lib/python2.7/site-packages/adodbapi/adodbapi.py", line 116, in connect\n    raise api.OperationalError(e, message)\nOperationalError: (InterfaceError("Windows COM Error: Dispatch(\'ADODB.Connection\') failed.",), \'Error opening connection to "Provider=SQLOLEDB;Data Source=_host_,_port_;Initial Catalog=master;User ID=datadog;Password=******;"\')\n'

If you're interested in having our SQL Server integration support on linux-hosted agents, reach out to our support team to submit a feature request.

Have more questions? Submit a request

1 Comments

  • 0
    Avatar
    Ziquan Miao

    Enabling TCP connection from sqlserver settings: 

    Security -> Server Roles -> Public -> EndPoints -> TSQL Default TCP

Please sign in to leave a comment.
Powered by Zendesk