SQL PASS Summit 2013 – Day 0

20131016-091604.jpg

After a short hop from Indianapolis I arrived at Charlotte and took a cab ride to the Westin. After a quick checkin I headed down to the second floor where Microsoft has the certification area setup. I happened to luck into wondering down there right when a 70-461 quick test prep was starting so I went ahead and sat in. There was a lot of great info in there about what might be covered in the exam.

Afterwards I headed over to the convention center and ran into the IndyPASS chapter leader (Ray Lucas) and chatted with him for a while. Just before 6 I went to the first timer’s welcome and met a lot of interesting people. I was surprised that someone standing behind me kept making derogatory remarks about whatever the speaker was saying. In my experience with PASS events, usually everyone is there because they want to be and as such they’re in a good mood. I managed to strain my back on the plane ride so I had to bail early and go find somewhere to sit down, once again meeting some awesome people from all over the U.S.

After the first timers welcome came the official conference welcome with drinks and food. Good food, good people. What an amazing conference! Everyone is happy to talk to just about anybody. Unfortunately I’m typing this in the Westin lobby after I went back to the room to lay down for a bit (back) but I can’t think of a better experience I’ve had with people in the IT industry than the people here at the Summit.

I can’t wait to get to Day 1!

Don’t forget, if you weren’t able to make it this year you can tune into a live stream of some of the event here: http://www.sqlpass.org/summit/2013/Live.aspx

What I did on my Summer Vacation

It’s been a few months since I’ve had a new post. I don’t know when exactly it happened but I got busy over the summer and decided I’d give blogging a short break. In general I don’t want to do that but for personal reasons, I needed a few more distractions that weren’t all professional.

