Azure Active Directory Password-based Authentication

This article shows how to authenticate with a username and password.

Common Prerequisites

• Available account in Azure Active Directory.
• Available Azure MS SQL server.
• Azure Active Directory is set to centrally manage identity and access to Azure SQL Database.
• Installed MS ODBC Driver for SQL Server. The current version is 17.

DSN Connection

You can connect with DNS using Data Connection Manager (DCM) or without it.

Prerequisites: User or System DSN is configured in ODBC data source manager to use Azure Active Directory Password authentication.

DSN Connection without DCM

1. Place the Input Data tool on the Designer canvas.
2. Select the down arrow in the Connect a File or Database field.
3. From Data Sources, choose Microsoft Azure SQL Database - ODBC.
   
4. Choose DSN created in the preconditions from the dropdown.
5. Provide Username and Password and select OK.
   
6. Choose any table from your database in the Query Builder.
7. Select OK.

DSN Connection Using DCM

1. Make sure that DCM is enabled.
2. Place the Input Data tool on the Designer canvas.
3. Below Connect a File or Database field, select Set Up a Connection.
4. From Data Sources, choose Microsoft Azure SQL Database - ODBC.
5. Provide data source details and select Save.
6. ODBC DSN should have the name of DSN created in ODBC Data Sources.
   
7. Select Connect Credential.
8. Choose the User name and password authentication method credential type.
9. Select Link or Create and Link.
10. Select Create New Credential for the Credential.
11. Enter a descriptive name for Credential, User name and Password, and select Create and Link.
    
12. Select Connect.
    
13. Choose any table from your database in the Query Builder.
14. Select OK.

DSN-less Connection

You can establish a DSN-less connection using Data Connection Manager (DCM) or without it.

DSN-less Connection without DCM

1. Place Input Data tool on Designer canvas
2. Enter this connection string to Connect a File or Database field:
   odbc:Driver={ODBC Driver 17 for SQL Server};Server=tcp:<_your_server_>,1433;Database=<_your_db_>;Uid=<_user_name_>;Pwd=<_your_pw_>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword
   Replace text between <> with actual values.
3. Select the 3 dot icon next to the Table or Query option.
4. Choose any table from your database in the Query Builder.
5. Select OK.

DSN-less Connection Using DCM

1. Make sure that DCM is enabled.
2. Place the Input Data tool on the Designer canvas.
3. Below the Connect a File or Database field, select Set Up a Connection.
4. Choose Microsoft SQL Server - Quick connect from Data Sources.
5. Select the Add Data Source button.
6. Provide a descriptive name for Data Source Name, actual server, and database name.
7. Make sure the Driver is set to ODBC Driver 17 for SQL Server.
8. Select the Save button.
   
9. Select Connect Credential.
   
10. Choose the Azure Active Directory Password authentication credential.
11. Select Create New Credential for the Credential.
12. Enter a descriptive name for Credential, User name, and Password and select Create and Link.
    
13. Select Connect.
    
14. Select any table from your database in the Query Builder.
15. Select OK.