How to Connect Copilot Studio to SQL Server Using the Power Platform Data Gateway

You can Connect Copilot Studio to your on-premises SQL Server securely by using the power platform data gateway. This setup enables you to query and update local databases in real time with natural language, making data management much easier. The gateway uses robust encryption, which keeps your sensitive information safe and compliant with regulations. You benefit from a hybrid AI architecture, where SQL Server acts as your system of record, the gateway provides secure transport, and Copilot Studio delivers a conversational interface.

Key Takeaways

• Connect Copilot Studio to your on-premises SQL Server using the Power Platform Data Gateway for secure, real-time data access.

• Ensure your SQL Server version is compatible and that you have the right permissions set for a smooth connection.

• Follow the installation steps for the data gateway carefully to create a secure bridge between Copilot Studio and your SQL Server.

• Use natural language queries in Copilot Studio to interact with your SQL data, making data management easier and more intuitive.

• Regularly monitor and optimize your connection to maintain performance and security, applying least-privilege access principles.

Prerequisites for Connecting Copilot Studio

Before you connect Copilot Studio to your on-prem sql server, you need to prepare your environment. This step ensures a smooth setup and secure data access.

Supported SQL Server Versions

You should verify that your on-prem sql server meets the compatibility requirements. Copilot Studio works best with recent versions of SQL Server, including SQL Server 2016, 2017, 2019, and Azure SQL. Older versions may not support all features or security protocols. Always check your SQL Server documentation for the latest compatibility updates.

Required Permissions

You must have the right permissions to connect Copilot Studio to your database. Grant the minimum necessary access to the service account that will interact with SQL Server. This account should have read and write permissions only on the tables you plan to use. Avoid giving administrative privileges.

Tip: Assign permissions at the database level, not the server level, to follow least-privilege principles.

Network and Security Setup

You need to configure your network and firewall settings to allow secure communication between Copilot Studio, the Power Platform Data Gateway, and your on-prem sql server. The gateway uses outbound-only, encrypted connections, which protect your data from unauthorized access.

Here are the minimum system requirements for installing the Power Platform Data Gateway:

You should also set up firewall rules to allow the following endpoints and ports:

Review these prerequisites before you begin. This preparation helps you avoid common setup issues and ensures a secure, reliable connection.

Install and Configure Data Gateway

Setting up the data gateway is a crucial step for connecting Copilot Studio to your on-premises sql server database. You will use the gateway as a secure bridge, enabling real-time, encrypted access to your data. This section guides you through downloading, installing, registering, and configuring the gateway for your sql environment.

Download and Install Gateway

You need to install the gateway on a Windows server that meets the minimum requirements. Follow these steps to install the gateway:

1. Open Power Automate and go to the Data section, then select Gateways.

2. Click on New Gateway to start the download for the On-Premises Data Gateway.

3. Run the installer and choose the installation location on your server.

4. After installation, enter your organizational email address to associate with the gateway.

5. Register your new gateway by providing a unique name and setting a recovery key for security.

6. Confirm the recovery key and select Configure to complete the process.

Tip: Always install the gateway on a dedicated, secure server. This practice helps you maintain control over access and performance.

Register Gateway with Power Platform

After you install the gateway, you must register it with the Power Platform. Registration links your gateway to your Microsoft 365 environment and enables secure management.

• Use a service account with limited permissions for registration. This approach supports least-privilege access and reduces risk.

• Sign in with your Microsoft organizational account during registration.

• Assign a clear, descriptive name to your gateway. This helps you identify it easily in the Power Platform admin center.

• Set a strong recovery key and store it securely. You will need this key for future gateway management or recovery.

• Limit access to gateway settings and connections to only those who need it.

Note: Regularly monitor gateway usage and apply software updates to keep your environment secure and reliable.

Set Up On-Premises SQL Server Connection

Now you can connect your sql server database to the data gateway. This step allows Copilot Studio to access your on-premises sql data securely.

1. In the Power Platform admin center, go to Manage Gateways.

2. Select your registered gateway and add a new data source.

3. Choose SQL Server as the data source type.

4. Enter the server name and database name for your sql server database.

5. Select the authentication method that fits your security needs. You can choose from several options:

   • Microsoft Entra interactive authentication, which supports multifactor authentication.

   • Microsoft Entra integrated authentication, which removes the need for passwords.

   • Azure Managed Identity, which allows you to connect without managing credentials.

   • Certificate-based authentication, which uses certificates instead of passwords.

