This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement SQL solutions by using AI-assisted tools
      --> Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

As AI-powered development tools continue to evolve, developers increasingly need AI assistants that can interact with live enterprise systems rather than relying solely on the knowledge contained within large language models. The Model Context Protocol (MCP) provides a standardized way for AI assistants, such as GitHub Copilot and Microsoft Copilot, to securely connect to external tools, databases, services, and applications.

For DP-800 candidates, understanding how MCP enables AI-assisted database development is becoming increasingly important. Rather than simply generating SQL code, AI assistants can use MCP to retrieve database metadata, inspect schemas, execute approved queries, explore Fabric Lakehouse data, and assist with troubleshooting in real time.

This article explains how MCP works, how to connect to MCP server endpoints, common use cases involving Microsoft SQL Server and Microsoft Fabric Lakehouse, and best practices for secure implementation.

Learning Objectives

After studying this topic, you should be able to:

• Understand the purpose of the Model Context Protocol (MCP)
• Explain the relationship between AI clients and MCP servers
• Describe how GitHub Copilot and Microsoft Copilot use MCP
• Connect AI assistants to SQL Server MCP endpoints
• Connect AI assistants to Microsoft Fabric Lakehouse MCP endpoints
• Understand authentication and authorization requirements
• Follow security best practices
• Troubleshoot common MCP connection issues

What is the Model Context Protocol (MCP)?

The Model Context Protocol (MCP) is an open protocol that standardizes communication between AI applications and external systems.

Instead of building custom integrations for every database or service, AI clients communicate with MCP servers using a consistent protocol.

Think of MCP as a standardized “USB-C connector” for AI applications.

Without MCP:

AI Client
   |
Custom SQL Connector
Custom Fabric Connector
Custom REST Connector
Custom File Connector

With MCP:

AI Client
      |
      MCP
      |
-------------------------------------
SQL Server
Fabric Lakehouse
REST APIs
Files
GitHub
Azure Services

This standardized approach simplifies integration while improving maintainability and interoperability.

Why MCP Matters

Traditional AI coding assistants only generate code based on:

• User prompts
• Training data
• Conversation history

Using MCP, AI assistants can also access:

• Database schemas
• Table definitions
• Views
• Stored procedures
• Lakehouse metadata
• Files
• Documentation
• Business knowledge
• External APIs

This enables AI to generate more accurate, context-aware responses.

MCP Architecture

An MCP solution consists of three primary components.

MCP Client

The MCP client is the AI application.

Examples include:

• GitHub Copilot
• Microsoft Copilot
• Visual Studio Code
• Visual Studio
• Other MCP-compatible AI assistants

The client sends requests to one or more MCP servers.

MCP Server

The MCP server exposes tools and resources that AI assistants can access.

Examples:

• SQL Server
• Fabric Lakehouse
• Azure services
• GitHub repositories
• File systems
• REST APIs

The server determines which operations are available.

Resource or Tool

Resources exposed by an MCP server may include:

• Database tables
• Views
• Stored procedures
• SQL execution tools
• Schema information
• Lakehouse metadata
• Documentation
• APIs

MCP Communication Flow

A typical workflow is:

Developer
↓
GitHub Copilot
↓
MCP Server
↓
SQL Server
↓
Results
↓
GitHub Copilot
↓
Developer

The AI assistant acts as the intermediary, translating user requests into approved tool invocations.

Connecting to an MCP Server

Connecting to an MCP server typically involves:

1. Configuring the AI client
2. Registering the MCP endpoint
3. Authenticating
4. Discovering available tools
5. Authorizing access
6. Using the available resources

Authentication

Authentication verifies the identity of the user or application.

Common authentication methods include:

• Microsoft Entra ID
• OAuth
• Personal Access Tokens (PATs)
• API Keys (less common)
• Managed Identity (Azure-hosted scenarios)

Authentication occurs before any tool or data is accessed.

Authorization

Authorization determines what operations the AI may perform.

For example:

Allowed:

• Read schema
• Execute SELECT statements
• View metadata

Denied:

• DROP TABLE
• DELETE production data
• ALTER DATABASE

Least privilege remains an essential security principle.

Connecting to Microsoft SQL Server

An SQL Server MCP server exposes database capabilities to AI assistants.

Common resources include:

• Tables
• Views
• Stored procedures
• Functions
• Database metadata
• Execution plans
• Query execution tools

Example workflow:

Developer asks:

