LulzSec: Why You Should Care

Updating Resume is NOT Lulz

Wow, it’s been a crazy last couple of months with LulzSec running around doing what they do. Oh, what’s that? You’ve never heard of them? Well for many outside of IT this is probably the case. For those of us who ARE in IT have more than likely heard of them as well as their high-profile hacking exploits over the last couple of months.

This weekend TechCrunch posted a pretty good discussion piece on how the media has handled LulzSec’s exploits. To summarize in my own words, the author states that the general media cowered in the coverage of what this group was doing by hacking and leaking info of high profile targets such as the CIA (website), AT&T (internal data leaked) and Arizona Department of Public Safety (internal documents and sensitive information leaked). Carr goes on to say that rather than report on the seriousness of the group’s crimes and activities media would rather cheerlead them due to fear of retaliation from the group itself.

So what does this have to do with SQL Server? Well, if anything, I hope this rash of high-visibility targets has raised your awareness about something that far too many people slack in: Security. When was the last time you did a true security audit of your database servers? Are your web applications authenticating with the sa account (read also: “God” rights)? Are they authenticating with Windows accounts that are backed by stringent and contained groups via Active Directory? If you’re not certain of any of those questions I highly suggest you take a look at Brian Kelley’s (Blog | Twitter) SQL University Security Week posts from this past semester and start to at least formulate some kind of plan.

Security shouldn’t be an afterthought, it should be a base. As data professionals we hold are tasked with protecting the most vital piece of any organization: its data. Do you want to answer to your supervisor, manager and Executives when someone walks away with sensitive information from YOUR databases? Do yourself a favor and if you’re not already discussing security in your offices, start it. How do you handle security in your organization? Afterthought? Hardcore? What’s security? Let me hear your thoughts in the comments.


Pragmatic Tools: DTSxChange (Or DTS Must Die!)

Ah DTS packages…they suck. Seriously, it’s 2011. It’s been over a decade and we still have SQL 2000 and DTS packages lingering around our shops…sitting there…taunting us…asking to be upgraded but HOW? There’s SO much work having to learn SSIS, figure out what the current DTS package even does, figure out what shiny new components in SSIS are equivalent to that legacy crap we created a decade ago and recreate it on the new platform! Well fret not, we at Pragmatic Works got you covered with a nifty tool called DTSxChange that will actually convert your DTS packages to SSIS for you!

DTSxChange is a powerful tool that converts your legacy DTS packages to SSIS (can convert to SQL Server 2005 or 2008 SSIS) for you with minimal effort. In addition to saving you a ton of work, the tool actually tells you how much time (and money) you’re saving using this tool over a manual effort via the DTSxChange Profiler feature. This tool allows you to scan all of your current packages and it generates a really nice report (read also: bosses/managers/CFOs love this)

DTS sucks
Ooh shiny!

So now that you know the scope of the work you’re looking at, let’s get to the nitty gritty and start converting! The conversion portion of the software uses an easy step-by-step navigation that lets you customize all sorts of options for your conversion efforts such as destinations, checkpointing, logging options, auditing framework options, connection manager consolidation, converting child packages automatically associated with a package, etc. The nice thing about this is you can convert single packages or hundreds of packages at the same time! If you want to see some of this stuff in action you can watch the video here.

We converted the stuff and now we’re done right? NO WAY! With DTSxChange you also have the option of adding on our custom Auditing Framework which gives you tons of useful reports (Note: Use Report Viewer Application to view auditing data) so that you can easily and quickly view the state of your SSIS packages once their deployed. Here’s a listing of all of the different reports you get:

  • Which packages are currently running and which task is running inside the package
  • Historical package execution detail for selected date range (i.e. Run time, Errors, Warnings etc.)
  • Error and Warning by Task and Package
  • Run time by Task and Package
  • Extracted and Loaded Records along with their source and target information (e.g. Table/View, Sql Query, File Name, Component Name, Data Flow Name, Connection String etc.).
  • Run time Trend for several days/weeks/years by Package and Task
  • Error/Warning/ Trend by Package and Task
  • Extract/Load Trend by Package, DataFlow
  • Extract/Load Trend by Data Object (e.g. File, Table/View or Sql Query)

