SQL Saturday finished! Cannot general SSPI context

After I got through my session, BANG, I get sick.  Probably from the food or something as I could barely walk.  After that, I had a headache and its just now going away.  Note to self:  Bring my own food!

Today was pretty good.  Two P2 emergencies.  The interesting one though is the SSPI error.  What the heck is that?  Anyhow what it finally boiled down to was one of our sysadmins put his admin account as the SQL Engine service.  It was working fine until he changed his password and then brought the entire system down for about 3 hours.

When I changed it back the services started, but it wasn’t able to receive connections from the outside.  Mind you, it was working before his password change, so no firewall rule could be blocking it.  What could be wrong?

I figured it had to do with SPN so I decided to register two new ones:

setspn –A MSSQLsvc/ServerSP01:1433 Domain\sql_srv399
setspn –A MSSQLsvc/ServerSP01.domain.com:1433 Domain\sql_srv399

Unfortunately that didn’t work either.  So we called “Ghostbusters”, I mean Microsoft Support.  The Engineer was nice enough to let me tell the whole story before he looked at SQL’s error log.  He found the same issue and after digging around asked what the account that was previously running the agent jobs.  When he queried the SPN, bingo, they were registered too so he removed them:

setspn –D MSSQLSvc/Server1.domain.com:1433 Domain\adminbob
setspn –D MSSQLSvc/Server1.domain.com Domain\adminbob

From then as soon as he old connection worked a n outside connections were authenticated.  All that because of a service account.  Sheesh.

SQL Saturday #289 Here I come!

Its crunch time! Almost end of day Friday and we’re heading out to the speaker dinner! I had just finished my VM lab and I must say I’m pretty impressed myself. Take a look at my WORKSTATION VM!VMs
Ok, I admit, its only 8 VMs running at the SAME time on 12GB of RAM. What? Are you kidding me? 7 SQL Servers and 1 DC? Yeah, and I got AlwaysOn running as well.

How the heck am I doing that on 12GB. Ah, the secret sauce is CORE! I’ve ripped out the GUI when I finished my old installs and figured I’d go all the way and only have 1 GUI client and the rest CORE including the DC. Did you know that you can install SQL the ol’ fashioned way, Next–>Next–>Next, (reminds you when you stood in line at the DMV) and when you’re finally done, just remove the GUI and SQL will hummmm its way to happiness.

One thing great about going to SQL Saturday is all this preparation. Why do I want to learn Powershell? Core. She’s coming and what are you going to do when you RDP to the server and see this:
4-25-2014 2-25-42 PM

Anyhow, wish me luck on the Powershell speech. At least I have experience building a one-man shop on VM with enough SQL Servers to test out any scenario I could dream up. If you’re around, check me out at 8:30am.

God Bless,


Managed Service Accounts (MSA) and SQL 2014 and Windows 2012R2 add $

Two days of fighting and I finally got MSA to work on my new 2012R2 DC.
Of course I had to recreate a new DC with my 180-trial license but its good practice for me to stay up to date.
After reading the supposedly SIMPLE method I kept getting an Access Denied error! Very frustrating as I read and reread all the blog posts on it!
As you can see this is a core box. So I thought something was wrong because its CORE. I attempted on my newly install gui SQL and same thing. So after days of struggling I finally figured it out!


New-ADServiceAccount svc_agt1 –DNSHostName svc_agt1.USC.local `
-PrincipalsAllowedToDelegateToAccount "Domain Admins" `
-PrincipalsAllowedToRetrieveManagedPassword "Domain Admins",SQL01$ -Enabled $true

Add-ADComputerServiceAccount -Identity SQL01 -ServiceAccount svc_agt1
Take a look at that -PrincipalsAllowedToRetrieveManagedPassword it has to have the SERVERNAME+$

I did this with Microsoft’s lab because I thought it may have something to do with my version of AD as I am running the latest one. But after I figured it out, I tested it both on Win2K and Win2KR2.

Whew, Now I can set my Core box and see if it runs with MSA! Woo Hoo! No more changing and Admin passwords.

And best part, NO WAY TO CLICK your way through to get MSA! Must use Powershell!

I hope I can present these learnings on SQL Saturday coming up in two days! Now to polish my powerpoint slides!

Here’s the link for the answer in case you’re hunting around too. Scroll all the way to the bottom for the answer.

Wish me luck!


Powershell SMO

When you search for SQL Server Management Objects (SMO) and Powershell not too many great results come up for what I really want.  I need examples that tell me how to do something, and the closest thing I can find is Microsoft Official MSDN entry which doesn’t give me what I’m truly looking for.  I like the official statement though:  objects designed for programmatic management of SQL Server.  The idea that the tasks of a DBA can not only be scripted but programmed is a wonderful concept.  Now how the heck do I get the examples?  Get-help?

I enjoy Wayne Sheffield’s “A Month of Powershell” which was a wonderful work that should really be published, but its almost been a year and certainly needs a refreshing update, especially when teaching SMO.  Its not hard, but explaining it does take some time.

