Azure Data Factory Pipeline Job failure with SqlErrorNumber=18456

Go through the steps to troubleshoot and resolve the error message you encountered in your Azure Data Factory (ADF) pipeline job:

Step 1: Check Linked Service Configuration

Open your Azure Data Factory portal.
Go to the “Author & Monitor” section.
Click on “Connections” and find the linked service associated with your SQL Database.
Verify that the server name, database name, authentication method, and credentials are correct.

Step 2: Check Firewall Configuration

In the Azure portal, navigate to your SQL Database resource.
Go to the “Firewalls and virtual networks” section.
Ensure that the firewall settings allow access from the IP address or range associated with the integration runtime used by your ADF pipeline.
If needed, add the necessary IP address or range to the firewall rules.

Step 3: Verify Authentication Method

Double-check the authentication method specified in the linked service configuration for your SQL Database.
If using SQL authentication, confirm that the provided username and password are accurate.

Step 4: Check User Permissions

Verify that the user specified in the linked service configuration has the required permissions to connect to the SQL Database and perform the necessary operations.
Ensure that the user has appropriate permissions to create users in the target database.

Step 5: Inspect Error Details

Take note of the activity ID mentioned in the error message (e.g., b53939d9-414a-4f95-be1a-96f56f36b458).
Use this activity ID to locate specific logs related to the failed login attempt in the Azure Data Factory logs or diagnostic settings.

Step 6: Test Connectivity

Use a SQL client tool such as Azure Data Studio or SQL Server Management Studio.
Manually connect to the SQL Database using the same credentials as specified in the linked service.
Verify if the connection is successful and if you can perform the required operations, such as creating users in the database.

Steps with Examples

Step 1: Check Linked Service Configuration

Open your Azure Data Factory portal.
Go to the “Author & Monitor” section.
Click on “Connections” and find the linked service associated with your SQL Database.
Verify that the server name, database name, authentication method, and credentials are correct.

Example: Confirm that the server name is ‘c10wdedhauditu.database.windows.net’, the database name is ‘edhuwauditdb’, and the authentication method and credentials match the user specified in the linked service.

Step 2: Check SQL Database Firewall Configuration

In the Azure portal, navigate to your SQL Database resource.
Go to the “Firewalls and virtual networks” section.
Ensure that the firewall settings allow access from the IP address or range associated with the integration runtime used by your ADF pipeline.

Example: Add a firewall rule to allow access from the integration runtime’s IP address (e.g., 10.1.2.3) to the SQL Database.

Step 3: Verify Authentication Method

Double-check the authentication method specified in the linked service configuration for your SQL Database.
If using SQL authentication, confirm that the provided username and password are accurate.

Example: Verify that the linked service is configured with the correct SQL authentication credentials, such as the username ‘myusername’ and the corresponding password.

Step 4: Check User Permissions

Verify that the user specified in the linked service configuration has the required permissions to connect to the SQL Database and perform the necessary operations.
Ensure that the user has appropriate permissions to create users in the target database.

Example: Confirm that the user specified in the linked service has sufficient privileges to connect to the ‘edhuwauditdb’ database and create users.

Step 5: Inspect Error Details

Take note of the activity ID mentioned in the error message (e.g., b53939d9-414a-4f95-be1a-96f56f36b458).
Use this activity ID to locate specific logs related to the failed login attempt in the Azure Data Factory logs or diagnostic settings.

Example: In the Azure Data Factory portal, search for the activity ID ‘b53939d9-414a-4f95-be1a-96f56f36b458’ to find the corresponding logs and detailed error information.

Step 6: Test Connectivity

Use a SQL client tool such as Azure Data Studio or SQL Server Management Studio.
Manually connect to the SQL Database using the same credentials as specified in the linked service.
Verify if the connection is successful and if you can perform the required operations, such as creating users in the database.

Example: Use Azure Data Studio to connect to the ‘c10wdedhauditu.database.windows.net’ server using the provided username and password. Verify if the connection is successful and if you can perform user-related operations.