Add an Azure Ad account and delegate administrator privileges to it on the local SQL server

Question

Hi,

I'm migrating a device from on-prem ad to azure ad. The device has a SQL server installed, which was logged in using an on-prem account.

After the migration, we can only use the local administrator account or SA.

The Azure AD account is a local administrator on the device. However, I can't login with error 18456.

Can I somehow add an Azure Ad account and delegate administrator privileges to it on the local SQL server?

Thank you.

Answer 1

What version of SQL Server do you have? To log on to SQL Server with AAD, you must be running SQL 2022. It is not supported by older versions. If you have SQL 2022, you need to first enable it for Azure Arc, and then you can activate AAD authentication through the portal.

See further the Docs article Azure Active Directory authentication for SQL Server.