Use SYDI & osql to patch sp_replwritetovarbin Remote Code Execution Vulnerability

I said I was taking rest of the year off, but this one’s too important to let go…you’ve seen the news about the latest SQL Server vulnerability right? You know, the one that affects almost every version of SQL 2000 and 2005? I’ve previously mentioned that I’m a fan of SYDI, and here’s another great way to use it - a query you can run against your SYDI repository that will generate the osql statements you can execute in a command window to apply the workaround for the vulnerability:

-- Generate osql statements for all servers that have the SQL 2000 or 2005 service
-- Exclude SQL 2005 SP3 and SQL 2008 (unaffected by vulnerability)
-- "-E" argument indicates a trusted connection
-- "-Q" argument exits osql after executing the query
SELECT  'osql -S ' + Computers.Computer.value('(system/@name)[1]',
                                              'varchar(255)')
        + ' -E -d master -Q "deny execute on sp_replwritetovarbin to public"'
FROM    SYDIServer WITH ( NOLOCK )
        CROSS APPLY Server_XML.nodes('computer') AS Computers ( Computer )
WHERE   ( Computers.Computer.exist('(services/service[@name="SQL Server (MSSQLSERVER)"])') = 1
          OR Computers.Computer.exist('(services/service[@name="MSSQLSERVER"])') = 1
        )
        AND Computers.Computer.exist('(installedapplications/msiapplication[
          contains(@productname, "Microsoft SQL Server 2005")
          and contains(@version, "9.3.4035")
        ])') = 0
        AND Computers.Computer.exist('(installedapplications/regapplication[
          contains(@productname, "Microsoft SQL Server 2008")
        ])') = 0
ORDER BY Computers.Computer.value('(system/@name)[1]', 'varchar(255)')

If you haven’t patched your servers yet, I strongly advise you to do so. I remember getting hit by SQL Slammer back in the day and it’s an experience I don’t intend to repeat.

Taking The Rest Of The Year Off

If you haven’t noticed, I’ve been pretty quiet the last few weeks. I’m taking time off to enjoy the holidays with family and detach for a while. I hope that you’re enjoying your holiday as well, however you celebrate. I’ve got lots of great stuff for 2009, I promise! So until next year…

SQL Quiz Part 2

Chris Shaw posed another question in his SQL Quiz and I was tagged by Jonathan Kehayias this time, so here goes…

The question:
What are the largest challenges that you have faced in your career and how did you overcome those?

Answer #1: Being let go
One of the ways we define ourselves is by how well we do our jobs; we take pride in what we do (well, most of us do anyways) and nothing makes you question your skills, worth, and value like being let go. Call it the professional version of being dumped by a significant other. I was let go once and it completely blindsided me. Not only did my supervisor tell me I had nothing to worry about but I thought I was pretty well respected as the go-to type of guy. My coworkers even told me I was irreplaceable - “they’d be stupid to let you go!”, they said, and I bought it. Wow was that a big mistake. To this day I remember the surreal feeling of sitting in my supervisor’s office and hearing him explain in what seemed like slow motion what was going down. To top it off, it was 5 days before Christmas and I had family visiting in town.

How I got over it:
I went through the typical range of emotions that come with a life altering event like this. But once I got over my anger\resentment I realized that for once I was going to be able to enjoy Christmas. No beeper. No 3 AM calls from the NOC. No piles of work waiting for me when I returned after the holidays. I was fortunate enough to receive a decent severance which allowed me to take a couple of weeks off and just enjoy life, and it was wonderful! The timing ended up working in my favor, too, since most companies start looking to fill positions Jan 1 when budgets kick in and money is available. While I looked for a new job I was able to reconnect with colleagues I had lost touch with, travel the state to visit siblings I hadn’t seen in a while, and spend more time with my wife and kids than I ever had the chance to do while I was working. Looking back, it was probably one of the best two months of my life! Now I’m a firm believer that things happen for a reason, and since my departure my former employer has changed (putting it politely). My supervisor and his supervisor were both let go not too long afterwards and the company “partnered” with a larger company that turned around and outsourced most of the business. The stories I hear from my former coworkers me make me realize that I would probably be so dissatisfied with the environment now that if I hadn’t been let go I would have left on my own. Although it sure as heck didn’t seem like it at the time, I realize now that being let go was a blessing in disguise and I am in a much better place today because of it.

What I learned:

• No one is irreplaceable. The minute you think you are something will happen to change that.
• Things happen for a reason. You may not understand it first, but give it time and you will.
• Sometimes you just have to play the cards you’ve been dealt, even if it feels like a raw deal. It’s how you play them that makes a difference in the outcome.

Answer #2: Finding the right balance between work and life
It’s easy to become so consumed by your career that it can happen without you even realizing it. I was so balls to the wall at my first job out of college that I would work at any time of the day or night I could to get my job done. I was recently married, didn’t have kids, and my wife knew the value of doing well so she encouraged\supported me. Fast forward to about three years ago when I had two kids and a career on the rise. I was was pushing 80 hour work weeks which was great for my company but not for life at home. Somewhere between hearing my kids say (on more than one occasion) “daddy works all the time” and telling my wife “things will get better, I just need to get through this project” for the umpteenth time is when it hit me that I was spending more quality time with my computer than with my family. Shortly after that, the events in my first answer played out.

