August 9, 2018 at 9:08 am
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?
August 9, 2018 at 2:08 pm
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
October 12, 2018 at 1:40 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply