How to Check Authentication Method into SQL Server (SharePoint)

How to Check the SQL Server Authentication Method for SharePoint

When SQL Server Reporting Services is deployed in SharePoint integrated mode, Kerberos authentication isn't optional - it's a hard requirement. If your SharePoint environment is only passing NTLM tokens to SQL Server, the Claims to Windows Token Service (C2WTS) will fail to translate SharePoint's claims-based authentication into the Kerberos tickets that SSRS needs. The result is broken reports, access denied errors, and a frustrating troubleshooting process.

The quickest way to check which authentication method is being used is to query the sys.dm_exec_connections and sys.dm_exec_sessions dynamic management views directly on the SQL Server instance supporting SharePoint. This tells you immediately whether active connections are authenticating via Kerberos or falling back to NTLM.

Why Authentication Method Matters for SharePoint and SSRS

Most SQL Server environments work fine with NTLM. It's the default Windows authentication protocol and it handles the majority of direct connection scenarios without any special configuration. The problem arises specifically in multi-hop authentication scenarios, and the SharePoint plus SSRS integrated mode deployment is a textbook example of one.

Here's what happens in a correctly configured environment. A user authenticates to SharePoint using claims-based authentication. SharePoint's C2WTS service translates that claims identity back into a Windows identity. That Windows identity then needs to be passed through to SQL Server Reporting Services, which in turn connects to your SQL Server data sources. That's two or three authentication hops depending on your architecture.

NTLM cannot handle this. It's a challenge-response protocol that breaks down when credentials need to be delegated across multiple servers. Kerberos, by contrast, uses tickets that can be forwarded and delegated across hops, which is exactly what this architecture requires. Microsoft's documentation on SSRS and Kerberos is explicit on this point - without Kerberos delegation configured correctly, integrated mode SSRS in SharePoint simply won't authenticate properly to backend data sources.

This is why checking the SQL Server authentication method is one of the first diagnostic steps when troubleshooting SharePoint reporting failures.

How to Query Active Authentication Schemes on SQL Server

The following T-SQL script queries two dynamic management views to show you exactly which authentication protocol each active session is using. Run this directly on the SQL Server instance that supports your SharePoint environment.

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    c.auth_scheme
FROM
    sys.dm_exec_connections c
    INNER JOIN sys.dm_exec_sessions s
        ON c.session_id = s.session_id

The query joins sys.dm_exec_connections with sys.dm_exec_sessions to pair each active connection's authentication scheme with the login name and host machine that established it. The auth_scheme column is the one you're interested in. You'll see either KERBEROS or NTLM returned for each session.

In a correctly configured SharePoint plus SSRS environment, connections originating from your SharePoint application servers should show KERBEROS in the auth_scheme column. If you're seeing NTLM for those connections, your Kerberos delegation is not working correctly and your SSRS integrated mode deployment will have authentication problems.

What to Look For in the Results

When you run the query, focus on the host_name values that correspond to your SharePoint application servers and SSRS servers. A few things to check:

• All SharePoint app server connections showing NTLM - this points to a Kerberos configuration problem, most likely missing or incorrect Service Principal Names (SPNs) for the SQL Server service account.
• Mixed results (some KERBEROS, some NTLM) - this can indicate that some service accounts have SPNs configured correctly and others don't, or that certain connection paths are bypassing Kerberos delegation.
• All connections showing KERBEROS - your authentication is configured correctly. If you're still having SSRS issues, the problem lies elsewhere.
• No connections from SharePoint servers visible - the SharePoint application pool may not currently be active, or you may be looking at the wrong SQL Server instance.

It's worth running this query while actively triggering a report render in SharePoint so you can see the connections in real time. Idle environments may not show the relevant sessions.

Common Reasons Kerberos Fails in SharePoint Environments

If your query is returning NTLM where you expect Kerberos, the most common causes are:

Missing or incorrect SPNs. Kerberos relies on Service Principal Names registered in Active Directory to identify services. If the SPN for the SQL Server service account is missing, duplicated, or registered against the wrong account, Kerberos negotiation will fail and Windows will silently fall back to NTLM. Use setspn -L <serviceaccount> to list registered SPNs and verify they match your SQL Server instance name and port.

Constrained delegation not configured. Even with correct SPNs, the SharePoint service accounts need to be configured in Active Directory to allow constrained delegation to the SQL Server service. This is set in the Active Directory Users and Computers console under the Delegation tab for the relevant service accounts.

SharePoint application pool running under a local account. If the SharePoint web application pool is running under a local machine account rather than a domain account, Kerberos delegation cannot work. All service accounts in the chain need to be domain accounts.

SQL Server not listening on the expected port. If SQL Server is running on a non-default port and the SPN doesn't include the port number, Kerberos negotiation will fail. Named instances in particular require careful SPN configuration.

Filtering the Query for Specific Hosts

If your SQL Server has a large number of active connections, you can filter the query to focus on specific SharePoint servers:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    c.auth_scheme
FROM
    sys.dm_exec_connections c
    INNER JOIN sys.dm_exec_sessions s
        ON c.session_id = s.session_id
WHERE
    s.host_name IN ('SP-APP01', 'SP-APP02')
ORDER BY
    s.host_name,
    s.login_name

Replace the host name values with the actual server names of your SharePoint application servers. This makes it much easier to isolate the relevant sessions in a busy environment.

Key Takeaways

• The auth_scheme column in sys.dm_exec_connections is the definitive way to check whether SQL Server sessions are authenticating via Kerberos or NTLM.
• Kerberos is a hard requirement for SharePoint integrated mode SSRS deployments. NTLM cannot handle the multi-hop credential delegation that this architecture requires.
• If connections from SharePoint application servers show NTLM, investigate SPN registration and constrained delegation configuration in Active Directory first.
• Run the diagnostic query while actively triggering a report render to capture the relevant sessions in real time.
• Mixed Kerberos and NTLM results in the same environment usually point to inconsistent SPN or delegation configuration across service accounts.

Authentication configuration issues in SharePoint and SQL Server environments are one of the more time-consuming problems to diagnose because the symptoms (access denied errors, broken reports) can have multiple causes. If you're working through a SharePoint and SSRS deployment and hitting authentication walls, DBA Services can help. Our SQL Server health checks include a review of authentication configuration, service account setup, and delegation settings as part of a broader assessment of your environment. Get in touch with the team to find out more.