> ## Documentation Index
> Fetch the complete documentation index at: https://docs.evidence.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server

> Query your SQL Server or Azure SQL database directly from Evidence without syncing data.

The SQL Server direct connector lets Evidence run queries live against your own SQL Server database using a SQL login.

Supported:

* **SQL Server 2017** and later (on-premises, VMs, or containers)
* **Azure SQL Database**
* **Azure SQL Managed Instance**

## Connecting

You need the server **hostname**, **port**, **database** name, and a **SQL login** with its **password**.

### Prerequisites

* **Network access.** Evidence connects to your server over TDS (port `1433` by default). Allow inbound connections from Evidence's [egress IPs](https://docs.evidence.dev/evidence-warehouse.md#ip-whitelisting) on that port in your firewall or network security group.
  * **Azure SQL Database:** add the IPs as server-level firewall rules (**SQL server → Networking → Firewall rules**).
  * **Azure SQL Managed Instance:** enable the **public endpoint** (port `3342`) and allow the IPs in the instance's network security group.
* **SQL authentication** enabled on the server ("SQL Server and Windows Authentication mode"). Windows / Microsoft Entra authentication isn't supported by this connector yet.

### 1. Create a read-only login

Create a dedicated login for Evidence that can only read data. Don't reuse an admin login or `sa`.

```sql theme={null}
-- On the server (in master):
CREATE LOGIN evidence_reader WITH PASSWORD = '<strong-password>';

-- In the database Evidence should query:
USE analytics;
CREATE USER evidence_reader FOR LOGIN evidence_reader WITH DEFAULT_SCHEMA = dbo;
ALTER ROLE db_datareader ADD MEMBER evidence_reader;
```

> **Azure SQL Database: use a contained user instead**
>
> Azure SQL Database also supports contained database users, which need no server-level login. Run this in the target database:
>
> ```sql theme={null}
> CREATE USER evidence_reader WITH PASSWORD = '<strong-password>', DEFAULT_SCHEMA = dbo;
> ALTER ROLE db_datareader ADD MEMBER evidence_reader;
> ```

`db_datareader` grants `SELECT` on every table and view in the database. To limit access to specific schemas, skip the role and grant per schema:

```sql theme={null}
GRANT SELECT ON SCHEMA::sales TO evidence_reader;
```

### 2. Choose your TLS settings

**Encrypt** is on by default and Azure SQL requires it. Evidence verifies the server certificate against publicly trusted CAs, so Azure SQL works without extra setup.

If your server uses a **self-signed** or **internal-CA** certificate, supply that CA as the **SSL CA certificate** (PEM). Evidence then verifies the server against your CA. **Trust server certificate** also gets you connected, but it skips certificate validation entirely and leaves the connection open to an active man-in-the-middle. Use it only for testing.

### 3. Configure the connector in Evidence

**1. Open the connectors page**

Go to **Connectors** in the sidebar. In the **Warehouse** card at the top, select **SQL Server**.

**2. Enter connection details**

Fill in the **Server** (hostname only, e.g. `sql.octocorp.example` or `octocorp.database.windows.net`, not a connection string), **Port**, **Database**, **User**, and **Password**. For a named instance, enter the instance's port rather than `host\instance`.

**3. (Optional) Restrict the schema browser**

Add schema names to **Schemas** to limit which ones appear in the schema browser. Leave empty to show every schema the login can see.

**4. Test and save**

Click **Test Connection**. Once it passes, click **Save**.

## Configuration reference

**Studio**

#### Credentials

**Server** `string` *required*

SQL Server hostname, e.g. sql.example.com or myserver.database.windows.net. For a named instance, use the instance port rather than host\instance.

**Port** `number`

TDS port. Default: `1433`.

**User** `string` *required*

SQL login to connect as.

**Password** `string` *required*

Password for the SQL login.

**Database** `string` *required*

Database to connect to.

**Encrypt** `boolean`

Encrypt the connection with TLS. Required by Azure SQL. Default: `true`.

**Trust server certificate** `boolean`

Skip server certificate validation. Leaves the connection open to an active man-in-the-middle — prefer supplying your CA in ssl\_ca for servers with a self-signed or internal-CA certificate. Default: `false`.

**SSL CA certificate** `string`

PEM CA bundle used to verify the server certificate.

**Default schema** `string`

Schema the CLI uses to qualify bare table names (e.g. in `evidence describe`). T-SQL can't switch schema per session, so unqualified names in queries resolve against the login's default schema — set it with ALTER USER … WITH DEFAULT\_SCHEMA. Default: `"dbo"`.

#### Visibility

**Schemas** `string[]`

Allowlist of schemas exposed to the editor and schema browser. Empty exposes every schema the login can see. Default: `[]`.

#### Row-level security

**Session context** `\{ contextKey: string; evidenceVariable: `user.email`|`user.id`|`user.name`|`organization.id` \}[]`

Evidence identity → SQL Server SESSION\_CONTEXT key, set per query for row-level security. Default: `[]`.

**CLI (connection.yaml)**

If your organization has already set up this connection in Evidence Studio, you don't need a `connection.yaml`. Run `evidence login` instead. See [Connect your CLI to your data](https://docs.evidence.dev/cli/connections.md).

```yaml theme={null}
type: sqlserver
server: sql.octocorp.example
# port: 1433
user: evidence_reader
password: <your-password>
database: analytics
# encrypt: true
# trust_cert: false
# ssl_ca_path: ./certs/ca.pem # PEM CA bundle for a self-signed or internal-CA server certificate
# schema: dbo
# schemas: [dbo, sales]
# session_context:
#   - contextKey: user_email
#     evidenceVariable: user.email
```