There are seven inbuilt reports are shipped this version and more will be added in the future release.

Report Name Description
SSIS Execution Dashboard This dashboard gives one place view of most common counters of SSIS package execution.
Package Execution Trend This report provides many useful information about trend of package runtime, task runtime and errors/warnings.
Errors/Warnings Provides detailed error/warning report including package level errors (package level errors not listed in any other report)
Recent Executions Provides information of running packages/tasks and already completed executions.
Running Packages Provides real time information of currently running packages and tasks.
Extract/Load Detail Provides extract/load detail (e.g. row count, source/target information, query, file name …) about each dataflow in each package execution.
Extract/Load Trend Provides graphical view of extract and load trend over several years, weeks, months and days.

DTSxChange helps you migrate away from those legacy DTS packages and gets you on to the latest SSIS platform in minutes as opposed to days or weeks! Don’t believe me, try it out yourself. When you download a trial version of our product it comes with 3 conversions for you to play with. Would you rather have one of the experts at Pragmatic Works do a live demo for you and your company? We can set that up as well for you by sending us a quick email. So what are you waiting for? Kiss DTS goodbye today!


Pray For Me…I’m Writing the Bible!

Prayer Cat Prays

Well not THE Bible, but rather the SQL Server Bible! I’m incredibly excited and proud to say I’ll be one of the primary authors on the 2011 edition (aka “Denali”). Who are the rest of the folks involved on this venture? Check out this great list of authors:

Primary Authors

Contributing Authors

Lots of exciting new stuff coming down the pipeline with this release of SQL Server so I think I can safely speak for all of us when we say we’re extremely excited to be putting this title together!


Pragmatic Tools Week: Task Factory

If you’ve ever spent any time doing SSIS development work, you’ll eventually come to a point when developing something and you think to yourself “there has GOT to be an easier way of doing this”. We at Pragmatic Works feel the same way so our team of developer monkey ninjas put together a nifty toolset called Task Factory to help ease some of those pains!

Continue reading Pragmatic Tools Week: Task Factory


SQLRally Overdrive: Birds of a Feather Discussions Event

Just wanted to post a quick note/invitation to everyone to come join us on Thursday, May 12th from 5:45-7:00 for the SQLRally Overdrive: Birds of a Feather Discussions event.

We’ll have several tables, each with a dedicated expert in the field hosting conversations. Want to delve into some shop talk about PowerShell? Curious about how Analysis Services are being setup by other folks in the field? Care to swap architecture design ideas about SSIS with others? Well this is the place to do this and more! Here’s the list of folks who have graciously volunteered to host tables at this event:

Mike Davis (Blog | Twitter) – SSRS

Grant Fritchey (Blog | Twitter) – Indexing/Performance Tuning

Adam Jorgensen (Blog | Twitter)- SSAS

Devin Knight (Blog | Twitter) – SSIS

Andie Letourneau (Blog | Twitter) – Women in Technology

Brian Moran (Blog | Twitter)- Professional Development

Jorge Segarra (Blog | Twitter) – Administration

Ed Wilson (Blog | Twitter) – PowerShell


Pragmatic Tools Week: BIxPress

tim-taylor-aus-tool-timeMuch like Tim “The Toolman” Taylor had his themed weeks, we’re going to do something similar. This week we salute: (play fanfare music here) the Pragmatic Works BI toolset. Today we’re going to talk about BIxPress.

I’ve previously posted on BIxPress and how it helps DBAs out, but the heart of this product is really aimed at making your development quick and easy. Today we’re going to focus on a few things that really make this tool worthwhile by looking at the top 3 features of this product that I really love.

Continue reading Pragmatic Tools Week: BIxPress


SQLRally OverDrive Events!

