SQL Saturday Kalamazoo is less than a month away! We are busily preparing the event to ensure a fantastic day of learning and networking.
One part of the event we have been working on is securing the Pre-conference information. We are now happy to announce two pre-conference options for SQL Saturday attendees. This is our first year holding pre-conferences and we are very excited to bring in Allen White and Eddie Wuerch to spend a full day diving deep into their topics. Interested in attending? See below for session details, and registration links.
Automate and Manage SQL Server with PowerShell with Allen White
This soup-to-nuts all day workshop will first introduce you to PowerShell, after which you’ll learn the basic SMO object model, how to manipulate data with PowerShell and how to use SMO to manage objects. We’ll then move on to creating Policy-Based Management policies, work with the Central Management Server, manage your system inventory and gather performance data with PowerShell. We’ll wrap up with a look at the new PowerShell cmdlets introduced for SQL Server 2012 and how you can use PowerShell to manage SQL Server 2012 in server environments including Windows Server Core. After this one day, you’ll be ready to go to work and able to use PowerShell to make you truly effective.
Start with a simple proposition: a process is either working or waiting. You can tune the working part, but are you seeing the whole picture? There are many different resources on which your process could be waiting – a lock, memory, disk, CPU, and much more. When a process must wait, SQL Server will log it. There are hundreds of different wait types, and they are a gold mine of data for finding and solving performance problems – and proving the changes worked. Eddie Wuerch takes his extensive experience as a speaker, trainer, mentor, and DBA in one of the largest and busiest SQL Server environments in the world and distills it into a collection of performance tuning topics for DBAs and developers tuning databases of all sizes. After attending this seminar, you will be able to gather wait stats and use them to zero in on performance issues affecting your databases. Stop guessing, start knowing!
Please note that pre-conference registration IS separate from the SQL Saturday registration. Early bird pricing for Pre-conferences ends on 10/15/2013, so sign up now for the best pricing options! If you’re still looking to register for SQL Saturday to see Allen, Eddie, and 25 other great presenters, sign up today!
Whether running SharePoint 2010 or SharePoint 2013, Kerberos Delegation is an important part of delivering a fully functional BI solution. It’s also not a trivial task to configure correctly. There are many layers involved and the larger the SharePoint environment, the more complex it is.
The purpose of this post isn’t to explain how to configure delegation from the ground up, that topic has been covered very well with documents such as Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products. This document, by the way, is still largely relevant for SharePoint 2013, although some of the SharePoint setting have changed, the foundational elements and Active Directory settings are the same.
Rather, the purpose of this post is to help troubleshoot an existing environment where delegation is not working properly. This task can often feel like fighting the mythical monster namesake, a Kerberos.
Troubleshooting an established environment can be a mammoth task. I’ve worked through enough of them to know how to break it into manageable pieces, which most of the time, will lead to a configuration error that can be fixed and get the environment delegating identities as expected.
My goal is, by end of this post, to help explain how to identify the major pieces of configuration, determine which pieces are or are not working, and offer some common issues and solutions to get this up and running correctly. I’ve found that by following the three or four major steps below, the majority of environments with incorrect delegation configuration will be corrected.
Verify Delegation to Data Source – Example: SSAS
Using a domain connected client machine, create a new connection to the domain connected SSAS server in Excel. One the data base list is populated, log into the database server and check the Security Event Log for Event 4624. If Event does not specify Kerberos as Logon Process, delegation is not configured.
1. Incorrect Service Principal Name (SPN) registration
2. Service account not trusted for delegation
3. Computer account not trusted for delegation
1. Using setspn.exe, verify currently registered SPN entries and adjust as needed for data source
2. Using an account with Domain Admin rights, adjust the delegation trust level for the service account to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only”
3. Using an account with Domain Admin rights, adjust the delegation trust level for the computer accounts to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only.” Note: this should be done for any computer accounts that fall in the path from client to middle tier to data source
Verify Delegation to Web Front End
If delegation to the data source is working – Event 4264 lists Kerberos as the Logon Process – then the next step is to verify that delegation to the SharePoint Web Front End is working properly. Very similar to the data source, this test should be done on a domain connected client machine.
Open IE and browse to the root site, http://spdemo, for example. Once the page loads, log into the SharePoint WFE server and again check for Event 4264 in the Security log. Just as before, if the Logon Process does not mention Kerberos, then delegation is not configured correctly. See below for common causes.
1. Root site not in client machine INTRANET zone.
2. Client machine not configured to use Integrated Windows Auth
3. Service account not trusted for delegation
4. Computer account not trusted for delegation
5. SPN registration incorrect for site collection
6. Site collection authentication provider setting incorrect
7. IIS configuration incorrect
1. Verify root site is in INTRANET zone. Add if not.
2. Check Internet Options -> Advanced tab for “Use Integrated Windows Authentication”. Select if currently unselected (will require IE restart)
3. Using an account with Domain Admin rights, adjust the delegation trust level for the service account to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only”
4. Using an account with Domain Admin rights, adjust the delegation trust level for the computer accounts to “Trust this user for delegation to any service” or “Trust this user for delegation to specified services only.” Note: this should be done for any computer accounts that fall in the path from client to middle tier to data source
5. Use setspn.exe to verify registered SPN for AppPool service account. Correct as necessary.
6. In Central Administration verify the setting for the default Authentication Provider for the site collection. Ensure that the Integrated Windows Authentication option is set to “Negotiate (Kerberos)”
7. Ensure the Application Pool is configured correctly for Kerberos delegation. See Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products for detailed IIS configuration settings
Verify Delegation of Service Application – Example: PerformancePoint Services
The final step to test is the service application itself. For example, testing PerformancePoint Services would mean creating a new data source with “Per User Identity” enabled. Tracing the database while creating this should show whether or not Kerberos delegation is working.
1. Service account not trusted for delegation
2. Claims to Windows Token Service
1. If the application is running under a different service account that the WFE or databases, then it’s possible that it’s not being trusted for delegation like the others (set correctly above). However, the Delegation tab might not be visible in AD Users and Computers. In order to make it visible, create a bogus SPN. For example, the following SPN can be used for PerformancePoint Services
setspn.exe –s PPS/SPDEMO DEMO/svc_pps
setspn.exe –s PPS/SPDEMO.DEMO.LOCAL DEMO/svc_pps
Note: In this example, this SPN does not point to a real Service Principal, but it does enable the Delegation tab in AD Users and Computers to be able to check the Trust settings.
2. If the service account is already trusted, then there is one more big section to check IF Claims authentication is being used. Note that Claims authentication is available in both SP2012 and SP2013, but enabled by default in SP2013. The following steps do not apply if Basic Authentication is being used.
Verify Claims to Windows Token Service
When claims authentication is being used in SharePoint, it’s important that everything is configured with it correctly to allow the C2WTS to create the appropriate Kerberos tickets. If it’s not configured correctly, it won’t work, but there may not be many logs pointing to that fact. This makes the service rather tricky to troubleshoot. The best way I’ve found is just to verify all of the settings when it’s believed to not be working correctly.
The white paper Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products goes over this really well, but see below for a high level checklist.
- Ensure the service account for C2WTS is a local administrator on the WFE
- Make sure the following SECPOL settings have the service account included (Local Policies/User Rights Assessment)
- Logon as a service
- Act as part of the operating system
- Impersonate a client after authentication
- In Central Admin make sure the C2WTS service account is included in Managed Accounts
- In Central Admin make sure the C2WTS service is configured to use the above managed account
- In the Services snap-in make sure the Claims to Windows Token Service is dependent on Cryptography Services
Once all of those setting are verified, restart the Secure Token Service in Central Admin (Services on Server). If C2WTS had incorrect settings, and everything above in this document tested successfully, then Kerberos delegation should be working now.
What if it’s still not delegating?
If after all of the above troubleshooting has been performed and delegation is still not happening, then it time to go through the referenced White paper (Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products) step and step and make sure that EVERYTHING is 100% configured correctly.
In my experience with troubleshooting a number of different environments, spending an hour to two and verifying correcting any issues with the above three areas, Data Source, WFE, Application/C2WTS, will usually result in an environment with working delegation. If not, then more time will need to be taken to go through the steps in close detail.
The good news is that SharePoint has some of the best documentation in the Microsoft landscape. If you’ve taken the time to read it all, then you’ll be very well prepared to take on the task of troubleshooting delegation in an existing environment.
Not only was I honored to be selected as a Summit Speaker this year (with TWO sessions no less), but I was also asked to present at #24HOP.
I will be presenting a fun session titled “Delivering Analytics with Excel: 10 Secrets for Success” – due to the shortened session length for #24HOP I will probably only get through 8 secrets, but still, it’s a lot of good content that will benefit any report developer.
The reason I love to talk about Excel is that EVERYONE uses it. Sure SSRS is nice and flashy, and PerformancePoint is my second true love (see my Summit sessions), but Excel is where business is done. Talking about Excel and how to really make it work well as a reporting tool is one of my favorite career-related pastimes.
I’ve managed to pull the prestigious 3AM EST time slot for my #24HOP presentation, so if you are in the US, put on your jammies and come join me as I talk about rows and columns and pivots (oh my!). Everyone else, grab some coffee, or some dinner and join me as well. I can’t wait to see you all there. While you’re at it, take a look at the other great #24HOP sessions. Sign up for those as well!
I’m excited and honored to announce that I’m presenting a Community Session at the PASS Business Analytics Conference in Chicago, IL on April 10 – 12.here.
Register for the PASS Business Analytics Conference here. See you there!
See you there!
By the end of this post, I will have you convinced that you can work out and stay fit all day at work. Seriously.
If you’ve not been to a gym in a long time, this might seem like a lot of working out, but I promise, in no time, you’ll be so used to this schedule that you’ll start to incorporate fitness into other aspects of your life; like dinner time!
Read on for my story:
Hi. I’m husky (read: fat). And out of shape. Well, I was… I liked to blame lots of outside factors: Fast food, preservatives, salt, fruit (no, really), sitting at a desk all day…
Wait, what was that last one? Sitting all day? That probably has something to do with it, right? About a year ago I decided to really do something about it. I really didn’t want to go as drastic as a new desk with fancy heights for standin’; I like my desk and I like my chair. I don’t want to stand all day. But, I did want to get in shape.
Problem is, I work from home. I also live in the woods. There is no gym or fitness center close to me. And honestly, I typically go 4 or 5 days without leaving the house so there’s little chance that I would end up at the gym on a regular basis.
Since I tend to put in a lot of hours in between work and family, completing a long, drawn out weekly workout schedule isn’t going to cut it either. I need something that I could do during the day that wouldn’t impact my productivity.
It took me a few iterations (I’m agile!) and several months, but I finally ended up on something that works well for me and I’d like to share it.
Staying fit really is simple. Although having a Shake Weight is pretty awesome, you don’t need a bunch of fancy gadgets. To stay fit, you only need your body. Nothing more than that; well, maybe a floor, and some gravity. But really, that’s it.
So, how do I stay fit AND get lots of fantastic work done without changing into workout gear? Simple. Read the following three tips:
1.) I have an hourly reminder set (on my phone) that tells me to do 10 push-ups.
It’s like having a drill instructor, without all of the yelling. It’s easy, takes 10 seconds to complete, and through the day I’m routinely doing 80 – 100 pushups. Spreading them out over the day helps with the “But I can’t do 100 push-ups” mentality. Everyone can do 10 push-ups. You can use your knees to start with (see here). Over time, you’ll get stronger and be able to do standard push-ups.
As you advance you can get really crazy and start doing things like
- Military Style Push-ups
- Explosive Push-ups
- Diamonds of Death
- Under the Fence
There are lots of examples online. I’ll let you investigate further.
2.) I also have a reminder to tell me to do 10 sit-ups.
Again, you CAN do 100 sit-ups a day. Start simple; build complexity as you get stronger. Anyone can do this. Other example for hourly reminders can include:
- Tricep Dips – Use your desk chair for simplicity!
- Squats – weighted or no!
- Lunges – great for cardio strength!
- Jumping Jacks – 25 for plyometric strength and cardio in one!
I routinely change up my reminders throughout the week so I’m not sticking with the same exercise every day. Variety is key when it comes to easy fitness.
3.) Engage a friend!
This is probably the most important thing I did to take my fitness to the next level. I found a friend (YAY!) that was willing to play a little game with me. Have you heard of tabatas? If not, read this, then come back….
As I was saying, I found a friend that was willing to play a little game with me. At random intervals through the day, we IM each other – keeping mindful of Presence Indicators – and say “Tabata Time!” Ok, we really don’t say that — it sounds to TMNT-ish.
After describing what the tabata is (at our discretion), we complete said tabata. An example might be: 60 second wall-sit, 10 push-ups, 20 lunges, Repeat, GO. This does take a little more time than the 10 push-ups mentioned above, but we have a limit that it can’t take any more than 5 minutes and there is a 10 minute window for accepting and starting. Doing this craziness with a friend makes it fun, and also helps to PUSH me. That’s a big one. Having that accountability is key.
Generally we do this 3 or 4 times a day, although some days may be more or less depending on uncontrollable factors. Still, it’s fun, it’s a workout, and it helps to keep you in shape.
If Cardio is more your style; there are things you can do there too!
Some of my friends have had the pleasure of building a treadmill desk – which I understand as a treadmill with a shelf attached for a laptop. Setting the speed to something nice and slow – under 2 mph – is good enough for an hour or two long walk while you check email, or write queries, or build cubes! That’s a lot of caloric burn during that productive time.
So what did we learn?
In a normal day, I spend no more than 30 minutes completing all of this working out: 100 or so push-ups, sit-ups, and various tabatas. No workout gear, no special attire. Simplicity. That’s what I love about this kind of working lifestyle. Even if you work in an office you can do this. Sure, you may look a little foolish to those around you…but, I ask you, who will look more foolish in 6 months when you’ve toned up and others are spreading out?
The above points have worked well for me and my friends. What would you like to be able to incorporate into your day to help stay in just a little better shape?
With the November meeting complete, the year is now closed for West MI SQL. We’ll be meeting again in January, with a special holiday event!
I would like to thank each and every one of our members for not only showing up to the meetings but also for making this a little bit of #sqlfamily right here in Grand Rapids. Without YOU this organization would not exist. Thank you for showing up, for bringing friends, for asking questions, and for stepping up to the plate, getting out of your comfort zone and speaking!
Looking back at 2012 I think I’m most proud of our members who have stepped up and gave their first community presentation at our podium. We have had 4 this year! Four! And of those four, two have went on to speak at one or more SQL Saturdays. As a User Group leader, nothing makes me happier than to see members from our group up there on stage at a SQL Saturday. A special thank you to Kevin, Anthony, Emily, and Dustin for your efforts for the SQL Community this year. I’m looking forward to MORE presentations from you, and others from the audience as well!
I also would like to thank a few of our sponsors for helping to burden some of the cost of dinners, and for the great giveaways that we are able to provide. To New Horizons for the generous use of their facility month after month, thank you! To TekSystems, Confio, and BlueGranite for their monetary support through the year, thank you! To O’Reily, Apress, RedGate Publishing, and Microsoft Press, for the generous donations of books and other giveaways, thank you!
These “thank yous” wouldn’t be complete without a special shout-out to PASS, for providing support via Regional Mentors (thank you Joe and Arlene), Karla Landrum, and the online presence — thanks PASS IT!
We are well under way to making this a great SQL Server event!
Lots of hard work as been done up to this point, and we are extremely excited to be bringing all of YOU a day of FREE SQL Server training. We would like to take a minute to thank all of our wonderful speakers who have submitted, our sponsors for making this event possible, and our attendees for trusting us to provide them with great community training!
The SQL Saturday 160 team is committed to provide YOU, our SQL Saturday attendees, with top-shelf training. One way we are holding this commitment is by providing an opportunity to attend Spotlight Sessions.
What is a Spotlight Session?
A Spotlight Session is very much like normal SQL Saturday session only twice as long. We have invited well known community speakers who have expressed interested in spending more time on one of their topics to submit abstracts.
Topic areas for Spotlight Sessions will include DBA and BI topics.
What does this mean for me?
Having 2 hours of speaking time will allow a presenter to dive much deeper into a topic, and have more time for discussions with the crowd. This means that, should you choose to join a spotlight session, you will receive more information to take back to your workplace. Think of it this way: Would you rather spend an hour learning about different types of indexes, or two hours and add in practical indexing knowledge as well?
But what if I don’t want to attend a spotlight session?
No problem! We will have MANY other sessions to attend as well. The spotlight sessions are being working into our schedule to take up exactly two sessions slots. Attendance to a spotlight will not in any way affect your schedule before or after. Attendance is TOTALLY optional, and no special charge nor requirement is made to join in.
When will I find out what the sessions are?
We will be announcing the Spotlight Sessions along with our standard sessions during the first week of August. Check back soon for more details.
Thanks for your time today, and if you haven’t registered for SQL Saturday #160 in Kalamazoo yet, do so now! Limited space is available and will fill quickly!