1

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?

15
  • I'm not familiar with this setting. I'm AAD Workplace-joined but log in with a normal local account. You should be able to add a Windows credential to the user's Credential Store to use alternate credentials for a specific network host. Jul 21, 2022 at 20:28
  • I think you need Azure AD Federation learn.microsoft.com/en-us/azure/active-directory/hybrid/… in other words your domain needs to have a trust relationship with the Azure domain. This allows login. The certificate error however is caused because the machine does not trust the issuer of the SSL certificate used by SQL Server. Who issued it, is it a self-signed cert? Jul 21, 2022 at 20:31
  • Shot from the dark based on past experiences, but does checking off "Trust server certificate" in theConnection Properties get you any further? (I would guess you should be logging in with the AAD - Integrated or AAD - Password authentication methods.)
    – J.D.
    Jul 21, 2022 at 21:22
  • @DavidBrowne-Microsoft - Adding credentials for server host to Credential Store yield no change.
    – manniongeo
    Jul 21, 2022 at 21:33
  • @Charlieface - Per the OP, there is no local domain, just a workgroup, so nothing to federate
    – manniongeo
    Jul 21, 2022 at 21:35

1 Answer 1

0

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.