So you’re going to SQLRally in Orlando next week? Want to take advantage of ALL of the awesome opportunities presented? In addition to all of the amazing training content you’ll see at the event itself, we’re holding some great stuff after-hours on Thursday. We’re calling this SQLRally OverDrive!

These are three different panel events being hosted by members of the SQL community. You have your choice of three cool events to choose from:

Birds of a Feather Discussions

Sit down with a group of people who are interested in the same SQL Server and BI topics as you are. Interested in SSIS? Gather with your peers to discuss the problems you’ve encountered and solved. Each table will be moderated by an expert in that area.

Conducting an Interview, Sponsored by OPASS

Conducting an interview can be intimidating and confusing. What makes a good interview? How do you differentiate candidates? This session will consist of 3 short interviews. After the interviews are complete, attendees will discuss how the interviews were conducted. Did the interviewers ask the right questions? Did the interviewers conduct themselves well? Which candidate would you hire and why? Quest SQL Server expert Kevin Kline will moderate the discussion.

Speed Networking, Sponsored by MagicPASS

Do you have trouble meeting people? Need help starting a conversation? Based on the speed-dating model, you’ll spend a few minutes with several different people with a set of pre-defined questions to ask and answer. Take the time to exchange business cards as well. When moderator Kendal Van Dyke says, “Go!” be ready to make some new contacts.

I’ll be hosting a table at the Birds of a Feather discussions so make sure to swing by and say ‘hi’ and we can talk shop. Just look for the tall guy with the rubber chicken on the table!

SQL University: Precedence Constraints

Welcome back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the two in order to complete a unit of work. In today’s class we’re going to continue exploring that data load package and take a look at another critical piece of SSIS that you should know about: precedence constraints.

So what exactly are precedence constraints? They are the connectors that link together tasks in the Control Flow, and they define the workflow of your package. When two tasks are tied together with a constraint, the destination task will execute based on two things: The final state of the task that precedes it and special rules you can define on the constraint using specialized expressions.

Constraint Types

You can have different types of constraints between tasks. You can read more about constraints in detail from MSDN article (Link). I’ll briefly cover each of the constraint types in an abbreviated list and then we’ll discuss how we used these constraints in our parent-child package from our previous lesson.

  • Success – Workflow will proceed when the preceding container executes successfully. Visually indicated in control flow by a solid green line.
  • Failure – Workflow will proceed when the preceding container’s execution results in a failure. Visually indicated in control flow by a solid red line.
  • Completion – Workflow will proceed when the preceding container’s execution completes, regardless of success or failure. Visually indicated in control flow by a solid blue line.
  • Expression/Constraint with Logical AND – Workflow will proceed when specified expression and constraints evaluate to true. Visually indicated in control flow by a solid color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).
  • Expression/Constraint with Logical OR – Workflow will proceed when either the specified expression or the logical constraint (success/failure/completion) evaluates to true. Visually indicated in control flow by a dotted color line along with a small ‘fx’ icon next to it. Color of line depends on logical constraint chosen (e.g. success=green, completion=blue).


Note: In these screenshots there are labels next to the precedence constraints indicating the type of constraint chosen. This is not a default behavior. To enable this click on Tools menu, go to Options. Under Business Intelligence Designers, go to Integration Services Designers and under the Accessibility section in the General menu, check the box for ‘Show precedence constraint labels’. This is helpful for folks who are color blind and are not able to differentiate between green/red/blue lines in designer. Big thanks to Dan English for this great tip.


Constraints in Action

Now that we’ve seen the different constraint types, let’s examine how they’re used in conjunction with parent-child package setup. Our first use of constraints comes at the top of the child package from the script task to the sequence containers. We’ve used an empty script task as an “anchor” task. This is used as a starting point to continue on to the corresponding workflow. As we learned in our last class, we have a variable being passed from our parent package with the value of the name of the file we are currently processing.

