kerberos not working evenafter spn setup

ACDBA 416 Reputation points
2022-03-28T11:26:34.867+00:00

Hi All,

I have an sql server 2012 ENT on windows 2012. There are two instances in the server. One default and one named instance. Kerb is working fine with the default instance. But not working for named instance. Named instance is configured with dynamic port.(There is a specific reason to set this instance to dynamic port). The current port which is in use is used to set SPN as below.

setspn is added for

  1. MSSQLSVC:Servername:InstanceName
  2. MSSQLSVC:Servername:InstanceName:port
  3. MSSQLSVC:Servername.FQDN:InstanceName
  4. MSSQLSVC:Servername.FQDN:InstanceName:port

But below query always shows NTLM.

SELECT auth_scheme FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
GO

A reporting server SSRS is not able to connect to this instance saying "Cannot create connect to data source".

We can connect to SQL servers from other servers using SSMS and other testing tools with out issues.

Thank you.

ACDBA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions
{count} votes