I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted connection, I get the "SSPI context not generated" error. What I have done: * I have used the setspn -X to confirm there is no duplicate SPN. * I have changed the order of protocol to follow the order: Shared Memory, Named Pipes, TCP/IP. * I have verified that when I restart SQL server, the service registers and deregisters. This was found in the SQL server log. * I have checked that on the SQL configuration the TCP/IP network protocol has the right IP and is active and enabled on both 32bit and 64bit. I am running out of ideas and I am still getting the same error. I can't find any log that point to Kerberos.

Apologies for the length of this but I'm app developer, not a SQL DBA nor network Admin, so I'm out of my area and just wanted to be sure I documented everything as well as I could. This is a problem about authenticating from a client app using Kerberos authentication and a Windows Domain Identity, to a SQL 2022 instance on a host that hosts both SQL 2019 and now SQL 2022. I’ve inherited a problem that was abandoned more than a year ago and got it simply because it involves SQL client connections from an application, or that I’m the old man in group which really doesn’t mean I have more experience, at least not like it used to, but they think it does, so I let them believe it. I’ve solved a number problems that were missed, but I’m still short of making it work because I’m having trouble getting the SPN’s and network protocols correct for Kerberos client connections. Each instances of SQL Server has a unique instance name, the 1st has the instance name “Sql2019”, the 2nd instance “Sql2022” and it’s the 2nd instance that needs Kerberos authentication. One of the things missed by the original group were the client libraries used for OLE DB connections; they were still using SQL Native Client. I have since installed the newer MSOLEDBSQL libraries into the client machines and made the code changes to use Ole DB SQL Connections and other client side components for Kerberos client connections, following MS Documentation on this topic. And not knowing if I was going at this the right way, I installed these client libraries on the machine hosting SQL 2019 and SQL 2022. I believe I have all of the correct elements set up on the client side, I’m not sure about the server side, because I can make an OLE DB connection from the client app and access the application databases, but when a connection is made that requires access to the restricted elements of SQL 2022, such as the Master DB or SSIS when installing and configuring DTS Packages, client authentication fails. The error message I’m getting right now is a SQL Connection timeout SQL Error 87. If I understand MS Docs correctly, the reason for this is because the original SPN setup is no longer valid and or, when an OLE DB client connection initially fails Kerberos authentication, it falls back to NTLM. But SQL 2022 can’t authenticate the NTLM connection because something is incomplete or missing with the SPN’s or something is incorrectly configured with regards to the network protocols used, Named Pipes vs TCP/IP. I’m stuck in this latter area and re-reading the MS Docs is not giving me any insights on where to go next. Here is where things stand as of this post: 1. The SQL Server name is “**SqlServer**” 2. The instance names are “**Sql2019**” and “**Sql2022**”. 3. The host is joined to the domain. 4. The TCP/IP ports for both instances use the default port numbers. 5. The Named Pipes protocol use the default SQL Names assigned at installation. 6. The account that both SQL instances runs under was changed before I was given this task, from NT Service to a domain account named (**SysCred**) and it’s used exclusively for services. That account is a member of the domain wide *Administrators Group*, and I have confirmed that on SQL Server, it is also a member of the *local Administrators Group*. I’m not sure if this is relevant or not, but I have left it as it is. 7. The same can be said for the application that they want accessing SQL 2022 with Kerberos authentication, it too runs under a domain account (**CoreSrvcs**), and at present, that domain account is a member of the *local SQL Server’s Administrators Group*, so I have left it as it is. 8. On SQL Server 2022 I found the following: 9. A SQL Login for the domain account (**SysCred**), who is a member of (*sysadmin, serverdmin*) 10. A SQL 2022 Login for the domain account (**CoreSrvcs**), who is a member of (*sysadmin, serverdmin*) 11. This login is mapped to the application databases, the *Master DB*, and to the *SSIS database*. 12. The SQL Host has AD LDS service installed to synch with the domain controller, and that services appears to be working correctly, I see no error messages in the event logs for it. 13. I queried for SPN’s on the SQL Server and found the following: 14. For the domain service account (**SysCred**) that SQL instances run under 15. *MSSQLSvc/SqlServer.domainame:50895* 16. *MSSQLSvc/SqlServer.domainame:SQL2022* 17. *MSSQLSvc/SqlServer.domainame:1433* 18. *MSSQLSvc/SqlServer.domainame:SQL2019* 19. For the domain account (**CoreSrvcs**) that applications run under: 20. *MSSQLSvc/SqlServer.domainame:50895* 21. *MSSQLSvc/SqlServer.domainame:SQL2022* 22. *MSSQLSvc/SqlServer.domainame:1433* 23. *MSSQLSvc/SqlServer.domainame:SQL2019* 24. In the application components that need to query the *Master DB* or the *SSISSDB*, I changed the code from using the elements in the *System.Data.SqlClient* namespace or the *Microsoft.Data.SqlClient* namespace, to use elements from *System.Data.Ole* as per Microsoft Docs, and as long as I only query the application database, everything works. 25. But when the application attempts to access the *Master DB*, or install and configure DTS packages against the *SSISDB* database, the client connection times out and I see this SQL server error 87. 26. NOTE one of the last changes made per MS Docs was to use a SPN in place of the SQL Server Instance name when setting up an Ole DB Connection, on my last few tries I was using one of the SPN’s listed above, but all produced the same error. My last thought before asking for help was to change the protocol order of SQL Server Configuration Manager, from having TCP/IP first to Named Pipes 1st, not sure this would make a difference, and while I wait for a reply I’ll checkout some trouble shooting tips and tools from https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki

