Azure SQL data source for a resolver

Article
04/15/2024

The sql-data-source resolver policy configures a Transact-SQL (T-SQL) request to an Azure SQL database and an optional response to resolve data for an object type and field in a GraphQL schema. The schema must be imported to API Management as a GraphQL API.

Note

This policy is in preview. Currently, the policy isn't supported in the Consumption tier of API Management.

Note

Set a policy's elements and child elements in the order provided in the policy statement. Learn more about how to set or edit API Management policies.

Policy statement

<sql-data-source> 
    <connection-info>
        <connection-string use-managed-identity="true | false">
            Azure SQL connection string
        </connection-string>
        <include-fragment>...include-fragment policy configuration...</include-fragment>
        <authentication-certificate>...authentication-certificate policy configuration...</authentication-certificate>     
    </connection-info>
    <include-fragment>...include-fragment policy configuration...</include-fragment>
    <request single-result="true | false">
        <include-fragment>...include-fragment policy configuration...</include-fragment>
        <set-body>...set-body policy configuration...</set-body>
        <sql-statement>T-SQL query</sql-statement>
        <parameters>
            <parameter sql-type="parameter type" name="Query parameter name in @ notation">
                "Query parameter value or expression"
            </parameter>
            <!-- if there are multiple parameters, then add additional parameter elements -->
        </parameters>
    </request>
    <response>
        <include-fragment>...include-fragment policy configuration...</include-fragment>
        <set-body>...set-body policy configuration...</set-body>
        <publish-event>...publish-event policy configuration...</publish-event>
    </response>
</sql-data-source>

Elements

Name	Description	Required
connection-info	Specifies connection to Azure SQL database.	Yes
include-fragment	Inserts a policy fragment in the policy definition. If there are multiple fragments, then add additional `include-fragment` elements.	No
request	Specifies the resolver's T-SQL request and optional parameters.	Yes
response	Optionally specifies child policies to configure the response from the Azure SQL database. If not specified, the response is returned from Azure SQL as JSON.	No

connection-info elements

Note

Except where noted, each child element may be specified at most once. Specify elements in the order listed.

Element	Description	Required
connection-string	Specifies the Azure SQL connection string. The connection string uses either SQL authentication (username and password) or Microsoft Entra authentication if an API Management managed identity is configured.	Yes
include-fragment	Inserts a policy fragment in the policy definition. If there are multiple fragments, then add additional `include-fragment` elements.	No
authentication-certificate	Authenticates using a client certificate in the resolver's SQL request.	No

connection-string attributes

Attribute	Description	Required	Default
use-managed-identity	Boolean. Specifies whether to use the API Management instance's system-assigned managed identity for connection to the Azure SQL database in place of a username and password in the connection string. Policy expressions are allowed. The identity must be configured to access the Azure SQL database.	No	`false`

request attribute

Attribute	Description	Required	Default
single-result	Boolean. Specifies whether the response to the query is expected to return one row at most. Policy expressions are allowed.	No	`false`

request elements

Note

Each child element may be specified at most once. Specify elements in the order listed.

Element	Description	Required
include-fragment	Inserts a policy fragment in the policy definition.	No
set-body	Sets the body in the resolver's SQL request.	No
sql-statement	A T-SQL statement for the request to the Azure SQL database. The SQL statement may include multiple independent substatements such as UPDATE, DELETE, and SELECT that will be executed in sequence. Results are returned from the final substatement.	Yes
parameters	A list of SQL parameters, in `parameter` subelements, for the request.	No

parameter attributes

Attribute	Description	Required	Default
name	String. The name of the SQL parameter.	Yes	N/A
sql-type	String. The data type of the SQL parameter.	No	N/A

response elements

Note

Each child element may be specified at most once. Specify elements in the order listed.

Name	Description	Required
include-fragment	Inserts a policy fragment in the policy definition.	No
set-body	Sets the body in the resolver's response.	No
publish-event	Publishes an event to one or more subscriptions specified in the GraphQL API schema.	No

Usage

Policy scopes: GraphQL resolver
Gateways: classic

Usage notes

To configure and manage a resolver with this policy, see Configure a GraphQL resolver.
This policy is invoked only when resolving a single field in a matching operation type in the schema.

Configure managed identity integration with Azure SQL

You can configure an API Management system-assigned managed identity for access to Azure SQL instead of configuring SQL authentication with username and password. For background, see Configure and manage Microsoft Entra authentication with Azure SQL.

Prerequisites

Enable a system-assigned managed identity in your API Management instance.

Enable Microsoft Entra ID access

Enable Microsoft Entra authentication to SQL Database by assigning a Microsoft Entra user as the admin of the server.

In the portal, go to your Azure SQL server.
Select Microsoft Entra ID.
Select Set admin and select yourself or a group to which you belong.
Select Save.

Assign roles

