# MSSQL

Connect to Microsoft SQL Server / Azure SQL

## Usage Instructions

## Security & least-privilege

The block applies defense-in-depth guards, but the **definitive access control is the database login's own permissions**. Grant each connection the least privilege it needs.

**Built-in guards (defense-in-depth, not a substitute for least privilege):**

* The **Query** operation accepts read-only `SELECT` / `WITH` statements only; anything that mutates data or schema is rejected.
* The **Execute** operation permits per-object DDL and static `EXEC dbo.<proc>`, but blocks cluster-level, privilege-escalation, and out-of-band T-SQL: `xp_cmdshell` and other `xp_*` procedures, `sp_configure`, `OPENROWSET` / `OPENQUERY` / `OPENDATASOURCE`, `BULK INSERT`, dynamic `EXEC(...)` / `EXEC @var` / `sp_executesql`, `EXECUTE AS`, `sp_execute_external_script`, `BACKUP` / `RESTORE`, `RECONFIGURE`, `SHUTDOWN`, `ALTER SERVER`, login/user/role changes, `GRANT` / `REVOKE` / `DENY`, and linked-server / SQL Agent procedures.
* **Update** and **Delete** wrap the supplied `WHERE` clause so an injected fragment cannot batch a second statement onto the query.

**Recommended login setup — this is the real guarantee:**

* **Query (read-only) route:** connect with a login that has only `db_datareader`. A read-only login is the definitive read-only guarantee; the SELECT-only guard is a second layer.
* **Insert / Update / Delete / Execute route:** use a scoped login with only the roles the workflow needs — `db_datawriter` for CRUD, and `db_ddladmin` only if it creates or alters tables. Avoid `db_owner` / server admin.
* Prefer **separate, purpose-scoped logins per workflow** over one broad login. A permitted `EXEC dbo.<proc>` runs at the connecting login's permissions, so a least-privilege login also bounds what any allowed procedure can do.

**Connection hygiene:**

* Store the password or access token as a **workspace secret** (`{{VAR}}`), never inline in the block.
* Keep `encrypt` enabled (default) and `trustServerCertificate` disabled (default) against real servers; only trust a self-signed certificate for a known internal host you control.

Example — a read-only login for the Query route (SQL auth):

```sql
CREATE USER app_readonly WITH PASSWORD = '<strong-password>';
ALTER ROLE db_datareader ADD MEMBER app_readonly;
```

Integrate Microsoft SQL Server and Azure SQL into the workflow. Supports SQL authentication and Azure AD access-token authentication. Can query, insert, update, delete, execute raw T-SQL, and introspect schema.

## Tools

### `mssql_query`

Execute a SELECT query on a MSSQL / Azure SQL database

#### Input

| Parameter                | Type    | Required | Description                                                      |
| ------------------------ | ------- | -------- | ---------------------------------------------------------------- |
| `host`                   | string  | Yes      | MSSQL server hostname or IP address                              |
| `port`                   | number  | No       | MSSQL server port (default: 1433)                                |
| `database`               | string  | Yes      | Database name to connect to                                      |
| `authMode`               | string  | No       | Authentication mode: sql or azure-ad-access-token (default: sql) |
| `username`               | string  | No       | Database username (SQL authentication)                           |
| `password`               | string  | No       | Database password (SQL authentication)                           |
| `token`                  | string  | No       | Azure AD access token (azure-ad-access-token authentication)     |
| `encrypt`                | boolean | No       | Encrypt the connection (default: true)                           |
| `trustServerCertificate` | boolean | No       | Trust a self-signed server certificate (default: false)          |
| `query`                  | string  | Yes      | T-SQL SELECT query to execute                                    |

#### Output

| Parameter  | Type   | Description                           |
| ---------- | ------ | ------------------------------------- |
| `message`  | string | Operation status message              |
| `rows`     | array  | Array of rows returned from the query |
| `rowCount` | number | Number of rows returned               |

### `mssql_insert`

Insert data into a MSSQL / Azure SQL database

#### Input