A separate domain account was created for the database engine service of a SQL Server Failover Cluster Instance. Server Principal Names (SPNs) were added manually via the SetSPN.exe command line tool.

If the domain account for the database engine service is a member of Domain Admins, SQL can successfully (and automatically) register SPNs when the service starts (eg server reboot, cluster failover event, manual restart of db engine service). When the domain account for the db engine service is removed from the Domain Admins Active Directory group, SQL fails to register SPNs. The db engine service account needs certain permissions to register or modify a SPN. Microsoft has listed the permissions necessary for Automatic SPN Registration in their documentation: - Permission: Validated Write servicePrincipalName - Property: Read servicePrincipalName - Property: Write servicePrincipalName I've followed along with the documentation. I've also verified Effective Access for the domain account. But I just can't get automatic SPN registration to work without elevated permissions (in Domain Admins). Something has been missed, but I don't know what. Did I miss a step? Is anything missing from the documentation? **UPDATE**
I may have found an answer. Everything I posted above relates to the Active Directory account for the ***computer*** that hosts the SQL Server instance (or in my case, the virtual cluster name account for the SQL Server Failover Cluster Instance). The logon account for the database engine service also needs these permissions in Active Directory: - Read servicePrincipalName - Write servicePrincipalName However, it does not appear those permissions can be added in the *Active Directory Users and Computers* GUI tool. *ADSIEdit* is what I used. Special thanks to Randolph West. His answer to another similar question has all the details.

I have installed SQL Server 2022 on Windows Server 2019 Datacenter, and applied the March SQL Server 2022 Cumulative Updates. Also installed on the server is SSMS 18.12.1 and 19.0.2. On a remote machine, I have SSMS 19.0.2. Both machines are on the same domain. I'm using mixed-mode authentication with a custom instance name and custom TCP port. I'm using a self-signed certificate that is installed on both the server and the client machines. I can connect to SQL Server just fine on the server, using both SQL and Windows Authentication methods. From the remote machine, I can connect to this instance using SQL Authentication, but not with Windows Authentication. (I can connect with Windows Authentication to other instances on other machines from the client, although they are SQL Server 2016, not 2022.) I have been round and round over the past couple of weeks with trying out suggestions in other posts. I have enabled "Allow remote connections to this server". I have tried deleting and creating SPNs on the server, although when I start and stop the SQL Server service, it appears to recreate them anyway. I have enabled the custom, static port through SSCM and SQL Server Client Network Utility. (I have enabled TCP and assigned aliases with both utilities since I read that SQL Server 2022 does not use SQL Server Native Client.) The firewall is disabled (although I did go ahead and create an inbound rule for TCP). When I try to connect from the client machine with Windows Authentication and without the "Trust server certificate" box checked, I get: >Cannot connect to xxxxx.xxxxx.xxxxx\xxxx,xxxx. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server) The target principal name is incorrect. When I try to connect from the client machine with the "Trust server certificate" box checked, I get: >Cannot connect to xxxxx.xxxxx.xxxxx\xxxx,xxxx. The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server) I have two SPNs registered: MSSQLSvc/servername.xxxxx.xxxxx:portnumber and MSSQLSvc/servername.xxxxx.xxxxx:instancename. I also tried installing the Kerberos Config Manager on the SQL server. It errors with: >Unable to connect to server, please ensure that the server name is correct, SQL Server is installed properly, and the user has administrator permissions. If the problem persists, please contact Microsoft Support. I should also mention that the two SPNs are registered under the AD account that is used to start and run the SQL Server service. I am using the full FQDN servername.xxxxx.xxxxx. The only two SPNs with the "MSSQLSvc/" prefix are MSSQLSvc/servername.xxxxx.xxxxx:portnumber and MSSQLSvc/servername.xxxxx.xxxxx:instancename registered for the domain\dbaccount AD account used to run the SQL Server service, no duplicates.

