The second challenge is live! Mike won the first one, and Stacy, Alex and Chris have weighed in on what they did wrong. Let’s see if they learned from their mistakes this week:

You know, this thing might just take off. KOMO 4 news interviewed us on this challenge (, and the chatter is building!

I’ve received lots of entries for the “Play@Home” contest, and I’ll announce today’s winner….

Challenge Background

This time I was joined by boB (he spells his name backwards) “The tool man” Taylor ( . He’s a Program Lead for the Microsoft Certified Master certification – one of our highest certification programs. He passed 28 certification exams on the first try. Oh, and did you know he’s a magician? Yup. He did some magic for me on the set:

As you can see in today’s challenge video, we took the contestants to a remote location to work with their laptops on a security challenge. Remember that the contestants have a default installation of SQL Server 2012 with all features, running on a Windows 7 operating system. Their “mini challenge” was to restore the  AdventureWorks 2005 database on that system. This week’s challenge is deceptively simple:

"Three people new people have been hired. They need to have various levels of access to the AdventureWorks database. We are using SQL Server Authentication for the server, that's what they need logins for. Only the Adventureworks database is affected. They must have no more permissions than the following:

  • JaneManager - Find all sales and salespersons by store. Be able to change a "special offer" percentage.
  • DaveSalesperson - Show first and last name of customer, along with their territory. Change a customer's address.
  • JamilAssembler - Find in-work orders by due date. Be able to change a workorder duedate.

 All of them will use the following password: P@ssword1

Do not make them change it on first use."

 Easy, no? Ah, but in that simple set of requirements, many things must be done to complete the challenge.

The settings for SQL Server must be changed to allow Mixed Authentication Mode – the default installation of SQL Server sets the security authentication to Windows Logins only. Server Logins and Database Users need to be created, with the proper password. Settings need to ensure that the user doesn’t have to change their password on first login. And, because SQL Server allows you to enforce Windows policies, you might want to remove that setting or the password policies from Window might conflict with the ones you want from the requirements.

But that’s actually pretty straightforward – anyone with experience with Windows and SQL Server will be able to do this part of the challenge.

Now comes a few more difficult requirements. Jane needs to “find” all of the salespersons, by store. And she must be able to “change” special offer percentages. So it goes with Dave and Jamil – only certain permissions are required on certain objects. And therein lies the real challenge…

What does “find” mean? That’s the way our managers talk to us – they don’t use nice, normal, T-SQL language like regular people – they don’t say SELECT or UPDATE and DELETE but not INSERT, they say “find” and “change”. So the first part of the difficulty is to translate manager-eze into T-SQL. The contestants have to be familiar enough with the GRANT and DENY statements to know what the user needs to have. You need to clarify what rights the users really need, and that means ensuring you know not only technology, but business.

Oh, and don’t forget the effective permissions. Unless it’s a DENY statement, a user has all the rights from the groups they belong to (including public) combined – now, we judges wouldn’t have changed any of that, would we? Better check. Youch, that checking takes time….tick…tick…tick….

And that’s still not the hard part.

The hardest part – and most of what we’re testing for here - is knowing where all the tables are that allow the users to do what they need to do. That’s the actual security challenge – to find the objects themselves. And it’s non-trivial. Without knowing the link I’m about to show you, would you know all the tables, views and stored procedures the users need access to? Would you create a view, a stored procedure, or go straight for the base-tables? Wandering through a schema is hard when you have hundreds of tables and only a few minutes to solve a puzzle….

...unless you remember how specific I was about the database version. Unless you remember I said you could look things up. Unless you remember that AdventureWorks 2005 has a LOT of documentation, including…..the schema, with all of the business cases. Which contain all the tables.  A quick search on “AdventureWorks” and “Business Scenarios” would quickly show those objects. (Check it out: )

If you looked there, you would find all of the tables you needed. You could focus on least-privilege, you could focus on writing code, or using the base-table approach, whatever you wanted to do.

Challenge Recap

So did the contestants do that, or did they focus on users first, and objects last? Well….

boB ran a script he wrote to test the logins, settings and security in the requirements. He made sure each user could do what they needed to, and no more. Everyone got the security settings right – well, mostly. Some missed the Windows policies. Everyone created the users. And everyone pretty much figured out the translation from “find” to “SELECT” and so on.

But everyone missed a thorough schema exploration. If you try to do this manually, it’s almost impossible to locate all of the tables you need. There were actually only a few, but you wouldn’t know that unless you had some sort of documentation to tell you what the system uses for that information, hence the use of AdventureWorks 2005 and the great documentation it has on the schema and business uses. It’s a lesson we all have to learn about finding out what the “real” challenge is in a situation, and go after the big things first. The schema was the key – not really the users or the GRANT statements.

In the end, none of the contestants got all of the objects covered. It’s actually impressive they got any of them covered without reading the business documentation (one contestant found the link, but missed the tables needed). But the winner got really close – and did a few other things correctly. Things like catching those Windows settings, setting the users to have a default database of AdventureWorks, and paying close attention to each part of the security. In fact, the winner only missed a couple of tables needed to successfully negotiate the challenge.

Your Challenge and Last Week’s Winners

So, now it’s time for you to play along at home. We had some great submissions from the last challenge – and I’m happy to announce the three winners: Joseph Hagan, Ryan Roper and Garrett Stevens! We’ll be contacting you shortly with your prizes, and all of you have been entered for the grand prize of a brand-new laptop! The current winners – and all of you who submitted – along with everyone else can enter this week’s challenge as well. Remember, there’s a prize each week, and then we’ll take the best of the winners and decide who wins the laptop.  

Design a challenge like this one on security. What we’re looking for this week is how well the challenge tests real-world applications of security – tracking down a break-in, using auditing, migrating users – whatever you think fits a data professional’s day in dealing with security. We’ll judge your entry on the following:

  • How well you describe the challenge
  • How difficult (but not impossible – hey, they only get 100 minutes for this) the challenge is
  • How completely the challenge tests the contestant on security
  • How well the challenge makes the contestant think

One or two sentences isn’t going to cut it. This week’s contestants sent amazing, well thought-out documents with complete challenges. If you want that laptop, you’ve got to put a little time in.

See you next week!