Copilot Studio SQL Server Connector: Access and Query Checks

The SQL Server connector in Copilot Studio lets an agent query a database to answer questions or retrieve data within a Power Platform flow. Setting it up correctly requires matching authentication to your database environment, routing traffic through the right gateway, and keeping queries within the scope that is safe for an AI agent to execute. fenbs can hold the non-sensitive data definitions and reviewed implementation decisions so any team member or connected assistant can understand the setup without asking the original builder.

Most connection failures come from one of three places: authentication credentials, gateway routing, or overly broad query permissions. Check each in turn.

Authentication: SQL versus Azure AD

The SQL Server connector in Power Platform supports two authentication methods: SQL Server authentication (a username and password for a SQL login) and Azure Active Directory integrated authentication. The right choice depends on your environment.

SQL Server authentication is simpler to configure but less secure for production: the credentials are stored in the connection and can be harder to rotate. Azure AD authentication is preferable for production environments because it ties access to managed identities and lets you revoke access centrally.

For either method, use a dedicated service account with only the permissions the agent needs. Do not use an administrator account or a shared account that is also used for other purposes.

On-premises SQL Server and the data gateway

If your SQL Server is on-premises or in a private network, you need the on-premises data gateway to bridge the Power Platform connector to your database. The gateway must be installed on a machine that can reach both the internet and the SQL Server host.

Common gateway issues include: the gateway service not running, firewall rules blocking the gateway machine from reaching SQL Server, and the gateway not being registered in the right Power Platform environment. Check the gateway status in Power Platform Admin Center before debugging the connector itself.

Azure SQL Database does not need the on-premises gateway. If your database is in Azure, connect directly using the SQL Server connector without gateway configuration.

Setting query scope and permissions

The database account the connector uses should have SELECT permissions on only the tables and views the agent needs to query. Granting broader access, such as EXECUTE on stored procedures that modify data, or write permissions, expands what an agent can do if something goes wrong or if a prompt injection causes it to run an unexpected query.

Create a dedicated SQL user for the Copilot Studio connection with a GRANT that lists exactly the tables and views in scope. Document those grants so the team knows what the agent can reach.

Consider using parameterized views rather than letting the agent write ad-hoc SQL. A view returns a defined, safe result set; ad-hoc queries carry more risk if the agent is not correctly constrained.

Validating queries before deployment

Test each query the Copilot Studio agent will run against the database before deploying. Confirm that: the query returns the expected rows, the response time is acceptable for a chat context, and there are no permission errors for the service account.

Large result sets make the agent's response slow and expensive. Add row limits to queries, either in the SQL itself (TOP in T-SQL) or in the Power Platform action. Define what a reasonable result size is for your use case and enforce it explicitly.

Handling sensitive data

SQL Server databases often contain personally identifiable information, financial records, or health data. Before connecting Copilot Studio to a database, review which tables and columns contain sensitive data and exclude them from the scope the agent can query.

Check whether your organization's data governance policies or applicable regulations (such as HIPAA, the SEC rules on financial data, or state-level privacy laws) restrict how AI agents may access certain data. These checks belong in the implementation decision record, not just in someone's head.

Testing the connection before going live

Before deploying a Copilot Studio agent with SQL Server access to real users, test the connection against a development or staging database. Use a dataset that is realistic but contains no real personal or financial data. This lets you verify authentication, gateway routing, query performance, and error handling without risk.

Run each query the agent will use and measure the response time. A query that takes five seconds on an unloaded development server may take longer under production load. Set a realistic timeout and test what happens when the database is slow: the agent should return a clear, useful error rather than hanging or returning garbled output.

How to do this with fenbs

1. Create a free account at fenbs. Add a project for the Copilot Studio integration.
2. Record the authentication method chosen and the reasoning as a Decision. Include the database server type, whether a gateway is in use, and the service account name (not the password).
3. List the tables and views in scope as context notes, with the reason each is included or excluded.
4. Add tasks for each validation step: authentication test, gateway status check, query performance test, permission verification.
5. Connect a developer assistant over MCP from Integrations so it can read the integration decisions and pick up where the last session left off.

fenbs does not connect to SQL Server or Power Platform. It holds the agreed decisions and non-sensitive schema notes so the team can work on the integration across sessions without rebuilding the context each time. For the Outlook connector on the same platform, see Copilot Studio Outlook connector.