Simple Steps To Connect SQL Azure To SSMS

Microsoft Azure is a cloud computing service for building, testing, deploying, and managing applications through the Microsoft data centers. It provides the Software as Service (SaaS), Platform as Service (PaaS), and Infrastructure as a Service (IaaS), and supports different database servers, programming languages, and tools.

Azure allows creating databases on various database server platforms, both open source and paid. The deployment is fast and scalable. Besides, the Azure license model is flexible, which helps to reduce the infrastructure costs.

The current article will highlight the following points:

1. Create an Azure SQL Server instance.
2. Connect to the Azure SQL Server using SQL Server Management Studio.
3. Create an Azure SQL database using SQL Server Management Studio.

Let’s proceed to these points.

Create an Azure SQL Server Instance and a Database

To create a new Azure SQL Server instance, log in to the Azure portal with your credentials. On the welcome screen, click SQL databases:

In the next SQL Databases screen, click Create SQL Database:

You should specify the following:

1. Subscription or the Resource group.
2. Database Server
3. Compute + storage.
4. Use the SQL elastic pool.

In this demonstration, I have chosen the Pay-As-You-Go subscription.

To create a new resource group, click Create New. Provide the desired resource group name and click OK.

In the Database name field, set the appropriate name for your database. To create a new SQL Server, click Create New:

In the New Server section, provide the following details:

• Server name – the name of the desired server.
• Server admin login – here you need to add an administrator account with the dbmanager and loginmanager server role. This account is necessary for connecting the SQL database. Thus, provide the username for that account.
• Password – provide the password for the server administrator account and confirm that password.
• Location – enter the nearest geographical location where you want to deploy the Azure SQL Server (select it from the drop-down list).

After that, we get to the Create SQL Database screen demonstrating the data we’ve already entered. Here, we need to configure the Compute + storage parameters.

I am using the Basic variant in this demonstration, but you can select another option for your requirements.

Click Next: Networking to proceed to the further configuration step.

The Networking section provides the options for configuring the network access and connectivity for the Azure SQL database.

In this demonstration, I am using the Public endpoint.

Also, I have enabled the Add current client IP Address option to connect to the Azure SQL Database. It adds an entry of my current IP address to the server firewall.

Click Next: Additional settings to configure the following details:  

• Data source. Here you can create a blank SQL database, restore a backup, or populate the new light-version database with sample data. In our case, we've chosen to create the AdventureWorks sample database.
• Database collation. Here you point to the rules of data sorting and comparing. Specify the default collation of the database. Note: These rules aren’t subject to change after the database creation.
• Azure Defender for SQL. You can enable it to use the security package or disable it if you prefer other methods. The Defender is paid after the 30-days free trial period.

We are installing an AdventureWorksLT database so click on Sample. We were not changing the collation and did not enable the Azure Defender for SQL. Click on Review + create.

The deployment process starts. Once it is complete, you can see the Azure SQL Server instance and the Azure SQL database on the All Resources page.

Connect to Azure SQL Server Instance

To connect to the Azure SQL Server instance, we need Server Name, Username, and password. These details are present on the SQL Server resource group page.

Log in to the Azure portal and click on the Azure SQL Server instance named myazuresqlserverdb. The server name and admin login of the Azure SQL Server instance will be on its resource page:

Now, let’s apply the SQL Server Management Studio.

In the Connect to Server window, specify the details:

• The server name should be myazuresqlserverdb.database.windows.net.
• Authentication should be SQL Server Authentication (select it from the drop-down menu).
• Provide the appropriate username and password and click Connect.

A new dialog window will open. There, you should add the firewall rule.

Note: When the IP Address of the workstation/Network used to create Azure SQL Server differs from the IP Address of the computer used to connect to the Azure SQL Server, the dialog window opens. It allows us to add the IP Address of the computer used to connect to the Azure SQL Server.

Click Sign In.

Provide the appropriate username and password of the Microsoft account.

Once you are successfully authenticated, you can set the firewall rules. The Firewall Rule dialog box gets enabled. There, you need to provide the desired name, the IP Address, and click OK.

We have successfully connected to the Azure SQL instance. To check the Azure SQL Server version, run the following command in the SSMS query editor:

Use master
Go
Print @@version

Now, let us create an Azure SQL database.

Create the Azure SQL Database Using SQL Server Management Studio

In this section, we’ll explore creating the SQL database with the help of SSMS. Another popular tool for doing the task is dbForge Studio for SQL Server that applies to both SQL Server and SQL Azure. You can use this tool as an alternative.

Here, let us examine creating the Azure SQL Database with the SQL Server Management Studio.

Open SSMS > right-click on Databases > New Database.

The New Database window opens. 

Note: The SSMS wizard serving to create and configure a new database is entirely different from the wizard that we use for creating an on-premises database.

Enter the desired database name into the Database Name field:

In the Options section, you can see various options to configure the SQL database. Apply or change them according to your business requirements:

Click OK to create the database. Once it is created, you can view it in the Databases folder in Object Explorer:

Alternatively, you can run the following query to view the database:

select database_id,a.name,b.name,a.create_date,compatibility_level,collation_name
from sys.databases a 
left join sys.server_principals b on a.owner_sid=b.sid

Output:

Summary

This article demonstrated the ways of configuring an Azure SQL Server instance. Also, it explained how we can connect it using SQL Server Management Studio.

If you liked the article, go on CodingSight to find out more similar ones. Here you can find everything you need to know about SQL and other database technologies.

Previously published at https://codingsight.com/connect-to-sql-azure-via-ssms/