SQL server & Azure active directory - creating new contained azure ad guest users

0
votes

I'm trying to create database users that are integrated with azure active directory. All of our users are guest users. I've been following multiple articles on how to create users in the SQL db but none have worked.

For example, this article: https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases/

Suggest to create users like so:

CREATE USER [[email protected]] 
FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = dbo;

This yields the error:

Principal '[email protected]' could not be found or this principal type is not supported.

Googling this error lands me on stackoverflow post (https://dba.stackexchange.com/questions/148325/add-active-directory-user-for-azure-sql-db):

which suggests:

CREATE USER [name_domain.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com] FROM EXTERNAL PROVIDER

and accesses:

EXEC sp_addrolemember 'db_datareader',  'name_domain.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com'
EXEC sp_addrolemember 'db_datawriter',  'name_domain.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com'

and this does not give an error, but it also does not provide access to the database. Since I get error NT AUTHORITY/ANONYMOUS LOGIN

I also tried to create an AAD group and provide that group access, also no error here but couldn't login either.

Couple of notes:

  1. All IP addresses are allowed on the firewall
  2. all users have been added in sql db IAM (not sure if this is even necessary)
  3. I've enabled Active Directory Admin in the sql server, I put the subscription admin here
  4. This is also the users with which I created users in the SQL DB
  5. I'm able to create native sql users without a problem

Still I'm only able to login using the Active Directory Admin, and no other user is able to login.

Any advice on how I can login to my Azure sql database using windows credentials from Azure Active Directory?

1
sql-serverazureauthenticationazure-active-directory
You are running the queries as the AAD admin right? And when you are connecting with those other users, are you using the AAD login method? If you connect through code, you need to set an access token on the SqlConnection before opening it. – juunas
Yes running the queries as the AAD admin. I'm connecting through SSMS portal, using Azure active directory - password & Azure active directory - MFA, neither work. – Anton
Are those users external users (invited) in your tenant? In that case you need to set the tenant id as well in the advanced settings before connecting. – juunas
Yes they are, not sure what you mean? But your comment lead me to test out different advanced settings and specifying the DB seemed to have been the issue, since the permissions aren't created on the master. – Anton
Right, thank you. Wanna formulate that as an answer and so I can mark this question as answered? – Anton

1 Answers

1
votes

When using external users, you need to use the "mangled user principal name" when adding them. That's this one:

CREATE USER [name_domain.com#EXT#@<your-azure-ad-default-domain>.onmicrosoft.com] FROM EXTERNAL PROVIDER

Secondly, the users will be created only in that database; they cannot connect to master. So you need to choose the DB to connect to.

You may also need to specify the AAD tenant id in advanced connection settings. The reason you might need to do this is because by default an external user will login to their home tenant. Which is not the one connected to your DB. So you may need to specify the tenant to have them explicitly login against your tenant.