Show me the Sales schema.

Copilot sends an MCP request.

SQL Server returns:

• Tables
• Columns
• Relationships

Copilot explains the schema.

SQL Server MCP Use Cases

Examples include:

Schema Discovery

Instead of guessing table names:

Copilot retrieves:

• Customers
• Orders
• Products
• Sales

The generated SQL becomes much more accurate.

Generate SQL

Developer:

Show total revenue by country.

Copilot:

• Reads schema
• Finds relationships
• Generates correct JOIN statements

Explain Stored Procedures

Developer:

Explain usp_ProcessOrders.

Copilot retrieves:

• Procedure definition
• Parameters
• Business logic

Then provides a detailed explanation.

Query Optimization

Copilot can:

• Inspect indexes
• Analyze execution plans
• Suggest rewrites
• Recommend indexing improvements

Connecting to Microsoft Fabric Lakehouse

Fabric Lakehouse combines:

• Data Lake
• Data Warehouse
• Spark
• Delta tables

Using MCP, Copilot can interact with Lakehouse metadata.

Available resources may include:

• Delta tables
• Shortcuts
• SQL endpoint metadata
• Semantic information
• OneLake structure

Fabric Lakehouse Use Cases

Examples include:

Discover Tables

Developer:

List all sales tables.

Copilot queries metadata.

Generate SQL Analytics Queries

Developer:

Calculate monthly sales growth.

Copilot examines available tables.

Generates optimized SQL.

Explain Lakehouse Structure

Developer:

Explain this Lakehouse.

Copilot can describe:

• Schemas
• Delta tables
• Relationships
• Storage organization

Data Exploration

Developers can ask:

• Which tables contain customer data?
• Which columns contain dates?
• Which datasets contain revenue?

MCP Tool Discovery

One advantage of MCP is automatic discovery.

After connecting, Copilot can identify available tools such as:

• Execute SQL
• Read schema
• Read documentation
• Search metadata
• Retrieve files

The user does not need to manually configure every capability.

Multiple MCP Servers

An AI assistant may connect to multiple MCP servers simultaneously.

Example:

GitHub Copilot
├── SQL Server MCP
├── Fabric Lakehouse MCP
├── GitHub MCP
├── Azure MCP
└── Documentation MCP

This allows a single conversation to span multiple enterprise systems.

Security Considerations

Organizations should never allow unrestricted AI access to production databases.

Best practices include:

• Read-only access whenever possible
• Least privilege permissions
• Entra ID authentication
• Audit logging
• Approval workflows for sensitive actions
• Data classification awareness
• Secure network connectivity
• Encryption in transit
• Regular permission reviews

Network Considerations

Successful MCP connections require:

• Network connectivity
• Firewall configuration
• DNS resolution
• TLS encryption
• Endpoint availability

Connection failures often result from blocked network paths or invalid authentication.

Common Connection Issues

Common problems include:

Authentication Failure

Possible causes:

• Expired token
• Invalid credentials
• Missing permissions

Authorization Failure

The user authenticates successfully but lacks permission to use a tool.

Endpoint Unavailable

Possible causes:

• Incorrect URL
• Server offline
• Network outage

Firewall Restrictions

Corporate firewalls may block communication.

Tool Discovery Failure

Possible causes:

• Unsupported MCP version
• Server configuration issues
• Missing capabilities

Best Practices

Microsoft recommends:

• Connect only trusted MCP servers.
• Use Microsoft Entra ID when available.
• Apply least privilege permissions.
• Validate AI-generated SQL before execution.
• Audit AI tool usage.
• Separate development and production environments.
• Monitor server logs.
• Keep MCP server software updated.
• Limit write operations unless required.
• Review AI responses for correctness before acting on them.

SQL Server vs. Fabric Lakehouse MCP Connections

DP-800 Exam Tips

For the exam, remember these key points:

• MCP is a standardized protocol for connecting AI applications to external tools and data sources.
• GitHub Copilot and Microsoft Copilot can use MCP servers to access live enterprise resources.
• SQL Server MCP servers expose relational database metadata and tools.
• Fabric Lakehouse MCP servers expose Lakehouse metadata, Delta tables, and analytics resources.
• Authentication verifies identity; authorization determines permitted actions.
• AI assistants should operate with least privilege.
• Developers remain responsible for validating all AI-generated code and database operations.
• Organizations should use secure authentication, auditing, and network protections when deploying MCP-enabled AI solutions.

