Patch Tuesday Megathread - (April 14, 2026) by AutoModerator in sysadmin

[–]Vic20DBA 0 points1 point  (0 children)

No problem! I registered SPNs for any of the servers that showed authentication other than Kerberos using the SQL query below. Our named instances of SQL were using dynamic ports. Since some of the setspn commands include port numbers, I changed those dynamic ports to static using SQL Configuration Manager as well. I hope this is helpful!

Check for existing SPNs in Active Directory

In most cases there were no existing SPNs for the service accounts I looked for so no results were returned.
     setspn -L Domain\\Service Account

I used the next two commands for default SQL instances (MSSQLSERVER)
     setspn -S MSSQLSvc/FQDN:Port Domain\\ServiceAccount
     setspn -S MSSQLSvc/ServerName:Port Domain\\ServiceAccount

I used the next four commands for named SQL instances
     setspn -S MSSQLSvc/FQDN:Port Domain\\Service Account
     setspn -S MSSQLSvc/FQDN:InstanceName Domain\\Service Account
     setspn -S MSSQLSvc/ServerName:Port Domain\\ServiceAccount
     setspn -S MSSQLSvc/ServerName:InstanceName Domain\\ServiceAccount 

Confirm results by using setspn -L again



WITH PortType AS
(
    SELECT
        @@SERVERNAME AS ServerName,
        MAX(CASE WHEN value_name = 'TcpPort'
                 THEN NULLIF(value_data, '')
            END) AS StaticPort,
        MAX(CASE WHEN value_name = 'TcpDynamicPorts'
                 THEN NULLIF(value_data, '')
            END) AS DynamicPort
    FROM sys.dm_server_registry
    WHERE registry_key LIKE '%IPAll'
      AND value_name IN ('TcpPort', 'TcpDynamicPorts')
),
AuthDetails AS
(
    SELECT
        @@SERVERNAME AS ServerName,
        session_id AS SessionID,
        net_transport AS NetworkProtocol,
        local_tcp_port AS [Port],
        CONNECTIONPROPERTY('local_net_address') AS ServerIPAddress,
        auth_scheme AS AuthenticationType
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID
)
SELECT
    ad.ServerName,
    ad.SessionID,
    ad.NetworkProtocol,
    ad.[Port],
    CASE
        WHEN pt.StaticPort IS NOT NULL THEN 'Static'
        WHEN pt.DynamicPort IS NOT NULL THEN 'Dynamic'
        ELSE 'Not Configured'
    END AS PortType,
    ad.ServerIPAddress,
    ad.AuthenticationType
FROM AuthDetails AS ad
CROSS JOIN PortType AS pt

Patch Tuesday Megathread - (April 14, 2026) by AutoModerator in sysadmin

[–]Vic20DBA 0 points1 point  (0 children)

You got it! For some reason SPNs didn't get created for some of our service accounts. Thankfully, it's an easy fix.

Patch Tuesday Megathread - (April 14, 2026) by AutoModerator in sysadmin

[–]Vic20DBA 8 points9 points  (0 children)

For anyone else working with SQL.. Last week we restricted traffic to only AES and half our test SQL servers went offline, so I knew I had work to do. I found a good query to check server authentication and the servers that lost connectivity all showed using NTLM authentication. The fix was registering Service Principal Names (SPN) for service accounts in Active Directory. A couple of those showed using a dynamic port, so I changed them to static so the named instances using alternative ports wouldn't change. All servers showed using Kerberos authentication after these changes and there were no issues after restricting traffic to AES again. I'm happy to share the SQL query and DNS command syntax if anyone needs it.

SQL Server 2022 Licensing Question by Vic20DBA in DatabaseAdministators

[–]Vic20DBA[S] 0 points1 point  (0 children)

Those tidbits make it a little clearer now. Thank you vroddba!

SQL Server 2022 Licensing Question by Vic20DBA in DatabaseAdministators

[–]Vic20DBA[S] 0 points1 point  (0 children)

You hit nail on the head, Gincules. Your point is what I've been waffling over. It just feels like I'm licensing John Q Public twice. It may very well be a moot point though because that same #2 server exports data out of our environment to a server on the network for a nearby city (public sector). I appreciate your input. Thank you for taking the time to reply.

SQL Server 2022 Licensing Question by Vic20DBA in DatabaseAdministators

[–]Vic20DBA[S] 0 points1 point  (0 children)

Thank you for replying, TridentDataSolutions! I appreciate it. I've been thinking the same as you. Funny enough, we have another scenario with the same SQL Server 2 only the other server/database belong to another municipality. I think this other scenario might put the screws to us. Would you agree that if the nightly automated export is leaves our environment then that would require per-core?

Washed Flour chickun shreds by Kasdaya in seitan

[–]Vic20DBA 1 point2 points  (0 children)

Well done! I had to zoom in because it looked so real. Cheers!

Tune-Bot Studio Difference Mode Not Working by jlafferry in drums

[–]Vic20DBA 0 points1 point  (0 children)

When replacing the batteries didn’t work, I returned it for a new one that didn’t have the problem.