Docs / Connections: SQL Server

Set up SQL Server

Verbiqo needs one login that reads the system views, and nothing that writes. SQL Server 2016 to 2022, on Windows or Linux, is supported: which versions and platforms.

At a glance

Supported versions

Verbiqo signs in with SQL Server authentication only: Windows authentication and Microsoft Entra ID sign-in are not supported yet.

1. Create the monitoring login

As an administrator. Replace the password, and run the msdb part for backup and Agent job history.

USE master;
CREATE LOGIN verbiqo_monitor WITH PASSWORD = 'choose-a-password', CHECK_POLICY = ON;

-- Required: requests, waits, blocking, query statistics
GRANT VIEW SERVER STATE TO verbiqo_monitor;
GRANT VIEW ANY DEFINITION TO verbiqo_monitor;
CREATE USER verbiqo_monitor FOR LOGIN verbiqo_monitor;
GRANT SHOWPLAN TO verbiqo_monitor;

-- Recommended: Query Store and statistics inside each database (metadata only)
GRANT CONNECT ANY DATABASE TO verbiqo_monitor;

-- Recommended: backup and SQL Server Agent history
USE msdb;
CREATE USER verbiqo_monitor FOR LOGIN verbiqo_monitor;
GRANT SHOWPLAN TO verbiqo_monitor;
GRANT SELECT ON dbo.backupset TO verbiqo_monitor;
GRANT SELECT ON dbo.sysjobs TO verbiqo_monitor;
GRANT SELECT ON dbo.sysjobhistory TO verbiqo_monitor;

Verbiqo signs in with a SQL login and password, so the server must allow SQL Server authentication (mixed mode).

2. Add the connection

1. Open the form

   Extensions… › SQL Server.

2. Fill it in

   Name, host, port (1433), user verbiqo_monitor, password. For a named instance, type its name in Instance instead of a port. Database is optional (master).

3. Choose encryption

   The same choices as SSMS. The default, Mandatory with Trust server certificate, encrypts and works with SQL Server’s own self-signed certificate. With a certificate from your CA, untick Trust server certificate and give the CA.

4. Test, then save

   The test shows the product, version, edition, sessions and TLS in use.

verbiqo mssql add sales --host sql01 --port 1433 --user verbiqo_monitor --password-stdin --encrypt mandatory --trust-server-certificate
verbiqo mssql test sales

A named instance: --instance REPORTING instead of --port, or --host sql01\REPORTING. The SQL Server Browser service must be running for it. In an availability group, save a connection to each replica, not the listener: through the listener every question lands on the primary.

Encryption

• Host name in certificate. When you connect by IP address or an alias, the name the certificate was issued for, so Mandatory and Strict can check it.
• No client certificate. SQL Server signs logins in with a login and password, never a client certificate, so there is none to give.
• The test says what happened. It reads the session’s encryption from the server itself, for example encrypted. Encrypt: Mandatory, server certificate trusted.

From a terminal: --encrypt optional|mandatory|strict, --trust-server-certificate, --host-name-in-certificate NAME and --ssl-ca FILE.

What the login may hold

Checked against SQL Server 2022 with a login holding exactly the grants above.

Refused: a login in any fixed server role that administers (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator), or holding any permission beyond reading state and definitions, such as CONTROL SERVER, ALTER ANY LOGIN, INSERT or EXECUTE. Verbiqo names the permission. sa is refused. Create a separate login.

How each statement stays read-only

• One read. One SELECT (or WITH … SELECT): no INTO, EXEC, SET, WAITFOR, locking hints or remote reads.
• Planned first. Verbiqo asks SQL Server for the plan without running the statement, and refuses it if the plan touches anything but system views and the msdb tables above, or would be costly.
• Rolled back. It runs inside a transaction that is always rolled back, with a 2-second lock timeout and low deadlock priority, so Verbiqo yields to your applications.
• Hidden values. Literals in statement text and plans are hidden, and password hashes are never shown.

Settings that help

Ask what should I enable? to see each item as ready or not.

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

Optional: log shipping

To see log shipping’s backups and restores against their thresholds:

USE msdb;
GRANT SELECT ON dbo.log_shipping_monitor_primary TO verbiqo_monitor;
GRANT SELECT ON dbo.log_shipping_monitor_secondary TO verbiqo_monitor;

Optional: SSIS and Reporting Services

