Take the 2-minute tour ×
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.

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

migration rejected from stackoverflow.com Sep 27 '13 at 11:38

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Max Vernon, RolandoMySQLDBA, dezso, Paul White, bluefeet Sep 27 '13 at 11:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RolandoMySQLDBA, dezso, Paul White, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the 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 '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 '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 '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 '13 at 15:53