Skip to main content

Microsoft SQL Server Integration

Integrate MCP Express with Microsoft SQL Server (MSSQL) databases to create tools that can query and interact with your data. This integration allows you to execute SQL queries against your MSSQL database and use the results in your MCP tools.

Note: This integration currently uses a thin client driver, which may have limited functionality compared to full client implementations. Advanced features may not be available.

Configuration​

Connection Parameters​

ParameterRequiredDescription
hostYesIP address or URL of the SQL Server
databaseYesName of the database to connect to
userYesUsername for authentication
passwordYesPassword for authentication
QueryYesSQL query template for dynamic input using variable placeholders
PortNoDatabase port (defaults to 1433)
TimeoutNoConnection timeout in seconds (defaults to 10)
Dangerous KeywordsNoList of SQL keywords to block for security purposes

Setting Up MSSQL Integration​

  1. Select MSSQL Integration: In your MCP server dashboard, choose "MSSQL" from the available integrations
  2. Configure Connection: Enter your database host, database name, username, and password
  3. Set Port and Timeout: Optionally customize the port (default: 1433) and connection timeout (default: 10 seconds)
  4. Define Query Template: Create your SQL query using variable placeholders to accept dynamic inputs
  5. Configure Security: Add any SQL keywords to block via the Dangerous Keywords list
  6. Test Connection: Use the built-in connection test to verify your setup works correctly

MSSQL Configuration

Query Templates​

The MSSQL integration uses templating to create dynamic SQL queries based on tool inputs. This allows you to safely parameterize your queries while maintaining flexibility.

Template Syntax​

Variables from tool inputs can be referenced using {{ variable_name }} syntax:

Example:

SELECT * from users where id={{userId}}

Example Usages​

For all examples below, we assume the tool accepts the following input parameters:

Example Simple Tool Input

Static Queries (No Input Parameters Required)​

The MSSQL integration can execute fixed SQL queries, useful for performing predefined tasks. For example, counting the number of users living in postal code 71250:

SELECT COUNT(*) as user_count
FROM users
WHERE postalCode='71250'

Dynamic Template Queries​

The MSSQL integration supports parameterized queries that accept user input. For example, counting users in a postal code specified by the user:

SELECT COUNT(*) as user_count
FROM users
WHERE postalCode='{{postalCode}}'

Complete Query from LLM (Requires Special Attention)​

The MSSQL integration can accept complete SQL queries generated by the LLM. Important: Carefully evaluate all security risks, as the LLM may generate incorrect or potentially harmful queries based on user input.

For this scenario, configure the input as follows:

Example Full Query Input

The input query is then forwarded directly to the integration:

{{query}}

Security Features​

Dangerous Keywords Blocking​

The Dangerous Keywords parameter enables you to specify SQL keywords that will be blocked in queries, helping prevent potentially harmful operations:

Common keywords to block:

  • DROP
  • DELETE
  • TRUNCATE
  • ALTER
  • CREATE
  • GRANT
  • REVOKE

Best Practices for Query Templates​

  • Use parameterized queries with variable placeholders rather than string concatenation
  • Validate and sanitize all user inputs before they reach the query template
  • Limit database user permissions to only what's necessary for the tool's function
  • Use read-only database users when tools only need to retrieve data

Best Practices​

Security​

  • Use strong passwords and secure credential storage
  • Create database users with minimal required permissions
  • Always use the Dangerous Keywords list to block destructive operations
  • Never expose raw database credentials in tool configurations
  • Use SSL/TLS connections when connecting to remote databases

Performance​

  • Optimize your query templates for efficiency
  • Use appropriate indexes on frequently queried columns
  • Set reasonable connection timeouts to prevent hanging connections
  • Consider connection pooling for high-traffic scenarios
  • Limit result set sizes to prevent memory issues

Reliability​

  • Test all query templates thoroughly before deployment
  • Implement proper error handling for connection failures
  • Monitor query execution times and optimize slow queries
  • Have backup and recovery plans for database operations
  • Use transactions for operations that modify data

Troubleshooting​

Connection Issues​

  • Verify the host, port, and database name are correct
  • Check that the database user has proper permissions
  • Ensure the SQL Server is running and accessible
  • Verify firewall rules allow connections on the specified port
  • Check if the connection timeout is sufficient for your network
  • Ensure TCP/IP connections are enabled in SQL Server Configuration Manager

Authentication Problems​

  • Confirm the username and password are correct
  • Verify the database user has been created with appropriate privileges
  • Ensure the password has not expired or been changed
  • Check if SQL Server authentication mode is set to "SQL Server and Windows Authentication mode"

Query Execution Issues​

  • Review the query template syntax for formatting errors
  • Verify all template variables are provided by the tool input
  • Check if Dangerous Keywords are inadvertently blocking legitimate operations
  • Examine SQL Server error logs for detailed diagnostic information
  • Ensure the database user has sufficient permissions for all operations in the query

Thin Client Limitations​

  • Some advanced SQL Server features may not be available with the thin client driver
  • If you encounter compatibility issues, verify that your queries use standard SQL syntax
  • Complex stored procedures or advanced T-SQL features may have limited support