2

In my Azure environment I have many resources, and I have a virtual machine that contains my sql server database

This is my virtual machine:

enter image description here

What I would like to achieve is to create logins inside my sql server that is inside this virtual machine, but I dont want sql server logins, that I can do, I would like logins based on my Azure Active Directory.

I believe that when\if I can connect my sql server database to the Azure Active Directory I would be able to run the following command to create a user in my database:

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

Everything is in the same domain. pretty simples.

when I try to create the login, using SSMS the below is the form I see, please not that the search button is disabled. Creating sql server logins is working fine but that is not what I want.

enter image description here

  1. Basically I would like to have access from inside the sql server database to the AZURE active directory so that I could create logins and users more or less that same way we do in the on premises databases – create a windows login inside sql server and that will use windows authentication.

the part that I am struggling with, or (forgive if I am wrong) is to attach the resource (Azure Active Directory) to be visible from inside my sql server database that lives inside a virtual machine.

I know this sounds like a complex question but basically I would like to know

How do I create a login in sql server based on the azure active directory?

or at least just mention the steps as a starting point.

Improve this question
4
  • 1
    I've personally haven't done this yet but if I can help I'll try, so forgive me for this trivial question but have you verified your VM is on the same domain and has access to your Azure Active Directory? Active Directory Explorer used to be a helpful tool, but I'm not sure if it's useable for Azure Active Directory.
    – J.D.
    Commented Mar 8, 2022 at 23:15
  • 1
    Ok, and again sorry for the trivial question, but if you connect to your SQL Server using SSMS, and you use the UI to create a new Login, it has a search window for the account. In there you can type the part of the account name or the domain name and account name, and then click the "Check Names" button for it to verify it against the domain controller. If you try that does it find your Azure AD account? (Again I'm not sure if this works with Azure but it might.)
    – J.D.
    Commented Mar 9, 2022 at 12:34
  • I have updated the question to reflect this. the search window is disabled as you can see.
    – Marcello Miorelli
    Commented Mar 9, 2022 at 14:09
  • Ok, interesting though, SSMS UI shows "AAD Authentication" instead of the usual "Windows Authentication" so that's a good start. If you manually type the Azure AD Account name (I think with the domain prefix) in the "Login name" textbox, does it let you create a Login for that AAD Account?
    – J.D.
    Commented Mar 9, 2022 at 14:25

0

Reset to default

Your Answer

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