# PMI MCP Server for SQL Server

Server MCP in Python che espone il tool `execute_query` e inoltra il testo SQL
ricevuto direttamente a Microsoft SQL Server tramite `pyodbc`.

Il server non analizza, riscrive, filtra o limita le query. Autorizzazioni,
permessi e guardrail SQL sono responsabilità del database e dell'utenza usata
nella stringa di connessione.

## Piattaforme supportate

Il codice del server è indipendente dal sistema operativo ed è predisposto per:

- Windows 10/11 e Windows Server
- macOS Intel e Apple Silicon
- Linux nelle distribuzioni supportate dal Microsoft ODBC Driver 18

La matrice CI esegue i test su Windows, macOS e Ubuntu.

## Requisiti comuni

- Python 3.10 o successivo
- Microsoft ODBC Driver 18 for SQL Server
- Un ODBC driver manager (`unixODBC`) su macOS/Linux; su Windows è già incluso
  nel sistema operativo
- Accesso a un'istanza Microsoft SQL Server

## Driver ODBC

### Windows

1. Installa il [Microsoft Visual C++ Redistributable][vc-redist] adatto
   all'architettura del sistema, se non è già presente.
2. Scarica e installa [Microsoft ODBC Driver 18 for SQL Server][odbc-download]
   scegliendo x64, x86 oppure ARM64 in base al sistema.
3. Verifica da PowerShell che il driver sia registrato:

```powershell
Get-OdbcDriver -Name "ODBC Driver 18 for SQL Server"
```

Python e il driver ODBC devono avere architetture compatibili. In una normale
installazione Windows x64, usa Python x64 e il driver x64.

### macOS

Con Homebrew:

```bash
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18
```

Il pacchetto installa anche `unixODBC` come dipendenza.

### Linux

Microsoft pubblica pacchetti per Ubuntu, Debian, RHEL, Oracle Linux, SLES,
Alpine e Azure Linux. Segui la [procedura ufficiale per la distribuzione
utilizzata][odbc-linux]. Ad esempio, dopo aver configurato il repository
Microsoft su Ubuntu/Debian:

```bash
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc-dev
```

## Installazione Python

Con `uv`:

```bash
uv sync
```

Con `pip` su macOS/Linux:

```bash
python -m venv .venv
source .venv/bin/activate
python -m pip install -e .
```

Con `pip` da PowerShell su Windows:

```powershell
py -3 -m venv .venv
.\.venv\Scripts\python.exe -m pip install -e .
```

## Configurazione

Usa `.env.example` come riferimento e passa le variabili nell'ambiente del
processo MCP. La sola variabile obbligatoria è:

```dotenv
SQLSERVER_CONNECTION_STRING=DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=my_database;UID=my_user;PWD=my_password;Encrypt=yes;TrustServerCertificate=no
```

Variabili opzionali:

| Variabile | Default | Descrizione |
| --- | --- | --- |
| `SQLSERVER_AUTOCOMMIT` | `false` | Se `false`, il server esegue commit a fine richiesta e rollback in caso di errore |
| `SQLSERVER_CONNECTION_TIMEOUT` | `30` | Timeout della connessione in secondi |
| `MCP_TRANSPORT` | `stdio` | `stdio` oppure `streamable-http` |
| `MCP_HOST` | `127.0.0.1` | Host usato con Streamable HTTP |
| `MCP_PORT` | `8000` | Porta usata con Streamable HTTP |
| `MCP_BEARER_TOKEN` | — | Token opzionale, lungo almeno 32 caratteri, richiesto a ogni chiamata HTTP |
| `MCP_TLS_CERT_FILE` | — | Percorso del certificato TLS in formato PEM |
| `MCP_TLS_KEY_FILE` | — | Percorso della chiave privata TLS in formato PEM |
| `MCP_TLS_KEY_PASSWORD` | — | Password opzionale della chiave privata |

Il file `.env` non viene caricato automaticamente dal processo: configura le
variabili nel client MCP o impostale nell'ambiente prima dell'avvio. Questo
evita differenze di caricamento tra Windows e sistemi Unix.

Su Windows è possibile usare anche l'autenticazione integrata dell'identità che
avvia il processo MCP:

```dotenv
SQLSERVER_CONNECTION_STRING=DRIVER={ODBC Driver 18 for SQL Server};SERVER=my-server;DATABASE=my_database;Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=no
```

Per verificare che Python rilevi il driver installato:

```bash
python -c "import pyodbc; print(pyodbc.drivers())"
```

L'output deve includere `ODBC Driver 18 for SQL Server`.

## Autenticazione Bearer

Imposta `MCP_BEARER_TOKEN` per proteggere tutte le richieste Streamable HTTP.
Il token deve contenere almeno 32 caratteri. Puoi generarne uno casuale su tutte
le piattaforme con Python:

```bash
python -c "import secrets; print(secrets.token_urlsafe(32))"
```

Esempio di avvio:

```bash
MCP_TRANSPORT=streamable-http \
MCP_BEARER_TOKEN='inserire-un-token-casuale-di-almeno-32-caratteri' \
SQLSERVER_CONNECTION_STRING='...' \
uv run pmi-mcp-server
```

Il client deve inviare questo header:

```http
Authorization: Bearer inserire-un-token-casuale-di-almeno-32-caratteri
```

Una richiesta priva del token corretto riceve `401 Unauthorized`. Il confronto
avviene in tempo costante e il token non viene scritto nei log. Se la variabile
non è configurata, l'autenticazione HTTP resta disabilitata. Usa sempre HTTPS
quando il token attraversa una rete non fidata.

## HTTPS

Il trasporto Streamable HTTP usa HTTPS automaticamente quando sono configurati
entrambi `MCP_TLS_CERT_FILE` e `MCP_TLS_KEY_FILE`. Se uno dei due manca o il
percorso non esiste, il server termina subito con un errore di configurazione.
I percorsi possono usare la sintassi nativa di Windows, macOS o Linux.

Esempio macOS/Linux:

```bash
MCP_TRANSPORT=streamable-http \
MCP_HOST=127.0.0.1 \
MCP_PORT=8443 \
MCP_TLS_CERT_FILE=/percorso/server-cert.pem \
MCP_TLS_KEY_FILE=/percorso/server-key.pem \
SQLSERVER_CONNECTION_STRING='...' \
uv run pmi-mcp-server
```

Esempio Windows PowerShell:

```powershell
$env:MCP_TRANSPORT = "streamable-http"
$env:MCP_HOST = "127.0.0.1"
$env:MCP_PORT = "8443"
$env:MCP_BEARER_TOKEN = "inserire-un-token-casuale-di-almeno-32-caratteri"
$env:MCP_TLS_CERT_FILE = "C:\certificati\server-cert.pem"
$env:MCP_TLS_KEY_FILE = "C:\certificati\server-key.pem"
$env:SQLSERVER_CONNECTION_STRING = "..."
.\.venv\Scripts\pmi-mcp-server.exe
```

Il client si collega quindi a `https://127.0.0.1:8443/mcp`. Il certificato deve
contenere l'indirizzo o il nome host usato dal client nel campo Subject
Alternative Name e deve essere considerato attendibile dal sistema del client.
Per ambienti di produzione è generalmente preferibile terminare TLS su un
reverse proxy o load balancer e lasciare il server MCP su una rete privata.

Esempio generico di client MCP con URL e Bearer token:

```json
{
  "mcpServers": {
    "sql-server": {
      "url": "https://127.0.0.1:8443/mcp",
      "headers": {
        "Authorization": "Bearer inserire-un-token-casuale-di-almeno-32-caratteri"
      }
    }
  }
}
```

La sintassi esatta degli header e l'eventuale lettura del token da una variabile
d'ambiente dipendono dal client MCP utilizzato. Non salvare token reali nel
repository.

## Avvio

### macOS/Linux

Trasporto locale `stdio`:

```bash
SQLSERVER_CONNECTION_STRING='...' uv run pmi-mcp-server
```

Trasporto Streamable HTTP:

```bash
MCP_TRANSPORT=streamable-http MCP_PORT=8000 \
SQLSERVER_CONNECTION_STRING='...' uv run pmi-mcp-server
```

L'endpoint predefinito è `http://127.0.0.1:8000/mcp`.

### Windows PowerShell

Trasporto locale `stdio`:

```powershell
$env:SQLSERVER_CONNECTION_STRING = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=my_database;UID=my_user;PWD=my_password;Encrypt=yes;TrustServerCertificate=no"
.\.venv\Scripts\pmi-mcp-server.exe
```

Trasporto Streamable HTTP:

