Warp SQL Server MCP

# Security Policy ## 🔒 Overview Warp SQL Server MCP implements a **three-tier graduated security system** designed to prevent accidental or malicious database operations. This system provides granular control over what SQL operations are permitted, with secure defaults that prioritize safety over functionality. **🚀 First time setup?** Choose your preferred environment: - **[Warp Terminal Quick Start Guide](QUICKSTART.md)** - Original 5-minute setup - **[VS Code Quick Start Guide](QUICKSTART-VSCODE.md)** - Complete VS Code + Warp integration Both guides get the server running with secure defaults, then return here for detailed security configuration. ## 🛡️ Security Architecture ### Three-Tier Safety System The MCP server implements three independent security layers: | Security Level | Environment Variable | Default | Controls | | ----------------------------- | ----------------------------------------- | ------- | -------------------------------- | | **🔒 Read-Only Mode** | `SQL_SERVER_READ_ONLY` | `true` | Restricts to SELECT queries only | | **⚠️ Destructive Operations** | `SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS` | `false` | Controls INSERT/UPDATE/DELETE | | **🚨 Schema Changes** | `SQL_SERVER_ALLOW_SCHEMA_CHANGES` | `false` | Controls CREATE/DROP/ALTER | ### Security by Default **By default, the MCP server runs in maximum security mode:** - ✅ Only SELECT queries are permitted - ❌ No data modifications (INSERT/UPDATE/DELETE) - ❌ No schema changes (CREATE/DROP/ALTER) - ❌ No stored procedure execution - ❌ No administrative operations ## 📋 Security Configurations ### 🔒 Maximum Security (Default - Production Recommended) ```bash SQL_SERVER_READ_ONLY=true SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS=false SQL_SERVER_ALLOW_SCHEMA_CHANGES=false ``` **Permitted Operations:** - ✅ SELECT queries (with JOINs, CTEs, subqueries) - ✅ SHOW/DESCRIBE/EXPLAIN operations - ✅ Database and table inspection - ❌ All modification operations blocked **Use Cases:** Production monitoring, business intelligence, data analysis, reporting ### 📊 Data Analysis Mode ```bash SQL_SERVER_READ_ONLY=false SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS=true SQL_SERVER_ALLOW_SCHEMA_CHANGES=false ``` **Permitted Operations:** - ✅ All SELECT operations - ✅ INSERT/UPDATE/DELETE operations - ✅ Data import/export operations - ❌ Schema modifications blocked **Use Cases:** Data migration, ETL processes, application development, testing with real data ### 🛠️ Full Development Mode ```bash SQL_SERVER_READ_ONLY=false SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS=true SQL_SERVER_ALLOW_SCHEMA_CHANGES=true ``` **Permitted Operations:** - ✅ All SQL operations including DDL - ✅ CREATE/DROP/ALTER operations - ✅ Index and constraint management - ⚠️ **UNRESTRICTED ACCESS** **Use Cases:** Database development, schema migration, full development environments **⚠️ WARNING:** Only use this configuration in isolated development environments! ## 🔍 Security Validation ### Query Validation Engine The MCP server includes a comprehensive query validation engine that: 1. **Parses SQL statements** using regex patterns to identify operation types 2. **Enforces security policies** before query execution 3. **Provides clear error messages** when operations are blocked 4. **Logs security decisions** for audit purposes ### Validation Logic ```text ┌─────────────────┐ │ SQL Query │ └─────────────────┘ │ ▼ ┌─────────────────┐ ┌─────────────────┐ │ Read-Only Mode? │─Yes──▶│ Allow SELECT │ │ Enabled │ │ Block All Else │ └─────────────────┘ └─────────────────┘ │ No ▼ ┌─────────────────┐ ┌─────────────────┐ │ Destructive │─No───▶│ Block DML │ │ Ops Allowed? │ │ Operations │ └─────────────────┘ └─────────────────┘ │ Yes ▼ ┌─────────────────┐ ┌─────────────────┐ │ Schema Changes │─No───▶│ Block DDL │ │ Allowed? │ │ Operations │ └─────────────────┘ └─────────────────┘ │ Yes ▼ ┌─────────────────┐ │ Allow All │ │ Operations │ └─────────────────┘ ``` ## 🚨 Threat Model ### Threats Mitigated 1. **Accidental Data Loss** - Protection: Read-only mode prevents accidental DELETE/UPDATE - Impact: Prevents business-critical data corruption 2. **Unauthorized Schema Changes** - Protection: Schema change controls prevent DROP TABLE/ALTER - Impact: Prevents structural database damage 3. **Malicious Query Injection** - Protection: Query validation blocks dangerous patterns - Impact: Reduces attack surface for SQL injection 4. **Insider Threats** - Protection: Granular permission controls - Impact: Limits potential damage from compromised credentials 5. **Configuration Drift** - Protection: Secure defaults with explicit opt-in for dangerous operations - Impact: Prevents accidental exposure of production systems ### Threats NOT Mitigated ⚠️ **Important Limitations:** 1. **Network Security**: This MCP does not provide network-level security 2. **Authentication**: Relies on SQL Server authentication mechanisms 3. **Authorization**: Does not implement user-level access controls 4. **Encryption**: Does not enforce connection encryption (configurable separately) 5. **Audit Logging**: Provides basic logging but not comprehensive audit trails ## 🔐 GitHub Actions & CI/CD Security ### Token Permissions Policy All GitHub Actions workflows implement **least-privilege token permissions** to minimize attack surface: ```yaml permissions: contents: read # Read repository code only pull-requests: write # Create/update PRs (if needed) packages: write # Publish packages (if needed) ``` **Security Benefits:** - Prevents unauthorized repository modifications - Limits scope of potential token compromise - Follows GitHub's security best practices - Reduces supply chain attack vectors ### CLI Security Hardening **Atomic File Operations**: The CLI tool uses atomic file creation to prevent race conditions: ```javascript // Secure: Atomic file creation with O_CREAT | O_EXCL const fd = fs.openSync(configFile, flags, 0o600); ``` **Security Features:** - **TOCTOU Prevention**: Eliminates Time-of-Check Time-of-Use vulnerabilities - **Atomic Operations**: File creation and permission setting in single operation - **Race Condition Safe**: Multiple concurrent processes handled safely - **Secure Permissions**: Files created with 0o600 (owner read/write only) **Vulnerability Mitigated:** ```javascript // Insecure: Race condition vulnerable if (!fs.existsSync(file)) { // ⚠️ TOCTOU vulnerability fs.writeFileSync(file, data); // Another process could create file here } ``` ## 🏥 Production Deployment Guidelines ### Environment-Specific Recommendations > **📖 Complete Environment Variables Reference**: See **[ENV-VARS.md](ENV-VARS.md)** for comprehensive documentation of all environment variables and their defaults. #### Production Environment ```bash # MANDATORY SETTINGS FOR PRODUCTION SQL_SERVER_READ_ONLY=true SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS=false SQL_SERVER_ALLOW_SCHEMA_CHANGES=false # RECOMMENDED SECURITY SETTINGS SQL_SERVER_ENCRYPT=true SQL_SERVER_TRUST_CERT=false # Explicit for production - don't rely on auto-detection # CONNECTION LIMITS SQL_SERVER_CONNECT_TIMEOUT_MS=5000 SQL_SERVER_REQUEST_TIMEOUT_MS=10000 ``` #### Staging Environment ```bash # STAGING SETTINGS (Limited Write Access) SQL_SERVER_READ_ONLY=false SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS=true SQL_SERVER_ALLOW_SCHEMA_CHANGES=false # SECURITY SETTINGS SQL_SERVER_ENCRYPT=true SQL_SERVER_TRUST_CERT=false # Explicit for staging - ensure production-level SSL ``` #### Development Environment ```bash # DEVELOPMENT SETTINGS (Full Access) SQL_SERVER_READ_ONLY=false SQL_SERVER_ALLOW_DESTRUCTIVE_OPERATIONS=true SQL_SERVER_ALLOW_SCHEMA_CHANGES=true # RELAXED SETTINGS FOR DEVELOPMENT SQL_SERVER_ENCRYPT=false # SQL_SERVER_TRUST_CERT=true # Usually not needed - auto-detects development environment ``` ### Security Checklist Before deploying to production: - [ ] **Verify Read-Only Mode**: Confirm `SQL_SERVER_READ_ONLY=true` - [ ] **Test Security Enforcement**: Verify DML/DDL operations are blocked - [ ] **Enable Encryption**: Set `SQL_SERVER_ENCRYPT=true` for remote connections - [ ] **Validate Certificates**: Set `SQL_SERVER_TRUST_CERT=false` for production (see SSL Certificate Trust section) - [ ] **Configure Timeouts**: Set appropriate timeout values for your environment - [ ] **Review Connection Limits**: Configure connection pool settings - [ ] **Test Error Handling**: Verify blocked operations return clear error messages - [ ] **Monitor Logs**: Ensure security warnings appear in MCP logs - [ ] **Document Configuration**: Record security settings in deployment documentation - [ ] **Regular Reviews**: Schedule periodic security configuration reviews ## 🔐 SSL Certificate Trust Security ### Smart Environment Detection The MCP server uses **conservative security defaults** for SSL certificate trust through intelligent environment detection. This prevents accidental certificate trust in production environments. > **📖 Complete SSL Configuration**: See **[ENV-VARS.md#ssl-tls-security-settings](ENV-VARS.md#ssltls-security-settings)** for comprehensive SSL configuration options. #### Trust Behavior **🔧 Strong Development Indicators** (always auto-trust certificates): - `NODE_ENV=development` or `NODE_ENV=test` - `SQL_SERVER_HOST=localhost` or `SQL_SERVER_HOST=127.0.0.1` **⚠️ Weak Development Indicators** (only trust with explicit NODE_ENV): - `SQL_SERVER_HOST` ends with `.local` **AND** `NODE_ENV=development/test` - Private IP addresses **AND** `NODE_ENV=development/test`: - `192.168.x.x` ranges - `10.x.x.x` ranges - `172.16.x.x` through `172.31.x.x` ranges **🔒 Production Environment (default)** (require valid certificates): - All other scenarios, including: - `NODE_ENV=production` or no NODE_ENV set - Public domain names and IP addresses - Private IPs without explicit NODE_ENV=development/test - `.local` domains without explicit NODE_ENV=development/test #### Security Rationale **Why Conservative Defaults Matter:** 1. **Cloud Production Safety**: Prevents accidental certificate trust in cloud environments using private IP addresses (AWS VPC, Azure vNET, GCP VPC) 2. **Corporate Network Security**: `.local` domains require explicit development marking 3. **Container Deployment Safety**: Internal networking doesn't automatically trust certificates 4. **Zero-Trust Principle**: Explicit configuration required for relaxed security **Examples:** ```bash # ✅ TRUSTED (Strong indicators) NODE_ENV=development SQL_SERVER_HOST=localhost NODE_ENV=test SQL_SERVER_HOST=127.0.0.1 # ✅ TRUSTED (Weak indicator + explicit NODE_ENV) NODE_ENV=development SQL_SERVER_HOST=db.local NODE_ENV=development SQL_SERVER_HOST=192.168.1.100 # ❌ NOT TRUSTED (Production defaults) SQL_SERVER_HOST=db.local # Missing NODE_ENV SQL_SERVER_HOST=192.168.1.100 # Could be cloud production NODE_ENV=production SQL_SERVER_HOST=db.local # Explicit production ``` #### Explicit Override Options For explicit control, override the smart detection: ```bash # Force trust certificates (development only) SQL_SERVER_TRUST_CERT=true # Force require valid certificates (production) SQL_SERVER_TRUST_CERT=false ``` **⚠️ Security Warning**: Only use `SQL_SERVER_TRUST_CERT=true` in isolated development environments with self-signed certificates. ## 🔧 Configuration Validation ### Startup Security Summary The MCP server prints a configuration summary at startup showing current security status: ```text Connected to localhost:1433/master (Windows Auth) Security: 🔒 SECURE (RO, DML-, DDL-) ``` ### Security Status Indicators | Indicator | Meaning | Security Level | | ----------- | ------------------------------------------- | -------------- | | `🔒 SECURE` | Maximum security - read-only mode | **Secure** | | `⚠️ UNSAFE` | Reduced security - write operations allowed | **Caution** | | `RO` | Read-only mode enabled | **Secure** | | `RW` | Read-write mode enabled | **Caution** | | `DML-` | Destructive operations blocked | **Secure** | | `DML+` | Destructive operations allowed | **Caution** | | `DDL-` | Schema changes blocked | **Secure** | | `DDL+` | Schema changes allowed | **High Risk** | ### Runtime Security Information Every query response includes current security status: ```json { "safetyInfo": { "readOnlyMode": true, "destructiveOperationsAllowed": false, "schemaChangesAllowed": false } } ``` ## 🛠️ Testing Security Features ### Security Test Suite The MCP server includes comprehensive security tests: ```bash # Run all security-related tests npm test -- --grep "safety\|security\|validation" # Test security configuration npm test -- --grep "validateQuery" ``` ### Manual Security Testing #### Test Read-Only Mode ```sql -- This should work in read-only mode SELECT * FROM Users LIMIT 10; -- These should be blocked in read-only mode INSERT INTO Users (name) VALUES ('test'); UPDATE Users SET name = 'test' WHERE id = 1; DELETE FROM Users WHERE id = 1; ``` #### Test DML Protection ```sql -- With destructive operations disabled, these should be blocked INSERT INTO Products (name) VALUES ('test'); UPDATE Products SET price = 10 WHERE id = 1; DELETE FROM Products WHERE id = 1; ``` #### Test DDL Protection ```sql -- With schema changes disabled, these should be blocked CREATE TABLE TestTable (id INT); ALTER TABLE Products ADD COLUMN description TEXT; DROP TABLE TestTable; ``` ## 📞 Security Issue Reporting ### Reporting Security Vulnerabilities If you discover a security vulnerability, please: 1. **Do NOT create a public GitHub issue** 2. **Email security concerns privately** to the maintainer 3. **Provide detailed information** about the vulnerability 4. **Allow reasonable time** for assessment and patching ### Security Response Process 1. **Acknowledgment**: Security reports are acknowledged within 48 hours 2. **Assessment**: Vulnerability is assessed and prioritized within 7 days 3. **Patching**: Critical issues are patched within 14 days 4. **Disclosure**: Coordinated disclosure after patch is available 5. **Credit**: Security researchers are credited (if desired) ## 📚 Additional Resources - [OWASP SQL Injection Prevention](https://owasp.org/www-project-cheat-sheets/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html) - [Microsoft SQL Server Security Documentation](https://learn.microsoft.com/en-us/sql/relational-databases/security/) - [CIS Database Security Guidelines](https://www.cisecurity.org/controls/v8/) --- **Remember**: Security is a layered approach. This MCP's safety features are one layer in your overall database security strategy.