| Parameter                | Type    | Required | Description                                                      |
| ------------------------ | ------- | -------- | ---------------------------------------------------------------- |
| `host`                   | string  | Yes      | MSSQL server hostname or IP address                              |
| `port`                   | number  | No       | MSSQL server port (default: 1433)                                |
| `database`               | string  | Yes      | Database name to connect to                                      |
| `authMode`               | string  | No       | Authentication mode: sql or azure-ad-access-token (default: sql) |
| `username`               | string  | No       | Database username (SQL authentication)                           |
| `password`               | string  | No       | Database password (SQL authentication)                           |
| `token`                  | string  | No       | Azure AD access token (azure-ad-access-token authentication)     |
| `encrypt`                | boolean | No       | Encrypt the connection (default: true)                           |
| `trustServerCertificate` | boolean | No       | Trust a self-signed server certificate (default: false)          |
| `table`                  | string  | Yes      | Table name to insert data into                                   |
| `data`                   | object  | Yes      | Data object to insert (key-value pairs)                          |

#### Output

| Parameter  | Type   | Description              |
| ---------- | ------ | ------------------------ |
| `message`  | string | Operation status message |
| `rows`     | array  | Rows returned            |
| `rowCount` | number | Number of rows affected  |

### `mssql_update`

Update data in a MSSQL / Azure SQL database

#### Input

| Parameter                | Type    | Required | Description                                                      |
| ------------------------ | ------- | -------- | ---------------------------------------------------------------- |
| `host`                   | string  | Yes      | MSSQL server hostname or IP address                              |
| `port`                   | number  | No       | MSSQL server port (default: 1433)                                |
| `database`               | string  | Yes      | Database name to connect to                                      |
| `authMode`               | string  | No       | Authentication mode: sql or azure-ad-access-token (default: sql) |
| `username`               | string  | No       | Database username (SQL authentication)                           |
| `password`               | string  | No       | Database password (SQL authentication)                           |
| `token`                  | string  | No       | Azure AD access token (azure-ad-access-token authentication)     |
| `encrypt`                | boolean | No       | Encrypt the connection (default: true)                           |
| `trustServerCertificate` | boolean | No       | Trust a self-signed server certificate (default: false)          |
| `table`                  | string  | Yes      | Table name to update data in                                     |
| `data`                   | object  | Yes      | Data object with fields to update (key-value pairs)              |
| `where`                  | string  | Yes      | WHERE clause condition (without WHERE keyword)                   |

#### Output

| Parameter  | Type   | Description              |
| ---------- | ------ | ------------------------ |
| `message`  | string | Operation status message |
| `rows`     | array  | Rows returned            |
| `rowCount` | number | Number of rows affected  |

### `mssql_delete`

Delete data from a MSSQL / Azure SQL database

#### Input

| Parameter                | Type    | Required | Description                                                      |
| ------------------------ | ------- | -------- | ---------------------------------------------------------------- |
| `host`                   | string  | Yes      | MSSQL server hostname or IP address                              |
| `port`                   | number  | No       | MSSQL server port (default: 1433)                                |
| `database`               | string  | Yes      | Database name to connect to                                      |
| `authMode`               | string  | No       | Authentication mode: sql or azure-ad-access-token (default: sql) |
| `username`               | string  | No       | Database username (SQL authentication)                           |
| `password`               | string  | No       | Database password (SQL authentication)                           |
| `token`                  | string  | No       | Azure AD access token (azure-ad-access-token authentication)     |
| `encrypt`                | boolean | No       | Encrypt the connection (default: true)                           |
| `trustServerCertificate` | boolean | No       | Trust a self-signed server certificate (default: false)          |
| `table`                  | string  | Yes      | Table name to delete data from                                   |
| `where`                  | string  | Yes      | WHERE clause condition (without WHERE keyword)                   |

#### Output

| Parameter  | Type   | Description              |
| ---------- | ------ | ------------------------ |
| `message`  | string | Operation status message |
| `rows`     | array  | Rows returned            |
| `rowCount` | number | Number of rows affected  |

### `mssql_execute`