Something that I will continue to build in all my sessions is the framework for learning:

  1. Crisis.  An event that catapults you to the height of fear or success
  2. Challenge.  This is about change, especially within you.
  3. Overload.  Too many choices that you burn into overwhelm
  4. Transformation.  What changes will I need to make to successfully overcome the crisis?

My crisis came when I suddenly faced a layoff, and although the company continued to give us warning signs I still believed we would survive.  One of my worst enemies is complacency.  I thought I knew enough.  Now I faced unemployment for the first time.  I remember feeling overwhelm on where to concentrate my time.  Do I learn a new programming language, become a mobile developer, learn linux or even worse come out of the tech industry that I love?

My transformation came slowly because it wasn’t a tech answer.  I didn’t learn Big Data, statistical language like R or Iphone development.  It was a soft skill that actually helped me gather true strength through speaking.  Toastmasters.

Many people join Toastmaster simply to develop their public speaking skills.  I other hand have another agenda.  Its to help me to spread my message, which is to empower people through Powershell.  Now that I’ve found my voice, its time to sing!

I hope you’ll join me as I present in Orange County, CA on April 26, 2014 @8:30.  Yes, that’s the early morning session!

I’ll post tomorrow about Powershell and SMO and give you a preview on my session.

Learn.  Grow.  Share


SQL PASS Abstract Review – Day 2

I just completed my second portion of my assigned abstracts.  Here’s some more gotchas I learned. 

  • If you’re doing a 300 level with 0% demos I think something is wrong.  There should be a nice mix of demos and presentation.
  • This is an easy one –CAPITALIZATION.  Camel Case in your titles!
  • Overuse of punctuation and questions??? Get my drift?!!?!
  • Language.  Avoid WTF and cr*p.  :–]
  • Opening, Body and Closing

Let me discuss that last one.  In your abstract, I should see an opening, body and conclusion.  Let’s start with the end in mind.


The conclusion should sound like this:  In this session, you will learn all the tools you need to become a great DBA.  Some abstracts had no conclusion or at least I couldn’t tell if somehow they cut and paste and missed those sections when submitting.  So many were like this:

“We’ll talk about x,y,z.  You will learn the difference of 1 and 2.”


“Whether you’re a novice or an expert, come and learn with me.”

Why?  Learn what?


The opening should be what catches my attention.  Why should I continue to read your abstract all the way through?  Pretend you’re going to tell a humorous story.  Would you really begin with questions, unless they’re obvious like “would you like to win a million dollars?”  If you can come up with clever titles that can open in your abstract that would be great, but don’t repeat your title.  Capture my attention!


Attempt to limit your sentences to a maximum of 5.  Make them third grade level.  Short and sweet.  Be kind to me as I’m reading a dozen or so abstracts and the clearer and easier to read makes me grade easier (and faster)

I ALWAYS start with 5s (the highest grade) and begin to take points down as I see these issues.

Probably the one thing I should have done before I submitted was to put it out there, well at least to the gurus and see what they think.  Of course it helps if you already presented at a club or SQL Saturday, which makes you practice for the annual event.

Now let’s take stock.  Maybe you knew all these pointers and submitted the best you could.  There’s always room for improvement.  If you didn’t rank well, then focus on what you can do for next time.  I can tell you that even superstar heroes that we all know and love gave terrible abstracts, and I graded accordingly.

I will suggest to the Program Committee that we don’t just send a rejection email and that’s it.  I would like to see the PC have a session on “How To Speak At SQL PASS Summit” and walk through some of the submissions that were accepted AND rejected.  This would be a consolation prize if you were rejected, and of course they’d have to NDA your material (or at least the abstract) and that part feels warm and fuzzy.

The ultimate take away though is NOT technical.  Let me state that one more time because maybe you didn’t get it.


It is about how you presented yourself through words and how you express yourself in the community (or at least on YouTube).  We all have technical knowledge, some more than others.  But how we effectively give our message to others is the key to speaking and hence broadcasting your message.

Continue to Learn, Grow and Share!

SQL PASS Abstract Review – Day 1

As I complete my set of abstracts some thoughts come to me.  The best one I reviewed was a play on words.  The title told the introduction and the description told the story.  I have to admit that my submission was not as good as this and I wish I put a little more effort making it that clever.  At this stage I won’t divulge the details, but if you look at past submissions you’ll probably see what I’m talking about.

There was one that I was disappointed with because I knew the speaker and heard this person before.  This person is very knowledgeable but the abstract didn’t reflect that.  It looked as if it were a rush after thought than a thoughtful submission.  Maybe they’re too busy to put something together, but then why submit at all?  The reason why you speak is to get your message out there.  If you’re too busy to put an abstract together, then let it go.  Better to concentrate on a few engagements and make them exceptional than too many engagements and having them duds because you’re spread too thin.