6. Enter the required credentials and click Add to complete the connection.

The data gateway uses an outbound-only connection model. It initiates connections to Azure Service Bus over TCP port 443, which is standard for HTTPS traffic. This approach reduces security risks because you do not need to open inbound firewall ports. You can enforce HTTPS by enabling the "force HTTPS" setting, ensuring all traffic between the gateway and Azure remains encrypted and compliant with your security policies.

Security Reminder: Always use the least-privilege principle when assigning permissions to the service account that connects to your sql server database. Grant only the necessary read and write access to the required tables.

By following these steps, you create a secure, reliable connection between Copilot Studio and your on-premises sql server database. The data gateway acts as a trusted transport layer, keeping your data safe while enabling powerful AI-driven interactions.

Connect Copilot Studio to On-Premises SQL Server

Connecting Copilot Studio to your on-premises SQL Server unlocks real-time, conversational access to your enterprise data. You can follow these steps to set up a secure and efficient integration that leverages the hybrid AI architecture.

Add SQL Server as Knowledge Source

You begin by adding your on-premises SQL Server as a knowledge source in Copilot Studio. This process allows Copilot Studio to interact with your database using natural language queries and actions.

• Set up the Power Platform Data Gateway to establish a secure bridge between Copilot Studio and your on-premises SQL Server.

• Add your SQL Server as a data source in Copilot Studio. You need to specify the server and database details.

• Enable SQL actions so Copilot Studio can read and write data in real time.

• Use natural language to query and update tables in your on-premises SQL Server.

Tip: Design read-optimized views in SQL Server to simplify queries and improve performance. Clean views help Copilot Studio deliver faster and more accurate results.

Create Data Connection in Copilot Studio

After you add SQL Server as a knowledge source, you need to create a data connection in Copilot Studio. This step links your conversational interface to the database through the gateway.

1. Open Copilot Studio and navigate to the Data section.

2. Select Add Data Connection and choose SQL Server from the list of available sources.

3. Enter the connection details, including the server name, database name, and authentication method.

4. Map entities in Copilot Studio to tables or views in your on-premises SQL Server. This mapping ensures that Copilot Studio understands your data structure and can interact with it effectively.

5. Test the connection to confirm that Copilot Studio can access your SQL Server data.

Note: Use least-privilege accounts for the connection. Limit access to only the tables and views needed for your Copilot scenarios.

Enable SQL Actions for Read/Write

You can configure Copilot Studio to perform read and write operations on your on-premises SQL Server. This flexibility allows you to automate data retrieval and updates using natural language.

To change the default mode for Copilot Studio from Read Only to Read/Write with Approval, navigate to Tools, select Options, then Copilot, and use the dropdown list to adjust the Default Execution Mode.

Security Reminder: Always review and approve write actions before allowing Copilot Studio to modify your on-premises SQL Server data. This practice helps you maintain control and compliance.

Hybrid AI Architecture Overview

You benefit from a hybrid AI architecture when you connect Copilot Studio to your on-premises SQL Server:

• SQL Server acts as the system of record. It provides a robust platform for storing and managing your enterprise data.

• The Power Platform Data Gateway serves as the transport layer. It securely moves data between SQL Server and Copilot Studio.

• Copilot Studio functions as the user interface. You interact with your data using natural language, making queries and updates simple and intuitive.

This architecture ensures that your data remains secure and accessible while enabling advanced AI-driven interactions.

Tip: Map entities in Copilot Studio to read-optimized views in SQL Server. This approach streamlines queries and improves response times for your users.

By following these steps, you create a seamless connection between Copilot Studio and your on-premises SQL Server. You gain the ability to query and update your data securely, using conversational AI to drive productivity and insight.

Secure and Optimize Your Connection

Authentication and Least-Privilege Access

You need to secure your connection between Copilot Studio and your on-premises SQL Server. Start by choosing the right authentication method. You can:

1. Use no authentication for test environments only.

2. Authenticate with Microsoft for seamless integration.

3. Authenticate manually for custom setups.

Always apply least-privilege access. In Azure, use role-based access controls. Place Active Directory users in groups that match SQL Server roles. Grant each role only the permissions needed for the copilot agent to perform its tasks. Enforce multi-factor authentication for all users. Leverage Conditional Access policies to restrict access to trusted devices and locations. Limit copilot permissions so users only access what they need.

Performance and Latency Tips

Optimizing performance ensures your copilot agent responds quickly. Follow these steps:

1. Utilize SQL views for data operations to reduce client-side processing.

