SQL Server Windows Authentication when signed in with Office 365 Azure AD

Question

Environment

I have a home office with several Windows computers for testing. These computers are part of a Windows workgroup - they are not joined to a domain.

Historically, I've created a local user on each computer with the same username/password in order to share resources, such as filesystems and SQL Server. In this scenario, Windows automatically recognizes me as the "same" user (albeit with a different SID) and allows me to connect without additional prompts for credentials.

Among other things, I could use a SQL Server client on one machine to connect to a SQL Server instance on another machine via Windows Authentication.

Recent Change

I've recently switched to logging into Windows with my Office 365 credentials, instead of with a local Windows user. Here's the relevant Windows Settings screen:

enter image description here

Now my Windows username is AzureAD\MichaelMannion, instead of a local name like citra\Michael (where "citra" is the name of the local Windows computer).

NOTE: In case you're unfamiliar with this setup, it's essentially using a lightweight Azure Active Directory (AD) instance that supports my Office 365 subscription. This is not full Azure AD / AD DS. I do not have an on-prem AD instance. My machines are still just part of a workgroup, not a domain. This is only using the lightweight Azure AD that backs Office 365 when you log into Windows using a Microsoft "work or school" account.

The transition has been a little rocky, but I have the basics working, like sharing folders and setting file permissions with the new Office 365 AD users.

Problem

I just built my first computer with a SQL Server instance since switching to the new Windows authentication method, and:

• I have been unable to connect a client on one machine to SQL Server on another machine using Windows Authentication when logged into Windows with my Office 365 Azure AD credentials (i.e. "work or school" account)

I can connect with:

• Windows Authentication when the client and server are running on the same computer
• SQL Server Authentication from a client on any computer

So, things are generally working; the problem seems to be limited to the new Azure AD style Windows credentials. Here's a screenshot using SQL Server Management Studio (SSMS); note that my Windows user on the client (AzureAD\MichaelMannion) is not the user that SSMS is apparently using to try to connect (NT AUTHORITY\ANONYMOUS LOGON):

enter image description here

This SSMS client (18.12.1) does offer three "Azure Active Directory" authentication options:

enter image description here

I don't think they're relevant here, but I tried them anyway. In every case, I get a certificate error:

enter image description here

Neither the server (red) requires, nor client (green) requests, an encrypted connection:

enter image description here

enter image description here

So, I'm not sure which certificate the error is complaining about. This might be irrelevant anyway - from some initial reading, I think these Azure AD options may only apply to the new SQL Server 2022 Azure AD authentication feature. I'm never clear what requires "real" Azure AD and what works with "lightweight" Office 365 AD, though, so I figured I'd show these examples.

Question

My question is:

- Is it possible to connect SSMS on one computer to SQL Server on another computer using Windows Authentication (or an Azure AD equivalent) when logged into Windows using my Office 365 "work or school" account?

• manniongeo: Adding credentials for server host to Credential Store yield no change. Trusting server certificate yields the same 18456 error for "ANONYMOUS LOGON"
• David Browne - Microsoft: Azure Active Directory auth isn't supported in SQL Server. You're trying always to get "Windows Authentication" working. All the other selections are currently only valid for Azure SQL Database.
• manniongeo: @DavidBrowne-Microsoft - Agreed re: Windows Authentication. Windows just doesn't seem to fully support 0365 auth yet. For example, can't share directory to AzureAD\UserName with File Explorer, but can with NET SHARE /GRANT. Same with file permissions / CACLS. For whatever reason, SSMS is using anonymous instead of AzureAD\UserName. Thanks for guidance on AAD / SQL Server support, too!

Answer 1

here are steps to get rid of the encryption and server trust error on a standalone pc -

If using a version of SQL older than 2019 then install SQL 2019 or above client tools to get the newer sql configuration manager (Otherwise you need to add the sha1 thumbprint in regedit to tell sql which cert to use.)

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver16).

rename the windows pc in advanced system settings, click more on full computer name and add a dns suffix like example.com so that the full pc name becomes citra.example.com (doesn't matter if it's joined to a domain)

create a self-signed cert for citra.example.com with a private key and then use sql configuration manager to import that certificate. Finally add the certificate to your trusted root certificate store and you should not have issues with encryption or server trust.

Good luck with the authentication part