How to Connect to Azure SQL database (Multiple ways)

There are multiple ways to connect to an Azure SQL database. Various IDEs have the functionality to connect to the Azure SQL database. In this article, you will see the various ways of connecting to the Azure SQL database.

• Connect to Azure SQL database from SQL server management studio
• Connect to Azure SQL database from visual studio
• Connect to Azure SQL database from Azure data studio
• Connect to Azure SQL database from Azure portal
• Connect to Azure SQL database using powershell
• Connect to Azure SQL database using ip address

Connect to Azure SQL database from SQL server management studio

In our previous articles, we used the SQL server management studio to manage any SQL server instance on a local machine. However, we can also use the SQL server management studio to manage Azure SQL databases remotely.

In this section, you will see how you can connect to the Azure SQL databases through the SQL server management studio.

• The first step is to configure the firewall rules for your Azure SQL server.
• You need to allow your public IP address or an IP addres range to connect to the Azure SQL server on which you have created the database.
• To configure the firewall rules, open the Azure SQL server Overview and click on Show firewall settings.

• Also, note down the Server name as you will use it for makin a connection.
• In the firewall settings, click on Add client IP to add your current public IP address to the firewall rules. You can also define a rule alowing an IP address or a range of IP addresses manually. Click on Save to update the firewall rules.

• Once you have updated the firewall rules, open SQL server management studio.
• It will ask you to connect to a database. If not, click on the Connect option and then click Database Engine as shown below in the image.

• Enter the server name that you noted down in one of the above steps.
• Select the SQL Server Authentication in the Authentication option.
• Enter the admin name and password and click Connect to connect to the database.

• Now you can see the Azure SQL server and the database in the Object Explrorer Window.
• You can expand the database and see the database objects. Also, you can run queries on your database.

• Let us try to query the sample database created by Azure SQL.
• We will execute a Select query to retrieve top 10 records of a table:

USE DemoDB
GO
SELECT Top(10)* FROM SalesLT.Customer

Thus, you might have learned how to connect to an Azure SQL database with SQL Server management studio.

Read Pause and resume Azure SQL database

Connect to Azure SQL database from visual studio

In this section, you will learn how to use Visual Studio to connect to an Azure SQL database.

Firstly, you have to install the SQL Server tools in the Visual Studio. To install the SQL Server tools, type install SQL server tools in the search box in Visual Studio. Click on Install SQL server tools.

Once you have installed the SQL Server tools, you can now connect to any Azure SQL database and query the database. To connect to an Azure SQL database, follow the steps below:

• In the Visual Studio, click on View in the menu bar and click SQL Server Object Explorer.

• In the SQL Server Object Explorer Window, right click on SQL Server and then click on Add SQL Server… to add a new server.

• In the Connect dialogue box, enter the Azure SQL server credentials like server name and type of authentication. You can find the servername on the Azure portal. the server name looks like ******.database.windows.net. Click on Connect.

• Now the Visual Studio is connected to the Azure SQL. Now you ccan view the database and its objects in the SQL Server Object Explorer Window and also execute queries in the database.

Thus, you might have learned how to connect to the Azure SQL database using Visual Studio.

Read How to rename a database in Azure SQL

Connect to Azure SQL database from Azure data studio

In this section, you will learn how to connect to an Azure SQL database using Azure data studio.

When you install SQL server management studio, the Azure studio is also installed along with it. You do not need to download the Azure studio. To connect to an Azure SQL database, follow the below steps:

• Open the Azure data studio and click on the Connections icon and then click on the connection icon.

• Enter the server name and select the sql server authentication. Enter the login credentials.
• If you do not know the server name, you can see the server name in the Azure portal. Click on the server or the database. In the Overview, you will see the server name.
• You can also set a default database to which the Azure Data studio will connect.

• After entering all the details in the Azure Data Studio, click on Connect.

• Once you have successfully created a connection, you can explore and view the database objects. You can also run queries on the database.

Thus, you might have learned how to connect to an Azure SQL database using the Azure Data Studio.

Read Backup and restore SQL Server to Azure Blob storage

Connect to Azure SQL database from Azure portal