```powershell
$env:MCP_TRANSPORT = "streamable-http"
$env:MCP_HOST = "127.0.0.1"
$env:MCP_PORT = "8000"
$env:SQLSERVER_CONNECTION_STRING = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=my_database;UID=my_user;PWD=my_password;Encrypt=yes;TrustServerCertificate=no"
.\.venv\Scripts\pmi-mcp-server.exe
```

### Configurazione del client MCP

macOS/Linux:

```json
{
  "mcpServers": {
    "sql-server": {
      "command": "uv",
      "args": [
        "--directory",
        "/percorso/assoluto/pmi-mcp-server",
        "run",
        "pmi-mcp-server"
      ],
      "env": {
        "SQLSERVER_CONNECTION_STRING": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=my_database;UID=my_user;PWD=my_password;Encrypt=yes;TrustServerCertificate=no"
      }
    }
  }
}
```

Windows (nei file JSON i backslash devono essere duplicati):

```json
{
  "mcpServers": {
    "sql-server": {
      "command": "C:\\percorso\\pmi-mcp-server\\.venv\\Scripts\\pmi-mcp-server.exe",
      "args": [],
      "env": {
        "SQLSERVER_CONNECTION_STRING": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=my_database;UID=my_user;PWD=my_password;Encrypt=yes;TrustServerCertificate=no"
      }
    }
  }
}
```

## Tool MCP

### `execute_query`

Input:

```json
{
  "query": "SELECT TOP (10) * FROM dbo.Customers"
}
```

Output:

```json
{
  "result_sets": [
    {
      "columns": ["CustomerId", "Name"],
      "rows": [[1, "Acme"]],
      "row_count": 1
    }
  ]
}
```

Il tool restituisce tutti i result set. `decimal`, date/ore e UUID vengono
serializzati come stringhe; i valori binari sono codificati in Base64. Le query
senza righe restituiscono comunque un result set con `columns` e `rows` vuoti.

## Guardrail SQL

Il server inoltra il testo SQL senza analizzarlo. I controlli devono quindi
essere applicati da SQL Server secondo il principio del privilegio minimo.

### Architettura consigliata

1. Crea un database di reporting separato, ad esempio `McpReporting`. Non
   collegare l'utenza MCP direttamente al database transazionale.
2. Popola il database tramite ETL, replica o snapshot gestiti da un'identità
   diversa da quella MCP.
3. Crea uno schema di esposizione per ogni reparto, ad esempio `sales`,
   `finance` e `hr`.
4. Inserisci negli schemi di reparto esclusivamente viste. Conserva le tabelle
   materializzate in schemi interni, non accessibili all'utenza MCP.
5. Crea un login, un utente e un ruolo differenti per ogni reparto. Se più
   reparti devono essere isolati, avvia un'istanza MCP per reparto con la sua
   stringa di connessione.
6. Concedi `SELECT` alle singole viste, non all'intero schema. Un
   `GRANT SELECT ON SCHEMA` comprenderebbe anche eventuali tabelle aggiunte in
   futuro.
7. Non aggiungere l'utenza ai ruoli `db_owner`, `db_datareader`,
   `db_datawriter`, `db_ddladmin`, `db_securityadmin` o `sysadmin`.

Le viste sono utilizzabili come barriera di sicurezza senza concedere accesso
diretto alle tabelle sottostanti. Quando possibile, usa `SCHEMABINDING`, elenca
esplicitamente le colonne ed escludi o maschera i dati sensibili.

### 1. Database e schemi separati

Esegui come amministratore e sostituisci nomi e oggetti di esempio:

```sql
USE [master];
GO

IF DB_ID(N'McpReporting') IS NULL
BEGIN
    CREATE DATABASE [McpReporting];
END;
GO

USE [McpReporting];
GO

-- Schemi interni: contengono le tabelle alimentate dall'ETL.
CREATE SCHEMA [internal_sales] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [internal_finance] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [internal_hr] AUTHORIZATION [dbo];
GO

-- Schemi pubblicati: devono contenere esclusivamente viste.
CREATE SCHEMA [sales] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [finance] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [hr] AUTHORIZATION [dbo];
GO
```

Se uno schema esiste già, ometti il relativo `CREATE SCHEMA`.

### 2. Esporre esclusivamente viste

Esempio per il reparto vendite:

```sql
USE [McpReporting];
GO

CREATE VIEW [sales].[v_customers]
WITH SCHEMABINDING
AS
    SELECT
        [CustomerId],
        [CustomerName],
        [CountryCode]
    FROM [internal_sales].[Customers];
GO

CREATE VIEW [sales].[v_orders]
WITH SCHEMABINDING
AS
    SELECT
        [OrderId],
        [CustomerId],
        [OrderDate],
        [NetAmount]
    FROM [internal_sales].[Orders];
GO
```

Non usare `SELECT *` nelle viste. Con `SCHEMABINDING`, gli oggetti referenziati
devono trovarsi nello stesso database ed essere indicati con nome a due parti.

### 3. Utente e ruolo dedicati

Il login può essere SQL Server, Windows o Microsoft Entra. Dopo averlo creato
con il sistema di identità scelto, mappalo nel solo database di reporting:

```sql
USE [McpReporting];
GO

CREATE USER [mcp_sales_user] FOR LOGIN [mcp_sales_login];
GO

CREATE ROLE [mcp_sales_reader] AUTHORIZATION [dbo];
GO

ALTER ROLE [mcp_sales_reader] ADD MEMBER [mcp_sales_user];
GO

GRANT CONNECT TO [mcp_sales_reader];
GO
```

Non creare lo stesso utente negli altri database e non concedere
`CONNECT ANY DATABASE` al login.

### 4. Consentire `SELECT` solo sulle viste autorizzate

La variante più restrittiva concede ogni vista esplicitamente:

```sql
USE [McpReporting];
GO

GRANT SELECT ON OBJECT::[sales].[v_customers]
    TO [mcp_sales_reader];
GRANT SELECT ON OBJECT::[sales].[v_orders]
    TO [mcp_sales_reader];
GO

-- Nessun accesso diretto alle tabelle interne.
DENY SELECT ON SCHEMA::[internal_sales]
    TO [mcp_sales_reader];
DENY SELECT ON SCHEMA::[internal_finance]
    TO [mcp_sales_reader];
DENY SELECT ON SCHEMA::[internal_hr]
    TO [mcp_sales_reader];
GO
```

Ripeti il modello con un ruolo diverso per `finance` e `hr`. Quando viene
pubblicata una nuova vista, concedila esplicitamente dopo la revisione.

### 5. Bloccare scrittura, esecuzione e DDL

L'assenza di permessi è già sufficiente a negare le operazioni; i `DENY`
seguenti rendono esplicita la policy e impediscono che permessi meno restrittivi
ereditati da altri ruoli le riabilitino:

```sql
USE [McpReporting];
GO

-- DML: blocca INSERT, UPDATE, DELETE e quindi anche MERGE.
DENY INSERT, UPDATE, DELETE
    TO [mcp_sales_reader];

-- Stored procedure e funzioni eseguibili.
DENY EXECUTE
    TO [mcp_sales_reader];

-- Controllo, modifica degli oggetti e cambio di proprietà.
DENY ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION
    TO [mcp_sales_reader];

-- Creazione di oggetti nel database.
DENY CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION,
     CREATE SYNONYM, CREATE TYPE, CREATE SCHEMA
    TO [mcp_sales_reader];
GO
```

Questi permessi bloccano anche:

- `TRUNCATE TABLE`, perché richiede `ALTER` sulla tabella;
- `DROP` e `ALTER` degli oggetti;
- `SELECT ... INTO` permanente, perché l'utente non può creare tabelle;
- `WITH ... INSERT`, `WITH ... UPDATE`, `WITH ... DELETE` e
  `WITH ... MERGE`, perché mancano i relativi permessi DML;
- chiamate a stored procedure tramite `EXEC`/`EXECUTE`.

`WITH` non è un permesso SQL Server separato: introduce una CTE che può essere
usata da `SELECT`, `INSERT`, `UPDATE`, `MERGE` o `DELETE`. Con questa policy è
consentita solo quando l'operazione finale è un `SELECT` autorizzato.

### 6. Rendere il database read-only, quando possibile

Per snapshot o database aggiornati solo tramite sostituzione completa, una
seconda barriera consiste nel renderli `READ_ONLY` dopo il caricamento:

```sql
USE [master];
GO

ALTER DATABASE [McpReporting]
    SET READ_ONLY
    WITH ROLLBACK IMMEDIATE;
GO

SELECT
    [name],
    [is_read_only]
FROM sys.databases
WHERE [name] = N'McpReporting';
GO
```

Non applicare questa opzione se l'ETL deve aggiornare continuamente lo stesso
database. In quel caso mantieni `READ_WRITE`, usa un'identità ETL separata e
lascia l'utente MCP con i soli permessi descritti sopra.