In the portal, go to your Azure SQL database resource.
Select Query editor (preview).
Login using Active Directory authentication.

Execute the following SQL script. Replace <identity-name> with the name of your API Management instance.

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

Examples

Example schema

The examples in this section are resolvers for the following GraphQL schema:

type Family {
  id: Int!
  name: String!
}

type Person {
  id: Int!
  name: String!
}

type PersonQueryResult {
  items: [Person]  
}

type Query {
  familyById(familyId: Int!): Family
  familyMembers(familyId: Int!): PersonQueryResult
}

type Mutation {
  createFamily(familyId: Int!, familyName: String!): Family
}

Resolver for GraphQL query using single-result T-SQL request

The following example resolves a GraphQL query by making a single-result T-SQL request to a backend Azure SQL database. The connection string uses SQL authentication with username and password and is provided using a named value. The response is returned as a single JSON object representing a single row.

<sql-data-source>
    <connection-info>
        <connection-string>
            {{my-connection-string}}
        </connection-string>
    </connection-info>
    <request single-result="true">
        <sql-statement>
            SELECT 
                f.[Id] AS [id]
                f.[Name] AS [name]
            WHERE @familyId = f.[Id] 
        </sql-statement> 
        <parameters> 
            <parameter name="@familyId">       
                @(context.GraphQL.Arguments["id"])
            </parameter> 
        </parameters> 
    </request>
    <response />
</sql-data-source>

Resolver for GraphQL query with transformed multi-row query response

The following example resolves a GraphQL query using a T-SQL query to an Azure SQL database. The connection to the database uses the API Management instance's system-assigned managed identity. The identity must be configured to access the Azure SQL database.

The query parameter is accessed using the context.GraphQL.Arguments context variable. The multi-row query response is transformed using the set-body policy with a liquid template.

<sql-data-source> 
    <connection-info>
        <connection-string use-managed-identity="true">
            Server=tcp:{your_server_name}.database.chinacloudapi.cn,1433;Initial Catalog={your_database_name}; 
        </connection-string>
    </connection-info> 
    <request> 
        <sql-statement> 
            SELECT 
                p.[Id] AS [Id] 
                p.[FirstName] AS [FirstName] 
                p.[LastName] AS [LastName] 
            FROM [Person] p 
            JOIN [Family] f ON p.[FamilyId] = f.[Id] 
            WHERE @familyId = f.[Id] 
        </sql-statement> 
        <parameters> 
            <parameter name="@familyId">       
                @(context.GraphQL.Arguments["id"])
            </parameter> 
        </parameters> 
    </request> 
    <response> 
        <set-body template="liquid"> 
            { 
                "items": [ 
                    {% JSONArray For person in body.items %} 
                        "id": "{{ person.id }}" 
                        "name": "{{ person.firstName }} + "" "" + {{body.lastName}}" 
                    {% endJSONArrayFor %} 
                ] 
            } 
        </set-body> 
  </response> 
</sql-data-source>

Resolver for GraphQL mutation

The following example resolves a GraphQL mutation using a T-SQL INSERT statement to insert a row an Azure SQL database. The connection to the database uses the API Management instance's system-assigned managed identity. The identity must be configured to access the Azure SQL database.

<sql-data-source> 
    <connection-info>
        <connection-string use-managed-identity="true">
            Server=tcp:{your_server_name}.database.chinacloudapi.cn,1433;Initial Catalog={your_database_name};</connection-string>
    </connection-info> 
    <request single-result="true"> 
        <sql-statement> 
                INSERT INTO [dbo].[Family]
                       ([Id]
                       ,[Name])
                VALUES
                       (@familyId
                       , @familyName)

                SELECT
                    f.[Id] AS [id],
                    f.[Name] AS [name]
                FROM [Family] f
                WHERE @familyId = f.[Id]
        </sql-statement> 
        <parameters> 
            <parameter name="@familyId">       
                @(context.GraphQL.Arguments["id"])
            </parameter>
            <parameter name="@familyName">       
                @(context.GraphQL.Arguments["name"])
            </parameter> 
        </parameters> 
    </request>    
</sql-data-source>

GraphQL resolvers

Next steps

For more information about working with policies, see:

Tutorial: Transform and protect your API
Policy reference for a full list of policy statements and their settings
Policy expressions
Set or edit policies
Policy samples

Azure SQL data source for a resolver

Policy statement

Elements

connection-info elements

connection-string attributes

request attribute

request elements

parameter attributes

response elements

Usage

Usage notes

Configure managed identity integration with Azure SQL

Prerequisites

Enable Microsoft Entra ID access

Assign roles

Examples

Example schema

Resolver for GraphQL query using single-result T-SQL request

Resolver for GraphQL query with transformed multi-row query response

Resolver for GraphQL mutation

Related policies

Next steps

Additional resources