In this first example, we’re going to look at the constraint leading to the sequence containers for the Supplier table. We’ve used and Expression and Constraint here and chosen the value for Success. We’re also looking at the value of the variable being passed to the child package. For this particular workflow, we are waiting until the value of the variable ‘Parent_TblName’ is set to “supplier”. Once both of these situations evaluate as being true, we will execute this container.


Now that we’re inside our sequence container, we have another set of constraints. Once again we’re using an empty script task as an “anchor” for our precedence constraints. This time we’ve got two possible paths to go down. The first is to execute an Execute SQL task. This task checks for the existence of the table (in this case, the supplier table). If it exists it will drop the table and then recreate it. The other path leads directly to a data flow task which simply loads the table specified from the flat file.

I’ve created another variable on this package called ‘AppendFlag’ which is a boolean value. The purpose of this flag is so that you can choose to load the tables with a fresh load from the flat file (the Execute SQL task path) or you can simply append an already existing table’s data with data (data flow path). The default value of the variable is false.

The first path to the Execute SQL task uses an expression and constraint which is looking at the value of the ‘AppendFlag’ variable. In order for us to go down this workflow both value of ‘AppendFlag’ must be false AND the previous component executed successfully. The other path from the script task leads directly to the data flow task which actually loads the table. For this path, I’ve set the precedence constraint to look for the value of ‘AppendFlag’ to be true. In this path, however, we’ve chosen to use a logical OR. The reason for this being that the Execute SQL task, once complete, also leads to the data flow task. Due to the data flow having two different input paths, we must use the logical OR (if you try to choose logical AND, BIDS will quickly yell at you).


When we bring it all together, we now have a parent-child package that passes variable values. These values are used to execute specific workflows based on the value of the variable passed. Precedence constraints are an extremely helpful and invaluable tool in your SSIS toolkit. Using precedence constraints can help you create very dynamic workflows within your packages.


Policy-Based Management and Local Password Policy

This post is based on an interesting question/situation that was posted over at today. The user asked how to create a policy condition that enforces local sql accounts to adhere to password expiration policies.

First off, to create the condition itself is relatively easy. Below I’ve provided the T-SQL code so that you can create the condition that way. I’ve also included a quick list on how to create it via SSMS GUI.

T-SQL method:

SSMS method:

  1. Under PBM node, right-click conditions folder and select New Condition
  2. Name your new condition something useful
  3. Select Login facet from drop-down menu
  4. Click on field box and select @PasswordExpirationEnabled from properties list
  5. Set the operator value to True
  6. Click on ‘Click here to add clause’ to add another clause to policy
  7. Click on field box and select @PasswordPolicyEnforced from properties list
  8. Set the operator value to True
  9. Click OK. You’ve now just created a new condition!

Now we’re left with another question: Where does this password policy come from? For details on that you can refer to the Books Online article about it (link). If your box is on a domain that has Active Directory policies regarding password expiration, when you select the box for ‘Enforce password policy’ as well as ‘Enforce password expiration’, these settings will come from that policy. Don’t have an Active Directory policy? No problem! If a policy isn’t provided from Active Directory, Windows then looks to its local security policies for these values.


To see the local values, click on your Start button, then type in ‘secpol.msc’ (don’t type type the single-quotes). This will open up the Local Security Policy MMC Snap-in. Expand the Account Policies folder and then click on the Password Policy folder. In the right side pane you will see the various password-related options you can set such as Maximum password age or password length.


While policy-based management can help you check whether or not the accounts have the option enabled to enforce the policy checks, Policy-based management itself has not bearing on the Local Security Policy settings. This is something you, as an administrator, will have to set and configure outside of SQL Server.


SQL University: Parents Just Don’t Understand

Welcome to the second week of SSIS this semester at SQL University. Today we’re going to talk about the relationship between children and parents. Ever had communication issues with your kids when you ask them to complete a chore? When they’re done, wouldn’t it be nice if they always came back and let you know they took what you said, applied it, and completed the job? What does that have to do with SSIS? Read on and find out!
Continue reading SQL University: Parents Just Don’t Understand