Which brings me to my next point:  Questions?  In speaker submissions there’s no sense in putting a question unless you can do it artfully.  By that I mean a rhetorical question that gets the audience and reviewer’s attention with a yes and no.  Then MOVE ON.  There’s no reason to continue to have continual line of questions when you don’t have the reaction of the audience or reader.  Unfortunately, there were several submissions I downgraded when I saw something like:  Do you act like a dead dog?  Do you think you can find out ways to improve yourself?  Can you ever like cats?  Why or why not?  This belongs in a conversation, not in a submission.

There is a science to speaker submissions.  If you follow the science you’ll get a good score and the opportunity of speaking.  If you want to venture on your own, remember, I grade in 30 seconds and unless you have a super copywriting skills like Scott Fitzgerald, play by the rules.

God bless!


Lessons Learned from your own Lab

I only had 89 more days until my “evaluation” days are ended on both my DC and my SQL Lab servers.  I decided to see if I can use the new Volume License that was downloaded and upgrade my DCs.  Unfortunately, you can’t upgrade a DC, so I opted to install another DC and then just shut that one down.  However, my evaluation was Windows 2012R2.  I didn’t realize that the VL I had was Windows 2012.  I ran into “functional error”, which of course flashes up on the GUI and then closes and reboots automatically.

gui functional errorThis is one of the reasons I hate the GUI.  This screen flashes for a brief second, doesn’t give you time to read the message and then reboots.  Argh!  Luckily, I am able to save the powershell script and run it in powershell, but that does the same thing.  However, I think I’m better off because I actually can capture the whole error message and at least I don’t have to step through the mind-numbing next–>next–> screens.  The screen shot shows that there is s functional problem.  Of course, I’m blind to the fact that its Windows 2012R2 not Windows 2012.  Then researching on the web I find

functional error
my main problem.  I’m attempting to join using an older version…Of course, I just clicked next–>next–>next as any good lab admin does and took the defaults not realizing what would be the consequences.  Come on, I’m just a SQL DBA and didn’t think I’d be doing any DC stuff.  Here’s a quick chart of what I had.  Noticed that I cannot join the DC because its a Windows 2012 DC.

functional levels

You can see I somehow need to downgrade the main DC so that I can join the new one to the domain and then eventually drop the DC.  How?  And my first question is how do I know what domain and forest level I have?  I have Windows 2012R2 Core, so even worse, I don’t have the pleasure of having the GUI, as I hate the GUI on a DC that just authenticates my lab.  I always set my RAM down to 368MB.  Yea, you heard it and it runs like a champ.  I end up using powershell and these commands.  (Get-ADDomain).domainMode and (Get-ADForest).forestmode, which results in Windows 2012R2.  Duh!

Now I just need to change the domain and functional levels.  I end up using the command set-ADForestMode and I’m able to downgrade the Forest and Domain Levels.  Here is my command

Set-ADForestMode -identity “USC.local” -ForestMode “Windows2012Forest”
Set-ADDomainMode -identity “USC.local” -DomainMode “Windows2012Forest”

forest-change-functional-levelNow I am able to run the powershell command to install the DC and after an all day of wondering what happened I figured out, duh I’m attempting to use Windows 2012, not R2…sigh

Maybe next time I use another evaluation and just keep “re-evaluating” for another 180-days, just for the learning fun.  But I’d rather be doing SQL than Windows any day!

Build Your Powershell Empire One Brick At A Time

sleepydogI just submitted speaker session to SQL Pass and honestly I did work on it on and off, much better than I did last year, but I still think I should have submitted more as I don’t have much of a shot since I didn’t put as much video work as I would have liked.  I still have to prepare the one I have for SQL Sat in Orange County and I can see why speakers redo their sessions multiple times before they get to PASS.  It’s hard work to prepare.

Although being on the Program Committee has several advantages, one of them being how the inner workings of PASS works.  I’m honored that I will be on the committee again and I can use my experience to eventually become a speaker at PASS, which will top my career at that point.

Wish me luck.  In the following days when the speaker submission closes, I’ll post up more on this session giving the Program Committee something to look at than just my abstract.  Hopefully that will make a difference in their decision.

God Bless,


Another call to be a speaker

2 and 3 times a charm right?  Placed a submission for SQL Saturday in Orange County and I’m going to polish up the abstract and put one in for SQL Summit in November as well.  I should have been working on that all along but you know, LIFE, gets in the way.  Wish me luck.  I’ll post more details on the actual submission notes here.

For sure, Powershell for the Enterprise is going to be one I’m going to put on the list.  I feel that there’s not enough knowledge on this.

Also, it should be the monitoring Phase 3 that Brent Ozar kinda started, but was never completed quite well.  I think with my polish as I have done and is still in production until today should be another topic I hadn’t thought of submitting.

Lastly I’m on the 2014 Program Committee again.  I feel honored somehow because I was chosen since I know some who did not.  I’m not on the Powerpoint Review team which should be interesting…does that mean we just look for typos! hahaha

I’ll post more later!  thanks for reading