I have an Azure SQL Server and can SSMS into it. I also have an Azure Active Directory with a user named [email protected]. I want to add this user to have permissions to a database in my Azure SQL Server. The first step is trying to add it to the primary security of the Azure SQL Server.
I have tried the following on the Master Database:
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
CREATE USER mytestuser;
But this generates the errors of:
Principal '[email protected]' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.
and
'mytestuser' is not a valid login or you do not have permission.
How do I add an Azure Active Account to Azure SQL? Once I have added it via the Master so it shows up in Security, I should be able to add it to any number of created databases via:
CREATE USER mytestuser FROM LOGIN mytestuser;
After wasting 4 hours of my day trying to do this, below are the steps that worked for me:
I cannot believe I wasted almost two working days trying to do something as simple as adding a user to db. This is beyond belief. (/rantover)
For me there was a trick where you do some steps in SSMS using Active Directory - Integrated and some steps using local SQL Authentication. Here's what worked for me:
I set the domain account to use for the "Active Directory admin" setting in the Azure Sql Server features screen. Then I was able to connect using SSMS running under this account.
Note: To simplify running SSMS as this other user I used runas: C:\Windows\System32\runas.exe /savecred /user:[email protected] "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"
Running as this user, I connected using the SSMS authentication option, "Active Directory - Integrated". From here I ran the following in the master db:
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo
Then I connected to same server in SSMS using local SQL Authentication, logging in with the account set as the "Server admin" for the Azure Sql Server instance. From here I ran alter role commands in master db:
ALTER ROLE dbmanager ADD MEMBER [[email protected]]
ALTER ROLE loginmanager ADD MEMBER [[email protected]]
Now I could go back to the to SSMS running as the AD Admin user and from there I could run CREATE USER commands as above but for other domain accounts:
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo
You can decide which database to run the above (e.g., master and your non-system databases).
The domain users can now log in using "Active Directory - Integrated". Note if you add a domain user that is configured for MFA, then for that user to log on using SSMS they should select the SSMS authentication option, "Azure Active Directory - Universal with MFA", and their username should be with an "@" not backslash.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With