2

I have two users ([email protected]) in our Azure AD that have been granted owner permissions to an Azure server via the Azure portal.

First, is it possible to create a login that links/pulls from Azure AD for these login credentials? I've searched and haven't found a specific answer to this, though my suspicion is no.

Second, I have created logins/users for the same database, however, while access to the server is fine, access to the database is denied. I have granted connect to the logins as well as executed sp_addrolemember as datareader to each for the database. In double checking my work, I had referenced several examples that show the same syntax I'm using for Azure logins/users, and yet access is still denied.

Any help would be appreciated.

Steve.

Code:

CREATE LOGIN [login_name]
   WITH PASSWORD = N'password'

CREATE USER [user_name]
   FROM LOGIN [login_name]
   WITH DEFAULT_SCHEMA = dbo

GO

GRANT CONNECT TO [user_name]

EXEC sp_addrolemember 'db_datareader', 'user_name'
Improve this question

3 Answers 3

Reset to default
3

First, is it possible to create a login that links/pulls from Azure AD for these login credentials? I've searched and haven't found a specific answer to this, though my suspicion is no.

No. In Azure SQL Database you can only use users and logins created in Azure SQL Database - SQL Login.

Second, I have created logins/users for the same database, however, while access to the server is fine, access to the database is denied. I have granted connect to the logins as well as executed sp_addrolemember as datareader to each for the database. In double checking my work, I had referenced several examples that show the same syntax I'm using for Azure logins/users, and yet access is still denied.

Logins should be created in the Master db, while the users, grants and sp_addrolemember should be executed in the context of the targeted DB. If you executed sp_addrolemember in the Master database, your user will not have access to the targeted db.

Also, something important, when you try to connect to the DB with the new logins (and please note that to login to the db you use the login not the user), you have to explicitly select the database to which this new user has access!

My wild guess is that you have executed the create user, grantand sp_addrolemember in the context of your master database. Thus these users now have only access to the master database. You cannot grant explicit grants to other database when you are in the context of master.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

It is possible to access Azure SQL DB with Azure AD users: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

Improve this answer

Comments

-1

You can't use AD users to login to SQL server. Logins has to be created in the master database and you need to use that login to create user and permission grants by connecting the user database. However SQL DB V12 supports contained user where you don't need to create logins in the master any more. The login can be executed in the user database context itself and this will be very helpful if you setup Geo Replication for ( restore using Point in time restore feature of) for your database in Azure. Based on my experience, I would recommend contained users in Azure database.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.