Sql-server – What sort of SPN / AD Computer Delegation do you need for delegation of credentials to a linked server that connects to a file share

authenticationkerberoslinked-serversql serversql-server-2008-r2

enter image description here

The idea here is to perform a double hop and delegate the credentials of the client (leeand00@example.org), that have been sent to the SQL2.example.org server to SQL3.example.org (admittedly this is an SMB file share and not an SQL Server)

My belief is that I need to delegate the credentials in order to access the file share, since the error message that I get when trying to access a VIEW on the SQL2 server which pulls from a Linked Server using the VFPOLEDB Provider which points to the SMB Share on SQL3 when connecting with a client using a KERBEROS auth_scheme without the SPNs setup is:

OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure hatsum, Line 5
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME".

So this leads me to believe that an SPN on the SQL2 and SQL3 computers on the correct port will delegate the client credentials to the SMB Share. (Using Constrained Delegation of course…)

Any idea what sort of SPN / AD Computer Delegation settings I need to pull this off?

Please note that when connecting using any other sort of auth_scheme, be that NTLM or SQL, I am able to query the Linked Server without issue; but when using the KERBEROS auth_scheme it yields the error message above.

I also know I can list the SPNs that are already set using setspn -l <server-name>

Best Answer

There is only a single kind of Service Principal Name you need for SQL Server. 

MSSQLSvc/<servername>:<portname>

It is typically necessary to implement this twice, one for the short name, and one for the long name. If, for instance, the SQL Server is named "SQLServer1.somedomain.com" and was running on port 1433, then you'd need:

MSSQLSvc/SQLServer1.somedomain.com:1433
MSSQLSvc/SQLServer1:1433

The computer account for SQLServer1.somedomain.com in Active Directory should be granted the ability to delegate security. Once that is working correctly, you can confirm Kerberos authentication is working by looking at:

SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = dec.local_tcp_port
    , dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

If this shows Kerberos in the auth_scheme column, then Kerberos authentication is working. If you have a second SQL Server at the other end of a linked server, you can run that query over the linked server:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = dec.local_tcp_port
    , dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;
';
EXEC [linked_server_name].tempdb.sys.sp_executesql @cmd;

The error message makes me think you're seeing a problem with file share permissions - when using Kerberos SQL Server will impersonate you when accessing the SMB share - therefor your account needs access to it.

Related Question