{"id":35587,"date":"2023-05-24T14:24:16","date_gmt":"2023-05-24T14:24:16","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=35587"},"modified":"2023-05-24T14:24:18","modified_gmt":"2023-05-24T14:24:18","slug":"azure-data-factory-pipeline-job-failure-with-sqlerrornumber18456","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/azure-data-factory-pipeline-job-failure-with-sqlerrornumber18456\/","title":{"rendered":"Azure Data Factory Pipeline Job failure with SqlErrorNumber=18456"},"content":{"rendered":"\n<p>Go through the steps to troubleshoot and resolve the error message you encountered in your Azure Data Factory (ADF) pipeline job:<\/p>\n\n\n\n<p>Step 1: <strong>Check Linked Service Configuration<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open your Azure Data Factory portal.<\/li>\n\n\n\n<li>Go to the &#8220;Author &amp; Monitor&#8221; section.<\/li>\n\n\n\n<li>Click on &#8220;Connections&#8221; and find the linked service associated with your SQL Database.<\/li>\n\n\n\n<li>Verify that the server name, database name, authentication method, and credentials are correct.<\/li>\n<\/ul>\n\n\n\n<p>Step 2: <strong>Check Firewall Configuration<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>In the Azure portal, navigate to your SQL Database resource.<\/li>\n\n\n\n<li>Go to the &#8220;Firewalls and virtual networks&#8221; section.<\/li>\n\n\n\n<li>Ensure that the firewall settings allow access from the IP address or range associated with the integration runtime used by your ADF pipeline.<\/li>\n\n\n\n<li>If needed, add the necessary IP address or range to the firewall rules.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<p>Step 3: <strong>Verify Authentication Method<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Double-check the authentication method specified in the linked service configuration for your SQL Database.<\/li>\n\n\n\n<li>If using SQL authentication, confirm that the provided username and password are accurate.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<p>Step 4: <strong>Check User Permissions<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>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.<\/li>\n\n\n\n<li>Ensure that the user has appropriate permissions to create users in the target database.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<p>Step 5: <strong>Inspect Error Details<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Take note of the activity ID mentioned in the error message (e.g., b53939d9-414a-4f95-be1a-96f56f36b458).<\/li>\n\n\n\n<li>Use this activity ID to locate specific logs related to the failed login attempt in the Azure Data Factory logs or diagnostic settings.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<p>Step 6: <strong>Test Connectivity<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use a SQL client tool such as Azure Data Studio or SQL Server Management Studio.<\/li>\n\n\n\n<li>Manually connect to the SQL Database using the same credentials as specified in the linked service.<\/li>\n\n\n\n<li>Verify if the connection is successful and if you can perform the required operations, such as creating users in the database.<\/li>\n<\/ul>\n\n\n\n<p> <strong>Steps with Examples<\/strong><\/p>\n\n\n\n<p>Step 1: <strong>Check Linked Service Configuration<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open your Azure Data Factory portal.<\/li>\n\n\n\n<li>Go to the &#8220;Author &amp; Monitor&#8221; section.<\/li>\n\n\n\n<li>Click on &#8220;Connections&#8221; and find the linked service associated with your SQL Database.<\/li>\n\n\n\n<li>Verify that the server name, database name, authentication method, and credentials are correct.<\/li>\n<\/ul>\n\n\n\n<p>Example: Confirm that the server name is &#8216;c10wdedhauditu.database.windows.net&#8217;, the database name is &#8216;edhuwauditdb&#8217;, and the authentication method and credentials match the user specified in the linked service.<\/p>\n\n\n\n<p>Step 2: <strong>Check SQL Database Firewall Configuration<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>In the Azure portal, navigate to your SQL Database resource.<\/li>\n\n\n\n<li>Go to the &#8220;Firewalls and virtual networks&#8221; section.<\/li>\n\n\n\n<li>Ensure that the firewall settings allow access from the IP address or range associated with the integration runtime used by your ADF pipeline.<\/li>\n<\/ul>\n\n\n\n<p>Example: Add a firewall rule to allow access from the integration runtime&#8217;s IP address (e.g., 10.1.2.3) to the SQL Database.<\/p>\n\n\n\n<p>Step 3: <strong>Verify Authentication Method<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Double-check the authentication method specified in the linked service configuration for your SQL Database.<\/li>\n\n\n\n<li>If using SQL authentication, confirm that the provided username and password are accurate.<\/li>\n<\/ul>\n\n\n\n<p>Example: Verify that the linked service is configured with the correct SQL authentication credentials, such as the username &#8216;myusername&#8217; and the corresponding password.<\/p>\n\n\n\n<p>Step 4: <strong>Check User Permissions<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>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.<\/li>\n\n\n\n<li>Ensure that the user has appropriate permissions to create users in the target database.<\/li>\n<\/ul>\n\n\n\n<p>Example: Confirm that the user specified in the linked service has sufficient privileges to connect to the &#8216;edhuwauditdb&#8217; database and create users.<\/p>\n\n\n\n<p>Step 5: <strong>Inspect Error Details<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Take note of the activity ID mentioned in the error message (e.g., b53939d9-414a-4f95-be1a-96f56f36b458).<\/li>\n\n\n\n<li>Use this activity ID to locate specific logs related to the failed login attempt in the Azure Data Factory logs or diagnostic settings.<\/li>\n<\/ul>\n\n\n\n<p>Example: In the Azure Data Factory portal, search for the activity ID &#8216;b53939d9-414a-4f95-be1a-96f56f36b458&#8217; to find the corresponding logs and detailed error information.<\/p>\n\n\n\n<p>Step 6: <strong>Test Connectivity<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use a SQL client tool such as Azure Data Studio or SQL Server Management Studio.<\/li>\n\n\n\n<li>Manually connect to the SQL Database using the same credentials as specified in the linked service.<\/li>\n\n\n\n<li>Verify if the connection is successful and if you can perform the required operations, such as creating users in the database.<\/li>\n<\/ul>\n\n\n\n<p>Example: Use Azure Data Studio to connect to the &#8216;c10wdedhauditu.database.windows.net&#8217; server using the provided username and password. Verify if the connection is successful and if you can perform user-related operations.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230; <\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-35587","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/35587","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=35587"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/35587\/revisions"}],"predecessor-version":[{"id":35588,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/35587\/revisions\/35588"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=35587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=35587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=35587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}