How to Check Authentication Method into SQL Server (SharePoint)

If you’re deploying SQL Server Reporting Services 2012 (integrated mode) into SharePoint Server 2010, you will want to ensure that your SharePoint environment is successfully passing Kerberos authentication into your supporting SQL servers.

The following T-SQL script will allow you to easily interrogate your SharePoint’s SQL server for authentication schemes which are being used to connect to it. From this you’ll quickly determine whether your authentication in SharePoint has been correctly configured to pass Kerberos tickets, or whether it is only passing NTLM based authentication;

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

Kerberos is required in order for SharePoint’s Claims to Windows Token service to successfully translate a Claims based authentication (used within SharePoint) back to a Kerberos ticket for authentication into SQL Server Reporting Services.