Azure SQL Managed Instance - Unable to create Symmetric key and Certificate for Microsoft Entra Service Principles and Azure Active Directory Integrated authentication

Abhishek Modi (amodi) 0

Hi Team,

I am working on Azure SQL Managed Instance and trying to run the SQL statement below from the SQL server with Microsoft Entra Service Principles authentication in the Azure Managed Instance environment but getting an error - 'Cannot find the user 'xxxx-xxxx', because it does not exist or you do not have permission.'

USE [master];
GO
CREATE SYMMETRIC KEY [TestMISQL] AUTHORIZATION [AppId]
WITH ALGORITHM = AES_128,
KEY_SOURCE = N'1',
IDENTITY_VALUE = N'123'
ENCRYPTION BY PASSWORD = 'Test@123'
GO

Please find the screenshot below for reference -

CreateSymmentricKey_AzureMicrosoftEntra

Please suggest on the above query.

Thanks,

Abhishek Modi

Vinodh247 20,636 Reputation points

2024-06-21T09:25:56.7633333+00:00

when using microsoft entra service principals with azure SQL managed instance, there are a few steps to ensure proper authentication and avoid the error. Pls check the following...

Ensure that your logical server has an identity assigned. You can do this through the Azure portal or Azure CLI. Assigning an identity allows the server to authenticate with Azure AD and access resources. Grant the “Directory Readers” role to the logical server identity using Azure AD roles or Azure PowerShell.
Abhishek Modi (amodi) 0 Reputation points

2024-06-21T13:40:07.51+00:00

Hi @Vinodh247 ,

It's been already assigned still getting same error.

Thanks,

Abhishek Modi
Abhishek Modi (amodi) 0 Reputation points

2024-06-24T06:30:49.0366667+00:00

Hi @Vinodh247-1375 ,

Can you please update on above query. Earliest response will be highly appreciated.

Thanks,

Abhishek Modi
Vinodh247 20,636 Reputation points

2024-06-25T15:11:42.0166667+00:00

try providing grant control permission and try again

GRANT CONTROL ON DATABASE::[<YourDatabaseName>] TO [<ServicePrincipalName>];
Abhishek Modi (amodi) 0 Reputation points

2024-06-26T10:47:47.1233333+00:00

Hi @Vinodh247-1375 ,

How do we fetch Application (client) Id based on ServicePrincipalName using T-SQL? and in which system proc and view T-SQL having Application (client) Id details?

Thanks,

Abhishek Modi
Abhishek Modi (amodi) 0 Reputation points

2024-06-27T14:55:29.03+00:00

Hi @Vinodh247-1375 ,

Can you please update on above query. Earliest response will be highly appreciated.

Thanks,

Abhishek Modi
Abhishek Modi (amodi) 0 Reputation points

2024-06-27T14:56:03.74+00:00

Hi @Vinodh247-1375 ,

Can you please update on above query. Earliest response will be highly appreciated.

Thanks,

Abhishek Modi

Share via

Azure SQL Managed Instance - Unable to create Symmetric key and Certificate for Microsoft Entra Service Principles and Azure Active Directory Integrated authentication

Your answer