ADF SQL Managed Instance (SHIR) Service Principal Authentication - Inline vs Credential(Preview) - Test Connection Oddity

Chad Bentz 26 Reputation points Microsoft Employee
2021-09-02T20:13:24.25+00:00

Notable Scenario here that has different behavior switching settings back and forth that is reproducible: - SQL Managed Instance Linked Service - AAD Service Principal Authentication (using certificate stored in keyvault as credential) - Self Hosted Integration Runtime (version 5.8.7875.2) - KeyVault (using CREDENTIAL: User assigned managed identity) to pull "SECRET" (which is a certificate) - Credential - Service Principal linked to vault with certificate stored in secret - Credential - User Assigned Managed Identity (for vault) Having the SQLMI Utilize the Service Principal Credential (notably in preview) - TEST CONNECTION FAILS: ![128750-image.png][1] Connecting via INLINE authentication via Service Principal certificate Credential from the same Linked KeyVault with same(but Inline) credentials works (TEST CONNECTION SUCCEEDS) ![128813-image.png][2] Switching back to the Credential, with the same exact settings:... now it works(TEST CONNECTION SUCCEEDS) ![128863-image.png][3] [1]: /api/attachments/128750-image.png?platform=QnA [2]: /api/attachments/128813-image.png?platform=QnA [3]: /api/attachments/128863-image.png?platform=QnA

Azure Key Vault
Azure Key Vault
An Azure service that is used to manage and protect cryptographic keys and other secrets used by cloud apps and services.
1,124 questions
Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,599 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,559 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 77,676 Reputation points Microsoft Employee
    2021-09-07T12:34:00.887+00:00

    Hello @Chad-4001,

    Thanks for the question and using MS Q&A platform.

    From the above error message, it looks like the service principal don't have permission on the Azure SQL Managed instance.

    129850-image.png

    To use a service principal-based Azure AD application token authentication, follow these steps mentioned in the document - Service principal authentication.

    For more detail, refer to the below articles:

    Hope this helps. Do let us know if you any further queries.

    ---------------------------------------------------------------------------

    Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

  2. Chad Bentz 26 Reputation points Microsoft Employee
    2022-03-10T19:43:44.657+00:00

    We get similar behavior when trying to use Azure IR (Microsoft Managed Virtual Network) along with the Data Flow ... The Source Lookup Data set ...

    • Edit the Linked Service - Test Connection from the Linked Service to SQL MI ALWAYS works and connects to DB (Private Endpoints enabled)
    • When you are in the Data Flow and you use Test Connection to call the Data Set - it would fail with :

    exception:java.util.concurrent.ExecutionException: com.microsoft.aad.msal4j.MsalServiceException: AADSTS7000215: Invalid client secret provided. Ensure the secret being sent in the request is the client secret value, not the client secret ID, for a secret added to app '<our aad sp id goes here>. Trace ID: d6087b52-f309-4148-8635-20108ddbbe00 Correlation ID: e0e2b0b4-a4c0-4908-bd2e-20bcf1d20a69 Timestamp: 2022-03-09 22:36:15Z, authority:None,tenantId:Some(<our aad tenant goes here>), spnId:<our aad sp id goes here>

    We are using Service Principal with KeyVault credential as a CERTIFICATE stored in vault.

    IF we switch the SQL MI Linked Service to authenticate with Service Principal Key - with a hardcoded SECRET ... then it started working.

    THEN we switched it back to Key Vault (with credential as a CERTIFICATE stored under secret in vault) --- now the Test Connection on Data Flow works !!!!

    ^^ This scenario is very similar to the original issue I reported, looks like it is manifesting itself in different ways.