How I got over it:
I’ve come to realize that I don’t have to be a superman to do well, nor do I feel like I have to do it all anymore. When scheduling things that need to happen outside of normal hours I make it a point to find a time that doesn’t conflict with my responsibilities at home. Instead of working until 8 PM I’ll leave the office at a normal time, take a break for a few hours and spend time with my family, then go back to it once my kids are asleep. Or, as appalling as this might sound, I simply leave it for the next day. Working all the time wore me down, both mentally and physically; On the other hand taking a break gives my brain a break, which in turn makes me more productive and more friendly. I get that emergencies happen and there will be times when I have to drop everything and work for a few hours but now they’re true emergencies and not the artificial ones I was creating for myself.

What I learned:

• Going all out to do a good job is admirable, but not when it comes at the cost of those around you. A job should be a means to an end, not an end unto itself.
• Take time to take a break from work. Find a hobby or spend more time with your family. It will make you a happier and more productive person.

OK, time to tag two more people. Andy Warren and Jack Corbett, you’re it!

Hey Mr. DBA, What Permissions Do I Have On This Database?

I get this question at work from time to time. Fortunately SQL 2005\2008 has a built in table valued function to help answer this. It’s called fn_my_permissions and it returns the effective permissions granted to a securable. The syntax is:

fn_my_permissions(securable, 'securable_class')

In simple speak, securable means the table, procedure, function, etc. that you want to know what permissions you have on. Securable_class is the name of the class that corresponds to the securable. Examples of securables are: tables, views, functions, and procedures. Examples of securable classes are:  ASSEMBLY, DATABASE, OBJECT, and SERVER. Note that if you use the value “default” (minus the quotes) for either securable or securable_class SQL Server simply interprets it as NULL.

Where this comes in really useful is when you need to know the effective permissions of someone else besides you. To do this you can use EXECUTE AS to impersonate a login, then call fn_my_permissions to see what that user has. When you’re done, use REVERT to change the login context back to yourself. Note to do this requires IMPERSONATE permissions on the login\user that you’re trying to impersonate; for logins this is implicit if you’re a member of the sysadmin fixed server role and for database users this is implicit if you’ve got db_owner rights.

Examples

• List your effective permissions on the server

  SELECT * FROM fn_my_permissions(NULL, 'SERVER');

• List your effective permissions on the database

  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

• List your effective permissions on a procedure

  SELECT * FROM fn_my_permissions('sp_MSforeachtable', 'OBJECT'));

• List the effective permissions on the database for a server login

  EXECUTE AS LOGIN = 'MYDOMAIN\some.user';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;


• List the effective permissions on the database for a database user

  EXECUTE AS USER = 'MYDOMAIN\some.user';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;


• List all securable classes

  SELECT DISTINCT class_desc FROM fn_builtin_permissions(default) ORDER BY class_desc;

A New Look

Just a quick note to point out that I changed the look and feel of this blog. I was a blogging n00b when I started this back in May and wasn’t sure how it should really work; I just knew I wanted to blog and I wanted it to look cool so I spent a few hours surfing around for templates until I found one I liked. After 8 months and 40 posts I realized that an update was in order. For the new look I stated with the “Denim” theme provided by Blogger and dinked around with the CSS to make the content area fluid while keeping the sidebar a fixed width. I’ve also opened up anonymous comments, backlinks, and a few graphics here and there for eye candy. I’m not at the point where I ultimately want to be with how things look, and I’ll work some incremental changes in here and there but the main change is done. I think the design is simple, clean, and provides plenty of room for the most important thing - content.

Coincidentally, as I was working on the refresh Brent Ozar published a series on how to start a technical blog which provided some good advice. If you currently write a blog or are thinking about writing one I highly recommend you read his series here: Part 1, Part 2, Part 3, Part 4, and Part 5.

Finally, feel free to drop me a comment and let me know what you think or what else you would change.

Hey I Said NOT To Replicate Delete Statements!

You've been given the task to set up a new publication and one of the requirements is that delete statements should not be replicated. No problem, you know just what to do - set the delete delivery format for your articles in the publication properties to "Do not replicate DELETE statements", like so:

You create your publication, set up your subscribers, and go about your day...only to have Joe Salesguy come into your office the next day in a panicked frenzy yelling "The data's gone, the data's gone! What did you do with the data?!". Not convinced he knows what he's talking about, you pull up Management Studio, run a few queries, and much to your chagrin you discover that sure enough, the data's gone. Someone issued delete statements on your publisher that replicated out to your subscribers, even though you know that you set the publication up properly. So what gives??

It turns out that there's a nasty "feature" in the article properties window which affects all three delivery formats. When the option to set the properties of all table articles is selected the underlying script that is generated is missing the parameters @ins_cmd, @upd_cmd, or @del_cmd from sp_addarticle for whichever commands the “Do not replicate ins/upd/del statements” was selected for. When the parameters are missing  SQL tries to fill in the gap and what you end up with is statements that get replicated even though you didn’t want them to.

There are three ways to get around this problem:

1. When setting up your publication change the properties of the article(s) that you do not want to replicate commands for individually, rather than setting the properties of all articles at once.
2. When setting up your publication and setting the properties of all articles at once, instead of letting the UI create the publication specify that you want to script the commands to a file. Open the file, and add the parameters @ins_cmd, @upd_cmd, and\or @del_cmd with a value of "NONE" to each sp_addarticle call for the corresponding statements that you do not want delivered.
3. If you publication already exists, call sp_changearticle for each article in your publication and supply the necessary parameters to change the delivery format for inserts, updates, and deletes. This option is a bit more intrusive though because it will force you to reinitialize your subscriptions.

Fortunately this behavior has been corrected in SSMS 2008.