SQL Server to REST API and MCP Server

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.

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 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.

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
• 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 for screenshots of this flow.

Via CLI

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

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

Or with a connection string:

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

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

faucet db test mydb

Or list tables via the API:

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

Expected response:

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

Step 3: Query Your Data

List all records

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

Filter records

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

Select specific fields

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

Paginate

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

Count records

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

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:

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

Update a record

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

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:

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:

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:

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:

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 decides which tables it can read or change. For Claude Code:

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. 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 -- query filtering operators
• RBAC -- restrict access per role
• API Reference -- full REST API documentation
• Database Connectors -- all connector DSN formats