# SQL Server to REST API and MCP Server

Source: https://wiki.faucetdb.ai/tutorial-sqlserver

**How do I turn a SQL Server database into a REST API?** Add the database to Faucet, create an API key, and start the server. Every table is then available at `/api/v1/mydb/_table/<table>` with filters, sorting and pagination, described by an OpenAPI 3.1 spec at `/openapi.json`. AI agents can use the same database through the MCP endpoint at `/mcp`, limited by the same API key's role.

```bash
faucet db add --name mydb --driver mssql \
  --host localhost --user sa --password-prompt --database mydb
faucet role create --name reader --verbs GET
faucet key create --role reader          # prints the key once
faucet serve

curl "http://localhost:8080/api/v1/mydb/_table/Customers?limit=10" \
  -H "X-API-Key: faucet_YOUR_KEY"
```

Prefer a browser? The [admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) does the same with a form and a **Test connection** button. To give Claude, Cursor or another AI agent access, see [Give AI Agents Access](https://wiki.faucetdb.ai/tutorial-sqlserver#give-ai-agents-access-mcp).

This tutorial walks you through connecting a SQL Server database to Faucet and querying it via the REST API.

## Prerequisites

- Faucet installed and running (`faucet serve`) — see [Getting Started](https://wiki.faucetdb.ai/getting-started)
- An admin account created (`faucet admin create`)
- A SQL Server instance with a database and at least one table
- Supported versions: SQL Server 2012 through 2022, Azure SQL Database and Amazon RDS for SQL Server

## Step 1: Connect SQL Server to Faucet

The DSN format for SQL Server:

```
sqlserver://username:password@host:port?database=dbname
```

### Via Admin UI (easiest)

1. Open `http://localhost:8080` and sign in.
2. Go to **Databases** and click **Add database**.
3. Pick **SQL Server** and fill in:
   - **Host** and **Port**: for example `localhost` and `1433` (the default)
   - **Database**: for example `mydb`
   - **Username** and **Password**: for example `sa`; special characters need no escaping
   - **Encryption**: choose **On** for Azure SQL. Turn on **Trust server certificate** for a local server with a self-signed certificate
4. Check the **API name**. It is filled in from the database name and becomes the URL prefix (`/api/v1/<name>`).
5. Click **Test connection**. Faucet connects and reports how many tables it found, or explains what went wrong.
6. Click **Add database**. The REST API and MCP tools are live right away.

See [Admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) for screenshots of this flow.

### Via CLI

With separate connection flags (Faucet builds and escapes the connection string):

```bash
faucet db add --name mydb --driver mssql \
  --host localhost --port 1433 --user sa --password 'YourStrong!Passw0rd' --database mydb
```

Or with a connection string:

```bash
faucet db add \
  --name mydb \
  --driver mssql \
  --dsn "sqlserver://sa:YourStrong!Passw0rd@localhost:1433?database=mydb"
```

::: tip
A server that is already running loads databases added with `faucet db add` on its next start (`faucet stop && faucet serve`). Databases added in the admin UI or through the API are live immediately.
:::

### Via API

```bash
TOKEN=$(curl -s http://localhost:8080/api/v1/system/admin/session \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@example.com","password":"changeme123"}' \
  | jq -r '.session_token')

curl -X POST http://localhost:8080/api/v1/system/service \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "mydb",
    "driver": "mssql",
    "dsn": "sqlserver://sa:YourStrong!Passw0rd@localhost:1433?database=mydb"
  }'
```

## Step 2: Verify the Connection

```bash
faucet db test mydb
```

Or list tables via the API:

```bash
curl http://localhost:8080/api/v1/mydb/_table \
  -H "Authorization: Bearer $TOKEN"
```

Expected response:

```json
{
  "resource": [
    {"name": "Customers"},
    {"name": "Orders"},
    {"name": "Products"}
  ]
}
```

## Step 3: Query Your Data

### List all records

```bash
curl "http://localhost:8080/api/v1/mydb/_table/Customers?limit=10" \
  -H "Authorization: Bearer $TOKEN"
```

### Filter records

```bash
curl "http://localhost:8080/api/v1/mydb/_table/Orders?filter=Status%20%3D%20'Shipped'" \
  -H "Authorization: Bearer $TOKEN"
```

### Select specific fields

```bash
curl "http://localhost:8080/api/v1/mydb/_table/Customers?fields=Name,Email" \
  -H "Authorization: Bearer $TOKEN"
```

### Paginate

```bash
curl "http://localhost:8080/api/v1/mydb/_table/Orders?limit=10&offset=0&order=CreatedAt%20DESC" \
  -H "Authorization: Bearer $TOKEN"
```

### Count records

```bash
curl "http://localhost:8080/api/v1/mydb/_table/Orders?include_count=true" \
  -H "Authorization: Bearer $TOKEN"
```

## Step 4: Insert, Update, and Delete

### Insert a record

```bash
curl -X POST http://localhost:8080/api/v1/mydb/_table/Customers \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"resource": [{"Name": "Acme Corp", "Email": "contact@acme.com"}]}'
```

SQL Server supports RETURNING via OUTPUT clause, so the response includes server-generated values:

```json
{
  "resource": [
    {"Id": 42, "Name": "Acme Corp", "Email": "contact@acme.com", "CreatedAt": "2024-01-15T10:30:00Z"}
  ]
}
```

### Update a record

```bash
curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/Customers?ids=42" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"Name": "Acme Corporation"}'
```

### Delete a record

```bash
curl -X DELETE "http://localhost:8080/api/v1/mydb/_table/Customers?ids=42" \
  -H "Authorization: Bearer $TOKEN"
```

## SQL Server-Specific Notes

### OUTPUT clause (RETURNING equivalent)

SQL Server uses OUTPUT INSERTED.* instead of RETURNING. Faucet handles this transparently -- INSERT and UPDATE responses include the actual row data.

### Schema selection

SQL Server defaults to the `dbo` schema. To use a different schema:

```bash
faucet db add \
  --name mydb \
  --driver mssql \
  --dsn "sqlserver://sa:pass@localhost:1433?database=mydb" \
  --schema sales
```

### Stored procedures

Faucet can execute SQL Server stored procedures:

```bash
curl -X POST http://localhost:8080/api/v1/mydb/_proc/GetActiveUsers \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"params": {"MinAge": 18}}'
```

### Supported features

| Feature | Supported |
|---------|-----------|
| Schema introspection | Yes |
| RETURNING clause | Via OUTPUT clause |
| Upsert (MERGE) | Yes |
| Stored procedures | Yes |
| Views | Yes |
| Parameter style | `@p1, @p2, @p3` |
| Default schema | `dbo` |

### Docker for local development

Run SQL Server locally with Docker:

```bash
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrong!Passw0rd" \
  -p 1433:1433 --name sqlserver \
  -d mcr.microsoft.com/mssql/server:2022-latest
```

Then connect:

```bash
faucet db add \
  --name localdb \
  --driver mssql \
  --dsn "sqlserver://sa:YourStrong!Passw0rd@localhost:1433?database=master"
```

## Give AI Agents Access (MCP)

`faucet serve` also runs an MCP server at `http://localhost:8080/mcp`. Connect an agent with an API key, and the key's [role](https://wiki.faucetdb.ai/rbac) decides which tables it can read or change. For Claude Code:

```bash
claude mcp add --transport http faucet http://localhost:8080/mcp \
  --header 'X-API-Key: faucet_YOUR_KEY'
```

The agent can then call `faucet_list_tables`, `faucet_describe_table` and `faucet_query` on `mydb`. For Cursor, VS Code, Windsurf, Claude Desktop and ChatGPT, see [MCP Server → Connect an AI agent](https://wiki.faucetdb.ai/mcp-server#connect-claude-code). Use a read-only role for agents unless they need to write.

## Troubleshooting

### "Login failed for user"

Check your username and password. For `sa` account, ensure the SA_PASSWORD meets SQL Server's complexity requirements.

### "Cannot open server requested by the login"

The database specified in `?database=` doesn't exist. Create it first or connect to an existing database.

### "TLS Handshake failed"

Add `&encrypt=disable` for local development, or `&TrustServerCertificate=true` for self-signed certificates. For production, configure proper TLS certificates.

### "connection refused"

SQL Server isn't running, the port is wrong, or TCP/IP isn't enabled. Check SQL Server Configuration Manager and ensure TCP/IP is enabled on port 1433.

## What's Next

- [Filter Syntax](https://wiki.faucetdb.ai/filter-syntax) -- query filtering operators
- [RBAC](https://wiki.faucetdb.ai/rbac) -- restrict access per role
- [API Reference](https://wiki.faucetdb.ai/api-reference) -- full REST API documentation
- [Database Connectors](https://wiki.faucetdb.ai/database-connectors) -- all connector DSN formats