I have some issues setting up Kerberos together with SQL Server and SharePoint. Any help will be much appreciated! =)
Use Kerberos to avoid double-hop issue.
User logs onto SharePoint site. User uploads a file from his machine via sharepoint site. This triggers a SSIS package that loads the file into SQL Server database.
the sharepoint site does a pageload and returns to same screen. Logs and Network Monitor/Wireshark debugging reveals that it tries to authenticate against SQL Server with 'NT AUTHORITY\ANONYMOUS LOGON'
DC - Windows Server 2008R2 x64 - role: Domain Controller. Active Directory.
SQL01 - Windows Server 2008 R2 x64 - running SQL Server 2012 Enterprise
SQL Server is running one default instance on port 1433, and 1 named instance on static a port 57648. All services uses an AD Service Account. SQL Server Agent, Browser, SSIS services etc. are running.
SP01 - Windows Server 2008 R2 x64 - running SharePoint Server 2013 Enterprise.
SharePoint is running 1 site. The site can be connected to via browser using IP or URL, no other alternate access mappings.
I have set SPNs for the service accounts running database engine, ssis, browser, http for sharepoint site and also Claims2WindowstokenService (Delegated from SharePoint machine to SQL Server Database engine)
SetSPN -S MSSQLSvc/SQLSERVER:57648\NAMEDINSTANCE test\srvc-sql-de
SetSPN -S MSSQLSvc/SQLSERVER.test.com:57648\NAMEDINSTANCE test\srvc-sql-de
SetSPN -S SISvc/SQLSERVER test\srvc-sql-ssis
SetSPN -S SISvc/SQLSERVER.test.com test\srvc-sql-ssis
SetSPN -S http/WebApp test\srvc-sp-krb
SetSPN -S http/WebApp.test.com test\srvc-sp-krb
SetSPN -S MSOLAPDisco.3/SQLSERVER test\srvc-sql-browser
SetSPN -S MSOLAPDisco.3/SQLSERVER.test.com test\srvc-sql-browser
Because SharePoint uses Claims I have, I think, fixed the Claims2WindowsTokenService by delegating from the SharePoint Server to the SQL Server Computer. Both w/FQDN and without (Both includes port and instance name)
FROM: srvc-sp-krb TO SERVICES: (srvc-sql-de) MSSQLSvc w/FQDN and without (Both includes port and instance name)
FROM: srvc-sp-krb TO SERVICES: (srvc-sql-ssis) SISvc w/FQDN and without.
FROM: srvc-sql-ssis TO SERVICES: (srvc-sql-de)MSSQLSvc w/FQDN and without (Both includes port and instance name)
FROM: srvc-sql-browser TO SERVICES: (srvc-sp-krb) http w/FQDN and without
FROM: srvc-sql-browser TO SERVICES: (srvc-sql-de) MSSQLSvc w/FQDN and without (Both includes port and instance name)
I have tested running:
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
from SQL Server Management Studio from a client machine. Logging onto SQL Server via IP\NAMEDINSTANCE and every logged on users use NTLM.
If I log on to the SQL Server itself and run the same query I also only see NTLM authentication. I have the role registered as sysadmin on the sql server.
Also, as mentioned earlier I have tried to debug with network monitor etc.
So: In short. Kerberos does not work with SSIS triggered from SharePoint, nor does it work for SQL Server alone.
Right now, I`m completely lost. :P
Thanks for reading.