Connecting to SQL View using Excel on Azure Connected Computer

Question

We encountered an error Details: "Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context." My work computer is Azure AD Connected and not joined to the local domain. Previously with a domain joined computer this worked without issue. Now I get the error above when trying to connect to a SQL Server view from Excel. We setup a 2022 SQL Server and we have the same issue with those views. Any help is appreciated. Our goal is to setup computers without joining to our domain but we may not be able to work around this issue.

Answer 1

The cost for Arc-enabling a Windows Server or SQL Server is miniscule. I don't think Azure Arc itself comes with a cost, but I seem to recall that you need to set up some secrets in a key vault, and the key vault comes with a very small cost.

Once you have Arc-enabled you machine, you add various functionality to it, for instance Microsoft Defender, and these add-ons can be pricier. But they are not mandatory.

You will also need to set your license type; one option here is Pay-as-you-go. I seem to recall that some services are only available if you have Sofware Assurance; but I don't recall if this applies to Entra ID.

In any case, to use Entra ID with SQL Server instance, you need to Arc-enable it.

Answer 2

Hello @Tony Bowman

The error message “The target principal name is incorrect. Cannot generate SSPI context.” typically occurs when you use Windows authentication to connect to a SQL Server instance remotely. This error means that SSPI (Security Support Provider Interface) tries but can’t use Kerberos authentication to delegate client credentials through TCP/IP or Named Pipes to SQL Server. In most cases, a misconfigured Service Principal Name (SPN) causes this error.

An SPN is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. A typical SPN for a server running an instance of SQL Server is as follows: MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433. Here are some potential solutions:

1. Fix the error with Kerberos Configuration Manager.
2. Fix the error without Kerberos Configuration Manager.
3. Check your SQL Server Configuration Manager and ensure that TCP/IP is enabled and that the IP that the server resolves to when pinging is the same one here.
4. You can manually create an SPN for your computer that is running SQL Server and assign that SPN to the service account of the SQL Server service on that machine.
5. In the “Data Source Settings” window, select the affected data source and click on “Edit Permissions”. In the “Edit Permissions” window, under “Credentials”, click on “Edit”. Select “Database” and fill up the SQL credentials. Save and refresh all data.

Let us know if this solution works for you, and if so, please tag this as accepted. This helps many community readers who may have similar questions.

Answer 3

We just discovered two issues that fixed this. 1. We had to use port 1433 on the server name and two, On my Azure AD Connected computer, I had to start Excel as an administrator. Then the connection worked.