Looking for info on azure active directory authentication

  • We have an Azure DB in place and now need to make it accessible to a number of on prem AD users. I'm told there is a way to do this via azure active directory authentication. Any recommendations on articles on how to do this? Everything I've found is about creating the azure active directory which already exists. I just need to know how to then add these azure active directory users to the DB to access it like they would if it were on prem and they were a member of the DB Owner role.

    Kindest Regards,

    Just say No to Facebook!
  • Just my 2ct:

    Azure AD <> on-prem AD ! 

    Granting an Azure AD user or group requires :

    1 ) An Azure AD account ( user or group ) to be granted Azure Active Directory Admin for the Azure SQL Server (!) service that hosts the Azure SQLDB.

    2) That AAD account or a member of that AAD group then needs to connect to the Azure SQLDB ( AAD authenticated ) to be able to grant other Azure AD accounts for that database.

    3) This is not a transparent connection ! ( as on-prem AD ) One still has to provide login authentication with each connection using the newer connection options! ( AAD Userid + password / AAD Userid + password + MFA ) ( I have never used AAD integrated as this is prohibited by our CSO )

     

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The MS Doc I'm using for these SQL commands is https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=azuresqldb-mi-current 

    I really appreciate your replying but there is a disconnect somewhere and I'm sure its because of my lack of knowledge with Azure. I know SQL Server very well for DCL, DDL & DML but I'm VERY new to Azure.

    The Azure setup was done by our IT guys and I've been tasked with taking care of things like getting's users in place to access the DB. We have an Azure SQL Database and not a Managed Instance which if I understand correctly means somethings aren't supported.

    The end goal is to allow for a Microsoft Access based front end to connect to a SQL Database. Until now this has all been local within our on prem domain and each user has connected via Windows Authentication.

    NOTE: I don't deal with the MS Access app I just take care of the SQL Side.

    Currently I can get connected to the Azure SQL DB with SSMS 2018 using Azure Active Directory - Universal with MFA. When I try to create a login for a local Azure AD account using the DCL sown below I get the error message Msg 102, Level 15, State 48, Line 6 Incorrect syntax near 'PROVIDER'. If I read this right its because we have an Azure SQL DB and not a managed instance.

    CREATE LOGIN [jim.smith@mycompany.com] FROM EXTERNAL PROVIDER

    GO

    After this I tried and was bale to successfully execute the same command but for CREATE USER. I'm hoping that means that we can replicate in Azure what we've been doing on prem in our domain with this thing.

    CREATE USER [jim.smith@mycompany.com] FROM EXTERNAL PROVIDER

    GO

    Kindest Regards,

    Just say No to Facebook!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply