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

  • 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?

  • 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

  • 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