### 7. Query di collaudo

Esegui come amministratore questa verifica, che non modifica dati:

```sql
USE [McpReporting];
GO

EXECUTE AS USER = N'mcp_sales_user';
GO

-- Deve riuscire.
SELECT TOP (10)
    [CustomerId],
    [CustomerName]
FROM [sales].[v_customers];

-- Deve riuscire: WITH termina con SELECT.
WITH [recent_customers] AS
(
    SELECT [CustomerId], [CustomerName]
    FROM [sales].[v_customers]
)
SELECT *
FROM [recent_customers];

-- SELECT sulla vista deve valere 1; tutti gli altri controlli devono valere 0.
SELECT
    HAS_PERMS_BY_NAME(
        N'sales.v_customers', N'OBJECT', N'SELECT'
    ) AS [CanSelectPublishedView],
    HAS_PERMS_BY_NAME(
        N'internal_sales.Customers', N'OBJECT', N'SELECT'
    ) AS [CanSelectInternalTable],
    HAS_PERMS_BY_NAME(
        N'McpReporting', N'DATABASE', N'INSERT'
    ) AS [CanInsert],
    HAS_PERMS_BY_NAME(
        N'McpReporting', N'DATABASE', N'UPDATE'
    ) AS [CanUpdate],
    HAS_PERMS_BY_NAME(
        N'McpReporting', N'DATABASE', N'DELETE'
    ) AS [CanDelete],
    HAS_PERMS_BY_NAME(
        N'McpReporting', N'DATABASE', N'EXECUTE'
    ) AS [CanExecute],
    HAS_PERMS_BY_NAME(
        N'McpReporting', N'DATABASE', N'CREATE TABLE'
    ) AS [CanCreateTable];
GO

REVERT;
GO
```

Accedi poi con il login MCP reale ed esegui singolarmente le query seguenti.
Ognuna deve fallire per autorizzazione:

```sql
UPDATE [sales].[v_customers]
SET [CustomerName] = N'blocked'
WHERE [CustomerId] = -1;
```

```sql
DELETE FROM [sales].[v_customers]
WHERE [CustomerId] = -1;
```

```sql
WITH [target] AS
(
    SELECT *
    FROM [sales].[v_customers]
)
DELETE FROM [target]
WHERE [CustomerId] = -1;
```

```sql
SELECT *
FROM [internal_sales].[Customers];
```

```sql
CREATE TABLE [sales].[ShouldFail] ([Id] int NOT NULL);
```

Verifica inoltre che la stringa `SQLSERVER_CONNECTION_STRING` del server MCP
utilizzi `mcp_sales_login` e punti esplicitamente a `McpReporting`.

### Limite della policy basata sui permessi

I permessi SQL Server impediscono accesso, modifica ed esecuzione sugli oggetti,
ma non costituiscono una allowlist lessicale delle parole SQL: istruzioni di
sessione come `SET` o controllo di transazione possono essere accettate senza
consentire modifiche ai dati. Per bloccare sintatticamente qualsiasi testo che
non inizi con `SELECT` o `WITH` servirebbe anche un parser nel server o un proxy
SQL. La configurazione sopra mantiene invece i guardrail interamente nel
database, come previsto dal progetto, e rende inefficaci le operazioni non
autorizzate.

Riferimenti Microsoft: [sicurezza tramite viste][sql-create-view],
[permessi sugli schema][sql-schema-permissions], [CTE `WITH`][sql-cte] e
[database `READ_ONLY`][sql-read-only].

## Test

```bash
uv run --extra dev pytest
```

Su Windows senza `uv`:

```powershell
.\.venv\Scripts\python.exe -m pytest
```

[odbc-download]: https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server
[odbc-linux]: https://learn.microsoft.com/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
[vc-redist]: https://learn.microsoft.com/cpp/windows/latest-supported-vc-redist
[sql-create-view]: https://learn.microsoft.com/sql/t-sql/statements/create-view-transact-sql
[sql-schema-permissions]: https://learn.microsoft.com/sql/t-sql/statements/grant-schema-permissions-transact-sql
[sql-cte]: https://learn.microsoft.com/sql/t-sql/queries/with-common-table-expression-transact-sql
[sql-read-only]: https://learn.microsoft.com/sql/t-sql/statements/alter-database-transact-sql-set-options