Quickstart: Use SSMS to connect to and query Azure SQL Database or Azure SQL Managed Instance
Applies to: Azure SQL Database Azure SQL Managed Instance
In this quickstart, you'll learn how to use SQL Server Management Studio (SSMS) to connect to Azure SQL Database or Azure SQL Managed Instance and run some queries.
Prerequisites
Completing this quickstart requires the following items:
A database in Azure SQL Database. You can use one of these quickstarts to create and then configure a database in Azure SQL Database:
Action SQL Database SQL Managed Instance SQL Server on Azure VM Create Portal Portal Portal CLI CLI PowerShell PowerShell PowerShell Configure Server-level IP firewall rule Connectivity from a VM Connectivity from on-site Connect to SQL Server Load data Wide World Importers loaded per quickstart Restore Wide World Importers Restore Wide World Importers Restore or import Adventure Works from BACPAC file from GitHub Restore or import Adventure Works from BACPAC file from GitHub Important
The scripts in this article are written to use the Adventure Works database. With a managed instance, you must either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.
If you simply want to run some ad hoc queries without installing SSMS, use the Azure portal's query editor to query a database in Azure SQL Database.
Get server connection information
Get the connection information you need to connect to your database. You'll need the fully qualified server name or host name, database name, and login information to complete this quickstart.
Sign in to the Azure portal.
Navigate to the database or managed instance you want to query.
On the Overview page, review the fully qualified server name next to Server name for your database in SQL Database or the fully qualified server name (or IP address) next to Host for your managed instance in SQL Managed Instance or your SQL Server instance on your VM. To copy the server name or host name, hover over it and select the Copy icon.
Note
For connection information for SQL Server on Azure VM, see Connect to SQL Server
Connect to your database
Note
In December 2021, releases of SSMS prior to 18.6 will no longer authenticate through Microsoft Entra ID with MFA.
To continue utilizing Microsoft Entra authentication with MFA, you need SSMS 18.6 or later.
In SSMS, connect to your server.
Important
A server listens on port 1433. To connect to a server from behind a corporate firewall, the firewall must have this port open.
Open SSMS.
The Connect to Server dialog box appears. Enter the following information:
Setting Suggested value Description Server type Database engine Required value. Server name The fully qualified server name Something like: servername.database.chinacloudapi.cn. Authentication SQL Server Authentication This tutorial uses SQL Authentication. Login Server admin account user ID The user ID from the server admin account used to create the server. Password Server admin account password The password from the server admin account used to create the server.
Note
This tutorial utilizes SQL Server Authentication.
Select Options in the Connect to Server dialog box. In the Connect to database drop-down menu, select mySampleDatabase. Completing the quickstart in the Prerequisites section creates an AdventureWorksLT database named mySampleDatabase. If your working copy of the AdventureWorks database has a different name than mySampleDatabase, then select it instead.
Select Connect. The Object Explorer window opens.
To view the database's objects, expand Databases and then expand your database node.
Query data
Run this SELECT Transact-SQL code to query for the top 20 products by category.
In Object Explorer, right-click mySampleDatabase and select New Query. A new query window connected to your database opens.
In the query window, paste the following SQL query:
SELECT pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid;
On the toolbar, select Execute to run the query and retrieve data from the
Product
andProductCategory
tables.
Insert data
Run this INSERT Transact-SQL code to create a new product in the SalesLT.Product
table.
Replace the previous query with this one.
INSERT INTO [SalesLT].[Product] ( [Name] , [ProductNumber] , [Color] , [ProductCategoryID] , [StandardCost] , [ListPrice] , [SellStartDate] ) VALUES ('myNewProduct' ,123456789 ,'NewColor' ,1 ,100 ,100 ,GETDATE() );
Select Execute to insert a new row in the
Product
table. The Messages pane displays (1 row affected).
View the result
Replace the previous query with this one.
SELECT * FROM [SalesLT].[Product] WHERE Name='myNewProduct'
Select Execute. The following result appears.
Update data
Run this UPDATE Transact-SQL code to modify your new product.
Replace the previous query with this one that returns the new record created previously:
UPDATE [SalesLT].[Product] SET [ListPrice] = 125 WHERE Name = 'myNewProduct';
Select Execute to update the specified row in the
Product
table. The Messages pane displays (1 row affected).
Delete data
Run this DELETE Transact-SQL code to remove your new product.
Replace the previous query with this one.
DELETE FROM [SalesLT].[Product] WHERE Name = 'myNewProduct';
Select Execute to delete the specified row in the
Product
table. The Messages pane displays (1 row affected).
Next steps
- For information about SSMS, see SQL Server Management Studio.
- To connect and query using the Azure portal, see Connect and query with the Azure portal SQL Query editor.
- To connect and query using Visual Studio Code, see Connect and query with Visual Studio Code.
- To connect and query using .NET, see Connect and query with .NET.
- To connect and query using PHP, see Connect and query with PHP.
- To connect and query using Node.js, see Connect and query with Node.js.
- To connect and query using Java, see Connect and query with Java.
- To connect and query using Python, see Connect and query with Python.
- To connect and query using Ruby, see Connect and query with Ruby.