Unable to enable Microsoft Entra authentication for SQL Server 2022 in Azure Arc page in azure portal

Question

Unable to enable Microsoft Entra authentication for SQL Server 2022 in Azure Arc page in azure portal

Chew Kok Hoor 30

I tried enabling Entra ID in Microsoft Entra ID and Purview for my on premise SQL Server 2022 server registered successfully in Azure Arc. I have also added key vault access policy to the server name, and enabled network access from the server ip.

I receive following error:

Exception occurred while downloading AAD certificate. Unable to get a valid certificate from AKV. AKV does not contain a valid certificate that can be used for AAD authentication.. Exception occurred while installing AAD certificate. https://###.vault.azure.net/secrets/###-MSSQLSERVER######### Some or all identity references could not be translated.Exception occurred while installing AAD certificate. https://###.vault.azure.net/secrets/###-MSSQLSERVER##########

I have tried various settings including using service managed or customer managed keys and application but is the same error.

Givary-MSFT 35,621 Reputation points Microsoft Employee Moderator

2024-02-21T06:32:07.8266667+00:00

@Chew Kok Hoor Just wanted to check does the above-mentioned issue has been resolved? I see you mentioned below stating problem is resolved by updating to the latest Cumulative Update 11, are you getting a different error now?
JamesTran-MSFT 36,906 Reputation points Microsoft Employee Moderator

2024-02-26T22:05:01.58+00:00

@Chew Kok Hoor
I just wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.
Chew Kok Hoor 30 Reputation points

2024-03-11T03:59:58.7866667+00:00

@Givary-MSFT @JamesTran-MSFT

Sorry for late reply. The issue still exists. It is an SQL Server 2022 Web Edition which I last check supports Entra ID:

Exception occurred while downloading AAD certificate. Unable to get a valid certificate from AKV. AKV does not contain a valid certificate that can be used for AAD authentication.. Exception occurred while installing AAD certificate. https://XXXXXX-keyvault-mpn.vault.azure.net/secrets/XXXXXX-MSSQLSERVERYYYZZZ: Some or all identity references could not be translated.Exception occurred while installing AAD certificate. https://XXXXXX-keyvault-mpn.vault.azure.net/secrets/XXXXXX-MSSQLSERVERYYYZZZ

I once tried it succeeded but unfortunately after going back to the Microsoft Entra ID and Purview all settings seems gone. Now when I try again, this is the same error. I have checked and ensured the server has permission to access the vault.
Chew Kok Hoor 30 Reputation points

2024-03-11T11:20:14.94+00:00

Do I need to enable any Windows authentication with Entra to the on premise Windows Server 2022 first? I am using Standard Edition version 21H2.

Because the error can possibly be unable to map my Entra users to Windows or SQL Server? Anyway to recap the os and database:

Microsoft Windows 2022 Standard Edition
Microsoft SQL Server 2022 Web Edition
Dan Decker 0 Reputation points

2024-09-06T10:43:40.9+00:00

Anyone find a resolution on this? I have tried numerous times over the last 3-months to resolve this and it still comes up.
Chew Kok Hoor 30 Reputation points

2024-09-06T12:42:33.37+00:00

Still no good news from my end.
IdahoSpud 0 Reputation points

2024-10-02T21:40:37.8966667+00:00

I've been working on this exact same issue for several months with Micrsoft support. I worked with a key vault support person then was transferred to SQL server support. Still doesn't work. Tried a bunch of stuff too. :P
Dan Decker 0 Reputation points

2024-10-02T23:30:54.57+00:00

Still no good here. What’s it take for it to work according to the directions? Has anyone gotten this to work?

1 answer

Your answer

Givary-MSFT 35,621 Reputation points Microsoft Employee Moderator

2024-02-21T06:32:07.8266667+00:00

@Chew Kok Hoor Just wanted to check does the above-mentioned issue has been resolved? I see you mentioned below stating problem is resolved by updating to the latest Cumulative Update 11, are you getting a different error now?
JamesTran-MSFT 36,906 Reputation points Microsoft Employee Moderator

2024-02-26T22:05:01.58+00:00

@Chew Kok Hoor
I just wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.
Chew Kok Hoor 30 Reputation points

2024-03-11T03:59:58.7866667+00:00

@Givary-MSFT @JamesTran-MSFT

Sorry for late reply. The issue still exists. It is an SQL Server 2022 Web Edition which I last check supports Entra ID:

Exception occurred while downloading AAD certificate. Unable to get a valid certificate from AKV. AKV does not contain a valid certificate that can be used for AAD authentication.. Exception occurred while installing AAD certificate. https://XXXXXX-keyvault-mpn.vault.azure.net/secrets/XXXXXX-MSSQLSERVERYYYZZZ: Some or all identity references could not be translated.Exception occurred while installing AAD certificate. https://XXXXXX-keyvault-mpn.vault.azure.net/secrets/XXXXXX-MSSQLSERVERYYYZZZ

I once tried it succeeded but unfortunately after going back to the Microsoft Entra ID and Purview all settings seems gone. Now when I try again, this is the same error. I have checked and ensured the server has permission to access the vault.
Chew Kok Hoor 30 Reputation points

2024-03-11T11:20:14.94+00:00

Do I need to enable any Windows authentication with Entra to the on premise Windows Server 2022 first? I am using Standard Edition version 21H2.

Because the error can possibly be unable to map my Entra users to Windows or SQL Server? Anyway to recap the os and database:

Microsoft Windows 2022 Standard Edition
Microsoft SQL Server 2022 Web Edition
Dan Decker 0 Reputation points

2024-09-06T10:43:40.9+00:00

Anyone find a resolution on this? I have tried numerous times over the last 3-months to resolve this and it still comes up.
Chew Kok Hoor 30 Reputation points

2024-09-06T12:42:33.37+00:00

Still no good news from my end.
IdahoSpud 0 Reputation points

2024-10-02T21:40:37.8966667+00:00

I've been working on this exact same issue for several months with Micrsoft support. I worked with a key vault support person then was transferred to SQL server support. Still doesn't work. Tried a bunch of stuff too. :P
Dan Decker 0 Reputation points

2024-10-02T23:30:54.57+00:00

Still no good here. What’s it take for it to work according to the directions? Has anyone gotten this to work?

Answer 1

[UPDATE: Entra ID still not working even after updating SQL Server 2022 Web Edition to Update 11 and updated SQL Server Management Console to leatest. Somehow reappears again.]

Problem is resolved by updating to the latest Cumulative Update 11. However, I still face issue with error:

TITLE: Connect to Server

Cannot connect to #######.

ADDITIONAL INFORMATION: Login failed for user ''. (Microsoft SQL Server, Error: 18456) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error Upon checking in Azure Arc, the Entra admin became None again. Microsoft Entra admin Microsoft Entra authentication allows you to centrally manage identity and access to your Azure Arc enabled SQL Server. Admin login: None

Share via

Unable to enable Microsoft Entra authentication for SQL Server 2022 in Azure Arc page in azure portal

1 answer

TITLE: Connect to Server

Cannot connect to #######.

Your answer