Why it helps: Verbiqo reads SSIS’s run history and the report server’s execution log, to find failed and slow packages, slow and failing reports, and failed subscriptions.

SSIS, on SQL Server 2017 and later (the role reads the catalog and changes nothing):

USE SSISDB;
CREATE USER verbiqo_monitor FOR LOGIN verbiqo_monitor;
ALTER ROLE ssis_logreader ADD MEMBER verbiqo_monitor;

Reporting Services: only the columns Verbiqo reads, never report definitions, parameters or who ran a report. The report server database may be on another SQL Server: add that one as a connection.

USE ReportServer;
CREATE USER verbiqo_monitor FOR LOGIN verbiqo_monitor;
GRANT SELECT ON dbo.Catalog (ItemID, Path) TO verbiqo_monitor;
GRANT SELECT ON dbo.ExecutionLogStorage (ReportID, TimeStart, TimeDataRetrieval, TimeProcessing, TimeRendering, [RowCount], Status) TO verbiqo_monitor;
GRANT SELECT ON dbo.Subscriptions (Report_OID, LastStatus, LastRunTime, DeliveryExtension) TO verbiqo_monitor;
GRANT SHOWPLAN TO verbiqo_monitor;

These tables are read only by Verbiqo’s own fixed reads. A statement the model writes still reaches the system views alone.

Optional: replication

Why it helps: Verbiqo reads replication’s agents and history, to find agents that are failing (even while they retry), how far behind each subscriber is, the last day’s errors, and subscriptions that must be reinitialised.

On the server that holds the distribution database (often the publisher itself), grant only the columns Verbiqo reads. The built-in replmonitor role is not enough: it lets an account run Microsoft’s monitoring procedures, not read these tables, and Verbiqo runs no procedures. Do not use db_datareader: it would give the replicated rows (MSrepl_commands) and the agents’ stored credentials too.

USE distribution;
CREATE USER verbiqo_monitor FOR LOGIN verbiqo_monitor;
GRANT SHOWPLAN TO verbiqo_monitor;
GRANT SELECT ON dbo.MSdistribution_agents (id, name, publisher_db, publication, subscriber_db, subscriber_id, subscriber_name) TO verbiqo_monitor;
GRANT SELECT ON dbo.MSdistribution_history (agent_id, runstatus, time, delivery_latency, current_delivery_latency, comments) TO verbiqo_monitor;
GRANT SELECT ON dbo.MSlogreader_agents (id, name, publisher_db) TO verbiqo_monitor;
GRANT SELECT ON dbo.MSlogreader_history (agent_id, runstatus, time, delivery_latency, comments) TO verbiqo_monitor;
GRANT SELECT ON dbo.MSrepl_errors (time, source_name, error_code, error_text) TO verbiqo_monitor;
GRANT SELECT ON dbo.MSsubscriptions (publisher_db, subscriber_db, status) TO verbiqo_monitor;
GRANT SELECT ON dbo.MSreplservers (srvid, srvname) TO verbiqo_monitor;  -- SQL Server 2017 and later

On SQL Server 2016, leave out the last line and subscriber_name. A distribution database with another name: use that name. These tables are read only by Verbiqo’s own fixed reads. A statement the model writes still reaches the system views alone. Key values that an error message quotes are masked.

Optional: plans of statements you paste

Why it helps: Verbiqo can explain why one statement is slow from its estimated plan, without running it (Explain a slow statement).

SQL Server gives an estimated plan only to a login that may run the statement, though nothing runs. So plans of statements on application tables need SHOWPLAN and SELECT in that database. Grant it only if your policy allows the monitoring login to read those tables: Verbiqo plans statements there, it never runs them, and every other statement stays limited to the system views.

USE [YourDatabase];
CREATE USER verbiqo_monitor FOR LOGIN verbiqo_monitor;  -- if it has no user there yet
GRANT SHOWPLAN TO verbiqo_monitor;
GRANT SELECT ON SCHEMA::[dbo] TO verbiqo_monitor;

Logs and metrics

• Splunk: send the error log (the ERRORLOG file, or the Windows Application event log), with its host. What Verbiqo finds: Log findings.
• Prometheus: windows_exporter (Windows) or node_exporter (Linux) on the machine, for capacity forecasts and reboots on the timeline. Verbiqo finds the machine by the connection’s host.

Next: Ask SQL Server.

Common errors

The connection test and the answer say what went wrong. Find the message below, then follow the fix.