Connect to Synapse SQL with SQL Server Management Studio (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.

Note

If AAD login has connection open for more than 1 hour at time of query execution, any query that relies on AAD will fail. This includes querying storage using AAD pass-through and statements that interact with AAD (like CREATE EXTERNAL PROVIDER). This affects every tool that keeps connection open, like in query editor in SSMS and ADS. Tools that open new connection to execute query are not affected, like Synapse Studio. You can restart SSMS or connect and disconnect in ADS to mitigate this issue. .

Prerequisites

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

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:

    Connect to Server 1

    • 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:

    Explore AdventureWorksDW 1

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:

    Connect to Server 2

    • 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.

    Explore AdventureWorksDW 2

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.

    New query 1

  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.

    Run query 1

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

    Query results 1

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.

    New query 2

  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.

    Run query 2

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

    Query results 2

Next steps

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

To configure your environment for Azure Active Directory authentication, see Authenticate to Synapse SQL.