Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have some issues setting up Kerberos together with SQL Server and SharePoint. Any help will be much appreciated! =)

GOAL:

Use Kerberos to avoid double-hop issue.

USAGE:

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.

ERROR:

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'

SETUP:

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.

SPNs

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

DELEGATION:

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)

TESTED:

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.

share|improve this question
You should start with the SQL server - it should return KERBEROS for that query atleast for you. I would start from by assigning static port to your instance and defining spns only for that. When i did my own tango with sql spns documentation suggested that instance name is sufficient for spns however i never managed to get it to work. – drk Feb 13 at 11:29
Thanks for your reply. Yes, the sql server part is where I`m focusing now. I have set static ports on the named instance, and registered SPN with the port nr. included. Obviously I`m doing something wrong, because it still doesn`t work :P – Konstantine Feb 13 at 12:00
drop sql instance names from spns, make sure there are no dublicate spns on host accounts etc. Make sure that sql services are running under the account. Little note that sql server does know how to register its own spns but as generaly expected it lacks the permissions to do so – drk Feb 13 at 12:12
It sounds dumb, but make sure that the clocks on the servers, workstations & domain are synchronized. kerberos becomes unhappy if the clocks are too far apart and connections will fall back to NTLM. +1 for self-registering SPNs. I had various problems, plus bugging domain admins to run setspn commands for me. We gave in, started granting the right to create SPNs to the service accounts (we did NOT make those accounts domain admins). Problems went away and our workload dropped. Also, +1 for getting SQL & SSMS working first. Good chance that if SSMS works, the other stuff will follow. – darin strait Feb 14 at 15:53

migrated from stackoverflow.com Feb 14 at 11:35

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.