Connect to Synapse SQL with SQL Server Management Studio (SSMS)

• Azure Data Studio
• Power BI
• Visual Studio
• sqlcmd
• SSMS

You can use SQL Server Management Studio (SSMS) to connect to and query Synapse SQL in Azure Synapse Analytics through either serverless SQL pool or dedicated SQL pool resources.

Supported tools for serverless SQL pool

Azure Data Studio is fully supported starting from version 1.18.0. SSMS is partially supported starting from version 18.5, you can use it to connect and query only.

Prerequisites

Before you begin, make sure you have the following prerequisites:

• SQL Server Management Studio (SSMS).
• For dedicated SQL pool, you need an existing data warehouse. To create one, see Create a dedicated SQL pool. For serverless SQL pool one is already provisioned, named Built-in, in your workspace at creation time.
• The fully qualified SQL Server name. To find this name, see Connect to Synapse SQL.

Connect

Dedicated SQL pool

To connect to Synapse SQL using dedicated SQL pool, follow these steps:

1. Open SQL Server Management Studio (SSMS).

2. In the Connect to Server dialog box, fill in the fields, and then select Connect:

   

   • Server name: Enter the server name previously identified.
   • Authentication: Choose an authentication type, such as SQL Server Authentication or Active Directory Integrated Authentication.
   • User Name and Password: Enter your user name and password if SQL Server Authentication was selected above.

3. Expand your Azure SQL Server in Object Explorer. You can view the databases associated with the server, such as the sample AdventureWorksDW database. You can expand the database to see the tables:

   

Serverless SQL pool

To connect to Synapse SQL using serverless SQL pool, follow these steps:

1. Open SQL Server Management Studio (SSMS).

2. In the Connect to Server dialog box, fill in the fields, and then select Connect:

   

   • Server name: Enter the server name previously identified.
   • Authentication: Choose an authentication type, such as SQL Server Authentication or Active Directory Integrated Authentication:
   • User Name and Password: Enter your user name and password if SQL Server Authentication was selected above.
   • Select Connect.

3. To explore, expand your Azure SQL server. You can view the databases associated with the server. Expand demo to see the content in your sample database.

   

Run a sample query

Dedicated SQL pool

Now that a database connection has been established, you can query the data.

1. Right-click your database in SQL Server Object Explorer.

2. Select New Query. A new query window opens.

   

3. Copy the following T-SQL query into the query window:

   SELECT COUNT(*) FROM dbo.FactInternetSales;
   

4. Run the query by selecting Execute or use the following shortcut: F5.

   

5. Look at the query results. In the following example, the FactInternetSales table has 60398 rows.

   

Serverless SQL pool

Now that you've established a database connection, you can query the data.

1. Right-click your database in SQL Server Object Explorer.

2. Select New Query. A new query window opens.

   

3. Copy the following T-SQL query into the query window:

   SELECT COUNT(*) FROM demo.dbo.usPopulationView
   

4. Run the query by selecting Execute or use the following shortcut: F5.

   

5. Look at the query results. In this example, the usPopulationView view has 3664512 rows.

   

Next steps

Now that you can connect and query, try visualizing the data with Power BI.

To configure your environment for Microsoft Entra authentication, see Authenticate to Synapse SQL.