Daniel Calbimonte
drag and drop data flow

ODBC drivers for Azure SQL

November 9, 2022 by


In previous articles, we saw how to connect with ODBC to SQL Server using the ODBC Data Source Administrator in Windows. This time, we will connect to Azure and export the data from one table in Azure to SQL Server on-premises using SSIS.


  1. First, you will need an Azure Account.
  2. Secondly, you will need an Azure SQL database created in Azure. For more information about this topic, refer to the below article:
  3. SSDT installed and VS Installed AND SQL Server Integration Services were selected.
  4. SQL Server on-premises installed.


In this article we will talk about the following topics:

  1. Azure SQL Server, Database and credentials, firewall, and how to create a table with data in Azure.
  2. How to connect to Azure SQL using the ODBC Data Source Administrator.
  3. Use the connection to export data from a table in Azure SQL into SQL on-premises.

Azure SQL Server, Database, and credentials and how to create a table with data

If you accomplished the requirements, you already have an Azure SQL Database. In Azure go to the Azure SQL Section.

Azure Portal

In this example, the Azure server name is myAzure101 and the database name is myAzureDB. Click on it.

Azure database

Note that the complete name for the server is myserverazure101.database.windows.net. The full name is very important later to create the connection.

Press the set server firewall in the database. This option will allow you to add your current local machine to have access to the Azure SQL Database. If the firewall does not allow your client IP, the connection will be blocked.

Azure set password

To create a table with data (if you do have it), you can use the Query editor, connect and run the following query to create a table named dbo.Culture and insert some data.

Azure create table

If you go to the Azure SQL Server, to the Overview, you can see the login. In this example the administrator’s name is daniel. You can optionally reset the password here.

Azure Portal passwords

OK, it is time to return to our Windows on-premises machine and start doing our ODBC connection.

How to connect to Azure SQL using the ODBC Data Source Administrator

You can use Windows Search to open the ODBC Data Sources and open it.

Open ODBC Data Source

In the ODBC Data Source Administration, we will press the Add button to add an Azure SQL ODBC connection.

add azure connection

You can use any name for the data source. Just remember the name to use it later in SSIS. The description is optional, but it is a good practice to have a good description. Especially if you have a lot of connection and you don’t usually remember what the connection do.

The SQL Server is the Azure SQL Server name that we mentioned previously in the Azure section.

create odbc connection name

This is the most important part of the ODBC for Azure. There are several options that an On-premises guy is not familiar with.

You can connect using Azure Active Directory Integrated authentication. That means that the connection will detect your Azure Active Directory account. For more information about Azure Active Directory accounts, refer to the following link:

The other option is With SQL Server authentication using a login ID and password entered by the user. This is the easiest way and the way we will do it. Unfortunately, it is unsafe to do this way. The safest way is to do it using AD. However, for educational purposes and to avoid a very extensive article, we will use that option. As you can see, we are using the daniel login which is an administrator in the Azure SQL Server in the Portal

You can also enter the Azure Active Directory credentials or use an Azure Managed Service Identity authentication. This option allows authenticating without using the credential.

select azure authentication odbc

If the connection is working, you will be able to check the default database and select the myAzureDB database from the portal.

select azure database

You can keep the default values in the following step wizard and Finish.

ODBC wizard options

Use the connection to export data from a table in Azure SQL into SQL on-premises

We will need to open an SSIS Project for this section. If you did not complete the requirements, follow this tutorial to install the SSDT tools with SQL Server Integration Services and create an SSIS Project. We have a table named dbo.Culture created in the Portal and an ODBC Connection was created. We will export the table from Azure to SQL Server.

In the SSIS Project, double-click the Data Flow Task to create a new task.

drag and drop data flow

Double-click the Data Flow Task just created and drag and drop the ODBC Source and the OLE DB Destination.

We will use the ODBC Source to Connect to Azure and the OLE DB Destination to create a connection to SQL Server on-premises and import data from the Azure SQL table.

Drag and drop odbc source

Double click the ODBC Source Task and in the task press the New button.

create new azure sql odbc connection

Select the AzureConnection. This is the name of the ODBC Connection created on the Wizard when we created the ODBC Connection to Azure. If necessary, include the Login information from Azure.

select odbc connection ssis

Select the culture table from Azure. We created this table in the portal. If you have a different table in your portal use the table of your preference.

odbc source select table

Double click on the OLE DB Destination if you do not have a connection created. You will need to select a connection in your local SQL Server. In this example, we are using the Adventureworks on-premises database, but you can use any database to import the table from Azure. Once the connection is set. Press the New button to create a destination table.

oledb destination

Modify the table name and the data types according to your preferences. These are the T-SQL statements to create the destination table.

create table ssis

Go to the Mappings tab to map the columns from source to destination. In this example, the default values are fine.

Map azure to sql on-premises

Finally, run the package to export the data to SQL on-premises.

run package ssis


In this article, we set the firewall in our Azure SQL database to create a connection to it. We also created an Azure SQL table and then created an ODBC connection in Windows to our Azure SQL database. Finally, we exported the Azure SQL table to SQL Server on-premises.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)