You can also access the Azure SQL database from the Azure portal. There are two ways to login into the Azure SQL database from the Azure portal:

1. SQL Server authentication: Login using the credentials that you provided when you created the server.
2. Active Directory authentication: Logging into the server from you Microsoft Azure account credentials.

To connect to Azure SQL database from the Azure portal, follow the below steps:

• Open the Azure portal in the browser and login.
• Open the database overview in the Azure portal.
• Click on Query Editor(preview) in the side navigation bar.
• Now you will have two options i.e. SQL server authentication and Active Directory authentication for logging into the database.

• After logging in, you can view the database objects and start querying the database in the query editor window.

Read Cannot open backup device operating system error 50 azure

Connect to Azure SQL database using Powershell

We can connect to an Azure SQL database and execute queries on the database using Windows Powershell. But there are some prerequisites before you can connect to an Azure SQL database.

• You need to install the Az module in Windows Powershell. The Az module cointains various commands and uitilities to manage the Azure resorces from a command line. To install this module, run Windows Powershell as an administrative.
• Type the command Install-Module Az in Windows Powershell to install the module. It may ask you to download some more modules. Download the required modules.

Secondly, you will need to install SQL Server tools in Windows Powershell to execute SQL Server queries. You can install the SQL Server tools by executing the following command in PowerShell:

Install-module -Name SQLServer

These modules are pre-installed in the Azure PowerShell. You can access the Azure Powershell from the Azure portal homepage.

Once you have installed the Az module and all its dependencies in Windows PowerShell, you are ready to connect to your Azure account. Type the command Connect-AzAccount in Windows PowerShell to connect to your Azure account. This will pop up a login screen where you can use your login credentials to connect to your Azure account.

Once you have signed in to your Azure account, you will see an output like below in the image:

You can also verify if you are connected to your Azure account by using the following command:

Get-AzSqlServer -ResourceGroupName <your resource group name>

This command will give information about your resource group like the server name, server location, etc.

As you are connected to your Azure account from Windows Powershell, you can connect to any database and execute queries on it. There are some steps that need to be followed while connecting to the database and executing the queries.

Look at the below code:

# Importing the Az.Sql module
Import-Module Az.Sql -Force
 
# Setting up the parameters and writing the query
$Parameter = @{
   'ServerInstance' = 'mysql1000.database.windows.net';
   'Database' = 'DemoDB';
   'Username' = 'azureadmin';
   'Password' = '*******';
   'Query' = 'SELECT pc.Name as CategoryName, p.name as ProductName
FROM [SalesLT].[ProductCategory] pc
JOIN [SalesLT].[Product] p
ON pc.productcategoryid = p.productcategoryid;'
}
 
# Executing the query
Invoke-Sqlcmd @Parameter

• Firstly, we imported the Az.Sql module into our script in order to use it.
• Then we created some variables and prepared a parameter that we will pass to the executing statement.
• The variables contains the server information and the login credentials. Also we will write a query inside a variable that we want to execute. In our case, we used a Select query to retrieve some records from the sample database.
• The last step is to excecute the query with Invoke-Sqlcmd command and pass the parameter that we prepared earlier.

As you can see, the query got executed successfully and we can see the results. In the same way, you can create your own queries and execute them.

Read Reset Password Azure SQL database

Connect to Azure SQL database using ip address

If you want to connect to an Azure SQL database, you have to use the domain name. You cannot connect to an Azure SQL database using an IP address. This is because Azure SQL creates a logical server and does not offer a static IP address.

As there is not any static IP address associated with an Azure SQL database, you cannot connect to the database using an IP address. The only way to connect to the Azure SQL database is to use the logical server name. The logical server name looks like ********.database.windows.net.

You may like the following Azure SQL tutorials:

• Cannot open server requested by the login. client is not allowed to access the server

Here we learned different ways to connect to Azure SQL database.

• Connect to Azure SQL database from SQL server management studio
• Connect to Azure SQL database from visual studio
• Connect to Azure SQL database from Azure data studio
• Connect to Azure SQL database from Azure portal
• Connect to Azure SQL database using powershell
• Connect to Azure SQL database using ip address