Kerberos for SQL Server and SharePoint the easy way
Configuring Kerberos can be easy. Indeed, with favourable conditions and some preparation, the whole thing can be over in minutes. However, if hours later it still does not work, troubleshooting can take many days even with help of experts.
As you all know, real world implementations can deviate dramatically from lab scenarios considered in common whitepapers. What most resources usually cover is, at best, some step by step configuration instructions for a particular architecture. Such instructions often lack explanation of why it has to be done that way in terms of the role of every step in the authentication process, i.e. they do not explain principles, only give certain examples.
I would like to present easy to follow principles of Kerberos constrained delegation and protocol transition with handy tips and templates to get this right the first time for your particular environment. The goal is to explain the meaning of the settings in terms of the role in the Kerberos constrained delegation authentication rather than simply presenting another example of a particular scenario. I will also include a
jargon-busting glossary of terms to help you get started.
This presentation covers some very useful resources to help you tame your three-headed monster and make it behave in case it decides to go on a strop. I will also mention some useful tips and resources
on dealing with Claims To Windows Token services, an important part to Kerberos configuration. It plays a very important part in delegating authentication for services requiring protocol transition (Claims -> Windows), such as Excel Services, Performance Point and Power View.
Dmytro Andriychenko's Summary
I am a flexible senior data and systems integration professional who enjoys a challenge of both technical and personal nature. I am particularly intersted in projects involving novel clashes of technologies prompting unusual approaches or organisational challenges. I love performance tuning and optimising IT system infrastructures.
These are main areas of my technical expertise:
- SQL Server Development, especially Enterprise Data Warehouse design and implementation
- Microsoft BI: complete stack of SSIS, SSAS, SSRS and SharePoint BI
- SharePoint administration, configuration and development including Kerberos configuration and troubleshooting
- MicroStrategy, including Microstrategy Architect, administration and development
- Business Objects: Data Integrator and Crystal Reports
I also have experience of designing BI systems end to end from hardware and application tiers to systems integration, requirements engineering, database and ETL design and development.
My decade of IT experience rests on a solid educational platform of two degrees in Economic mathematics and Decision support systems completed with distinction.
I quickly adapt to any team of developers and find a way to improve things and channel the work in the right direction. I find technical challenges exciting and stimulating, I usually get on well with most people and always enjoy sharing my knowledge and experience with others.
Data Integration, Data Warehousing, SQL Server Tuning, Systems Architecture, Data architecture, Database development, design, development and maintenance,
Dmytro Andriychenko's Experience
Public Company; 1001-5000 employees; Computer Software industry
November 2009 – Present (3 years 2 months) Newcastle upon Tyne, United Kingdom
- Lead designer on Finance BI project replacing manual financial consolidation Excel spread mart. The result is fully automatic process based on user-editable business rules engine. I have written high-level and detailed design document and built a working proof-of-concept prototype using T-SQL and SSIS for ETL, SSAS and Excel for presentation and SharePoint for master data management.
- Key role in designing, implementing and troubleshooting a brand new high availability BI Infrastructure for Sage UK based on SQL Server 2012 cluster and four node SharePoint 2010 farm with Kerberos delegated authentication.
- Completed full cycle of an ETL project from requirements engineering and implementation using Business Object Data Integrator (BODI) to release and support. The result of the project was a reduction in the duration of the business process from three days to five minutes.
- Administered over a dozen of SQL Servers instances (2005 and 2012) in all development environments (Dev, PreProd and Production) with over 60 databases including the data warehouse (2TB in size).
- Optimised data warehouse performance by tuning indexes using SQL Server DMVs (dynamic management views) and advising ETL developers on more efficient ways of working with SQL Server.
- Optimised many existing BODI data flows to work better with SQL Server by leveraging native database processing to achieve 3-4 times faster execution (profiling, testing, business analysis, execution plan analysis etc).
- Designed and implemented new high-performance MicroStrategy v9 hardware and software infrastructure achieving better availability and reducing costs by virtualizing DEV environments.
- Designed Data Warehouse for MicroStrategy implementation of human resources reporting.
- Over a series of meetings and networking events negotiated a saving of £20k against software maintenance contract and achieved development environment license complience worth £40k.
Systems Integration Analyst/Developer
September 2007 – October 2009 (2 years 2 months) Newcastle upon Tyne, United Kingdom
- Coordinated a team of five in-house IT professionals and seven India-based (Bangalore) on developing complex integration platform for creating internal data warehouse from disparate bio-informatics data from a multitude of sources, formats and domains.
- Designed, developed and maintained MySQL database for the internal data warehouse.
- Developed ETL processes based on Pentaho Business Intelligence Suite and KNIME.
- Maintained and administered Oracle database backend of chemical information system.
- Designed and implemented a multi-user authoring tool for off-shore team (India) for collecting data on bio-chemical interactions. Supported the off-shore team on using the tool and managed outsourced workload and data exchange between the off-shore team and in-house data warehouse.
- Re-designed IT hardware infrastructure for the company including virtualisation of storage (FC SAN instead of internal storage) and disaster-recovery solution. Changed IT support model saving £15k pa while improving reliability and service level. Moved hosting of MPP solution to in-house server room saving £3k pa.
- Delivered analytical reports to CEO involving complex statistical analysis techniques such as logistic regression and multidimensional scaling.
- Achieved a saving of over 40 thousand pounds through successful managemanent of relationships with third parties (suppliers, partners, etc.).
Systems Analyst/Database architect
September 2004 – August 2007 (3 years) Newcastle upon Tyne, United Kingdom
- Created a complex XSLT transformation for porting the old contents management system (CMS) data into SQL Server 2005 database of the new authoring environment
- Designed, developed and maintained database layer for a drug ontology CMS tool featuring heavy database side business logic. The tool used database-side set operations for making inferences based on the drug ontology relationships.
- Designed, developed and maintained database layer for the company main product http://cks.library.nhs.uk/home. I have also successfully implemented and managed fail-over cluster and backup system for this website based on SQL Server 2005
- Implemented a system of automatic XML data update of one of the main data sources dm+d using SSIS.
- Conducted regular workshops on statistical analysis for the benefit of the clinical authors within the company