Run a trace against Azure SQL Managed Instance using Windows Authentication for Microsoft Entra principals

Article
09/29/2023

This article shows how to connect and run a trace against Azure SQL Managed Instance using Windows Authentication for principals in Microsoft Entra ID (formerly Azure Active Directory). Windows authentication provides a convenient way for customers to connect to a managed instance, especially for database administrators and developers who are accustomed to launching SQL Server Management Studio (SSMS) with their Windows credentials.

This article shares two options to run a trace against a managed instance: you can trace with extended events or with SQL Server Profiler. While SQL Server Profiler may still be used, the trace functionality used by SQL Server Profiler is deprecated and will be removed in a future version of Microsoft SQL Server.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Prerequisites

To use Windows Authentication to connect to and run a trace against a managed instance, you must first meet the following prerequisites:

Set up Windows Authentication for Azure SQL Managed Instance using Microsoft Entra ID and Kerberos.
Install SQL Server Management Studio (SSMS) on the client that is connecting to the managed instance. The SSMS installation includes SQL Server Profiler and built-in components to create and run extended events traces.
Enable tooling on your client machine to connect to the managed instance. This may be done by any of the following:
To create or modify extended events sessions, ensure that your account has the server permission of ALTER ANY EVENT SESSION on the managed instance.
To create or modify traces in SQL Server Profiler, ensure that your account has the server permission of ALTER TRACE on the managed instance.

If you have not yet enabled Windows authentication for Microsoft Entra principals against your managed instance, you may run a trace against a managed instance using a Microsoft Entra authentication option, including:

Password offers authentication with Microsoft Entra credentials
Universal with MFA adds multifactor authentication
Integrated uses federation providers like Active Directory Federation Services (ADFS) to enable single sign-on (SSO) experiences
Service Principal enables authentication from Azure applications
Managed Identity enables authentication from applications assigned Microsoft Entra identities

Run a trace with extended events

To run a trace with extended events against a managed instance using Windows Authentication, you will first connect Object Explorer to your managed instance using Windows Authentication.

Launch SQL Server Management Studio from a client machine where you have logged in using Windows Authentication.
The 'Connect to Server' dialog box should automatically appear. If it does not, ensure that Object Explorer is open and select Connect.
Enter the name of your managed instance as the Server name. The name of your managed instance should be in a format similar to managedinstancename.12a34b5c67ce.database.windows.net.
After Authentication, select Windows Authentication.
Select Connect.

Now that Object Explorer is connected, you can create and run an extended events trace. Follow the steps in Quick Start: Extended events in SQL Server to learn how to create, test, and display the results of an extended events session.

Run a trace with Profiler

To run a trace with SQL Server Profiler against a managed instance using Windows Authentication, launch the Profiler application. Profiler may be run from the Windows Start menu or from SQL Server Management Studio.

On the File menu, select New Trace.
Enter the name of your managed instance as the Server name. The name of your managed instance should be in a format similar to managedinstancename.12a34b5c67ce.database.windows.net.
After Authentication, select Windows Authentication.
Select Connect.
Follow the steps in Create a Trace (SQL Server Profiler) to configure the trace.
Select Run after configuring the trace.

Next steps

Learn more about Windows Authentication for Microsoft Entra principals with Azure SQL Managed Instance: