How To Connect To Azure SQL Database

How To Connect To Azure SQL Database

In this Azure tutorial, we will discuss How To Connect To Azure SQL Database. Along with this, we will also discuss the below topics.

  • How to Connect to Azure SQL Database From Visual Studio
  • How To Connect to Azure SQL Database From Visual Studio C#
  • Connect to Azure SQL Database C#
  • How To Connect To Azure SQL Database Programmatically
  • How to Connect to Azure SQL Database ADO.Net
  • How to Use SQL Azure Table for CRUD Operations in ADO.NET
  • Azure SQL Database Connection String

How To Connect To Azure SQL Database

Well, let’s discuss here How To Connect To Azure SQL Database. As part of this tutorial, we will discuss how to use SQL Server Management Studio to connect and query the Azure SQL database.

How To Access Azure SQL Database

Before we start, Below are few things we need as a prerequisites

  • An Azure SQL Database, if you don’t have created yet create an Azure SQL Database now.
  • SQL Server Management Studio needs to be installed in your machine.

Follow the below steps to connect to the Azure SQL Database

Step-1: Login to Azure Portal (https://portal.azure.com/)

Step-2: Search for the SQL databases and click on the same search result.

Connect To Azure SQL Database

Step-3: Now you will find the list of databases that you have created. Click on the one that you want to connect to the SQL Server Management Studio.

connect to azure SQL database

Step-4: Once you will click on the database you want to connect, you will find all the information like status, Resource group, Location, Subscription, server name, etc related to that database.

connect to azure sql database from azure portal

Now copy the Server name from the above page and keep it in a notepad that we need to connect from the SQL Server Management Studio.

Now the next step is Open the SQL Server Management Studio.

Connect to azure sql database using SQL Server Management Studio

Now enter the server name that you copied following the above steps and kept in a note pad, select SQL Server Authentication mode, enter the credentials and click on ‘Connect’.

how to connect sql database in azure

You need to set your firewall rule in Azure Portal, if you have not done it it will show you the below message, so we need to set the firewall rule.

how to connect to azure sql database from ssms

Now to set the firewall rule, navigate to your Azure SQL Database again, and click on the Set server firewall button from the top on the Overview tab.

connect to azure sql server from local ssms

On the Firewall settings, set the Allow Azure services and resources to access this server option to Yes and then click on the + Add client IP button and next click on the Save button to save the changes.

how to connect local sql server to azure

Now you can see below, Successfully updated the server firewall rules.

how to connect local sql server to azure using Azure Portal

Connect To Azure SQL Database From On-premise

Now from the SQL Server Management Studio, enter the credentials and click on the ‘Connect‘ button to connect to the Azure SQL database from the local SQL Server Management Studio.

how to connect sql database in azure

Now you can see below, we have connected to the Azure SQL Database successfully from the local SQL server management studio without any issue.

connect to azure sql database from ssms

This is How To Connect To Azure SQL Database by following the above mentioned steps.

Query data

Now we are connected to the Azure SQL Database, next is let’s try to query data

Let’s create a table in the the database first using the below SQL Script

CREATE TABLE MYINFO
(
LastName varchar(255),
FirstName varchar(255),
);
how to connect to azure sql database from ssms in Azure

Now you can see below, the table has been created successfully with out any issue

how to connect to azure sql database from local sql server management studio


Insert data

Now insert one value to the table MYINFO using the below SQL script.

INSERT INTO MYINFO (LastName, FirstName)
VALUES ('sahoo', 'Rajkishore');
connect azure sql database from ssms

Select the result

Now select the values from the table MYINFO using the below SQL Select Query

Select * from MYINFO
connect to azure sql server database from local ssms

Same way, you can perform the Update data and Delete data operation.

How to Connect to Azure SQL Database From Visual Studio

Above, we discussed How To Connect To Azure SQL Database using the SQL Server Management Studio. Now, let’s discuss How to Connect to Azure SQL Database From Visual Studio. Follow the below steps to connect to the Azure SQL Database from Visual Studio.

Before going to start, as a Prerequisites, we need an Azure account. If you don’t have, create a free Azure Account now.

Assuming you have a free account now, follow the below steps to connect to the Azure SQL Database from Visual Studio.

Step-1: Login to Azure Portal (https://portal.azure.com/)

Step-2: Search for the SQL databases and click on the same search result.

How to Connect to Azure SQL Database From Visual Studio

Step-3: Now you can able to see the list of databases that you have created. Click on the one that you want to connect from the visual studio.

Connect to Azure SQL Database From Visual Studio

Step-4: Now the next step is, set the server firewall rule.

Now to set the firewall rule, click on the Set server firewall button from the top on the Overview tab on your Database page.

how to connect to azure sql database from visual studio c#

On the Firewall settings, you can set the Allow Azure services and resources to access this server option to Yes and then click on the + Add client IP button and next click on the Save button to save the changes.

connect to azure sql database from visual studio 2017

Now you can see below, Successfully updated the server firewall rules. click on the Ok button.

how do i connect to my azure sql database from visual studio 2017

Now you have successfully set the server firewall rule, so the next step is On the database page —> Overview tab —–> click on the Connect with button —-> Click on the Visual Studio.

how to connect to azure sql database from visual studio 2019

Now click on the Open in Visual Studio button.

how to connect to azure sql database from visual studio 2015

Then, click on the Open Microsoft Visual Studio Web Protocol Handler Selector button on the below pop up.

how do i connect to my azure sql database from visual studio 2019

Then it will open the Visual Studio 2019 for me.

connect to azure sql database from visual studio 2019

Now you can see it connected successfully, the SQL Server Object Explorer window opens with a connection to the Azure SQL database.

visual studio connect to azure sql db

Run a test query

Now let’s try to execute a sample query to check if it is working fine. Right-click on the Database ——-> Select the New Query option.

how to connect to azure sql database from visual studio c#

Copy and paste the below SQL script in the query window and click on the Execute button to create a table

CREATE TABLE EmployeeDetails
(
LastName varchar(255),
FirstName varchar(255),
);
connect to azure sql database visual studio

Now you can see, the table EmployeeDetails is created successfully with out any issue.

publish database to azure from visual studio


Insert data

Now insert one value to the table EmployeeDetails using the below SQL script

INSERT INTO EmployeeDetails (LastName, FirstName)
VALUES ('Bijaya', 'Kumar');
Connect to Azure SQL Database From Visual Studio

View the result

Now select the values from the table EmployeeDetails using the below SQL Select Query

Select * from EmployeeDetails 
azure sql connect c#

This is How to Connect to Azure SQL Database From Visual Studio using the above steps and also how to connect to Azure SQL database from visual studio 2019.

How To Connect to Azure SQL Database From Visual Studio C#

Well, Let’s discuss how to connect to Azure SQL database from visual studio c#. As part of this topic, we will discuss how to use the .NET Framework and C# code in Visual Studio to query the Azure SQL database.

Connect to Azure SQL Database C#

Before we are going to start the actual development, As a Prerequisites we need the below things.

How To Connect To Azure SQL Database Programmatically

  • The first thing is the Azure Subscription, If you don’t have a free account, create an Azure free account now.
  • The next thing is you should have an Azure SQL Database created. If not then create one Azure SQL database before we start.
  • Visual Studio 2019 any edition needs to be installed in your machine. I am using here Microsoft Visual Studio Enterprise 2019.

Get Server Name

We need to get the server name from the Azure SQL Database.

Step-1: Login to Azure Portal (https://portal.azure.com/)

Step-2: Navigate to the Azure SQL Database that you have created.

Step-3: Copy the server name from the Database page —-> Overview tab —> Server name. Copy the server name and keep it into a note pad, that we have to use in the C# code. You can click on the copy icon to copy the server name.

How To Connect to Azure SQL Database From Visual Studio C#

Code to Query Azure SQL Database

The next step is to code to query the Azure SQL Database using the C# language using visual studio 2019. Follow the below steps to write the code to query the Azure SQL Database using the C#.

Step-1: Open the Visual Studio 2019, click on the Create New Project.

Code to Query Azure SQL Database

Step-2: On the Create a new project window, select the Console App (.NET Framework) and click on the Next button.

Connect to Azure SQL Database From Visual Studio C#

Step-3: On the Configure your new project window, Provide a name for the Project, Location, Solution name, and choose the Framework as the latest one. Then click on the Create button on the same window.

How To Connect to Azure SQL Database From Visual Studio 2019 C#

Then it will take few seconds to create the project. Now you can see the project has been created successfully.

connect to azure sql database c#

Now the next step is right click on the project name and click on the Manage NuGet Packages link.

asp.net connect to azure sql database

On the  NuGet Package Manager window, select the Browse tab, then search for System.Data.SqlClient and click on that. Now click on the Install button and then it will install the package successfully without any issue.

connect to azure database from visual studio 2019 c#

Now the next step is Open the Program.cs file and you can replace the code

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyDemoApp
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                SqlConnectionStringBuilder strngbuilder = new SqlConnectionStringBuilder();
                strngbuilder.DataSource = "mytestdb236.database.windows.net";
                strngbuilder.UserID = "rajkishore";
                strngbuilder.Password = "@@@@@@19";
                strngbuilder.InitialCatalog = "MyDemoDB";

                using (SqlConnection mysqlconnection = new SqlConnection(strngbuilder.ConnectionString))
                {
                    Console.WriteLine("\nMy Azure SQL DB:");
               
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT * from EmployeeDetails");
                    String mysql = sb.ToString();

                    using (SqlCommand command = new SqlCommand(mysql, mysqlconnection))
                    {
                        mysqlconnection.Open();
                        using (SqlDataReader datareader = command.ExecuteReader())
                        {
                            while (datareader.Read())
                            {
                                Console.WriteLine("{0} {1}", datareader.GetString(0), datareader.GetString(1));
                            }
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Console.ReadLine();
        }
    }
}
connect azure sql database c#
connect azure sql database visualstudio c#
How To Connect To Azure SQL Database Programmatically

Note: Change your DataSource, UserID, Password, InitialCatalog as per yours in the above code.

Now press F5 to run the application or you can select Debug > Start Debugging, and you can see we got the desired output as below.

connect azure database c#

How to Connect to Azure SQL Database ADO.Net

As part of this topic what we will do is we will do CRUD Operations in Azure SQL DB using ADO.Net How to Use SQL Azure Table for CRUD Operations in ADO.NET

As part of the functionality what we will do is, we will create ASP.Net web application and we will add few controls few controls like Gridview, text box and button.

So the overall idea is we will insert the data to the Azure SQL table and we will get the data from the table and bind those data to the grid view and display it.

So now let’s see the implementation step by step as below

We will use here, the latest version of Visual Studio i.e Visual Studio 2019 here as the IDE.

Open the Visual studio, click on the Create a new Project button

On the Create a new project window, select the ASP.NET Web Application(.NET Framework) and click on the Next button.

How to Use SQL Azure Table for CRUD Operations in ADO.NET

On the Configure your new project window, provide the project name, Location and select the latest framework and click on the Next button.

How to Connect to Azure SQL Database ADO.Net

On the Create a new ASP.NET Web Application, select Empty and then click on the Create button.

Connect to Azure SQL Database ADO.Net

Now you can see below the Project got created successfully.

Performing CRUD Operations On Azure SQL Database

Now open the Web.config file and add the connection string

 <connectionStrings>
    <add name="MynewConnection"
         connectionString="Server=tcp:mytestdb236.database.windows.net,1433;Database=MyDemoDB;User ID=rajkishore;Password=tamanna@19;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
Perform CRUD Operations On Azure SQL Database

Now the next step is I have added a file i.e WebForm1.aspx , on the design pat i have added the below code

<Table>
            <tr>
                <td>
                    <asp:GridView ID="GridView1" runat="server" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"></asp:GridView>
                </td>
            </tr>
            <tr>
                <br />
            </tr>
            <tr>
                <td>
                    FirstName:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
               <br />
                LastName: <asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox>

                </td>
            </tr>
            <tr>
                <td>
                     <asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click1" />
                </td>
            </tr>
            </Table>
How to Perform CRUD Operations On Azure SQL Database

Now open the WebForm1.aspx.cs file and add the below line of code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MyDemoWebApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
      public void BindGridView()
        {
            SqlDataAdapter daAdopter = new SqlDataAdapter("Select * from EmployeeDetails", ConfigurationManager
                .ConnectionStrings["MynewConnection"].ConnectionString);
            DataSet myds = new DataSet();
            daAdopter.Fill(myds);
            GridView1.DataSource = myds.Tables[0];
            GridView1.DataBind();
        }
        public int ExecuteCommand(string myQuery)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager
                .ConnectionStrings["MynewConnection"].ConnectionString);
            SqlCommand sqlcom = new SqlCommand(myQuery, sqlcon);
            sqlcon.Open();
            int res = sqlcom.ExecuteNonQuery();
            sqlcon.Close();
            return res;
        }
       protected void Button1_Click1(object sender, EventArgs e)
        {
            string qry = "Insert into EmployeeDetails Values ('" + TextBox1.Text + "','" + TextBox2.Text + "')";
            if (ExecuteCommand(qry) > 0)
            {
                BindGridView();
            }
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}
Use of SQL Azure In Visual Studio 2019 Step By Step Guide
Use of SQL Azure In Visual Studio 2019 Step By Step

Now run the application by pressing F5 or Debug –> Start Debugging, you can see below we got the expected output.

How to use SQL Azure In Visual Studio 2019 Step By Step

Azure SQL Database Connection String

We need the Azure SQL Database Connection String to connect to the Azure SQL Database that is there in the cloud from our local Visual Studio. We need to specify the connection strings in the config file to connect to the Azure SQL Database.

Provider System.Data.SqlClient

Standard

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <add name="MynewConnection"
             connectionString="Server=tcp:YourServerName.database.windows.net,1433;Initial Catalog=YourInitialCatalogName;User ID={YourUserName};Password={YourPassword};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
             providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>
azure sql database connection string c#

MARS enabled

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <add name="MynewConnection"
             connectionString="Server=tcp:YourServerName.database.windows.net,1433;Initial Catalog=YourInitialCatalogName;User ID={YourUserName};Password={YourPassword};Encrypt=True;Trusted_Connection=False;MultipleActiveResultSets=True;Connection Timeout=30"
             providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>
Azure SQL Database Connection String

Azure AD Identity With Windows Authentication

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <add name="MynewConnection"
              connectionString="Server=tcp:YourServerName.database.windows.net,1433;Authentication=Active Directory Integrated;Database=yourdatabasename"
             providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>
sql server connection string

With Encrypted

<connectionStrings>
    <add name="MynewConnection"
         connectionString="Data Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=true;Column Encryption Setting=enabled;" 
 providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>
connection string in c# web config

Connection Strings For .NET Framework Data Provider for SQL Server

Standard

connectionString="Server=tcp:YourServerName.database.windows.net,1433;Database=YourDataBaseName;User ID=YourUserID;Password=YourPassword;Trusted_Connection=False;Encrypt=True;

MARS enabled

connectionString="Server=tcp:YourServerName.database.windows.net,1433;Database=YourDataBaseName;User ID=YourUserID;Password=YourPassword;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True;

Azure AD Identity With Windows Authentication

connectionString="Server=tcp:YourServerName.database.windows.net,1433;Authentication=Active Directory Integrated;Database=YourdatabaseName;

With Encrypted

Data Source=YourServerName;Initial Catalog=YourInitialCatalogName;Integrated Security=true;Column Encryption Setting=enabled;

Connection Strings For OLE DB Driver for SQL Server

With SQL Authentication

Provider=MSOLEDBSQL;Data Source=YourServerName;Initial Catalog=YourInitialCatalog;Authentication=SQLPassword;User ID=YourUserName;Password=YourPassword;Use Encryption for Data=true;

Windows authentication with SSPI

Provider=MSOLEDBSQL;Data Source=YourServerName;Initial Catalog=YourInitialCatalog;Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true;

AAD integrated authentication

Provider=MSOLEDBSQL;Data Source=YourServerName;Initial Catalog=YourInitialCatalog;Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true;

So these are few lists of Azure SQL Database Connection String.

Powershell Connect To Azure SQL Database

You can also connect to Azure SQL Database using PowerShell. using the below information.

  • Now, you need to authenticate to Azure using the Azure PowerShell cmdlet. Once you will run the below PowerShell cmdlet, it will prompt you to enter your Azure Credential.
PS C:\WINDOWS\system32> Connect-AzAccount
  • As a next step, you can use the below Azure PowerShell cmdlet to verify if everything is working fine.
Get-AzSqlServer -ResourceGroupName <Your Resource Group>
Get-AzSqlServer -ResourceGroupName Demo123

If you will runt the above PowerShell cmdlet and all ok, you should get the output like below

Powershell Connect To Azure SQL Database
ResourceGroupName        : Demo123
ServerName               : test45
Location                 : eastus
SqlAdministratorLogin    : rajkishore
SqlAdministratorPassword : 
ServerVersion            : 12.0
Tags                     : {}
Identity                 : 
FullyQualifiedDomainName : test45.database.windows.net
ResourceId               : /subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourc
                           eGroups/Demo123/providers/Microsoft.Sql/servers/test45
MinimalTlsVersion        : 
PublicNetworkAccess      : Enabled
  • You can run the below script to import the Azure SQL module and set different parameters like database, username, credentials and query and you can run Invoke-SqlCmd to initiate the transact SQL query.
Import-Module Az.Sql -Force
 

$MyParams = @{
   'ServerInstance' = 'demo126.database.windows.net';
   'Database' = 'TSInfodemo';
   'Username' = 'Rajkishore';
   'Password' = 'Raj@12345';
   'Query' = 'Your select query;'
}

Invoke-Sqlcmd @MyParams
  • Now, if you want you can also read the Azure SQL server and DB then you can pass the information to the actual query as shown below
Import-Module Az.Sql -Force
 
$resourcegrpName = '<Your Resource Group Name>'
$myServerDetails = Get-AzSqlServer -ResourceGroupName $resourcegrpName
$mysqlDB = Get-AzSqlDatabase -ServerName $myServerDetails.ServerName -ResourceGroupName $resourcegrpName 
 
$MyParams = @{
   'ServerInstance' = $myServerDetails.FullyQualifiedDomainName;
   'Database' = $mysqlDB.DatabaseName[0];
   'Username' = $myServerDetails.SqlAdministratorLogin;
   'Password' = ‘<Password>’;
   'Query' = 'Your SQL Query;'
}
 
Invoke-Sqlcmd @MyParams

Connect To Azure SQL Database Python

You can easily connect to Azure SQL database using Python using the below instruction.

As a prerequisites, you need the below stuffs

  • You must have an Azure Account. If you don’t have an Azure Account, you can create an Azure Free account now.
  • You must have a database.
  • You must install Python and the other dependencies.

Open your favorite text editor and name it as test.py.

Now add the below script to that file.

import pyodbc
myserver = '<yourservername>.database.windows.net'
databasenm = '<yourdatabasename>'
username = '<username>'
password = '<password>'   
driverdetails= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driverdetails+';SERVER='+myserver+';PORT=1433;DATABASE='+databasenm+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 4 name FROM sys.databases")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()
  • Now, open the command prompt and run the below command to run the above script
python test.py

You may like following Azure tutorials:

Conclusion

Well, here we discussed, How To Connect To Azure SQL Database, How to Connect to Azure SQL Database From Visual Studio, How To Connect to Azure SQL Database From Visual Studio C#, How To Connect To Azure SQL Database Programmatically, Connect to Azure SQL Database C# and finally we also discussed How to Connect to Azure SQL Database ADO.Net, How to Use SQL Azure Table for CRUD Operations in ADO.NET and Azure SQL Database Connection String.