Rajeev Pentyala – Microsoft Power Platform

Connecting to Azure SQL Server from SSIS using ‘OLEDB Connection manager’  is not a straight forward way, if you are to connect using your ‘Azure Account’ credentials.

The only way to connect using ‘Azure Account’ is by using ‘ADO.NET connection’ manager.

But then, how to connect to Azure SQL using OLEDB? Answer is by using ‘SQL User’. And yes, we need to create ‘SQL User’ on the Azure DB which you are connecting to and use the same in SSIS OLEDB.

Below are the steps to create ‘SQL User’ and use that to connect to ‘Azure SQL Server’ from SSIS OLEDB.

Create a ‘SQL Login’ on Azure SQL Server:

• Connect to Azure SQL Server using SQL Server Management Studio (SSMS) using the Azure Account. ‘Azure Account’ must be Administrator to ‘Azure SQL Server’

• Select ‘Master’ Database and open a ‘New Query’ window.
  • Note that, ‘SQL Login’ creation query must only run on ‘Master’ database
• Create a ‘Login Account’ using below query. In my query I am creating a Login Account by name ‘MyLogin’

Create a ‘SQL User’ on required Databases:

• Post creation of ‘SQL Login’, now we have to create ‘SQL User’ account against all the Databases, which you want access from SSIS OLEDB.

• From the SSMS -> Object Explorer, select the Database and open a ‘New Query’ window.

• Create a ‘SQL User’ using below query. In my query I am creating a SQL User by name ‘MyUser’

• Add ‘db_datareader’ and ‘db_datawriter’ roles to the ‘MyLogin’ Login Account using below queries.

• Note: You can combine and run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries together.
• Also, run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries against ‘Master’ Database as well.
• Refer this article for list of ‘Roles’

Connect to Azure SQL from SSIS OLEDB:

As we have ‘SQL User’ created and granted access against the required databases, to connect to Azure SQL from OLEDB.

• Open the ‘OLEDB Connection Manager’
• Set ‘Server name’ to ‘Azure SQL Server’ name
• Set ‘Authentication’ to ‘SQL Server Authentication’
• Set ‘User name’ and ‘Password’ to ‘SQL User’ credentials created in above section

🙂