Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Copilot in SQL Server Management Studio (SSMS) uses an endpoint and deployment in Azure OpenAI. This article provides the steps to create the necessary Azure OpenAI resources. For more information, see Create and deploy an Azure OpenAI in Azure AI Foundry Models resource.
Access to deployments can be provided using Microsoft Entra authentication, or API Keys. Microsoft Entra authentication is recommended as a more secure option.
The cost of Copilot in SSMS depends on your use of the provisioned Azure OpenAI resources, as well as the model selected. It will be billed to the Azure subscription that hosts the resources. For more information, see Azure OpenAI Service pricing overview.
Create the endpoint
Endpoint creation is done in the Azure portal.
Sign in to the Azure portal.
Go to Services.
Select Azure AI services, which is listed under AI + Machine Learning.
Within Azure AI services*, select Azure OpenAI account.
Select + Create to create a new Azure OpenAI service.
On the Basics page, complete all the required details.
- The Name is used in the endpoint URL.
- The only option currently available for Pricing tier is Standard S0.
Select Next.
On the Network page, select All Networks unless you're setting up a VPN. SQL Server Management Studio (SSMS) must able to reach the endpoint in order for Copilot in SSMS to work.
Select Next.
On the Tags page, set any tags your organization uses. Tags aren't required for Copilot in SSMS, but should be set according to your company policy. For example, your company might require all resources to use an Owner tag.
Select Next.
On the Review + submit page review the information and then select Create.
The message Deployment is in progress appears, and then the message Your deployment is complete when it's provisioned.
Select Go to resource.
Within the resource, expand Resource Management, then select Keys and Endpoint.
Take note of the value for Endpoint on the Keys and Endpoint page because it will be used when you configure Copilot in SSMS.
Note
During the initial configuration of Copilot in SSMS, enter Endpoint in the Azure OpenAI Endpoint dialog.
If you're using API key access, then either Key 1 or Key 2 can be used when you configure Copilot in SSMS. API key access is optional; Microsoft Entra authentication is recommended as a more secure option.
Note
During the initial configuration of Copilot in SSMS, enter Key 1 or Key 2 in the Azure OpenAI API Key dialog if using API key access.
Use Microsoft Entra ID for access
If you would like to use Microsoft Entra to authenticate to Azure OpenAI for Copilot in SSMS, the following steps are required.
Within the endpoint resource, select Access Control (IAM).
From the Access Control page, select + Add to add a role assignment.
On the Role page, within the list of Job function roles, find Cognitive Services OpenAI User and select it, then select Next.
On the Members page, add the appropriate members by entering a user, a group, service principal, or managed identity.
Use +Select members to open the Select members pane.
Select the appropriate ID from the list, then Select.
Select Review + assign, and on the Review + assign page, review the members, and then select Review + assign again to assign the members to the deployment.
A notification that the role has been added appears.
Repeat steps 2 through 8 for the Cognitive Services OpenAI Contributor. All members, whether a user, group, service principal, or managed identity, must be added to both roles.
The Cognitive Services OpenAI Contributor role has read/write permissions and the ability to modify the endpoint. Alternatively, you can assign the Cognitive Services OpenAI Contributor role to the individual who manages the endpoint (it should be assigned to at least one person), and only assign the Cognitive Services OpenAI User role to other members. The Cognitive Services OpenAI User role is lower privilege and provides read-only access to the endpoint.
Create the deployment
The final step required is creating the deployment.
Within the endpoint resource, go to the Overview page.
Select Explore Azure AI Foundry portal, which launches a separate portal.
A warning that you're leaving the Azure portal appears. Select Continue.
Within the Azure AI Foundry portal, select Deployments under Shared resources.
Select + Deploy model, then select Deploy base model.
Within the Select a model pane, select the gpt-4o (Chat completion) model, then select Confirm.
Note
gpt-4ois the only model supported for preview.On the Deploy model page, select Customize.
Within the customization dialog, enter a value for Deployment name.
Take note of the value for Deployment name because it will be used when you configure Copilot in SSMS.
Note
During the initial configuration of Copilot in SSMS, enter Deployment name in the Azure OpenAI Deployment dialog.
Set the Deployment type. Each deployment type includes a description about performance versus data residency. A deployment with 'standard' in the name is recommended for preview. If data residency isn't a concern, then Global Standard offers the best performance. Data Zone Standard provides a balanced trade-off, and Standard optimizes for a specific region at the potential cost of extra latency.
The Model version should be
2024-11-20, and the resource location should be set by default (inherited from the endpoint you already created).Set the Tokens per Minute Rate Limit to the maximum value available. It can be modified in the Azure AI Foundry portal at any time, should you decide to lower the cap. The lower the value, the fewer the questions per minute that Copilot can answer. For more information, see Azure OpenAI Service quotas and limits.
Leave Content filter at the default value of DefaultV2, unless your company has its own policies around AI content filters.
Select Create resource and deploy.
When deployment is complete, the details page appears. Within the Get Started section, you can find the endpoint and deployment in the code for Run a basic code sample:
Use the following values and endpoint when configuring Copilot in SSMS:
If you need to edit any deployment settings, such as Rate limit (Tokens per minute), access the details page for the deployment.
Monitor cost
The cost of Copilot in SSMS varies based on use and follows a pay-as-you-go billing model. Because the cost isn't a fixed, recurring value, we recommend monitoring the resource spend on a regular basis to mitigate surprises in billing. For more information, see Plan to manage costs for Azure OpenAI Service.