Create managed identity on Azure SQL MI/ Azure SQL DB using sql login
-
I am able to successfully create managed identity on Azure SQLMI & Azure SQLDB, i login through MFA and my account has sysadmin privileges. I need to be able to create managed identity using sql login and that sql login only has db_owner permissions, when i try to create managed identity it fails with below error message however when i grant sysadmin access to that SQL login it is able to create that managed identity. Did some digging, looks like i could create another managed identity with "Directory Readers" role only, assign to that MI and somehow this sql login with db_owner permissions only should impersonate that UMI? Unfortunately that also didn't work. If any one else was able to crack please let me know , i would like to hear from people who have actually implemented this because Microsoft documentation on this is VERY poor and not clear. Goal is to NOT give sysadmin privileges to SQL login but the login should be able to create UMI. Thanks
CREATE USER [MyManagedIdentityDev] FROM EXTERNAL PROVIDER;
"Msg 33159, Level 16, State 1, Line 1
Principal 'MyManagedIdentityDev' could not be created.
Only connections established with Active Directory accounts can create other Active Directory users"
-
My 2ct ( these prerequisites may have evolved over time. U used it a couple of years ago )
- Did you grant AzureAD/Microsoft Entra to be used with this AzureSQLDB ( using the portal ! )
- Is your current session connected using an AAD login? ( SSMS run as a different user -> your AAD login )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- Johan Bijnens wrote:
My 2ct ( these prerequisites may have evolved over time. U used it a couple of years ago )
- Did you grant AzureAD/Microsoft Entra to be used with this AzureSQLDB ( using the portal ! )
- Is your current session connected using an AAD login? ( SSMS run as a different user -> your AAD login )
Yup yup, as mentioned there isn't any issue in creating Managed identity when i login using my MFA or even a sql login as long as it has sysadmin privilege, the issue is when sql login is NOT sysadmin and just db_owner i am not able create them .
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply