### Run Example Console Application

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/UsingMcpWithCSharp.md

Command to navigate to the example directory and run the MCP client console application.

```bash
cd mssqlMCP/Examples
dotnet run McpConsoleExample.cs
```

--------------------------------

### Example Copilot Agent Response (mssql_get_table_metadata)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

An example response for retrieving table metadata, demonstrating the `content` and `structuredContent` fields.

```json
{
  "jsonrpc": "2.0",
  "id": "copilot-request-1",
  "result": {
    "content": [
      {
        "type": "text",
        "text": "Table metadata retrieved successfully"
      }
    ],
    "structuredContent": {
      "tables": [
        {
          "name": "Users",
          "schema": "dbo",
          "columns": [
            {
              "name": "UserId",
              "dataType": "int",
              "isNullable": false,
              "isPrimaryKey": true
            },
            { "name": "Username", "dataType": "nvarchar", "isNullable": false },
            { "name": "Email", "dataType": "nvarchar", "isNullable": false }
          ]
        }
      ]
    },
    "isError": false
  }
}
```

--------------------------------

### MSSQL Initialize Connection Examples

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

C# examples for initializing SQL Server connections using the `mssql_initialize_connection` tool. You can initialize a default connection or a specific named connection.

```csharp
// Initialize the default connection
var result = await CallToolAsync("mssql_initialize_connection", new { connectionName = "DefaultConnection" });
```

```csharp
// Or specify a specific connection
var adventureWorksResult = await CallToolAsync("mssql_initialize_connection", new { connectionName = "AdventureWorks" });
```

--------------------------------

### Start Server with Encryption Enabled (Script)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

Execute the Start-MCP-Encrypted.ps1 script to launch the server with connection string encryption. This script manages key generation and environment variable setup.

```powershell
# Start the server with encryption enabled
$env:MSSQL_MCP_KEY = "ReplaceWithMyKeyForTheConnectionEncryption"
$env:MSSQL_MCP_API_KEY = "ReplaceWithMyApiKeyForClientAccess"
./Scripts/Start-MCP-Encrypted.ps1
```

--------------------------------

### Start Server with Encrypted Connection Strings (Script)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

Use the provided PowerShell script to automatically handle encryption key generation (if needed) and start the MCP server with encryption enabled. The script will display the generated key for secure storage.

```powershell
# Option 2: Use the automated script that handles key generation and server startup
$env:MSSQL_MCP_KEY = "ReplaceWithMyKeyForTheConnectionEncryption"
$env:MSSQL_MCP_API_KEY = "ReplaceWithMyApiKeyForClientAccess"
./Scripts/Start-MCP-Encrypted.ps1
```

--------------------------------

### Copilot Agent Example: Get Table Metadata

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

An example of a Copilot Agent request to retrieve table metadata using the `tools/call` method.

```APIDOC
## Copilot Agent Example

When GitHub Copilot uses the MCP server, it sends requests to the server via the JSON-RPC format with the `tools/call` method. Here's an example of how Copilot would retrieve table metadata:

### Request Body
```json
// Copilot Agent request to get table metadata
{
  "jsonrpc": "2.0",
  "id": "copilot-request-1",
  "method": "tools/call",
  "params": {
    "name": "mssql_get_table_metadata",
    "arguments": {
      "connectionName": "p330d_PROTO",
      "schema": null
    }
  }
}
```
```

--------------------------------

### Start MCP Server with Encrypted Connections (PowerShell)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/Security.md

Execute the provided PowerShell script to start the mssqlMCP server with connection string encryption enabled.

```powershell
./Start-MCP-Encrypted.ps1
```

--------------------------------

### Set Environment Variables and Start mssqlMCP Server

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/QUICK_INSTALL.md

Set the encryption and API keys as environment variables in PowerShell and then start the mssqlMCP server using the provided script or by running the .NET application directly.

```powershell
$env:MSSQL_MCP_KEY = "abcd1234567891-UseSomethingElseForYourMCPKey="
$env:MSSQL_MCP_API_KEY = "efghij1234567891-UseSomethingElseForYourAPIKey="

# Start the MCP Server
Scripts\Start-MCP-Encrypted.ps1
# or just dotnet run from the root of the project
dotnet run
```

