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

• How Do I Make Azure Tag
• Cannot Open Server Requested By The Login Azure SQL

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.

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.

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.

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.

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’.

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.

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.

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.

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

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.

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

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),
);

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

Insert data

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

INSERT INTO MYINFO (LastName, FirstName)
VALUES ('sahoo', 'Rajkishore');

Select the result

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

Select * from MYINFO

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.

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.

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.

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.

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

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.

Now click on the Open in Visual Studio button.

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

Then it will open the Visual Studio 2019 for me.

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

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.

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),
);

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

Insert data

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

INSERT INTO EmployeeDetails (LastName, FirstName)
VALUES ('Bijaya', 'Kumar');

View the result

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

Select * from EmployeeDetails 

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.

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.

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

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.

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

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

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.

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();
        }
    }
}

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.

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.

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

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

Now you can see below the Project got created successfully.

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>

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>

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)
        {

        }
    }
}

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

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>

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 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>

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 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.

• In order to connect to Azure SQL DB using PowerShell, as a prerequisite, you must install the Azure PowerShell module.

• 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

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:

• What is Microsoft Cloud Background Check
• How to Export Azure SQL Database
• How to create SQL managed instance in azure
• The Server Was Not Found Or Was Not Accessible
• How to add bulk guest users in Azure AD B2B from Azure Portal and PowerShell

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.