1

I have a problem with cross domain authentication. I have two domains: A and B. Most of the servers from domain B use NT Service as aa service account for SQL Server.

When user from domain A is trying to authenticate to the server from domain B is each time getting an error

The target principal name is incorrect. Cannot generate SSPI context

I tried to google that, and my understanding is a server is trying to use KERBEROS authentication, but it fails. Most of the search result were pointing me to SPN related issues. Thus, on the development server I changed SQL's service account to domain one and I manually registered relevant SPNs but the issue still exists. I do not know if that is relevant but after that change, I noticed that a system view dm_exec_connections shows that all active connections are using NTLM authentication.

I do not think that it has anything to do with trust between domains or firewall rules as when I am trying to authenticate with IP address inkstand of DNS it works correctly.

I also found one link that suggested that it is a problem with trust delegation, so I for the SQL service account I set up Trust this user for delegation to any service (Kerberos only) in Active Directory, but it also did not change anything.

Could you please advise what else can/should I check?

Thanks, Radek

Improve this question
5
  • Yea usually the 2 potential culprits at play for this kind of issue is correctly setting up the SPNs and correctly setting up Delegation. What is the workflow where you're encountering this issue?...i.e. is this an app that's connects to SQL Server A which references an object on SQL Server B through a Linked Server, for example? Essentially trying to understand if you have a double hop going on in the process of not.
    – J.D.
    Oct 7, 2022 at 12:21
  • It never works... I mean the problem exists with every possible workflow that I tried (linked servers, applications connecting to different domain with service accounts, my personal account in management studio). In each case I could workaround that issue by switching to SQL Authentication or replacing DNS with IP address in connection strings (I mean both of these workarounds always works). setspn -L <domain>\<login name> returns 6 results now for example: MSSQLSvc/<servername>.<domain name>
    – Radek Gąska
    Oct 7, 2022 at 12:29
  • 1
    Not really sure I follow what you're saying. Let's start at the simplest level. If you login to the computer running one of your SQL Server instances, and open SSMS directly on that computer, are you able to login to the SQL Server instance that is running on that machine? (I can't imagine you're receiving an SSPI error in that case.)
    – J.D.
    Oct 7, 2022 at 13:08
  • I have 2 domains (a and b) and 2 windows accounts (one for domain a, and for domain b) I want to authenticate: From VM that is in domain A With Login that is in domain: A To the server that is in domain B. It never works when I use DNS. I have never tried to connect From VM that is in domain B With Login that is in domain: A To the server that is in domain B. Simply because I never thought about that, and firewall rules are blocking such tests...
    – Radek Gąska
    Oct 7, 2022 at 14:39
  • As per your suggestion I tried to log in directly to VM from domain B that host server I connect to it with the account from domain A... and it worked!
    – Radek Gąska
    Oct 7, 2022 at 14:39

1 Answer 1

Reset to default
-1

What I have done to solve this in the past is to create a HOSTS file. This will give the computer you are connecting with the path to connect to your SQL server, assuming that their are no firewall or routing problems.

The HOSTS file is located at C:\Windows\System32\drivers\etc and looks like:

# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host
# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost

The new entry will not have a hashtag # and will list IP address then server name.

Improve this answer

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.