Manually registering SPNs for Availability group - 1. Do I need to register SPNs for the AG Listener **as well as the individual nodes**, for both the instance name and the listening ports for the managed service account? The application uses the Listener to connect, are SPNs required for the individual nodes if using Kerberos authentication? 2. Does the Listener name have to be same as the AG name?

I'm trying to solve some mystery in two of my environments. Both are SQL 2019 deployments, however one has Kerberos configured (i.e. SPNs are registered for the instance and port), while the other deployment does not. I've been trying to investigate how this happened but not got to the bottom of it yet. Basically, I would like to understand the requirement for future deployments. Am I supposed to manually create (or request to be created) the SPNs or there is some way in which they get automatically created? For example, could they have been created in the course of building the application tier? I'd appreciate if anyone is able to throw some light on this for me please. Of course I have been researching all about Kerberos authentication and SPNs but if there are any specific articles on this specific question, I'd appreciate a link to it please. Thank you

I'm finding contradictory information for how exactly to format SPNs (Service Principle Names) to get the proper Kerberos connections, and how many I need for each SQL instance. [This 2017 MS document](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections) contains the following: > Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory. The supported SPN formats for named and default instances are as follows. > > * Named instance: MSSQLSvc/FQDN:[port|instancename] > * Default instance: MSSQLSvc/FQDN:port|MSSQLSvc/FQDN > > **The new SPN format does not require a port number**. This means that a multiple-port server or a protocol that does not use port numbers can use Kerberos authentication. I took this last paragraph to mean that I only need a single entry, one of the following: * Named instance: MSSQLSvc/sqlbox1.mydomain.org/instance2 * Default instance: MSSQLSvc/sqlbox1.mydomain.org That seems to contradict [this older (2011) MS document](https://technet.microsoft.com/en-us/library/bb735885.aspx) , not just about the port number, but also concerning what name to use: > To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, **you must create an SPN for both the NetBIOS name and the FQDN**. When I look at the SPNs that already exist in my environment, I see a wide variety of combinations, some servers have up to 4 entries: * MSSQLSvc/sqlbox1 * MSSQLSvc/sqlbox1:1433 * MSSQLSvc/sqlbox1.mydomain.org * MSSQLSvc/sqlbox1.mydomain.org:1433 Even [MS's own Kerberos configuration manager](https://www.microsoft.com/en-us/download/details.aspx?id=39046) seems to want to generate the last two versions (with appropriate obfuscation): Similarly for existing named instances, I see a weird mix, some of them almost certainly invalid: * MSSQLSvc/sqlbox1:1522 * MSSQLSvc/sqlbox1:instance2 * MSSQLSvc/sqlbox1.mydomain.org:1522 * MSSQLSvc/sqlbox1.mydomain.org:instance2 * MSSQLSvc/sqlbox1.mydomain.org/instance2 * MSSQLSvc/sqlbox1.mydomain.org:1522:instance2 **So what should my DSNs actually look like, for both default and named instances, if I just use TCP in my environment?** **Should I include the port, or not? Or include one with the port and one without?** **Use the FQDN only, or do I need the entries with just the Netbios name? Or would that only be if we were using named pipes (which we are not)?** (For context, we run SQL 2005 through 2014, some clustered, others standalone. Connectivity is via TCP only, named pipes is disabled in config manager. We will be fixing/creating these manually instead of allowing the SQL service account to create them upon server start.)

following configuration: * SQL Server is running on server.domain.intra with an gMSA. * DNS alias: server-db-dev.domain.intra * gMSA Permissions on backup share: Full Control * SPNs set for gMSA: * MSSQLSvc/server.domain.intra:1433 * MSSQLSvc/server.domain.intra * MSSQLSvc/server-dev-db1.domain.intra:1433 * MSSQLSvc/server-dev-db1:1433 Problem: When the restore is started with \\server.domain.intra\BackupShare$, the restore finish successfully but with the DNS alias we get the error "Operating system error 1326(The user name or password is incorrect.)" I have the feeling that the gMSA should be configured to allow to be delegated to the dns alias. Or do you have any other ideas how to solve the problem? Thank you very much and kind regards, Olaf

Trying to set up Kerberos Authentication between two linked server to get past the double-hop issue. Server A is in a domain with several other SQL Servers. All SQL instances on all servers in this domain are using the same service (user) account for running the instance. Server B is in another domain using a separate domain account to run its instance. It is currently the only SQL server running with a domain account. Other are using local accounts. I am configuring Server B's service account to allow for self managing SPN's which *should* be easy. My problem is I will need to do this with Server A's service account as well. I can't find what I would like to make this clear to me but I am concerned about how Server A's service account will work once I allow it to "self manage" its SPNs i.e. give the AD account read/write ServicePrincipalNames of itself. Can an AD service account host SPNs for multiple SQL Instances? Currently the account is not hosting any when I query it via setspn -L [domainname]\sqlengine All SQL servers are running 2008 or higher. Most are 2016.

In SQL Server Configuration Manager on the Advanced tab in TCP/IP Protocol properties box there is an option Accepted NTLM SPNs. What is the use of that option and how it can be used?

We are getting below message: > ‘SQL Server cannot authenticate using Kerberos because the Service > Principal Name (SPN) is missing, misplaced, or duplicated.’ I found an article on [how to get it fixed](https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/) . But I still have the following questions: 1. Currently our auth scheme is NTLM. How is NTLM different from Kerberos? 2. What exactly is this SPN? I am worried about my PRODUCTION servers. 3. Which is more secure and recommended by Microsoft: "NTLM" or "Kerberos"? 4. In what cases is it used? 5. If I don't fix this error, what issues I am going to face ?

I am having an issue with what appears to be a kerberos double hop issue with SSIS 2017. The setup consists of a backend database cluster servers (DBSERVER01\02) an Application\SSIS server (APPSERVER01) and various clients connecting from other hosts with SSMS. Managed Service accounts are used for all SQL services and trusted for delegation, SPNs have been set for the MSSQL DB Service, the SQL Kerberos checker passes all tests. An SSISDB catalogue has been deployed and enabled for Always ON support, the SSIS configuration on APPSERVER01 has been updated to point to the catalogue database via the database cluster listener. Via SSMS on the APPSERVER01 when connecting to Integration Services it works fine, when connecting form any other host the following error is received: “The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)” Does SISS support this type of deployment? If so what am I missing?

