Shortly after I gave my presentation Trigger Happy Database Security down in Jacksonville for SQL Saturday #3, I received an email asking how to recover from a bad logon trigger that was blocking access to a SQL Server. The answer is to use the DAC, as I describe here:

 Connecting to SQL Server with a Bad Logon Trigger

I got the email with a Zoomerang survey to vote for the PASS board. All are excellent candidates, but ultimately, I could only vote for 3. My choices look like I'm stealing from Steve Jones, but I'm not. I voted for 3, but the 3rd choice were really hard. Two were not. Here are those easy ones:

 Andy Warren - I know Andy. Every time we see each other I spend a lot of time picking his brain, getting his advice, and generally absorbing his knowledge, not just about SQL Server and being an entrepreneur, but also about life. I know where his heart is with respect to PASS and the community. His efforts towards SQL Saturday and spreading that and assisting others in running it at their own locations is proof of how much he cares. That makes this choice easy.

Thomas LaRock - Here's a guy I've gotten to know a bit because of Twitter. I agree with Steve that SQLBatman, as he's known there, brings the social networking and "Web 2.0" side into the mix. However, I think Tom will bring passion and a "can do" attitude to the job. TJay Belt blogged about the candidates and one of the things he mentioned was how Tom got his SIG approved last year. As a chapter president it's been hard getting a lot of support from HQ. I'm hoping Tom and others he engages can change that.

These are my opinions, so take them for what they are worth. But whether you agree with me or not, vote for who you want to see on the PASS board. It does make a difference.

Speaker: Paul Shearer

Midlands PASS Chapter - December 4, 2008 Meeting

The Midlands PASS chapter will hold our normally scheduled meeting on Thursday, December 4, 2008. Paul Shearer will be giving a presentation entitled "Virtualization Everything" in which he'll cover virtualizing SQL Server for large scale apps using Hyper-V technology. This is definitely one you'll want to attend and invite your system administrators to as well!

The meeting will once again be held at Training Concepts off of Berryhill Road. We will begin our meet and greet time at 6:15 PM as usual and start the at 6:45 PM. Please feel free to forward this to anyone who you think would be interested in attending. If you haven’t already done so and plan on attending, please RSVP (kbriankelley {at} acm {dot} org) as soon as possible so we can ensure we have enough space and food. If you have time to help with setup, please email me and we’ll plug you in!

I'll send out an agenda, contact phone numbers, and the building code the week of the meeting.

One of the first things I tried to do when logon triggers came out with Service Pack 2 of SQL Server 2005 was audit for members of the sysadmin fixed server role logging on to the server. Here's a simple means to build a logon trigger to do just that: 

MSSQLTips.com: SysAdmin Login Auditing

Illiad has been running a series on his UserFriendly comic strip about veterans. It started on Monday and has continued thus far through today.

UserFriendly - Monday, November 10, 2008

You can read each one in turn by clicking "Next Cartoon" on the pencil bar.

It's not about the current conflicts. It's about the sacrifices veterans have made and the sacrifices their families have made when those veterans paid the ultimate sacrifice. It's a reminder that the luxuries, privileges, and freedoms we may have in this society were not without great cost. His storyline captures the cost better than anything I can say. All I have to add is, "You are not forgotten. Lest we forget."

On a historic note, the gravestone for Wednesday refers to the 1st Canadian Parachute Battalion and gives a date of June 6, 1944, D-Day. This unit was attached to the British 6th Airborne Division and dropped prior to the amphibious landings on the beaches as the 6th had key objectives to remove batteries which could fire on landing troops and capture bridges in-land. The Canadian activities are detailed here. If you want to know how bad the fighting was, all you need to do is look at the casualty count from the order of battle (Went in: 543   Died: 84   Wounded: 162   Missing: 101).

I've been asked by a few folks whether or not I'm going to the PASS Summit. The answer is I'm not. I'd love to, and the one I attended I found to be of great value, but basically, it's a work-life balance thing. A couple of weeks ago I took the family down to Disney World, and it was the first time for the kids. My current position doesn't provide a paid trip to the Summit. I did have free entry because I am a chapter president, but travel and lodging wouldn't be covered. Also, since my current position doesn't justify a trip to the Summit, that would mean taking vacation time. Vacation time I spent going down to Orlando. :)

I've been approached by co-workers and members of the local community about the value of the Summit. Some employers feel that since it's a conference, it's a waste of money. My reply has always been that it depends on the person. If a person intends to go to a conference just to spend a week off work, the company isn't going to get value. But if a person approaches a conference for the following reasons, likely the value is far more than the cost:

• Networking opportunities with other technology professionals
• Production information and contact with vendors that beats a cold call.
• Specialized training in subjects you're not likely to find at a local training center

