Registering an SPN - can't seem to get Kerberos authentication working

Question

Registering an SPN - can't seem to get Kerberos authentication working

jackimo

SSCommitted

Points: 1885
More actions
August 9, 2018 at 9:08 am

#369001

So I'm working with our domain administrators to register and SPN for a new SQL server.
Here are the commands we are running:
setspn -S MSSQLSvc/NewServer ourdomain\NewServer_sqlsrvc
setspn -S MSSQLSvc/NewServer:1433 ourdomain\NewServer_sqlsrvc
setspn -S MSSQLSvc/NewServer.ourdomain.ca:1433 ourdomain\NewServer_sqlsrvc
I restart SQL Server and we are still using NTLM.
If I run the following command:
setspn -l NewServer, I don't see the SPN as registered.
I tried this with a old server and I do find the SPN and its using Kerberos.
So I guess are we doing something wrong registering the SPN?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

try this

begin
DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
--SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS FQDN
declare @port nvarchar(100)
declare @auth nvarchar(100)
select @port =local_tcp_port ,@auth=auth_scheme
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID

-- Check SPN
select 'setSPN -L "' + service_account +'"' as cmd , @auth auth_schme
FROM sys.dm_server_services
where servicename like 'SQL Server%'
and servicename not like '%Agent%'

-- Create SPN
select 'setSPN -S "MSSQLSvc/' + Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain +
case when serverproperty('InstanceName') is null then '"' else ':' + Cast(SERVERPROPERTY('InstanceName') as nvarchar) + '"' end
+ ' "' + service_account +'"' as cmd_to_set_spn , @auth auth_schme
FROM sys.dm_server_services
where servicename like 'SQL Server%'
and servicename not like '%Agent%'
and @auth='NTLM'
union
select 'setSPN -S "MSSQLSvc/' + Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain +
case when serverproperty('InstanceName') is null then ':' +cast(@port as nvarchar) + '"' else '/' + Cast(SERVERPROPERTY('InstanceName') as nvarchar) + ':' +cast(@port as nvarchar) + '"' end
+ ' "' + service_account +'"' as cmd , @auth auth_schme
FROM sys.dm_server_services
where servicename like 'SQL Server%'
and servicename not like '%Agent%'
and @auth='NTLM'
end

DHeath SSCertifiable Points: 5200 More actions · Answer 2

Goher2000 awesome piece of code....Thanks

I for one can never completely understand SETSPN and how it works.... for some reason i just cant get is so i truly appreciate that snippet.

Thanks
Dheath

DHeath