I am trying to enable Kerberos for MS SQL Server Reporting Services. I am fairly familiar with Windows security and how it works. However, I am new to Scale Out Deployment and Clustering of Windows Servers. I know I need to add SPNs that direct towards both SSRS web service (using a domain account) and to the database engine. I am a little confused on exactly how the SPNs should be structured. I have looked in Stack Overflow and other resources and most reference a standard deployment without scale-out/clustering. My questions are: - Do I structure the SPNs to point to the node, the cluster, both? - How do I structure the SPNs for each? - Do I need to include ports? I ran the Microsoft Kerberos Configuration Manager and the Reporting Services SPN shows "Unauthorized". I don't know what this means and I can't find documentation on the tool. Here are the details of my setup: **Domain:** clearwd (not my actual domain) **Server OS:** Windows Server 2016 **Cluster:** DEV-CLUSTER **Node 1:** DEV-SQL1 **Node 2:** DEV-SQL2 **Role:** DEV-CLUSTER1 (SQL Server / MSSQLSERVER) **SQL Server Version:** 2016 Enterprise **SQL Server Name:** DEV-CLUSTER1 **SQL Server Port:** 1433 **SSRS Service Account:** ssrsuser.clearwd.com OR clearwd\ssrsuser **SSRS Mode:** Native **SSRS Report Server Web Service URL:** http://DEV-SQL01:80/ReportServer **SSRS Web Portal URL:** http://DEV-SQL01:80/Reports Let me know if any other information is needed. References: https://www.itprotoday.com/sql-server/implement-kerberos-delegation-ssrs-0 https://learn.microsoft.com/en-us/sql/reporting-services/report-server/register-a-service-principal-name-spn-for-a-report-server?view=sql-server-2017 https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/configure-report-server-urls-ssrs-configuration-manager?view=sql-server-2017