#### Credentials

**server** `string` *required*

SQL Server hostname, e.g. sql.example.com or myserver.database.windows.net. For a named instance, use the instance port rather than host\instance.

**port** `number`

TDS port. Default: `1433`.

**user** `string` *required*

SQL login to connect as.

**password** `string` *required*

Password for the SQL login.

**database** `string` *required*

Database to connect to.

**encrypt** `boolean`

Encrypt the connection with TLS. Required by Azure SQL. Default: `true`.

**trust\_cert** `boolean`

Skip server certificate validation. Leaves the connection open to an active man-in-the-middle — prefer supplying your CA in ssl\_ca for servers with a self-signed or internal-CA certificate. Default: `false`.

**ssl\_ca** `string`

PEM CA bundle used to verify the server certificate.

**ssl\_ca\_path** `string`

Path to a PEM CA bundle, resolved relative to connection.yaml.

**schema** `string`

Schema the CLI uses to qualify bare table names (e.g. in `evidence describe`). T-SQL can't switch schema per session, so unqualified names in queries resolve against the login's default schema — set it with ALTER USER … WITH DEFAULT\_SCHEMA. Default: `"dbo"`.

#### Visibility

**schemas** `string[]`

Allowlist of schemas exposed to the editor and schema browser. Empty exposes every schema the login can see. Default: `[]`.

#### Row-level security

**session\_context** `\{ contextKey: string; evidenceVariable: `user.email`|`user.id`|`user.name`|`organization.id` \}[]`

Evidence identity → SQL Server SESSION\_CONTEXT key, set per query for row-level security. Default: `[]`.

## Row-Level Security

Evidence supports row-level security on the SQL Server connector using SQL Server's native [security policies](https://learn.microsoft.com/sql/relational-databases/security/row-level-security) together with **session context**.

### How it works

Before each query, Evidence calls `sp_set_session_context` to store the current viewer's identity (for example their email) in the session under a key you choose, such as `user_email`. A **security policy** on your tables uses an inline table-valued function as a filter predicate. That function reads the value back with `SESSION_CONTEXT(N'user_email')` and returns only the rows the viewer is allowed to see.

The predicate runs inside SQL Server, so the server enforces the filter and Evidence never receives rows the policy excludes.

### Setup

The example below filters a `sales` table so each viewer only sees rows whose `owner_email` matches their Evidence email. Substitute your own table, column, and matching logic.

**1. Map the Evidence identity to a session key**

Add a mapping from a session-context key to an Evidence identity attribute under **Session context**. In `connection.yaml` this is the `session_context` block:

```yaml theme={null}
session_context:
  - contextKey: user_email
    evidenceVariable: user.email
```

Before every query, Evidence runs `EXEC sp_set_session_context @key = N'user_email', @value = '<viewer-email>', @read_only = 1`. `@read_only = 1` locks the value for that connection, so a query can't reset the key to impersonate another viewer. Supported identities are `user.email`, `user.id`, `user.name`, and `organization.id`.

> [!NOTE]
>
> Session context is applied by Evidence Studio, where each viewer has an identity. The local CLI runs every query as the configured login and ignores `session_context`.

**2. Create the predicate function**

Create an inline table-valued function that returns a row when the session value matches. Put it in its own schema (here `Security`) so the login Evidence uses can't change it.

```sql theme={null}
CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_rls_owner_email(@owner_email AS nvarchar(256))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
        SELECT 1 AS fn_result
        WHERE @owner_email = CAST(SESSION_CONTEXT(N'user_email') AS nvarchar(256));
GO
```

**3. Attach a security policy**

Bind the predicate to the column it filters on with `CREATE SECURITY POLICY`. A `FILTER PREDICATE` silently drops non-matching rows from reads.

```sql theme={null}
CREATE SECURITY POLICY Security.OwnerEmailPolicy
ADD FILTER PREDICATE Security.fn_rls_owner_email(owner_email)
    ON dbo.sales
WITH (STATE = ON);
GO
```

Add an `ADD FILTER PREDICATE ... ON <table>` line for every table that has the `owner_email` column.

**4. Decide on a fallback for unassigned users**

With the policy on, a viewer whose email matches no row sees zero rows. If you want some users (for example admins) to see everything, broaden the predicate:

```sql theme={null}
-- inside the function body:
WHERE @owner_email = CAST(SESSION_CONTEXT(N'user_email') AS nvarchar(256))
   OR CAST(SESSION_CONTEXT(N'user_email') AS nvarchar(256)) IN (N'admin@octocorp.example');
```

Keeping the predicate strict is the more secure default, but then every viewer needs a matching row.

## Security Considerations

Evidence runs queries against your SQL Server database. Take practical steps to protect it from misuse, accidental or malicious.

1. Use a dedicated login with `db_datareader` (or narrower schema-level `SELECT` grants), never `sa`, `db_owner`, or a `sysadmin` member.
2. Keep **Encrypt** on, and verify the server certificate: supply your CA in **SSL CA certificate** rather than enabling **Trust server certificate**.
3. Restrict network access to Evidence's [egress IPs](https://docs.evidence.dev/evidence-warehouse.md#ip-whitelisting) plus your team's networks.
4. Keep RLS predicate functions and security policies in a schema the Evidence login can't modify.
5. Rotate the login's password regularly, and audit its activity with [SQL Server Audit](https://learn.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-database-engine) or Azure SQL auditing.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.