Summary

The Model Context Protocol (MCP) provides a standardized framework for connecting AI assistants with enterprise resources such as Microsoft SQL Server and Microsoft Fabric Lakehouse. By using MCP, GitHub Copilot and Microsoft Copilot can retrieve live metadata, understand database schemas, generate more accurate SQL, explain existing database objects, and assist with analytics. Proper authentication, authorization, auditing, and adherence to least privilege principles ensure that these powerful capabilities are implemented securely. As AI-assisted database development becomes more prevalent, understanding MCP connectivity and governance is an important skill for DP-800 candidates.

Practice Exam Questions

Question 1

A development team wants GitHub Copilot to retrieve SQL Server table definitions before generating SQL queries. Which technology enables this standardized communication?

A. SQL Server Integration Services (SSIS)

B. Model Context Protocol (MCP)

C. Open Database Connectivity (ODBC)

D. SQL Server Agent

Answer: B

Explanation: MCP provides a standardized protocol that enables AI clients to communicate with external systems such as SQL Server.

Question 2

What is the primary role of an MCP server?

A. Execute operating system updates

B. Store AI model weights

C. Expose tools and resources that AI clients can access

D. Replace Microsoft Entra ID authentication

Answer: C

Explanation: An MCP server exposes resources such as database schemas, SQL execution tools, documentation, and APIs to compatible AI clients.

Question 3

Which authentication mechanism is most commonly recommended for connecting GitHub Copilot to enterprise MCP servers?

A. Anonymous authentication

B. Basic authentication with shared passwords

C. FTP credentials

D. Microsoft Entra ID

Answer: D

Explanation: Microsoft Entra ID provides secure, enterprise-grade authentication with support for modern identity management.

Question 4

An AI assistant successfully authenticates to an SQL Server MCP endpoint but cannot execute a query because of insufficient permissions. Which security concept is responsible?

A. Encryption

B. Compression

C. Authorization

D. Serialization

Answer: C

Explanation: Authentication confirms identity, while authorization determines what actions an authenticated user is permitted to perform.

Question 5

Which capability is most likely exposed by a Microsoft SQL Server MCP server?

A. Reading database schema metadata

B. Azure virtual machine creation

C. Configuring Microsoft Teams

D. Managing Windows updates

Answer: A

Explanation: SQL Server MCP servers commonly expose database metadata, tables, views, stored procedures, and SQL execution tools.

Question 6

Why would an organization use least privilege when configuring MCP server access?

A. To minimize security risks by limiting allowed operations

B. To increase database storage capacity

C. To improve AI response speed

D. To reduce SQL Server licensing costs

Answer: A

Explanation: Least privilege ensures AI assistants receive only the permissions necessary to perform approved tasks.

Question 7

Which Fabric resource is most commonly explored through a Fabric Lakehouse MCP server?

A. Windows Registry

B. Delta tables and Lakehouse metadata

C. DNS records

D. Azure Firewall rules

Answer: B

Explanation: Fabric Lakehouse MCP servers expose Lakehouse metadata, Delta tables, SQL endpoints, and related analytics resources.

Question 8

A developer asks Copilot, “List every customer table in my Lakehouse.” What is the AI assistant most likely doing?

A. Guessing based on its training data

B. Downloading the entire database

C. Using an MCP server to retrieve live metadata

D. Reading Windows Event Logs

Answer: C

Explanation: MCP allows AI assistants to query live metadata rather than relying solely on pretrained knowledge.

Question 9

What is one major advantage of connecting GitHub Copilot to multiple MCP servers?

A. It permanently stores database credentials.

B. It allows a single AI conversation to access multiple enterprise systems and tools.

C. It eliminates the need for authentication.

D. It replaces source control systems.

Answer: B

Explanation: Multiple MCP servers enable AI assistants to work across databases, repositories, documentation, APIs, and other enterprise resources within one workflow.

Question 10

Which statement best reflects Microsoft’s guidance regarding AI-assisted database operations through MCP?

A. AI-generated SQL should be executed automatically without review.

B. Production databases should always grant AI assistants full administrative permissions.

C. MCP eliminates the need for database security controls.

D. Developers should review AI-generated code and queries before executing them.

Answer: D

Explanation: Although MCP provides rich contextual information, developers remain responsible for validating AI-generated code, ensuring correctness, security, and compliance before deployment or execution.

Go to the DP-800 Exam Prep Hub main page