--------------------------------

### PowerShell Example: Execute Query

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

A PowerShell script demonstrating how to authenticate and execute a query against the MCP API.

```APIDOC
## PowerShell Example

```powershell
# Set API key
$apiKey = "your-api-key-here"
$headers = @{
    "Authorization" = "Bearer $apiKey"
    "Content-Type" = "application/json"
}

# Execute query
$body = @{
    jsonrpc = "2.0"
    id = 1
    method = "mssql_execute_query"
    params = @{
        query = "SELECT name FROM sys.databases"
        connectionName = "DefaultConnection"
    }
} | ConvertTo-Json -Depth 3

$response = Invoke-RestMethod -Uri "http://localhost:3001" -Method Post -Headers $headers -Body $body
```
```

--------------------------------

### MSSQL Get Database Objects by Type Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response for mssql_get_database_objects_by_type, showing table metadata including creation date.

```json
{
  "jsonrpc": "2.0",
  "id": 10,
  "result": {
    "objects": [
      {
        "name": "Customers",
        "schema": "dbo",
        "type": "TABLE",
        "createDate": "2023-01-01T00:00:00"
      },
      {
        "name": "Orders",
        "schema": "dbo",
        "type": "TABLE",
        "createDate": "2023-01-01T00:00:00"
      }
    ]
  }
}
```

--------------------------------

### Run mssqlMCP Server

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpcPowershellTesting.md

Start the mssqlMCP server using the dotnet CLI. Ensure the project path is correct.

```powershell
dotnet run --project "c:\Users\U00001\source\repos\MCP\mssqlMCP\mssqlMCP.csproj"
```

--------------------------------

### MSSQL Get Agent Jobs Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response containing a list of SQL Server Agent jobs with basic information.

```json
{
  "jsonrpc": "2.0",
  "id": 11,
  "result": {
    "jobs": [
      {
        "name": "DatabaseBackup",
        "description": "Daily backup job",
        "enabled": true,
        "category": "Database Maintenance",
        "lastRunOutcome": "Succeeded",
        "lastRunDate": "2025-06-28T01:00:00"
      }
    ]
  }
}
```

--------------------------------

### Start MCP Server with Encryption

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/CopilotAgent.md

Use this PowerShell script to start the MCP server with encryption enabled and API key authentication configured. This is the recommended method for secure access.

```powershell
# Start with encryption enabled and set up API key authentication (recommended)
./Scripts/Start-MCP-Encrypted.ps1

```

--------------------------------

### Start MCP Server with Encryption

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

Start the MCP server with encryption and API security enabled. Remember to replace the placeholder keys with your actual secure keys.

```powershell
# Start with encryption and API security enabled (recommended)
$env:MSSQL_MCP_KEY = "ReplaceWithMyKeyForTheConnectionEncryption"
$env:MSSQL_MCP_API_KEY = "ReplaceWithMyApiKeyForClientAccess"
./Scripts/Start-MCP-Encrypted.ps1
```

--------------------------------

### MSSQL Get Table Metadata with Schema Filtering

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

C# examples demonstrating how to use the `mssql_get_table_metadata` tool with schema filtering. This is useful for targeting specific schemas in large databases.

```csharp
// Get all database metadata (all schemas)
var metadata = await CallToolAsync("mssql_get_table_metadata", new { connectionName = "DefaultConnection" });
```

```csharp
// Get metadata for a specific connection (all schemas)
var awMetadata = await CallToolAsync("mssql_get_table_metadata", new { connectionName = "AdventureWorks" });
```

```csharp
// Get metadata for tables in a specific schema
var dboSchemaMetadata = await CallToolAsync("mssql_get_table_metadata", new { connectionName = "DefaultConnection", schema = "dbo" });
```

```csharp
// Get metadata for a specific schema in a specific database
var awSalesSchema = await CallToolAsync("mssql_get_table_metadata", new { connectionName = "AdventureWorks", schema = "Sales" });
```

--------------------------------

### Copilot MSSQL Tool Invocation Examples

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

Examples of how to invoke MSSQL tools using Copilot chat. These functions allow for initializing connections, executing queries, and retrieving table or database object metadata.

