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.

Subscribe
Notify of
guest
1 Comment
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
rakesh
rakesh
8 months ago

thanks to sharing jami for troubleshoot Pipeline Job failure in azure that really helps devops software engg, keep sharing blog

1
0
Would love your thoughts, please comment.x
()
x