In this quickstart, you can get started using SQL Server Management Studio (SSMS) to connect to your dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics and run some Transact-SQL (T-SQL) commands.
Connect to a dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics
Create a table in your new database
Insert rows into your new table
Query the new table and view the results
Use the query window table to verify your connection properties
Prerequisites
To complete this article, you need SQL Server Management Studio (SSMS) and access to a data source.
Connect to a dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics
Note
In December 2021, releases of SSMS prior to 18.6 will no longer authenticate to Database Engines through Microsoft Entra multifactor authentication (MFA).
To continue utilizing MFA, you need SSMS 18.6 or later.
Connectivity to Azure Analysis Services through Microsoft Entra multifactor authentication requires SSMS 18.5.1 or later.
Start SQL Server Management Studio. The first time you run SSMS, the Connect to Server window opens. If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine.
In the Connect to Server window, use the following list for guidance:
Setting
Suggested values
Description
Server type
Database engine
For Server type, select Database Engine (usually the default option).
Server name
The fully qualified server name
For Server name, enter the name of your dedicated SQL pool (formerly SQL DW) server name.
Authentication
SQL Server Authentication
Use SQL Server Authentication to connect to a dedicated SQL pool (formerly SQL DW).
The Windows Authentication method isn't supported for Azure SQL. For more information, see Azure SQL authentication.
Login
Server account user ID
The user ID from the server account used to create the server.
Password
Server account password
The password from the server account used to create the server.
Encryption1
Encryption method
Select the encryption level for the connection. The default value is Mandatory.
Trust server certificate
Trust Server Certificate
Check this option to bypass server certificate validation. The default value is False (unchecked), which promotes better security using trusted certificates.
Host Name in Certificate
Host name of the server
The value provided in this option is used to specify a different, but expected, CN or SAN in the server certificate.
1 The default value is Mandatory in SSMS 20. Strict (SQL Server 2022 and Azure SQL) encryption should be used for Azure SQL Database and Azure SQL Managed Instance. Strict (SQL Server 2022 and Azure SQL) encryption can be used for SQL Server when the instance has Force Strict Encryption enabled.
After you complete all the fields, select Connect.
You can also modify other connection options by selecting Options. Examples of connection options are the database you're connecting to, the connection timeout value, and the network protocol. This article uses the default values for all the options.
If your firewall isn't set up, a prompt appears to configure the firewall. Once you sign in, fill in your Azure account sign in information and continue to set the firewall rule. Then select OK. This prompt is a one time action. Once you configure the firewall, the firewall prompt shouldn't appear.
To verify that your dedicated SQL pool (formerly SQL DW) connection succeeded, expand and explore the objects within Object Explorer where the server name, the SQL Server version, and the username are displayed. These objects are different depending on the server type.
Troubleshoot connectivity issues
You can experience connection problems with dedicated SQL pool (formerly SQL DW). For more information on troubleshooting connection problems, visit Troubleshooting connectivity issues.
Create a table
In this section, you create a table in your dedicated SQL pool (formerly SQL DW).
In Object Explorer, right-click on your dedicated SQL pool (formerly SQL DW), select New query.
Paste the following T-SQL code snippet into the query window:
-- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
DROP TABLE dbo.Customers
GO
-- Create the table in the specified schema
CREATE TABLE dbo.Customers (
CustomerId INT NOT NULL,
Name NVARCHAR(50) NOT NULL,
Location NVARCHAR(50) NOT NULL,
Email NVARCHAR(50) NOT NULL
);
GO
Execute the query by selecting Execute or selecting F5 on your keyboard.
After the query is complete, the new Customers table is displayed in the list of tables in Object Explorer. If the table isn't displayed, right-click the dedicated SQL pool (formerly SQL DW) Tables node in Object Explorer, and then select Refresh.
Insert rows into the new table
Now let's insert some rows into the Customers table that you created. Paste the following T-SQL code snippet into the query window, and then select Execute:
-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers VALUES ( 1, N'Orlando', N'Australia', N'');
INSERT INTO dbo.Customers VALUES ( 2, N'Keith', N'India', N'keith0@adventure-works.com');
INSERT INTO dbo.Customers VALUES (3, N'Donna', N'Germany', N'donna0@adventure-works.com');
INSERT INTO dbo.Customers VALUES (4, N'Janet', N'United States', N'janet1@adventure-works.com');
Query the table and view the results
The results of a query are visible beneath the query text window. To query the Customers table and view the rows that were inserted, paste the following T-SQL code snippet into the query window, and then select Execute:
-- Select rows from table 'Customers'
SELECT * FROM dbo.Customers;
The query results are displayed under the area where the text was entered.
You can also modify the way results are presented by selecting one of the following options:
The first button displays the results in Text View, as shown in the image in the next section.
The middle button displays the results in Grid View, which is the default option.
The third button lets you save the results to a file whose extension is .rpt by default.
Verify your connection properties by using the query window table
You can find information about the connection properties under the results of your query. After you run the previously mentioned query in the preceding step, review the connection properties at the bottom of the query window.
You can determine which server and database you're connected to, and your username.
You can also view the query duration and the number of rows returned by the previously executed query.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.