I know I'd like to go next year and I'll try to ensure the money and the time-off is set aside. However, an opportunity came up for Disney World that was at roughly the same time, and I couldn't pass on that. As much as I loved attending the one PASS Summit I went to, I don't think it holds a candle to seeing the joy and excitement my children experienced during our week in Orlando. Nothing like having Stitch mess with your hair or getting sneezed on by a huge dog (after you've been shrunk) or going to the Playmobil Fun Park down in West Palm Beach or seeing the fireworks extravaganza at Epcot. I still mixed some business in on the trip and that covered most of the cost for the vacation, but even with that, Orlando was an absolute blast for the whole family. As a matter of fact, we're trying to determine exactly when we're going next year and saving up for it now.

 Jason Massie called me out for a new SQL Quiz that was the diabolical idea of Chris Shaw.The gist is to post two mistakes you've made in your database career. I'm normally a very cautious person when it comes to making changes and I tend to be meticulous in these situations, so I don't have any catastrophic stories, but here are ones that I definitely felt the pain for.

Mistake #1:

This is from back when I was primarily a web developer. I was working on a complete re-write of an existing intranet portal and upgrading it from Access to SQL Server 7.0. Needless to say, I had done a ton of T-SQL coding. And periodically I was taking backups of my work because this kind of development was relatively new for the organization and we didn't have a source control platform yet. After a marathon session of coding I forgot to take a full backup. The next day I went in, something was up (I don't recall anymore), and I grabbed the latest backup and restored... not thinking about the fact that it was taken before all that code had been written. That code was gone. About 7-8 hours worth of data structures and stored procedures. *poof*

Did I mention I was a contractor at the time? And my mistake had cost that 7-8 hours of work. And it had been an extremely productive 7-8 hours of work. Now I could have just said, "Oh, well," and billed the company for the time it took to get me back on schedule, but it was my mistake in the first place. My mistake, my time. So for the next 3 days I worked 2-3 hours "off the clock" catching back up. The good news was that since I had written the code once and I had done it just recently, I was able to rewrite it all faster than the first time around. However, at the end of the day, I still lost that time.

Lessons learned:

• Always use source control. If source control isn't available, create a mechanism, even if it's as simple as scripts saved off to a file share. It's not great, but it's better than nothing at all.
• Even in development, take regular, scheduled backups if there is active work being done. Had I been scheduling backups, I wouldn't have had to remember to take one (which I forgot).
• Before applying a restore, consider the activity since the date/time of the backup you just pulled.

Mistake #2:

This one is more recent. We were doing a power down of a bunch of servers and I automated the shutdown of our "admin" SQL cluster, along with many of the other Windows systems. It went down smooth. And I didn't think anything of it until the VMware admin yells out over the cube walls, "Hey, what happened to Virtual Center?!?" And then it hit me: the Virtual Center database was on my nicely shut down cluster. Oops. That meant running down to the data center, restarting one of the nodes, verifying the DBs came online, calling the VMware admin on his cell and saying, "Restart the service." Service restarted, and they were back in and able to begin shutting down the host ESX servers. Now I need to point out that the older versions of ESX this isn't a concern as you can SSH in and execute the full command set. However, the newest versions restrict what you can do unless you're at the console. You can override these settings, but why would you? Virtual Center is the answer as it allows you to shutdown without having to get to a console. Also, it's faster than logging on to every ESX server and issuing the shutdown command. The power down was on a timetable because we were redoing power to those racks and there were folks waiting on us. Total cost of the delay from discovery of the problem to resolution was about 15 minutes. We made out shutdown window by the skin of our teeth. Nothing like having your boss and your boss' boss looking at you for almost causing the whole timeline (which was tight to begin with) to slip.

Lessons learned:

• Virtual Center doesn't play nice without its database (admittedly, most apps don't).
• If it's an "admin" SQL Server, that means it may very well need to be the last server down before the domain controllers.

I'll pass the torch on to:

• Andy Leonard
• Andy Warren

In addition to the reading list I'm still struggling to finish, I've always got other books partially started. Maybe that's why I have so much trouble completing the books on my reading list. Okay, it is the reason. It's one of those inefficient habits I've got to break. But I digress...

 One of the things I've endeavored to do as I've gotten older is take time to read the classics. I remember hating this in high school and college because I saw these books as work I couldn't get out of, rather than literary pieces of art to be appreciated. Funny how a few years and a desire to write changes your perspective. Speaking of writing, every great writer I've read about giving advice about how to write says that part of great writing is great reading. Or better stated, reading great writing. Hence my pursuit of classical writings, even if it's fiction, like Of Mice and Men.

