Microsoft SQL Server Connector

Connect to Microsoft SQL Server and Azure SQL Database for natural language queries. ZenSearch discovers your schema and lets users ask questions in plain English — the AI generates and executes SQL queries automatically.

Overview

The MS SQL Server connector is a database connector — it does not ingest documents. Instead, it enables real-time natural language to SQL queries through ZenSearch's AI agent tools.

Supported Databases

• SQL Server 2016 and later
• Azure SQL Database
• Azure SQL Managed Instance

Prerequisites

• SQL Server instance accessible from ZenSearch
• A SQL login with read-only access
• Network connectivity (firewall rules allowing port 1433 or your custom port)

Authentication

SQL Authentication

Standard username and password authentication against SQL Server's built-in login system.

Windows Authentication

Windows/domain credential authentication. This requires additional network configuration (Kerberos) and is typically used in on-premise Active Directory environments.

Recommendation: Use SQL authentication for simplicity. It works across all deployment scenarios including Azure SQL and cross-platform environments.

Configuration Reference

Setting	Type	Required	Description
Host	string	Yes	Server hostname or IP address
Port	integer	Yes	Server port (default: 1433)
Database	string	Yes	Database name
Username	string	Yes	SQL login name
Password	string	Yes	Login password
Schema	string	No	Default schema (default: dbo)
Encrypt	string	No	Encryption mode: disable, false, or true
Trust Server Certificate	boolean	No	Trust the server certificate without validation
App Name	string	No	Application name shown in SQL Server activity monitor
Include Tables	array	No	Specific tables to include (empty = all tables)
Exclude Tables	array	No	Tables to exclude from schema discovery
Include Views	boolean	No	Include views in schema discovery
Max Rows Per Query	integer	No	Maximum rows returned per query (0–10,000)
Query Timeout	integer	No	Query timeout in seconds (0–300)
Sample Row Count	integer	No	Number of sample rows for schema understanding
Refresh Interval	string	No	How often to refresh the schema cache

Encryption Options

Mode	Description
disable	No encryption (not recommended for production)
false	Request encryption but allow unencrypted fallback
true	Require encryption — fail if not available

Note: Azure SQL Database requires encryption (true) and this is enforced by the server.

Setup Steps

1. Add Connector: Navigate to Knowledge → Add Data Source → MS SQL Server
2. Enter Connection Details: Host, port, and database name
3. Provide Credentials: SQL login username and password
4. Configure Encryption: Enable encryption for production environments
5. Set Schema (optional): Specify a schema other than dbo if needed
6. Filter Tables (optional): Include or exclude specific tables
7. Test & Create: Verify the connection and save

How Natural Language Queries Work

Once connected, users can ask questions like:

• "How many active customers do we have by state?"
• "What's the total revenue from last quarter's enterprise deals?"
• "Show me employees hired in the last 6 months with their departments"

The AI agent generates T-SQL (SQL Server's dialect) and handles SQL Server-specific syntax like TOP, OFFSET FETCH, date functions, and schema-qualified table names.

Query Safety

All generated queries are validated before execution:

• Blocked operations: DROP, DELETE, INSERT, UPDATE, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, EXEC
• Read-only enforcement: Only SELECT queries are permitted
• Row limits: Configurable maximum rows prevent large result sets
• Timeout protection: Queries are terminated if they exceed the configured timeout

Creating a Read-Only Login

-- Create a SQL login
CREATE LOGIN zensearch WITH PASSWORD = 'YourSecurePassword123!';

-- Switch to target database
USE your_database;

-- Create a database user mapped to the login
CREATE USER zensearch FOR LOGIN zensearch;

-- Grant read-only access
ALTER ROLE db_datareader ADD MEMBER zensearch;

For Azure SQL Database, use the same syntax but execute from within the target database (Azure SQL does not support USE statements).

Best Practices

1. Use SQL authentication — Simpler to configure and works across all deployment types
2. Enable encryption — Set encrypt: true for production, especially for Azure SQL
3. Create a dedicated read-only login — Use the db_datareader role for least-privilege access
4. Filter schemas — If your database has many schemas, use include_tables with schema-qualified names to limit scope
5. Set the App Name — Configure app_name (e.g., ZenSearch) so DBAs can identify ZenSearch queries in Activity Monitor
6. Add extended properties — SQL Server's extended properties can help the AI understand your schema:

   EXEC sp_addextendedproperty 'MS_Description', 'Customer purchase orders', 'SCHEMA', 'dbo', 'TABLE', 'Orders';

Troubleshooting

Connection failed

• Verify the host and port are correct (default: 1433)
• Check that SQL Server is configured to allow TCP/IP connections (SQL Server Configuration Manager)
• For Azure SQL, verify the firewall rules include ZenSearch's IP address

Login failed

• Verify the username and password
• Check that SQL Server authentication is enabled (not just Windows authentication)
• For Azure SQL, ensure the login exists in the specific database

Certificate error

• For self-signed certificates, enable trust_server_certificate
• For production, use a valid TLS certificate and set encrypt: true

Schema not found

• Verify the schema name (default: dbo)
• Check that the user has access to the specified schema
• Use include_tables with fully qualified names: schema.table_name

Related

• PostgreSQL
• MySQL
• ClickHouse