Kiro + SQL Server MCP

The SQL Server MCP server gives Kiro live access to your Microsoft SQL Server database — tables, columns, relationships, indexes, stored procedures, and views. Instead of pasting schema manually, Kiro reads it directly and generates accurate queries, EF Core models, and migrations.

What You Can Do

• “Write a query to find all orders placed in the last 30 days with their customer names”
• “Generate Entity Framework Core models for the Orders database”
• “Create a migration to add a soft-delete column to all tables that don’t have one”
• “What indexes exist on the Products table? Should I add any?”
• “Write a stored procedure that calculates monthly revenue by product category”
• “Explain the relationships in the schema — which tables join to which?”

---

Prerequisites

• Microsoft SQL Server (any edition — Express, Developer, Standard, or cloud: Azure SQL)
• SQL Server user with read access to the target database
• The MCP server package installed

---

Installation

npm install -g @executeautomation/database-server
# or the official Microsoft SQL Server MCP package
npm install -g mcp-server-mssql

---

Configuration

.kiro/mcp.json:

{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "mcp-server-mssql"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_PORT": "1433",
        "MSSQL_DATABASE": "YourDatabaseName",
        "MSSQL_USER": "${MSSQL_USER}",
        "MSSQL_PASSWORD": "${MSSQL_PASSWORD}",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "true"
      }
    }
  }
}

Set credentials in your environment (never in the JSON file):

# Linux/macOS
export MSSQL_USER="your_db_user"
export MSSQL_PASSWORD="your_db_password"

# Windows PowerShell
$env:MSSQL_USER = "your_db_user"
$env:MSSQL_PASSWORD = "your_db_password"

Azure SQL configuration:

{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "mcp-server-mssql"],
      "env": {
        "MSSQL_HOST": "your-server.database.windows.net",
        "MSSQL_PORT": "1433",
        "MSSQL_DATABASE": "YourDatabase",
        "MSSQL_USER": "${MSSQL_USER}",
        "MSSQL_PASSWORD": "${MSSQL_PASSWORD}",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

---

What Kiro Can Access

Capability	Description
List databases	All databases on the server
List tables	Tables in the connected database
Describe table	Columns, data types, nullability, defaults
Read relationships	Foreign key relationships between tables
List indexes	Existing indexes on tables
List views	Database views and their definitions
List stored procedures	Procedure names and parameters
Execute SELECT queries	Read data for context (read-only by default)

---

Practical Examples

Generate EF Core models:

User: Generate Entity Framework Core model classes for all tables in the database

Kiro:
1. Reads all tables from MSSQL MCP
2. Reads columns, data types, and relationships
3. Generates C# entity classes with correct navigation properties
4. Generates DbContext with DbSet properties and OnModelCreating configuration

Write an accurate query:

User: Write a query to find customers who placed orders in January but not February, with their total January spend

Kiro:
1. Reads the schema (Customers, Orders, OrderItems tables)
2. Understands column names and join conditions
3. Writes a correct query without guessing column names

Spot missing indexes:

User: Review the Orders table indexes and suggest any improvements

Kiro:
1. Reads table structure and existing indexes
2. Looks at foreign key columns (commonly under-indexed)
3. Recommends composite indexes for common query patterns

Create a migration:

User: Add soft delete (DeletedAt DATETIME2 NULL) to any table that doesn't already have it

Kiro:
1. Reads all tables in the database
2. Identifies which ones are missing a DeletedAt column
3. Generates a SQL migration script
4. Also generates the corresponding EF Core migration code

---

Read-Only vs Read-Write Mode

The MCP server should be configured as read-only for development use — Kiro reads the schema and data for context but does not modify the database directly. Kiro generates SQL scripts or EF Core migrations that you then review and run.

If you need Kiro to execute DDL/DML directly (not recommended for production), the MCP server can be configured with a user that has write permissions — but this should only be used with a local development database.

---

Using Windows Authentication

If your SQL Server uses Windows Authentication instead of SQL username/password:

{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "mcp-server-mssql"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "YourDatabase",
        "MSSQL_WINDOWS_AUTH": "true"
      }
    }
  }
}

This uses the current Windows user’s credentials — no password needed in the config.

---

Troubleshooting

“Login failed for user” — Verify the SQL username and password. If using SQL Auth, check that SQL Server Authentication is enabled (not just Windows Auth).

“Cannot connect to server” — Check that SQL Server is running and that TCP/IP is enabled in SQL Server Configuration Manager.

“SSL/TLS error” — Set MSSQL_TRUST_SERVER_CERTIFICATE: "true" for local development servers with self-signed certificates.

Azure SQL firewall — Ensure your local IP is in the Azure SQL firewall allow list.