Creating Azure AD User Login in Azure SQL

Question

Creating Azure AD User Login in Azure SQL

Guru Karnik 41

I was able to create the user for the managed identity

CREATE USER [mi-adventureworks2017-appuser]
FROM EXTERNAL PROVIDER

I'm unable to create a login for a managed identity in Azure SQL using the below syntax having logged in as the sql server admin as set in the Azure Portal.

CREATE LOGIN [mi-adventureworks2017-appuser]
FROM EXTERNAL PROVIDER

Am I missing something or is this supported only in Managed Instance?

Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2020-11-05T15:19:19.267+00:00

mi-adventureworks2017-appuser -- Is this the service principal created in Azure AD? Something that you want to use to login to Database using application code?
Guru Karnik 41 Reputation points

2020-11-06T03:02:14.663+00:00

Yes, as I have mentioned it's the name of the managed identity against which I was able to create a database user.

As per the documentation create-login-transact-sql, there's no mention of creating login for a Azure AD user/managed-identity

Accepted answer

0 additional answers

Your answer

Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2020-11-05T15:19:19.267+00:00

mi-adventureworks2017-appuser -- Is this the service principal created in Azure AD? Something that you want to use to login to Database using application code?
Guru Karnik 41 Reputation points

2020-11-06T03:02:14.663+00:00

Yes, as I have mentioned it's the name of the managed identity against which I was able to create a database user.

As per the documentation create-login-transact-sql, there's no mention of creating login for a Azure AD user/managed-identity

Answer 1

Vaibhav Chaudhari 38,916 Volunteer Moderator

I think, Just creating the user should be fine. This user can be used to connect to DB using code as shown in below 2 blogs

https://techcommunity.microsoft.com/t5/azure-sql/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-49-does-azure-sql-database-support-azure-active/ba-p/369055

Edit #1

For Managed Identity access, see if this helps

https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#use-managed-identity-connectivity

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Guru Karnik 41 Reputation points

2020-11-06T04:37:54.663+00:00

I'm trying to use a managed identity associated with a app service to authenticate against a Azure SQL database.
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2020-11-06T04:49:15.487+00:00

Check Edit #1 in my response above
Guru Karnik 41 Reputation points

2020-11-06T06:35:05.373+00:00

Let me elaborate: I have a ASP.Net core web app deployed in app service and I want to use Azure AD Active Directory Integrated authentication to a Azure SQL database. I created a managed identity and assigned it to the user identity of the app. Then I created a user in the database for the managed identity. I was wondering if the next step is to create a login for the managed identity, just like how we would typically create a login for the AD account in an on-prem setup followed by a database user for the login.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2020-11-10T00:50:48.073+00:00

@Guru Karnik Hi Guru, as mentioned by Vaibhav creating user would be fine in this scenario as well. Only thing you need after creating the managed identity is to grant database permissions to it using using commands like - ALTER ROLE db_datareader ADD MEMBER [<identity-name>]; or ALTER ROLE db_datawriter ADD MEMBER [<identity-name>]; or ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>]; as required. You can also refer to the documentation shared above to get more details.
Please let us know if this helps to resolve your issue.
Navtej Singh Saini 4,226 Reputation points Microsoft Employee Moderator

2020-11-12T01:02:18.43+00:00

Thank you @Saurabh Sharma for the insight and Thank you for accepting the answer @Guru Karnik .

Share via

Creating Azure AD User Login in Azure SQL

0 additional answers

Your answer