```javascript
f1e_mssql_initialize_connection({ connectionName: "DefaultConnection" });
```

```javascript
f1e_mssql_execute_query({
  connectionName: "AdventureWorks2022",
  query: "SELECT TOP 5 * FROM Production.Product",
});
```

```javascript
f1e_mssql_get_table_metadata({
  connectionName: "AdventureWorks2022",
  schema: "Sales",
});
```

```javascript
f1e_mssql_get_database_objects_metadata({
  connectionName: "AdventureWorks2022",
  schema: "Person",
  includeViews: true,
});
```

--------------------------------

### Get API Key Usage Logs Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response containing usage logs for a specific API key. Each log entry details the timestamp, resource accessed, method, IP address, and user agent.

```json
{
  "jsonrpc": "2.0",
  "id": 6,
  "result": [
    {
      "id": "log-123456",
      "apiKeyId": "75e6d5f3-c851-4c7a-a4b7-874269a31bbb",
      "userId": "service-123",
      "timestamp": "2025-06-29T14:35:42.654321Z",
      "resource": "/mcp",
      "method": "POST",
      "ipAddress": "192.168.1.100",
      "userAgent": "curl/7.79.1"
    },
    {
      "id": "log-123457",
      "apiKeyId": "75e6d5f3-c851-4c7a-a4b7-874269a31bbb",
      "userId": "service-123",
      "timestamp": "2025-06-29T14:36:15.123456Z",
      "resource": "/mcp",
      "method": "POST",
      "ipAddress": "192.168.1.100",
      "userAgent": "curl/7.79.1"
    }
  ]
}
```

--------------------------------

### Start MCP Server with Encryption Enabled

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/SecurityReadme.md

Use this script to start the MCP server with connection string encryption enabled. It sets environment variables for the encryption and API keys and launches the server. Ensure you save the displayed encryption key securely.

```powershell
$env:MSSQL_MCP_KEY = "yourEncryptionKey"
$env:MSSQL_MCP_API_KEY = "YourApiKey" # This will be used as the Bearer token value
./Start-MCP-Encrypted.ps1

# Then access the API using Bearer token authentication:
# Authorization: Bearer YourApiKey
```

--------------------------------

### JSON-RPC tools/list Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Example response for the 'tools/list' method, detailing available tools and a cursor for the next page.

```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "tools": [
      {
        "name": "mssql_execute_query",
        "title": "mssql_execute_query",
        "description": "Executes a SQL query and returns the results as JSON.",
        "inputSchema": {
          "type": "object",
          "properties": {
            "connectionName": {
              "type": "string",
              "default": "DefaultConnection"
            },
            "query": {
              "type": "string"
            }
          },
          "required": ["query"]
        }
      }
    ],
    "nextCursor": "next-page-cursor"
  }
}
```

--------------------------------

### Copilot Agent Request to Get Table Metadata

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Example JSON-RPC request from GitHub Copilot to retrieve table metadata using the `tools/call` method.

```json
{
  "jsonrpc": "2.0",
  "id": "copilot-request-1",
  "method": "tools/call",
  "params": {
    "name": "mssql_get_table_metadata",
    "arguments": {
      "connectionName": "p330d_PROTO",
      "schema": null
    }
  }
}
```

--------------------------------

### Start SQL Server MCP Server with Docker Compose

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/DockerBuild.md

Starts the SQL Server MCP server container in detached mode using Docker Compose. Ensure environment variables are configured in the docker-compose.yml file.

```bash
docker-compose up -d;
```

--------------------------------

### Get User Usage Logs Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response containing API usage logs for a specific user. Each log entry details the timestamp, resource accessed, method, IP address, and user agent.

```json
{
  "jsonrpc": "2.0",
  "id": 7,
  "result": [
    {
      "id": "log-123456",
      "apiKeyId": "75e6d5f3-c851-4c7a-a4b7-874269a31bbb",
      "userId": "service-123",
      "timestamp": "2025-06-29T14:35:42.654321Z",
      "resource": "/mcp",
      "method": "POST",
      "ipAddress": "192.168.1.100",
      "userAgent": "curl/7.79.1"
    },
    {
      "id": "log-123457",
      "apiKeyId": "75e6d5f3-c851-4c7a-a4b7-874269a31bbb",
      "userId": "service-123",
      "timestamp": "2025-06-29T14:36:15.123456Z",
      "resource": "/mcp",
      "method": "POST",
      "ipAddress": "192.168.1.100",
      "userAgent": "curl/7.79.1"
    }
  ]
}
```

