Create SQL Login for Azure Active Directory User

1
votes

Problem : Configure Azure SQL DB so Azure AD Users can login to the server using SSMS, and be authenticated.

The Normal Process to configure a User in SQL is

  • Create Login
  • Create Users
  • Assign Roles

However, I am not able to create Login in a way that Password does not need to be specified, and the user can Login.

Do I need to use this command ? ' CREATE LOGIN login_name [FROM EXTERNAL PROVIDER] { WITH [,..]}

<option_list> ::=
    PASSWORD = {'password'}
    | SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

' How can we do this without having to create a password, since it will be Azure Active Directory Integrated ?

1
azure
I have gone through all the microsoft docs, but not getting the answer. - omi
It looks like you have to run that 'CREATE USER ... FROM EXTERNAL PROVIDER' first on master database (SQL server level) and then on each database that you want to connect to... Documentation is not helpful, I agree on that :) - Dima G

1 Answers

1
votes

See here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities

The command is

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

For example:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;