The last few months have been eventful however.

  • I helped Hope Foley ( twitter|blog ) organize the Indianapolis SQL Saturday (SQL Saturday #242).
  • I got promoted to senior quality analyst at work.
  • This semester I am helping teach Introduction to Data Management at IUPUI.
  • Last but not least in 2 weeks I am going to the SQL PASS Summit 2013.

It will be my first time attending a PASS Summit and I’m going to try to do some blogging during it, although depending on how excited I get that may be wishful thinking. If you’re going to the summit I’d be happy to meet as many people as possible so look me up.

PASS_2013_AttendingButton_Generic_250x250

P.S. I’d like to thank all the spammers for continuing to post junk comments on my blog. I don’t know what the internet would do without your waste of traffic.

How easily can I hack your SQL Server: Password edition

Security threats come in many shapes and from many directions. Today I’m writing about SQL Server passwords. A lot of servers use mixed authentication for many reasons, and thus have passwords for SQL Server specific users (not AD users). All of the SQL Server login information is accessible from the view master.sys.sql_logins.  That’s right, all of it.

From this table I can find out what user names there are, if they’re enabled, and what the password hash is. Generally users don’t have access to this table but the reality is, constant access isn’t needed; Just a one-time snapshot can divulge the keys to the kingdom.  There is nothing stopping a simple command such as:

INSERT INTO mydatabase.password_table_copy
     SELECT * FROM master.sys.sql_logins

“But how could you get access to that table in the first place?” This idea came from a live situation I ran across. A DBA had set a weekly job to run under their personal account.  That’s all it takes.  The short version to takeaway is this: Run jobs under special minimum-need privelage accounts that are just for jobs. Read on to see the full example of how the passwords can be grabbed and then cracked once you have the table.

Here is a screenshot copying the data from the password table from an account with low permissions (Notice login names are visible, but the password_hash is nulled)

Try to copy password table with low permissions

Try to copy password table with low permissions


Using the same code, a stored procedure is created and a job is setup to run it as someone with an admin role.
Run job that executes a stored procedure

Run job that executes a stored procedure


The job is successful and throws no errors or warnings.
Job shows success and no issues

Job shows success and no issues


Now when a select is performed on the password grab table it is populated with data exposing the full password hash.
Here's the password capture table afterwards

Here’s the password capture table afterwards


Now a piece of software called hashcat can be run against the password hashes to crack them. It’s a little difficult to use, but not that hard. See how I run my hashes against a dictionary.
hashcat cracking passwords

hashcat cracking passwords


Opening up the resulting file reveals a hash with the password I setup the account with. Yes, the password was “password”
Password crack results

Password crack results

The complexity of cracking password hashes in SQL Server 2012 was increased from previous SQL Server versions but they are still breakable with the right tools. I’m somewhat surprised that these passwords aren’t protected the same way linked server passwords are. Viewing linked server information requires Single User Mode. While it’s easy to write this off as too time consuming, don’t underestimate the someone who is angry or bored. With the ability to spin up AWS instances or things like this: 25-GPU cluster cracks every Windows password in under 6 hours — Cracking passwords will become easier as time goes on. In order to help prevent it, follow good security practices:

  1. Make a complex or long password:
  2. Change passwords on a regular basis – if a snapshot is managed, the cracking of an old password won’t help much
  3. Have special low privilege accounts for jobs. Don’t risk security breaks by taking the simple path

A few more technical details about how SQL Server passwords are stored can be found in this paper I wrote for a security class: SQL Server 2012 password security

Familiarity with your Data can Help with Performance Tuning

My current job provides me the opportunity to help support my division where we all use SQL Server – most of us every day. This past week I got a request to look at a query that was running several hours. The goal of the query was to take a ~200,000 row table and find parent-child relationships in itself based off of a short text identifier. For instance:

0SW (PARENT) -> 0SW508Z (CHILD)

You might recognize this as an ICD-10 code and you’d be right. Part of our job in quality management is to verify that data files being built are built correctly. This process doesn’t need to be instantaneous but it should be reasonably repeatable so that it can be re-checked at a later date if needed. Disregarding any of that, this query was taking hours to run and we decided if nothing else it would be an academic study to shorten its run time.

My coworker had to go to a meeting so I decided to go ahead and see what was happening. My first thought was to pop open the execution plan. I put a TOP 100 on the query and immediately got something that looked like this:

know_your_data_execution_plan

I was looking at the parallel properties and noticed it was only using 2 processors (Out of 4).  “Surely I’ve discovered something that will help!”, I thought to myself.  I looked up how to force a more parallel plan and gave it a shot.  Before it would spread the work evenly I had to also update the statistics on the table.  While that did help some, it didn’t fix my problem.

I didn’t really know what else to do so I messed around with some random things and accomplished absolutely nothing.

I was pretty bummed that I couldn’t come up with anything.  I just started staring blankly at the raw table data.

I kept thinking back to the basics of a select query.  The real goal is to limit the return set or the data being processed so that it can all be processed quicker.  The best place to do that is in the join statement because logically it gets processed first for constraints.

Thinking about my data, I knew that that the minimum length of an ICD code was 2 and those would be the top level and contain children elements.  As a last ditch try I decided to put in a functional join constraint (AND LEFT(i1.ICD_CD,2) = LEFT(i2.ICD_CD,2)).  This dramatically reduced the number of ICD codes that had to be check for containing text strings and changed the run time of the query from hours to less than 2 minutes.

Detailed knowledge of all of your data isn’t likely or even necessary but this example shows how some familiarity with your data and remembering the basics can really pull through.  Think about any constraints you can apply when you are querying data.

Counting Uppercase Letters

Whoo hoo!  This week is spring break for IUPUI which means that I don’t have to study or do homework for Oracle administration or Geographic information science.  I do still have to work but I’m hoping to get some blogging done during the downtime.  For now, here’s a quick post.

Last week I had need of a way to count the number of uppercase letters in a string.  A quick Google didn’t yield any useful solutions even though several people had asked, so I made my own. Problem solved!

Of course, it’s never that easy. It then turned out that I needed a way to get the maximum sequential uppercase letters in a word so I had to work that up too.  Hopefully this will help anyone who has the same needs not spend as much time on it.

Here is the function:

CREATE FUNCTION [dbo].[CountUpperCase]
(
-- Add the parameters for the function here
@inString VARCHAR(1000)
, @sequentialOnly BIT
)
RETURNS INT
AS
BEGIN
--Return Variable
DECLARE @returnVar INT;
--A table to hold the counts of sequential uppercase
--letters per group so the max can be picked out
DECLARE @countTable TABLE(
uppercnt INT
);

SELECT @returnVar = 0;
--If the string is empty, there's no work to be done here
SELECT @inString = RTRIM(@inString);
IF LEN(@instring) = 0 GOTO exit_function;

IF @sequentialOnly = 0
--A simple uppercase count.  All the individual
--characters of a string passed in are pulled apart so
--those between ASCII 65 and 90 can be counted
BEGIN
WITH individual_letters (letter, string_left) AS (
SELECT RIGHT(@inString,1)
, LEFT(@instring, LEN(@instring)-1)
UNION ALL
SELECT RIGHT(string_left,1)
, LEFT(string_left, LEN(string_left)-1)
FROM individual_letters
WHERE LEN(string_left) >0
)
SELECT @returnVar = COUNT(*)
FROM individual_letters
WHERE ASCII(letter) >= 65 AND ASCII(letter) <= 90;
END
ELSE
BEGIN
--More complex stat of maxiumum sequential uppercase
--letters.  A lot of what happens in the simple
--version is also important here but some additional
--information is grabbed so we can make figure out what
--is sequential.
WITH individual_letters (letter, string_left,ugroup,lastcase,letternum) AS (
--Anchor query
SELECT RIGHT(@inString,1)
, LEFT(@instring, LEN(@instring)-1)
, CASE WHEN
(ASCII(RIGHT(@inString,1)) >= 65 AND ASCII(RIGHT(@inString,1)) <= 90 )
THEN 1
ELSE 0 END
, CASE WHEN
(ASCII(RIGHT(@inString,1)) >= 65 AND ASCII(RIGHT(@inString,1)) <= 90 )
THEN 'u'
ELSE 'l' END
, 1
UNION ALL
--Get last letter on the string
SELECT RIGHT(string_left,1)
--Get the rest of the string from the front
, LEFT(string_left, LEN(string_left)-1)
--if previous letter was uppercase
--and this letter is uppercase: keep group num
--otherwise: new group number
, CASE WHEN (ASCII(RIGHT(string_left,1)) >= 65
AND ASCII(RIGHT(string_left,1)) <= 90 )
AND il1.lastcase = 'u'
THEN il1.ugroup
WHEN (ASCII(RIGHT(string_left,1)) >= 65
AND ASCII(RIGHT(string_left,1)) <= 90 )
AND il1.lastcase = 'l'
THEN il1.ugroup+1
ELSE il1.ugroup+1 END
--mark letter as uppercase or lowercase
, CASE WHEN
(ASCII(RIGHT(string_left,1)) >= 65 AND ASCII(RIGHT(string_left,1)) <= 90 )
THEN 'u'
ELSE 'l' END
--This is just a seqential increasing number.
--It's helpful for debugging
, il1.letternum+1
FROM individual_letters il1
WHERE LEN(string_left) >0
)
--Insert a count of how many things are in each group
INSERT INTO @countTable
SELECT COUNT(letternum) OVER(PARTITION BY ugroup) AS upcount
FROM individual_letters
WHERE lastcase = 'u';
--Pick the max count to return
SELECT @returnVar = MAX(uppercnt) FROM @countTable
END

exit_function:
RETURN @returnVar;
END

Here’s an example of how to use it:

Example of CountUpperCase function

MCSA SQL Server 2012 Preparation – Equipment

Items used to prepare for the MCSA SQL Server exams.  Introductory blog post here.

Study Guides

Networking Hardware

MCSA SQL Server 2012 Preparation – Introduction

This year I’ve decided to work on the MSCA SQL Server 2012 certification. The areas focused on by the 3 tests are:

  1. T-SQL
  2. Database Administration
  3. Data Warehousing

I get experience every day in T-SQL, but not too much in DBA work and definitely not much in data warehousing.

I went ahead and got the T-SQL study book for the 70-461 exam: Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012.  While I’m working my way through that book, I’m going to create a home networking and server test bed so when I get to the 2nd and 3rd exams I have a way to do real world type work.  This post is a launchpad to how I’m setting up my home test bed.

Equipment I’ve bought

Links to all of the items I’ve purchased to setup my home test bed and for study

Setting up my home network

  1. Test Environment Setup – Part 1

T-SQL Tuesday #39 – PowerShell: How do I use it?

TSQL2sDay150x150
This week’s T-SQL Tuesday is being hosted by Wayne Sheffield ( b | t ) and the topic is using PowerShell for anything.

PowerShell as a solution

Last year I attended a great pre-con by Allen White ( b | t ) for SQL Saturday #126 (Indianapolis, 2012).  Allen White specializes in PowerShell and he started me on my tiny PoSh journey.

PowerShell definitely isn’t the first tool I pick up when I need a solution for SQL Server; In fact, I try to avoid it.  I have been able to use it quite effectively for a few solutions however.  One of the more interesting PoSh scripts I’ve worked up was an attempt to solve the issue with Windows File Caching.  A detailed post can be found here: A potential fix for the file cache issue.  It’s essentially a Windows COM call that is wrapped in C# that is wrapped in PowerShell and it’s purpose is to clear the memory taken up by File Caching in Windows.  If you’re thinking “Why did he do it that way?”, the answer is because I didn’t want to post a compiled .exe and say “just trust me”. Continue reading

Beginning CTEs – How and why to use them

Whenever I come across a problem that I can’t solve with a regular SQL statement in SQL Server, I tend to look toward Common Table Expressions (CTEs) for solutions.  While they can’t solve every issue, they can be used for some imaginative solutions involving recursion.

If something is so complex that a regular query can’t solve it, why wouldn’t I want to use procedural code?

My original programming background started with a lot of BASIC.  It’s very easy to turn to procedural programming and loops for a quick and easy solution.  The problem is, SQL Server is designed  and optimized to work with set and predicate mathematics.  What does that mean?  In short, it means that working on an entire set of information at once is almost always waaaaaay faster than processing something row by row (Commonly referred to as RBAR – pronounced reebar this stands for Row By Agonizing Row)

Enter the Common Table Expression.  Introduced in SQL Server 2005, the CTE is a way to create a derivative table “on the fly”.  It’s similar to a temp table or a table variable with a few exceptions.  You use the WITH command to create a CTE.

  1. Since WITH is a new T-SQL statement, the statement before it must end with a semi-colon (I believe this is true for all new T-SQL statements).
  2. CTEs don’t get statistics like temp tables do.
  3. A CTE can only be referenced once from the query immediately following it.
  4. By default SQL Server only allows 100 levels of recursion

While there are some other differences and limitations, these are the ones that I usually run into.  For my first and most simple CTE example, I’m going to produce something that is easy to do with loops, but also easy to do with recursion.  Lets say you need to produce a sequence of numbers from 1 to 100 but you don’t want to use a cursor or loop.  Just like recursion with objects, there usually needs to be some kind of “seeding” before the recursion happens.  What I mean is that if you want to count from 1 to 100, you need somewhere to start (at 1). Below is an example code that will return a result set from 1 to 100.

1 to 100 CTE example

 

For those not wishing to retype all of this, copy from below.

WITH numbers(seqnum) AS
(
SELECT 1 AS seqnum
UNION ALL
SELECT seqnum+1 AS seqnum
FROM numbers
WHERE seqnum &lt; 100
)
SELECT seqnum FROM numbers

Before I go

Let me mention a couple of things.  In this particular example, any number over 100 will result in a maximum recursion error.  To avoid this we can put an

OPTION (MAXRECURSION <X>)

after the final select query where <X> is how many levels deep you want to let it go.  The only problem with using the MAXRECURSION option is that we can’t put that in a view however, it can be used in a TVF (Table Valued Function).

A T-SQL FizzBuzz

dbareactions_fizzbuzz

Everyone has been getting a huge laugh from the DBA Reactions tumbler the past few days (Tumbler | Twitter). Today they posted a gif referencing fizzbuzz. I had no idea what it was but a quick Googling brought me to this website about it:  http://c2.com/cgi/wiki?FizzBuzzTest

I didn’t see a T-SQL implementation there.  It seemed like a fun little challenge and since I’m always looking for a distraction from what I should be doing, I worked one up.  The code is below (Should work in 2005+).  Can you come up with a better version?


WITH fizzbuzz(saywhat,internalcount) AS
(
SELECT CONVERT(VARCHAR(8),1) AS saywhat, 2 AS internalcount
UNION ALL
SELECT CONVERT(VARCHAR(8),
(
CASE WHEN (internalcount) % 3 = 0 THEN 'Fizz' ELSE '' END
+ CASE WHEN (internalcount) % 5 = 0 THEN 'Buzz' ELSE '' END
+ CASE WHEN (internalcount) % 3 = 0 OR (internalcount) % 5 = 0 THEN ''
ELSE CONVERT(VARCHAR,internalcount) END
)), internalcount+1

FROM fizzbuzz
WHERE internalcount < 101
)
SELECT saywhat FROM fizzbuzz