--------------------------------

### Connection String Encryption Setup

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/Security.md

Instructions on how to enable AES-256 encryption for connection strings by setting the MSSQL_MCP_KEY environment variable and using a provided script.

```APIDOC
## Enabling Connection String Encryption

To enable secure connection string encryption, follow these steps:

1.  **Set the `MSSQL_MCP_KEY` environment variable**: Use a strong, random value.
    ```powershell
    $env:MSSQL_MCP_KEY = "your-strong-random-key"
    ```

2.  **Start the server using the provided script**: 
    ```powershell
    ./Start-MCP-Encrypted.ps1
    ```

If the `MSSQL_MCP_KEY` is not set, a fallback mechanism will be used with reduced security.
```

--------------------------------

### Response Listing Available Tools

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

The response contains a list of available tools, including their names, descriptions, and input schemas. The 'mssql_execute_query' tool is shown as an example.

```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "tools": [
      {
        "name": "mssql_execute_query",
        "description": "Executes a SQL query and returns the results as JSON.",
        "inputSchema": {
          "type": "object",
          "properties": {
            "connectionName": {
              "type": "string",
              "default": "DefaultConnection"
            },
            "query": {
              "type": "string"
            }
          },
          "required": ["query"]
        }
      }
      // ... other tools
    ]
  }
}
```

--------------------------------

### List All API Keys Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response structure when listing all API keys. Includes details for both service and user keys.

```json
{
  "jsonrpc": "2.0",
  "id": 3,
  "result": [
    {
      "id": "75e6d5f3-c851-4c7a-a4b7-874269a31bbb",
      "name": "My Service API Key",
      "userId": "service-123",
      "createdAt": "2025-06-29T14:32:11.123456Z",
      "expirationDate": "2026-06-29T00:00:00Z",
      "lastUsed": "2025-06-29T14:35:42.654321Z",
      "isActive": true,
      "keyType": "service",
      "allowedConnectionNames": null
    },
    {
      "id": "9a8b7c6d-5e4f-3g2h-1i0j-klm123456789",
      "name": "User Dashboard Key",
      "userId": "user-456",
      "createdAt": "2025-06-25T09:12:33.123456Z",
      "expirationDate": null,
      "lastUsed": "2025-06-29T10:15:22.654321Z",
      "isActive": true,
      "keyType": "user",
      "allowedConnectionNames": null
    }
  ]
}
```

--------------------------------

### Start MCP Server with Encrypted Connections (PowerShell)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

Start the MCP server with encrypted connections by setting the MSSQL_MCP_KEY and MSSQL_MCP_API_KEY environment variables. For production, manage these keys securely using a secrets management solution.

```powershell
$env:MSSQL_MCP_KEY = "ReplaceWithMyKeyForTheConnectionEncryption"
$env:MSSQL_MCP_API_KEY = "ReplaceWithMyApiKeyForClientAccess"
./Scripts/Start-MCP-Encrypted.ps1
```

--------------------------------

### PowerShell Example for Executing SQL Query

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

A PowerShell script demonstrating how to set an API key, construct a JSON-RPC request for `mssql_execute_query`, and send it using `Invoke-RestMethod`.

```powershell
# Set API key
$apiKey = "your-api-key-here"
$headers = @{
    "Authorization" = "Bearer $apiKey"
    "Content-Type" = "application/json"
}

# Execute query
$body = @{
    jsonrpc = "2.0"
    id = 1
    method = "mssql_execute_query"
    params = @{
        query = "SELECT name FROM sys.databases"
        connectionName = "DefaultConnection"
    }
} | ConvertTo-Json -Depth 3

$response = Invoke-RestMethod -Uri "http://localhost:3001" -Method Post -Headers $headers -Body $body
```

--------------------------------

### Execute Query with Allowed Connection

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/MultiKeyAuthentication.md

Example of a successful query execution when the API key is allowed to access the specified connection. This demonstrates a valid request.

