Issue with connecting to Azure SQL MI from on prem with windows authentication

Ryan Pighin 91 Reputation points
2023-01-12T18:51:15.2366667+00:00

We are trying to connect to our SQL Managed Instance in Azure from an on Prem VM. We need to have it set up using Windows Authentication for our vendor. We are using AD Cloud sync to sync the vendor account over to AAD. I followed the documentation and we used the "incoming trust-based flow". I went thru all the Kerberos set up but when I try to connect, I get "The target principal name is incorrect. Cannot generate SSPI context." When I run the "klist get krbtgt/kerberos.microsoftonline.com" I get tickets returned. When I run klist get <miname>.<dnszone>.database.windows.net , I get 0x6fb: SQL SPN not found

that tells me just to revisit the steps.

I am kind of at a stand still. Any thoughts on what I can be missing?

User's image

Azure SQL Database
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ryan Pighin 91 Reputation points
    2023-02-24T12:06:55.6+00:00

    Finally found the issue. It was our conditional access that was blocking the connection.

    In the AAD sign-in logs, we went in to the user sign-ins (non-interactive) and saw the failure here. Under user sign-ins (interactive) it was not showing the failure.

    User's image

    Was this answer helpful?

  2. Ekkendonk van, Freek 0 Reputation points
    2023-01-23T16:26:05.1333333+00:00

    Did you find a solution?

    Was this answer helpful?

  3. Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator
    2023-01-12T19:39:55.8833333+00:00

    Since you are trying to connect from a SSMS installed on a host located on-premises, on the routing table that is added to the subnet where SQL Managed Instance is deployed, add another route to the routing table for your on-premises subnet with next hop Virtual Network Gateway,

    Make sure NSG rules allow communication between the subnets on port TCP 1433 for this connection. Allow traffic on the same port on Windows Firewall.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.