> ## Documentation Index > Fetch the complete documentation index at: https://docs-xcor.paloaltonetworks.com/llms.txt > Use this file to discover all available pages before exploring further. # Microsoft SQL Server > Microsoft SQL Server workload, memory, lock, transaction, database, file, and backup metrics. The Microsoft SQL Server integration requires CXDOT Collector 1.13.0 or greater. [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server) is a relational database management system. Use the Microsoft SQL Server integration to collect throughput, memory, lock, transaction log, and database file metrics from SQL Server instances running in your environment. The Microsoft SQL Server integration supports SQL Server 2017 or greater. ## Supported telemetry types The Microsoft SQL Server integration supports these telemetry types: | Type | Supported | | - | - | | Logs | No | | Metrics | Yes | | Traces | No | | Events | No | ## Prerequisites The Microsoft SQL Server integration has the following prerequisites: * Find the TCP port of each SQL Server instance. The Collector connects to a host and port, and doesn't resolve named instances through SQL Server Browser. A default instance listens on port `1433`. A named instance often uses a dynamic port, so [configure it to listen on a fixed port](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port): in SQL Server Configuration Manager, open the TCP/IP properties of the instance's network configuration, enable the protocol if it's disabled, confirm that **Listen All** on the **Protocol** tab is **Yes**, which makes the **IPAll** settings apply, and on the **IP Addresses** tab under **IPAll**, clear **TCP Dynamic Ports**, set **TCP Port**, and restart the instance. To find the port of an instance, connect to it over TCP as an administrator with a SQL client, such as `sqlcmd -S tcp:\ -U -C`, and run the following query. The `-C` option trusts the instance's certificate for this lookup only, because current versions of `sqlcmd` reject the certificate that SQL Server generates for itself. ```sql theme={null} SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID; ``` * Make that port reachable from the CXDOT Collector. On Windows, allow it inbound through Windows Firewall. For example, run the following command in an elevated PowerShell session on the SQL Server host: ```powershell theme={null} New-NetFirewallRule -DisplayName 'SQL Server 1433' -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow ``` * Enable SQL Server authentication on each instance. The Collector signs in with a login name and password, so an instance that allows only Windows authentication refuses it. To see which mode an instance uses, run the following query, which returns `1` when the instance allows only Windows authentication: ```sql theme={null} SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'); ``` To change the authentication mode, see [change server authentication mode](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode). The change takes effect only after you restart the instance, so plan it for a maintenance window. * Create a SQL Server login for the CXDOT Collector. On each SQL Server instance, connect as a login that can create logins and database users and grant server and database permissions, such as a member of the `sysadmin` fixed server role, and run the following statements: ```sql theme={null} USE master; IF SUSER_ID('cxdot') IS NULL CREATE LOGIN cxdot WITH PASSWORD = ''; GRANT VIEW SERVER PERFORMANCE STATE TO cxdot; GRANT VIEW ANY DEFINITION TO cxdot; USE msdb; IF DATABASE_PRINCIPAL_ID('cxdot') IS NULL CREATE USER cxdot FOR LOGIN cxdot; GRANT SELECT ON dbo.backupset TO cxdot; ``` Replace `cxdot` with the login name and `` with a password for your environment. The statements skip a login or user that already exists, so you can run them again to add a grant. SQL Server 2019 and earlier don't have the `VIEW SERVER PERFORMANCE STATE` permission. On those versions, grant `VIEW SERVER STATE` instead. The `msdb` user lets the Collector read backup history. SQL Server allows that access by default, and the user keeps it working on an instance that removes the default. Give the Collector a login of its own rather than one that another tool, such as the Datadog Agent, also signs in with. The Collector signs in at every collection, so a wrong or rotated password fails several sign-ins a minute on each instance. For a login created with its password policy enforced (`CHECK_POLICY = ON`), that locks the login, and with it every tool that shares it. Test the password with a SQL client before you configure it. If you configure the integration to report files and index usage for a database other than `master`, also create a user for the login in that database: ```sql theme={null} USE ; IF DATABASE_PRINCIPAL_ID('cxdot') IS NULL CREATE USER cxdot FOR LOGIN cxdot; ``` Replace `` with the configured database. ## Configure The Microsoft SQL Server integration is enabled by default. It collects metrics only from the SQL Server instances you identify, through annotations on their pods and Services or through a list of static targets. To configure the Microsoft SQL Server integration, follow these steps: 1. Annotate the pods or Services that run SQL Server. The CXDOT Collector collects from pods and Services that carry autodiscovery annotations for SQL Server. Each annotation carries the endpoint and credentials for its own target, so different instances can use different logins. An annotated target also takes its database and its backup and index usage intervals from its annotation, and encrypts its connection as its annotation specifies, never as the `tls` setting in your Helm values. Metrics from discovered pods arrive with that pod's Kubernetes metadata attached. For more information, see [autodiscovery](https://docs-xcor.paloaltonetworks.com/ingest/xcor/collector/discover/kubernetes.md). 2. Optional: Configure static targets instead of annotated targets, such as SQL Server instances running outside your Kubernetes cluster. Supply the password through your deployment's secret management and expose it on the cluster-tier CXDOT Collector through `clusterCollector.extraEnv`. For example, add the following to the Helm values file for your CXDOT Collector: ```yaml theme={null} clusterCollector: extraEnv: - name: SQLSERVER_PASSWORD valueFrom: secretKeyRef: name: sqlserver-credentials key: password config: integrations: sqlserver: instances: - endpoint: mssql.example.com:1433 username: cxdot password: "${env:SQLSERVER_PASSWORD}" ``` Every static target uses the same `username` and `password`. When you configure static targets, this integration instance collects from exactly those targets and stops collecting from annotated targets. The Collector reports each target's `server.address` as you spell the host in `endpoint`, including its case. Spell it as the dashboards and monitors you bring over filter on it, such as the `host` value in a Datadog annotation. Collect each SQL Server instance from exactly one Collector. One Collector can list every instance on a host, or on several hosts. Two Collectors that collect the same instance report it twice under the same `server.address`, so a query that sums by `server.address` counts the instance twice. In an Always On availability group, list each replica by its own host name rather than by the group's listener, which follows the primary replica. 3. Optional: Configure encryption for static targets. By default, the Collector encrypts each connection and verifies that a certificate authority it trusts issued the server certificate for the host name in the endpoint. SQL Server generates a certificate for itself when none is configured, and that certificate can't pass verification. SQL Server 2017 and earlier generate a new one at every start, and about half of them carry a negative serial number, which the Collector accepts. Install a certificate issued for the server's host name on each instance, or set `tls.insecure_skip_verify` to `true` to accept any certificate. If the system that runs the Collector doesn't trust the certificate authority that issued the server certificate, set `tls.ca_file` to a file that holds the authority's certificate. Mount the file into the cluster Collector from a Secret with `clusterCollector.extraVolumes` and `clusterCollector.extraVolumeMounts`. For example: ```yaml theme={null} config: integrations: sqlserver: instances: - endpoint: mssql.example.com:1433 username: cxdot password: "${env:SQLSERVER_PASSWORD}" tls: ca_file: /etc/sqlserver-ca/ca.crt ``` Every static target uses the same `tls` settings. The Collector accepts TLS 1.2 or later by default, so a server that can't negotiate TLS 1.2, such as one that hasn't received the update that adds it, fails the handshake. Setting `tls.min_version` to `"1.0"`, with `tls.insecure_skip_verify` set to `true` when the server certificate can't be verified, might let the Collector connect to such a server. The durable fix is to enable TLS 1.2 on the server. An endpoint can name a host by an alias or by an availability group listener name while the server certificate names the host itself. For such a target, set `tls.server_name_override` to the name in the certificate, in an instance of the integration of its own, so that the override applies only to the targets whose certificates carry that name. 4. Optional: Collect from annotated targets and static targets at the same time by adding a second instance of the integration. A key in the form `sqlserver/` defines another instance with its own settings. For example, the following Helm values keep annotated targets under `sqlserver` and collect from static targets under `sqlserver/external`: ```yaml theme={null} config: integrations: sqlserver: {} sqlserver/external: instances: - endpoint: mssql.example.com:1433 username: cxdot password: "${env:SQLSERVER_PASSWORD}" ``` 5. Optional: Report the files and index usage of a database other than `master` for static targets. The Collector reports server-wide metrics, and the state and backup count of every database, whatever this setting is. On a secondary replica of an Always On availability group whose databases aren't readable, keep `master`: the Collector switches to the configured database at every collection, and a database the replica doesn't allow reading fails the switch, so the files and index usage are missing and the Collector logs an error. For example: ```yaml theme={null} config: integrations: sqlserver: database: sales ``` 6. Optional: Change how often the Collector reads backup counts and index usage from static targets. Both run on the first collection and then every five minutes by default, independently of `collection_interval`, because the cost of each read grows with the backup history and the number of indexes. Between reads, the Collector reports the most recent values at every collection. A read that fails runs again at the next interval. The `timeout` setting bounds one collection of a target as a whole. On an instance with a large `msdb` backup history, the backup read can take most of it, and the metrics the Collector reads after it are missing from that collection. On such an instance, raise `timeout`, or raise `backup_collection_interval` so that the read runs less often. For example: ```yaml theme={null} config: integrations: sqlserver: backup_collection_interval: 15m index_usage_collection_interval: 10m ``` 7. Optional: Disable the integration. For example: ```yaml theme={null} config: integrations: sqlserver: enabled: false ``` ### Validate To validate the Microsoft SQL Server integration, follow these steps: 1. In Live Telemetry Analyzer, filter for `__name__=cxdot.integration.target.health` and `cxdot.integration.name=sqlserver`. Confirm that each target reports `1`, identified by its `server.address` and `server.port` attributes. A target dialed at `localhost` or another loopback address reports the Collector host's name as `server.address`. 2. In Metrics Explorer, run the following query while the SQL Server instances handle client requests: ```text theme={null} sum by ("server.address", "server.port") (rate({"sqlserver.batch.request.count"}[$__rate_interval])) ``` Confirm that the query returns a time series for each target. 3. Optional: Open the Overview dashboard that the integration provides, which shows server health, throughput, memory, file I/O, buffer cache, database state, and blocking. Confirm that its panels show data for your targets. ### Troubleshooting * A target reports `0` for `cxdot.integration.target.health`: The Collector can't connect and sign in to the instance. Confirm that the endpoint names the instance's TCP port, that the instance allows SQL Server authentication, and that the login name and password are correct. A named instance that listens on a dynamic port isn't reachable at port `1433`. * A static target reports `0` for `cxdot.integration.target.health` although its endpoint and login are correct: The Collector rejects the server certificate. By default, it verifies the certificate of every static target, and the certificate that SQL Server generates for itself fails verification. Install a certificate issued for the host name in the endpoint, set `tls.ca_file` to the certificate authority that issued it, or set `tls.insecure_skip_verify` to `true`. * A target reports `1` for `cxdot.integration.target.health` but few other metrics arrive: The health metric confirms only that the Collector can sign in. Grant the login `VIEW SERVER PERFORMANCE STATE`, or `VIEW SERVER STATE` on SQL Server 2019 and earlier. * File I/O metrics, such as `sqlserver.database.io` and `sqlserver.database.latency`, are missing while other metrics arrive: Grant the login `VIEW ANY DEFINITION`. Without that permission, SQL Server hides the files from the login without an error, and the Collector reports no time series for them. * `sqlserver.index.operation.count` is missing: The Collector reports index usage only for the user tables of the configured database, which is `master` unless you configure another, and `master` usually has none. Configure the database that holds your tables, and confirm that the login has a user in that database and `VIEW ANY DEFINITION`, without which SQL Server hides the indexes. * `sqlserver.database.backup.count` is missing: Confirm that the login has a user in `msdb` with `SELECT` permission on `dbo.backupset`. The Collector reads backup counts on its first collection and then every five minutes by default, so a new backup can take that long to appear in the count. * `sqlserver.database.io_queue.time` is always `0`: SQL Server counts that time only while Resource Governor limits the I/O of a resource pool, so an instance with no such limit reports `0`, as does an edition without Resource Governor, which Express and Web editions lack, and Standard edition before SQL Server 2025. * `sqlserver.memory.grants.pending.count` is always `0`: The counter counts queries waiting for workspace memory, so it stays `0` while the instance has enough, whatever the edition. * `sqlserver.replica.transaction_delay.time` is `0` on a replica: The primary replica of an availability group reports the delay in committing to its secondary replicas. A secondary replica, and an instance outside an availability group, report `0`. ## Configuration reference Configure one Microsoft SQL Server integration instance with the following settings. In Helm values, place these settings under `config.integrations.sqlserver`. In a Collector configuration file, place them under `cxdot.integrations.sqlserver`. ### Optional settings * **`enabled`** Type: `boolean`. Optional. Default: `true`. Whether to run the SQL Server integration. If true, the Collector collects SQL Server metrics. If false, the Collector doesn't run this integration. * **`username`** Type: `string`. Optional. SQL Server login name that the Collector uses for every statically configured target. Required when `instances` isn't empty. Targets found through annotations use the credentials in their annotation instead. * **`password`** Type: `string`. Optional. Password for `username`. Required when `instances` isn't empty. The Collector masks this value in diagnostic output, logs, and errors. * **`tls`** Type: `object`. Optional. Transport Layer Security (TLS) settings for connections to statically configured targets. By default, the Collector encrypts the whole connection and verifies that a trusted certificate authority (CA) issued the server certificate for the target's host name. Set `ca_file` or `ca_pem` to the CA that issued the server certificate when the system that runs the Collector doesn't trust it, and set `server_name_override` when the certificate names a host other than the one in the endpoint. The certificate that SQL Server generates for itself when none is configured can't pass verification: install a certificate issued for the server's host name, or set `insecure_skip_verify` to `true` to accept any certificate. Set `insecure` to `true` to encrypt only the sign-in, the least that SQL Server accepts, without verifying the certificate. Client certificates aren't supported. Targets found through annotations use the encryption settings in their annotation's connection string instead. * **`tls.insecure`** Type: `boolean`. Optional. Default: `false`. Whether to encrypt only the sign-in. If true, the Collector encrypts the sign-in, the least that SQL Server accepts, without verifying the server certificate, and sends the rest of the connection unencrypted unless the server requires encryption. If false, the Collector encrypts the whole connection. * **`tls.insecure_skip_verify`** Type: `boolean`. Optional. Default: `false`. Whether to skip verification of the SQL Server certificate. If true, the connection is encrypted but isn't authenticated and is vulnerable to man-in-the-middle attacks. If false, the Collector verifies the certificate. * **`tls.ca_file`** Type: `string`. Optional. Path to a PEM-encoded file with the certificate authority (CA) certificate that issued the SQL Server certificate. If omitted, the Collector verifies the certificate against the system certificate authority pool. * **`tls.ca_pem`** Type: `string`. Optional. PEM-encoded content of the certificate authority certificate that issued the SQL Server certificate, as an alternative to `ca_file`. * **`tls.include_system_ca_certs_pool`** Type: `boolean`. Optional. Default: `false`. Whether to trust the system certificate authority pool in addition to `ca_file` or `ca_pem`. If true, the Collector trusts both sources. If false, the Collector trusts only the configured certificate authority. * **`tls.server_name_override`** Type: `string`. Optional. Host name that the SQL Server certificate must name, when it differs from the host in the target's endpoint. * **`tls.min_version`** Type: `string`. Optional. Default: `1.2`. Minimum TLS version that the Collector accepts. Allowed values: `1.0`, `1.1`, `1.2`, `1.3`. * **`tls.max_version`** Type: `string`. Optional. Default: `1.3`. Maximum TLS version that the Collector accepts. Allowed values: `1.0`, `1.1`, `1.2`, `1.3`. * **`tls.cipher_suites`** Type: `array of string`. Optional. Default: `[]`. TLS cipher suites that the Collector can use with TLS 1.2 and earlier, in preference order. An empty list uses the secure defaults of the Go TLS library. For supported names, see [https://go.dev/src/crypto/tls/cipher\_suites.go](https://go.dev/src/crypto/tls/cipher_suites.go). * **`database`** Type: `string`. Optional. Default: `master`. Database whose files and index usage the Collector reports for every statically configured target. Server-wide metrics and the state and backups of every database are reported whatever this is set to. Targets found through annotations use the database in their annotation instead. * **`collection_interval`** Type: `duration`. Optional. Default: `15s`. How often the integration collects metrics from each SQL Server instance. * **`timeout`** Type: `duration`. Optional. Default: `10s`. Maximum time allowed to collect metrics from a SQL Server instance during one interval. A value of `0s` disables the timeout. * **`backup_collection_interval`** Type: `duration`. Optional. Default: `300s`. How often the integration reads the backup history of each statically configured instance. The backup history grows with every backup the server records, so it's read less often than the other metrics. Between reads, the integration reports the most recent backup counts every `collection_interval`. A value no longer than `collection_interval` reads it on every collection. Targets found through annotations use the interval in their annotation, or 300 seconds. * **`index_usage_collection_interval`** Type: `duration`. Optional. Default: `300s`. How often the integration reads the index usage of `database` on each statically configured instance, which grows with the number of indexes. Between reads, the integration reports the most recent counts every `collection_interval`. A value no longer than `collection_interval` reads it on every collection. Targets found through annotations use the interval in their annotation, or 300 seconds. * **`instances`** Type: `array of object`. Optional. Default: `[]`. SQL Server instances to monitor. Each `endpoint` uses `host:port` format, such as `sqlserver.default.svc:1433`. When this list contains an instance, the integration monitors only the listed instances and disables discovery through annotations for this integration instance. When the list is empty, the integration monitors targets supplied through SQL Server annotations, each with the credentials in its annotation. * **`instances[].endpoint`** Type: `string`. Required. `host:port` of the target. ## Related topics - [Azure metrics available in Palo Alto Networks Cortex XCOR](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/server/azure/azure-metrics.md) - [SQL DB Input source plugin](https://docs-xcor.paloaltonetworks.com/ingest/pipeline/plugins/source-plugins/sqldb.md) - [CXDOT Collector integrations](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/collector.md) - [Server integrations](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/server.md) - [Windows](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/collector/windows.md) This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.