```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "mssql_execute_query",
    "arguments": {
      "connectionName": "DevDatabase",
      "query": "SELECT * FROM Users"
    }
  }
}
```

--------------------------------

### JavaScript SDK Example

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Provides a JavaScript function to abstract the process of calling the MCP API. This function handles request construction, authentication, and response parsing.

```APIDOC
## JavaScript Function for MCP API Calls

### Description
This JavaScript function `callMcpApi` simplifies making requests to the MCP API. It takes a tool name and its arguments, constructs the JSON RPC payload, and handles the fetch request with necessary headers.

### Function Signature
`async function callMcpApi(toolName, arguments = {})`

### Parameters
- **toolName** (string) - Required - The name of the tool to be invoked.
- **arguments** (object) - Optional - An object containing the arguments for the tool.

### Usage Example
```javascript
async function callMcpApi(toolName, arguments = {}) {
  const response = await fetch("http://localhost:3001", {
    method: "POST",
    headers: {
      Authorization: "Bearer your-api-key-here",
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      jsonrpc: "2.0",
      id: Date.now(),
      method: "tools/call",
      params: {
        name: toolName,
        arguments: arguments,
      },
    }),
  });

  return response.json();
}

// Example of calling the function:
const result = await callMcpApi("mssql_get_database_objects_metadata", {
  connectionName: "DefaultConnection",
  includeViews: true,
});

console.log(result);
```
```

--------------------------------

### MSSQL MCP Response for Key Generation

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response containing the newly generated secure key for connection string encryption.

```json
{
  "jsonrpc": "2.0",
  "id": 15,
  "result": {
    "key": "Uew8Ap2aiZoh5Wae/XiaNX2PVHXpnC6kPVX0Tcow4FA="
  }
}
```

--------------------------------

### MSSQL MCP Response for Azure DevOps Info

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response structure for the mssql_get_azure_devops_info endpoint, detailing projects, repositories, builds, and work items.

```json
{
  "jsonrpc": "2.0",
  "id": 14,
  "result": {
    "projects": [
      {
        "name": "DatabaseProject",
        "repositories": [
          {
            "name": "SqlScripts",
            "url": "https://dev.azure.com/organization/DatabaseProject/_git/SqlScripts"
          }
        ],
        "builds": [
          {
            "id": 12345,
            "name": "Database CI Build",
            "status": "succeeded",
            "lastRunDate": "2025-06-27T10:30:00"
          }
        ],
        "workItems": [
          {
            "id": 67890,
            "title": "Update customer schema",
            "state": "Active",
            "type": "User Story"
          }
        ]
      }
    ]
  }
}
```

--------------------------------

### MSSQL Get Database Objects Metadata Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response structure for the mssql_get_database_objects_metadata endpoint, listing database objects.

```json
{
  "jsonrpc": "2.0",
  "id": 9,
  "result": {
    "objects": [
      {
        "name": "Customers",
        "schema": "dbo",
        "type": "TABLE"
      },
      {
        "name": "ActiveCustomers",
        "schema": "dbo",
        "type": "VIEW"
      }
    ]
  }
}
```

--------------------------------

### MSSQL Get SSIS Catalog Info Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Example response structure for the mssql_get_ssis_catalog_info endpoint, detailing SSIS catalog organization.

```json
{
  "jsonrpc": "2.0",
  "id": 13,
  "result": {
    "folders": [
      {
        "name": "ETL",
        "projects": [
          {
            "name": "DataWarehouseETL",
            "packages": [
              {
                "name": "LoadDimCustomer.dtsx",
                "description": "Loads customer dimension"
              }
            ]
          }
        ]
      }
    ]
  }
}
```

--------------------------------

### Set Encryption Key and Start Server (Manual)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

Manually set the MSSQL_MCP_KEY and MSSQL_MCP_API_KEY environment variables and then run the .NET application. Ensure the key is a strong, random value.

```powershell
# Option 1: Set the encryption key manually (should be a strong random value)
$env:MSSQL_MCP_KEY = "ReplaceWithMyKeyForTheConnectionEncryption"
$env:MSSQL_MCP_API_KEY = "ReplaceWithMyApiKeyForClientAccess"
dotnet run
```

--------------------------------