2. Monitor the health and configuration of all on-premises data gateway nodes.

3. Scale your data gateway cluster if you expect heavy data requests.

4. Continuously monitor and optimize the performance of your on-premises data gateway.

5. Profile and tune slow SQL queries to improve execution speed.

6. Address resource contentions in your SQL database.

You can use analytics dashboards and real-time telemetry to track copilot agent usage and key performance indicators. Microsoft Sentinel and the Power Platform admin center provide alerts and recommendations for your environment.

Audit Logging and Governance

You must track all copilot agent activities for compliance. Audit logs record user interactions and admin activities automatically if auditing is enabled. You can also record server and database audit action groups for more detailed tracking. Change data capture and change tracking features in SQL Server help you monitor changes without custom solutions.

For governance, establish clear boundaries between personal, departmental, and production environments. Implement a default-deny exfiltration policy to prevent unauthorized data movement. Automate change management using Power Platform pipelines with approval-based deployments. Use the Center of Excellence Starter Kit to track assets and workflows. Train your team to manage copilot agent creation and configuration responsibly.

By following these steps, you secure and optimize your connection between Copilot Studio and your on-premises SQL Server. You ensure your copilot agent operates efficiently, remains compliant, and supports your business needs.

Troubleshoot Data Gateway and Copilot Studio Issues

When you connect Copilot Studio to SQL Server using the Power Platform Data Gateway, you may encounter technical issues. Understanding how to identify and resolve these problems helps you maintain a reliable connection and smooth user experience.

Common Connection Errors

You might see error messages when setting up or using the data gateway. These errors often relate to credentials, database access, or gateway connectivity. The table below shows frequent connection errors and what they mean:

Tip: Always verify your username, password, database name, and permissions before troubleshooting further.

Permission and Authentication Problems

You may face authentication issues when connecting Copilot Studio to SQL Server. Follow these steps to resolve permission and authentication problems:

1. Look for a Microsoft Entra authentication dialog in your browser if the progress bar repeats after entering endpoint values.

2. Capture logs in the output window if you see errors after entering connection details.

3. Enter endpoint and deployment values in Tools > Options > Copilot, then select OK.

4. Open the output viewer in View > Output before launching Copilot.

5. Authenticate to Azure when launching Copilot, unless you use an API Key.

6. If no authentication prompt appears, check your browser for the Microsoft Entra dialog.

7. Use the output viewer to review errors in the Copilot chat.

8. Verify access to Azure OpenAI resources with the Azure CLI installed on the same machine as SSMS.

Note: Always confirm that your account has the necessary permissions for both SQL Server and Azure resources.

Preview Features and Limitations

Copilot Studio offers preview features that enhance your experience, but you should be aware of current limitations. The table below summarizes what you can expect:

Always review Copilot Studio outputs and test preview features in a safe environment before using them in production.

You now have a secure, step-by-step method to connect Copilot Studio to your on-premises SQL Server using the Power Platform Data Gateway. Always prioritize security, governance, and performance best practices. Next, test your setup with sample queries, review audit logs, and explore advanced features such as connectors and dynamic agents. For ongoing learning, check out these resources:

• Knowledge in Microsoft Copilot Studio

• What’s new in Copilot Studio: May 2025

Stay informed to make the most of your Copilot Studio integration.

FAQ

How do I set up the gateway in powerapps to bridge on-prem sql?

You install the on-premise data gateway on your server. You register it in powerapps studio. You select your SQL Server as a data source. The gateway lets you query your data sources and bridge on-prem sql with your powerapps app.

Can I use power bi gateway connection with power bi service to query my data sources?

Yes, you connect the gateway to power bi service. You add your SQL Server as a source. You use power bi to query your data sources and visualize results. The gateway ensures secure access and real-time updates.

How do I create and update records in powerapps studio using SQL Server?

You map entities in powerapps studio to your SQL tables. You use forms or actions in your powerapps app to create/update records. The gateway in powerapps securely transmits changes, allowing you to create and update records directly from your app.

What permissions do I need to make my database available for creating new customer records dynamically?

You grant least-privilege access to the service account in powerapps studio. You allow read and write permissions on tables for creating new customer records dynamically. The gateway manages authentication and keeps your database secure.

Can I use power bi and powerapps together to query my data sources and visualize queries?

You combine power bi and powerapps for a complete solution. You use power bi to visualize queries and powerapps to interact with data. The gateway connects both tools to your SQL Server, enabling seamless data operations.