0

I am attempting to deploy an SSIS package to a server. I can build connection managers to the target server and databases on that server. However, attempting to deploy the error appears:

enter image description here

John R Smith
  • 704
  • 4
  • 16
Sauron
  • 5,537
  • 13
  • 59
  • 106
  • Are you connecting to the sql server instance as a domain/windows user or as a SQL user (username and password specified when you connected with SSMS)? – billinkc Nov 30 '17 at 19:34
  • @T-Heron why removed your answer. it was a good one? if the OP ignores a good answer, it doesn't mean that it is not good. also other users can benefit from it. – Hadi Dec 25 '17 at 17:54
  • Thanks for your feedback. I'll place it back. – T-Heron Dec 25 '17 at 18:25

2 Answers2

3

There is an problem here: "The target principal name is incorrect." This typically happens when the SQL service originally ran under the Local System account and then was changed to a domain account. When running under the Local System account, registers an spn for every service it controls automatically up to , and attempts to unregister them when the service shuts down. The Local System account has the ability to communicate over the network as the computer account and thus can indicate to Active Directory as to when to make changes about itself and the SPN SQL Service wants to register. When you change the SQL Server account over to an AD domain user account, the Local System account immediately loses it's ability to control this; therefore you must manually delete the existing SPNs previously registered for that SQL service by Local System before registering new SPNs.

The Microsoft Kerberos Configuration Manager for SQL Server tool Hadi mentioned will, when run, very helpfully generate a script to fix the SQL service SPN from the command line. From the tool's instructions:

To Generate SPN List from Command Line:

  1. Go to command line.
  2. Switch to the folder where KerberosConfigMgr.exe is.
  3. Type KerberosConfigMgr.exe -q -l
  4. For more command line option, type KerberosConfigMgr.exe -h

The SQL server script will call for deletion of old SPN(s) followed by the registration of new one(s) in order to fix issues. Until this is done you'll continue to get an authentication error when the kerberos clients obtain a ticket for the old invalid SPN - because it was never deleted and any Kerberos-aware service will always reject a ticket for a wrong SPN. After you make SPN changes, always be sure to restart the SQL Server service and right after that if you’re testing with a user have that user log out and log back in.

T-Heron
  • 4,892
  • 7
  • 22
  • 44
2

According to this Microsoft support article:

"Kerberos Configuration Manager is a diagnostic tool that helps troubleshoot Kerberos-related connectivity issues with SQL Server. These issues may trigger errors such as "Cannot generate SSPI context." "

You can get more info at :New tool: "Microsoft Kerberos Configuration Manager for SQL Server" is ready to resolve your Kerberos/Connectivity issues

Hadi
  • 31,125
  • 9
  • 49
  • 111