### Initialize MCP HTTP Client in C#

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/UsingMcpWithCSharp.md

Sets up an HttpClient with the base URL and API key for MCP server communication. Ensure the base URL is correct and the API key is valid.

```csharp
using System;
using System.Net.Http;
using System.Net.Http.Json;
using System.Text.Json;
using System.Threading.Tasks;

public class McpClient
{
    private readonly HttpClient _httpClient;
    private readonly string _baseUrl;

    public McpClient(string baseUrl, string apiKey)
    {
        _baseUrl = baseUrl;
        _httpClient = new HttpClient();
        _httpClient.DefaultRequestHeaders.Add("X-API-Key", apiKey);
    }

    // Client methods will go here
}
```

--------------------------------

### Initialize

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Initializes a connection with the specified connection name.

```APIDOC
## Initialize

### Description
Initializes a connection with the specified connection name.

### Method
POST

### Endpoint
/rpc

### Parameters
#### Request Body
- **jsonrpc** (string) - Required - Specifies the JSON-RPC protocol version.
- **id** (integer) - Required - The identifier for the request.
- **method** (string) - Required - The name of the method to invoke, which is 'Initialize'.
- **params** (object) - Required - Parameters for the Initialize method.
  - **connectionName** (string) - Required - The name of the connection to initialize.

### Request Example
```json
{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "Initialize",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```
```

--------------------------------

### SQL Query Examples for Information Schema

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

These SQL queries demonstrate how to retrieve common database information using the INFORMATION_SCHEMA views. Use these to understand your database structure before querying data.

```sql
-- Get all tables in the database
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME
```

```sql
-- Get column information for a specific table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
ORDER BY ORDINAL_POSITION
```

```sql
-- Get primary key information
SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
    ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME
```

--------------------------------

### Start Local SQL Server MCP Server via HTTP

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/MCP_Examples.md

Configure environment variables and run the MCP server locally using dotnet. This is useful for local testing and development.

```powershell
cd "C:\Path\To\mssql-mcp-server"
$env:MSSQL_MCP_KEY = "abcd1234567891-UseSomethingElseForYourMCPKey="
$env:MSSQL_MCP_API_KEY = "efghij1234567891-UseSomethingElseForYourAPIKey="
$env:MSSQL_MCP_TRANSPORT = "http"

# Additional environment variables can be set here as needed
$env:MSSQL_MCP_DATA = "C:\Path\To\where\you\want\data\settings\db\stored"
# or will default to AppData\Local\mssqlMCP folder
# Logs will default to /app/Data in docker container  or inappsettings.json

dotnet run --no-launch-profile --no-build --project mssqlMCP.csproj
```

--------------------------------

### Migrate Connections

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Initiate the migration process for existing database connections.

```json
{
  "jsonrpc": "2.0",
  "id": "17",
  "method": "mssql_migrate_connections",
  "params": {}
}
```

--------------------------------

### Initialize Connection

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Use this JSON-RPC request to initialize a connection with a specified name.

```json
{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "Initialize",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```

--------------------------------

### Get Database Objects Metadata by Type

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/DatabaseMetadata.md

Retrieve metadata filtered by specific object types. Use 'ALL' to get all types, or specify 'TABLE', 'VIEW', 'PROCEDURE', or 'FUNCTION'.

```powershell
#GetDatabaseObjectsByType connectionName="YourConnection" objectType="ALL"
```

```powershell
#GetDatabaseObjectsByType connectionName="YourConnection" objectType="TABLE"
```

```powershell
#GetDatabaseObjectsByType connectionName="YourConnection" objectType="VIEW"
```

```powershell
#GetDatabaseObjectsByType connectionName="YourConnection" objectType="PROCEDURE"
```

```powershell
#GetDatabaseObjectsByType connectionName="YourConnection" objectType="FUNCTION"
```

--------------------------------

### Build and Run MSSQLMCP

Source: https://github.com/mcprunner/mssqlmcp/blob/main/RELEASE_DOCUMENT.md

Commands to build the project and run it in either HTTP or Stdio transport mode. Stdio mode is typically used for MCP communication.

```bash
# Build the project
dotnet build

# Run in HTTP mode
MSSQL_MCP_TRANSPORT=Http dotnet run

# Run in Stdio mode for MCP
MSSQL_MCP_TRANSPORT=Stdio dotnet run
```

--------------------------------

### Build Docker Image for MSSQLMCP

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/QUICK_INSTALL.md

Builds a Docker image tagged as mssqlmcp:latest from the current directory.

```bash
docker build -t mssqlmcp:latest .
```

--------------------------------

### List Configured SQL Server Connections

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/QUICK_INSTALL.md

Execute the #mssql_list_connections command in the VSCode Github Copilot chat window to view all configured SQL Server connections, including their names, descriptions, and status.

```prompt
#mssql_list_connections
```

--------------------------------

### Get Key Usage Logs

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Retrieve usage logs for a specific key.

```json
{
  "jsonrpc": "2.0",
  "id": "22",

```

--------------------------------

### Initialize Connection

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Initializes a SQL Server connection using the specified connection name.

```APIDOC
## POST /mcp (mssql_initialize_connection)

### Description
Initializes a SQL Server connection.

### Method
POST

### Endpoint
`http://localhost:3001/mcp`

### Headers
```http
Content-Type: application/json
Accept: application/json, text/event-stream
Authorization: Bearer <your-api-key>
```

### Request Body
```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "mssql_initialize_connection",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```

### Response
#### Success Response (200)
- **success** (boolean) - Indicates if the connection initialization was successful.
- **message** (string) - A message describing the outcome of the initialization.

#### Response Example
```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "success": true,
    "message": "Connection initialized successfully"
  }
}
```
```

--------------------------------

### Get SSIS Catalog Info

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Retrieve information about the SSIS Catalog for a given connection.

```json
{
  "jsonrpc": "2.0",
  "id": "13",
  "method": "mssql_get_ssis_catalog_info",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```

--------------------------------

### Get Azure DevOps Info

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Fetch Azure DevOps related information for a specified connection.

```json
{
  "jsonrpc": "2.0",
  "id": "14",
  "method": "mssql_get_azure_devops_info",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```

--------------------------------

### Get Agent Jobs

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Retrieve a list of SQL Server Agent jobs for a specified connection.

```json
{
  "jsonrpc": "2.0",
  "id": "11",
  "method": "mssql_get_agent_jobs",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```

--------------------------------

### Troubleshooting Connection Issues

Source: https://github.com/mcprunner/mssqlmcp/blob/main/README.md

If Copilot cannot connect to your database, verify that the connection string is correctly defined. Use `mssql_list_connections` to check available connections.

```text
User: Show me all tables in the AdventureWorksLT database

Copilot: I'll try to retrieve the metadata from the AdventureWorksLT database.

[Tool used: mssql_get_table_metadata with connectionName="AdventureWorksLT"]

Error: Connection string 'AdventureWorksLT' was not found in the configuration.
```

--------------------------------

### Get Table Metadata

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Retrieve metadata for tables within a specified schema on a given connection.

```json
{
  "jsonrpc": "2.0",
  "id": "3",
  "method": "mssql_get_table_metadata",
  "params": {
    "connectionName": "DefaultConnection",
    "schema": "dbo"
  }
}
```

--------------------------------

### Executing a Query with Connection Restriction

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/MultiKeyAuthentication.md

Example of executing a query against a database connection that is allowed by the API key.

```APIDOC
## Executing a Query with Connection Restriction

### Description
Executes a SQL query against a specified database connection that the API key is authorized to access.

### Method
POST (Implicit via `tools/call`)

### Endpoint
http://localhost:3001/mcp (Implicit via `tools/call`)

### Request Body
```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "mssql_execute_query",
    "arguments": {
      "connectionName": "DevDatabase",
      "query": "SELECT * FROM Users"
    }
  }
}
```

### Response
(Details not provided in source)
```

--------------------------------

### Request to List Available Tools

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Send a POST request to the /mcp endpoint with the 'tools/list' method to retrieve all available tools. Authentication is required.

```http
POST http://localhost:3001/mcp
Content-Type: application/json
Accept: application/json, text/event-stream
Authorization: Bearer <your-api-key>
```

```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/list",
  "params": {}
}
```

--------------------------------

### JSON-RPC Protocol Error Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Example of a JSON-RPC protocol error response, specifically for unknown tool errors.

```json
{
  "jsonrpc": "2.0",
  "id": 3,
  "error": {
    "code": -32602,
    "message": "Unknown tool: invalid_tool_name"
  }
}
```

--------------------------------

### Get Agent Job Details

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Fetch detailed information about a specific SQL Server Agent job by its name.

```json
{
  "jsonrpc": "2.0",
  "id": "12",
  "method": "mssql_get_agent_job_details",
  "params": {
    "connectionName": "DefaultConnection",
    "jobName": "DatabaseBackup"
  }
}
```

--------------------------------

### List Connections

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

This method lists all available database connections.

```json
{
  "jsonrpc": "2.0",
  "id": "4",
  "method": "mssql_list_connections",
  "params": {}
}
```

--------------------------------

### Initialize SQL Server Connection Response

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

A successful response to 'mssql_initialize_connection' indicates that the connection was initialized successfully.

```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "success": true,
    "message": "Connection initialized successfully"
  }
}
```

--------------------------------

### Getting API Key Usage Logs

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/MultiKeyAuthentication.md

Retrieve usage logs for a specific API key, with an optional limit.

```APIDOC
## Getting API Key Usage Logs

### Description
Fetches the usage logs for a specified API key, with an option to limit the number of results.

### Method
POST (Implicit via `tools/call`)

### Endpoint
http://localhost:3001/mcp (Implicit via `tools/call`)

### Request Body
```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "mssql_get_key_usage_logs",
    "arguments": {
      "apiKeyId": "api-key-id-here",
      "limit": 50
    }
  }
}
```

### Response
(Details not provided in source)
```

--------------------------------

### Get Database Objects by Type

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/McpJsonRpc.md

Fetch database objects of a specific type (e.g., TABLE) from a given schema.

```json
{
  "jsonrpc": "2.0",
  "id": "10",
  "method": "mssql_get_database_objects_by_type",
  "params": {
    "connectionName": "DefaultConnection",
    "schema": "dbo",
    "objectType": "TABLE"
  }
}
```

--------------------------------

### Create API Key (Direct Method)

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Alternative direct method format for creating a new API key. This bypasses the 'tools/call' wrapper and directly invokes the 'mssql_create_key' method. Requires master API key authentication.

```json
{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "mssql_create_key",
  "params": {
    "request": {
      "name": "My Service API Key",
      "userId": "service-123",
      "keyType": "service",
      "expirationDate": "2026-06-29T00:00:00Z",
      "allowedConnectionNames": ["conn1", "conn2"]
    }
  }
}
```

--------------------------------

### Retrieve SSIS Catalog Information

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/DatabaseMetadata.md

Use this script to get metadata from the SSIS catalog. Ensure 'DefaultConnection' is configured.

```powershell
# Get SSIS catalog metadata
Invoke-RestMethod -Uri "http://localhost:5000/tools/GetSsisCatalogInfo" -Method POST -Headers @{"Content-Type"="application/json"} -Body '{"connectionName":"DefaultConnection"}'
```

--------------------------------

### Setup API Key Authentication

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/CopilotAgent.md

This PowerShell script generates and configures an API key for secure access to the MCP server. It sets the key as an environment variable and updates the appsettings.json file.

```powershell
# Generate and configure an API key
./Scripts/Set-Api-Key.ps1

```

--------------------------------

### MSSQL Get Agent Jobs Request

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Fetches a list of SQL Server Agent jobs. Requires a valid connectionName.

```http
POST http://localhost:3001/mcp
Content-Type: application/json
Accept: application/json, text/event-stream
Authorization: Bearer <your-api-key>
```

```json
{
  "jsonrpc": "2.0",
  "id": 11,
  "method": "mssql_get_agent_jobs",
  "params": {
    "connectionName": "DefaultConnection"
  }
}
```

--------------------------------

### List All API Keys Request

Source: https://github.com/mcprunner/mssqlmcp/blob/main/Documentation/API_ENDPOINTS.md

Use this JSON-RPC request to list all API keys in the system. Requires administrator privileges.

```json
{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "tools/call",
  "params": {
    "name": "mssql_list_all_keys",
    "arguments": {}
  }
}
```