Connection issue for User Assigned Managed Identity (UMI)

Let me start with managed identities in Azure, Azure AD supports two types of managed identities:

· system-assigned managed identity (SMI)

· user-assigned managed identity (UMI)

You can create managed identity users from Managed Identities, I'll explain all steps later in seprate post.

There are several benefits of user-assigned managed identity you can read all from here, you can use UMI in different applications or in PowerBI as well as Data Factory.

In this case we already check UMI in Azure also ensure that Allow Azure Services and resources to access this server setting enabled. Referral link

Error message 18456 indicates login issue

Recently I got an issue with managed identities, users got the following error message in their end. I reproduced the error in my lab, here is the error message:

 
Cannot connect to SQL Database. Please contact SQL server team for further support. Server: xxxx.database.windows.net', Database: sample database', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user 'fb375f9f-c9e0-44cd-b80a-f6c521575d91@72f988bf-86f1-41af-91ab-2d7cd011db47'., SqlErrorNumber=18456 

As I explained before generally, error message 18456 indicates login issue and you need to check login and user in database. The user said he already created UMI in Azure portal, also the user has enough permission to ADF, it’s of scop for this part and I can explain this later, however you can check this in Access Control (IAM).

Check user-assigned managed created in database?

I asked the user that connect to the database and run the following query to see if their user has been created and has access to the database:

 
Select * from sys.database_principals
WHERE type_desc like 'External%'

Their username "new-iduser" did not exist in the database, then we need to create it with, please be aware you only can create “External” user with your AAD account, so you need to login to SSMS with your AAD account.

 
CREATE USER [new-iduser] FROM EXTERNAL PROVIDER

It’s better like I mentioned earlier you check the user again in sys.database_principals.

If you try again from ADF you still get same error message, why? The user does not have access to any database yet!

 
ALTER ROLE db_datareader ADD MEMBER [new-iduser]

In this stage I asked to verify the connection string in Azure Data Factory, as you see connection successful and the user could see the table with managed Identity.