Execute raw T-SQL on a MSSQL / Azure SQL database

#### Input

| Parameter                | Type    | Required | Description                                                      |
| ------------------------ | ------- | -------- | ---------------------------------------------------------------- |
| `host`                   | string  | Yes      | MSSQL server hostname or IP address                              |
| `port`                   | number  | No       | MSSQL server port (default: 1433)                                |
| `database`               | string  | Yes      | Database name to connect to                                      |
| `authMode`               | string  | No       | Authentication mode: sql or azure-ad-access-token (default: sql) |
| `username`               | string  | No       | Database username (SQL authentication)                           |
| `password`               | string  | No       | Database password (SQL authentication)                           |
| `token`                  | string  | No       | Azure AD access token (azure-ad-access-token authentication)     |
| `encrypt`                | boolean | No       | Encrypt the connection (default: true)                           |
| `trustServerCertificate` | boolean | No       | Trust a self-signed server certificate (default: false)          |
| `query`                  | string  | Yes      | Raw T-SQL to execute                                             |

#### Output

| Parameter  | Type   | Description                           |
| ---------- | ------ | ------------------------------------- |
| `message`  | string | Operation status message              |
| `rows`     | array  | Array of rows returned from the query |
| `rowCount` | number | Number of rows affected               |

### `mssql_introspect`

Introspect a MSSQL / Azure SQL database schema

#### Input

| Parameter                | Type    | Required | Description                                                      |
| ------------------------ | ------- | -------- | ---------------------------------------------------------------- |
| `host`                   | string  | Yes      | MSSQL server hostname or IP address                              |
| `port`                   | number  | No       | MSSQL server port (default: 1433)                                |
| `database`               | string  | Yes      | Database name to connect to                                      |
| `authMode`               | string  | No       | Authentication mode: sql or azure-ad-access-token (default: sql) |
| `username`               | string  | No       | Database username (SQL authentication)                           |
| `password`               | string  | No       | Database password (SQL authentication)                           |
| `token`                  | string  | No       | Azure AD access token (azure-ad-access-token authentication)     |
| `encrypt`                | boolean | No       | Encrypt the connection (default: true)                           |
| `trustServerCertificate` | boolean | No       | Trust a self-signed server certificate (default: false)          |
| `schema`                 | string  | No       | Schema to introspect (default: dbo)                              |

#### Output

| Parameter            | Type    | Description                                            |
| -------------------- | ------- | ------------------------------------------------------ |
| `message`            | string  | Operation status message                               |
| `tables`             | array   | Array of table schemas with columns, keys, and indexes |
| ↳ `name`             | string  | Table name                                             |
| ↳ `schema`           | string  | Schema name (e.g., dbo)                                |
| ↳ `columns`          | array   | Table columns                                          |
| ↳ `name`             | string  | Column name                                            |
| ↳ `type`             | string  | Data type (e.g., int, nvarchar, datetime2)             |
| ↳ `nullable`         | boolean | Whether the column allows NULL values                  |
| ↳ `default`          | string  | Default value expression                               |
| ↳ `isPrimaryKey`     | boolean | Whether the column is part of the primary key          |
| ↳ `isForeignKey`     | boolean | Whether the column is a foreign key                    |
| ↳ `references`       | object  | Foreign key reference information                      |
| ↳ `table`            | string  | Referenced table name                                  |
| ↳ `column`           | string  | Referenced column name                                 |
| ↳ `primaryKey`       | array   | Primary key column names                               |
| ↳ `foreignKeys`      | array   | Foreign key constraints                                |
| ↳ `column`           | string  | Local column name                                      |
| ↳ `referencesTable`  | string  | Referenced table name                                  |
| ↳ `referencesColumn` | string  | Referenced column name                                 |
| ↳ `indexes`          | array   | Table indexes                                          |
| ↳ `name`             | string  | Index name                                             |
| ↳ `columns`          | array   | Columns included in the index                          |
| ↳ `unique`           | boolean | Whether the index enforces uniqueness                  |
| `schemas`            | array   | List of available schemas in the database              |