Of Mice and Men is a rather short book and it's easy reading. But one of the things Steinbeck does well is he uses descriptive language to set each scene or location. For instance, here is a sentence from the very first paragraph: "The water is warm too, for it has slipped twinkling over the yellow sands in the sunlight before reaching the narrow pool." You get a picture in your mind's eye of the Salinas River, which this is a description of. Another thing Steinbeck does is he uses the language befitting his characters. As a result, you get a sense of each one based on how they talk. It makes for a clear identification of each character, even in a relatively short tale.

So this got me to thinking about my own writing. What about it is memorable and captures the imagination? Even in technical writing, this should be possible. Now the descriptive language may be over the top, but the way I write should leave a favorable impression on the reader. Part of that is about developing a writing style, but it's more than that. It's also about the way I choose words and constructs and examples. Do my examples accurately and clearly illustrate the situation I'm thinking of? Do the words I choose flesh out that example and bring a focus to the right aspect? Those of the kinds of things I need to think about as I write.

I've written my first tip for MSSQLTips.com:

How to setup and use a SQL Server alias

It's a pretty simple thing to do, but it can be extremely handy in a recovery situation. For our disaster recovery tests, we configure the aliases on the application servers such that their configuration settings don't have to be changed. As a result, what they think is "MySQLServer" may really be "MyDRSQLServer" but the OS handles the connection through the alias.

Speaker: Brian Kelley

Midlands PASS Chapter - November 6, 2008 Meeting

Sponsored by Red Gate Software

The Midlands PASS chapter will hold our normally scheduled meeting on Thursday, November 6, 2008. Brian Kelley will be presenting Red Gate's new product, SQL Response, and then continuing on with a introductory presentation on query tuning.

The meeting will once again be held at Training Concepts off of Berryhill Road. We will begin our meet and greet time at 6:15 PM as usual and start the at 6:45 PM. Please feel free to forward this to anyone who you think would be interested in attending. If you haven’t already done so and plan on attending, please RSVP (kbriankelley {at} acm {dot} org) as soon as possible so we can ensure we have enough space and food. If you have time to help with setup, please email me and we’ll plug you in!

When I came home from work tonight, there waiting on my was my copy of Professional Microsoft SQL Server 2008 Administration from Wrox (link goes to Amazon). I'm not listed as one of the primary authors (so I didn't get my face on the cover), but I did update and add to chapter 9, Securing the Database Engine. It was great working with SQL Server 2008 and getting to write about the new security features found in it. One of the other things I tried to do was bring some of my systems/security architecture experience and look at the server upon which SQL Server is installed. So you'll find coverage about what groups to be concerned about as well as what rights (found in the local security policy) that you'll need to be aware of and why they're important. I also spend time talking about the SQL Server related groups and the normal permissions on the SQL Server folders and where you have to think about adding to that (such as protecting your backups).

If you're looking for the book on how to administer SQL Server 2008, here's the book you want. There are some great authors (Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort, and Geoff Hiten)  who contributed some great material. I am rather humbled to get a chance to write a chapter alongside their work.

Auditing blank passwords in SQL Server 2005 and 2008 proves a bit more challenging than in SQL Server 2000. In SQL Server 2000 we could query syslogins and see what the password column contained. If it was NULL, then we knew that if that was a SQL Server login, we had a problem. In SQL Server 2005 and 2008, however, if we query sys.sql_logins, the password_hash column will always be populated, even if the password is blank. That means we'll need another means to audit the blank password. The trick is to attempt a password reset where we specify the old password to be blank. Here's a quick script that tests all of the SQL Server logins on a server.

DECLARE @SQL NVARCHAR(4000); 
DECLARE @login sysname; 

DECLARE cursLogins CURSOR FORWARD_ONLY 
FOR SELECT name FROM sys.sql_logins; 

OPEN cursLogins; 

FETCH NEXT FROM cursLogins INTO @login; 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  PRINT 'Testing [' + @login + ']...'; 
  SET @SQL = 'ALTER LOGIN [' + @login + '] WITH PASSWORD = '''' OLD_PASSWORD = '''';'; 
  EXEC(@SQL); 
  FETCH NEXT FROM cursLogins INTO @login; 
END 

CLOSE cursLogins; 
DEALLOCATE cursLogins;  

In our results pane, if we get an error, that's a good thing. It means that the old password wasn't blank. However, if we don't get an error, we have a problem. Here is an example:

With RegularJoe we receive an error saying we can't change the password because we don't have permission. This is because the OLD_PASSWORD option was specified. I'm running with sysadmin rights, so normally I would be able to do so. However, the OLD_PASSWORD option is the trick. By setting it to blank, we can verify that none of our SQL Server based logins are blank. If they are, we won't see an error, like with the login I created called BlankPassword. No error means the password reset was successful. And that means the old password was blank. That login will need correcting.