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.

Jami Raj

MotoShare.in is your go-to platform for adventure and exploration. Rent premium bikes for epic journeys or simple scooters for your daily errands—all with the MotoShare.in advantage of affordability and ease.