I have a Power BI Report Server working perfectly connecting to SQL Server machines with a default instance only. I can also connect to SSAS named instances with the proper SPNs and by allowing Kerberos constrained delegation from the Power BI service account to the MSOLAPSVC.3 service (SQL Server Browser). I believe I have all proper SPNs set up properly as I can run linked servers and query just about anything from anything. Kerberos Configuration Manager shows green check marks for everything. My only problem is connecting to named instances of the SQL Server engine (not SSAS). I get the error below, which you would normally get if SPNs are not registered properly. > DESCRIPTION: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. > Reason: Could not find a login matching the name provided. [CLIENT: > x.x.x.x] Is there a difference in how named instances must be connected to from Power BI (and perhaps SSRS as well)? I am trying to do a DirectQuery against a remote named instance of SQL Server in a .pbix file in my environment.

I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required. But now it is required. I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment. Is there a way of doing this? P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

I'm trying to configure a SQL Server 2014 (12.0.5000.0) instance to use a regular user account in AD and Kerberos auth. During installation the default accounts (local system ones) were used, and the SPNs MSSQLSvc/:[ | ] were both created on the server's computer account. I reconfigured all the SQL Server services to use the user account. When I manually restart the services via Configuration Manager the SPNs are correctly recreated on the user account and Kerberos auth works (checking via SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; from a domain joined client). I then manually removed the SPNs on the computer account to avoid duplicate SPNs. The problem is when the server restarts, the SPNs are not recreated (error follows), Kerberos auth does not work on the server, and I end up with NTLM auth instead. > SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required. > > The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/: ] for the SQL Server service. Windows return code: 0xffffffff, **state: 43**. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. > > The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/: ] for the SQL Server service. Windows return code: 0xffffffff, **state: 43**. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. Both the service (user) account, and the server (computer) account have been granted explicit permission to read and write SPNs on themselves and each other. If I manually restart the services via Configuration Manager once the machine is booted and I'm logged in, the SPNs **do** get recreated (log message follows) and Kerberos **does** work. > SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required. > > The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/: ] for the SQL Server service. > > The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/: ] for the SQL Server service. What I can't work out is - what **state: 43** means in the SQL Server ERRORLOG. Presumably it's not referring to Idaho . - why the SPNs **do** get recreated on a restart when using Configuration Manager, but **do not** get recreated on a server restart - why on a server restart the SPNs (which still exist) don't get used *[if I trigger the restart in Configuration Manager they get recreated, but in a server shutdown, they aren't deleted, therefore they still exist on server startup]* I notice there is an SQLWriter service which is still running as Local System but according to SQL Writer Service , this is correct. The FFL is 2008, and the DFL is 2008 R2.

After windows patching (windows 2008 R2 , SQL Server 2012 Sp2). I could not start SQL Server Service . I get error 1067 and this from the error log* The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x13, state: 2. Administrator should deregister this SPN manually to avoid client authentication errors." Yes it is a production system and any help is welcome. I tried using domain account that has sysadmin and local admin privilege. Thanks

I am trying to find out why there is no Kerberos authentication on my SQL instance : SELECT COUNT (auth_scheme) as nb, auth_scheme --net_transport, client_net_address FROM sys.dm_exec_connections GROUP BY auth_scheme > nb auth_scheme > > 2 (Unknown) > > 151 NTLM > > 4 SQL Using KerberosConfigurationManager, requiered SPNs seems to be correctly and automatically registered: Here's the error log regarding the SPN for the instance : Could you provide ideas to investigate why there is no Kerberos authentication? All the conditions list at [this Microsoft Docs page](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc280745(v=sql.105)) have been met. EDIT : Cannot add the output because this is confidential info, but the instance is correctly registered : > MSSQLSvc/hostname.domainName.fr:instanceName

I am receiving "cannot generate sspi" error when I try to connect a sql instance remotely. I suspected the issue might be with spn and verified that spn was registered properly on the service account(confirmed the same with AD team as well). Also tried to get the server removed from domain and rejoined. The issue still persists and issue started occurring all of sudden and there were no changes on the server (patching, reboot). Would appreciate if anyone can provide some lead to resolve this issue?

I have a question about how to manually set SPN for using Kerberos authentication on a SQL cluster. Do I set one SPN on the clustername or one on each node? I also have a default named instance, do i specify the name of the instance?