The SQL Hammer

Go Go SQL!

aegix — Wed, 20 Oct 2004 08:53:00 GMT

Helped to kick off the Greater Omaha SQL Server Users Group (again) last night. I say “again” because I had also helped to formulate the ancestor of this nearly six years ago, which had finally died after nobody wanted to step forward and keep the hamsters on the treadmill fed. Or water the ferns.

This time it'll be different. I promise.

I've learned from my mistakes.

I never wanted to hurt you, baby, but y'know, Smooth B is a busy man, right?

I'm done bein a playah. All them other user groups I been spendin time with. The Java cats. The Perl Mongers. The .NET posse. I'm done. It's just you an' me. Nobody else matters. Smooth B is gonna treat you right. You just wait and see.

Anywho, I think we're quite a bit better off in terms of tie-ins with Microsoft on several levels. This should greatly enhance our speaker credibility as well as help us with new technology gaps.

Our biggest challege will be marketing and getting warm bodies in the door for more than just the pizza and a chance at winning a koozie door prize.

Any thoughts or techniques you've used to bring people to user group meetings?

Ok, This Has Got To Stop

aegix — Fri, 08 Oct 2004 09:48:00 GMT

First it was Steve, a college roommate who went to work for Microsoft just before things really took off. You know the story: Stock options, monster growth, vesting...yadda, yadda, yadda...then a Ferrari pulls up in my middle-class driveway with Steve at the wheel.

Next, Ryan, a fraternity brother and drinking pal (I know, redundant), hooks up with a law school buddy, becomes the director of operations for a little company then it's deja vue all over again...EBay, stock options, going public and now it's all Kobe steaks and Crystal (not really, but my envious little mind thinks that way).

Now, it's Evan, a guy I hung around a bit during my interlude from college. Becomes the co-founder of Pyra.com, which birthed Blogger into the world, bought by Google, and now there's a healthy bank account to be reckoned with. Like a bank account that could easily float a Lear jet or something.

Don't get me wrong. Some of it could be serendipity, but all of these guys did work for it. I'm just torqued that I don't have the time or fungolas to take my sojourn through Nepal, swapping adventure stories with the climbers at the Everest base camp in some sort of Hemingway-inspired chapter.

Whatever.

All I want to do is be rich enough to fund a J-boat crew to ram Larry Ellison's boat during an America's Cup race. Not that I'm an Oracle hater, I just think it would be fun.

Anyway, you need cash to do that, my friend. Anyone have an idea for the next killer app?

ROT13 function

aegix — Wed, 06 Oct 2004 09:42:00 GMT

I've been reading Neal Stephenson's Cryptonomicon which has been pretty cool since it uses a pen and paper cipher using a deck of cards. An appendix from Bruce Schneier discusses how he came up with the algorithm for the book. It's explained pretty well here.

Since people of every have contributed algorithms in virtually every permutation of code, including K (WTF? -- actually, it looks kinda cool, but don't go Torquamada on me just because I think it's a novel approach), I thought I'd have a go at it in T-SQL. Well, five hours later, I have something in the code that isn't quite right, but I wanted to get something on the blog anyway. So, my best 10 minute effort is a ROT13 encoding/decoding function.

Stop laughing.

Besides, a cursory look at Google didn't reveal anything that someone else has been done before. Hey, just because it isn't entirely practical doesn't mean that somebody shouldn't do it. Like this example.

Anyway, since this wouldn't be considered "real security" (whereby the oppenent knows what your using the encode your data, but doesn't have the means to decode it) and would rather be "security through obfuscation" (i.e. if your opponent knows how you encoded your data, they can break the code), this could be somewhat practical in cases where you wouldn't want a casual look over your shoulder to reveal the data.

Maybe you have SQL authenticated passwords in a text file? We tend to change out passwords used by web applications every few months. Sure, the password file is in a secured location, but this goes a little further in helping obscure the data without being too limiting or imposing.

Whatever.

Here's the code, and in respects to the passing of one of the greatest comedians of all time yesterday:

V pna'g trg ab erfcrpg!

----------------- code below -----------

CREATE FUNCTION ufn_Rot13 (@phrase varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @j int,      --// loop iterator
   @phraseOUT varchar(8000), --// function output
   @letter int     --// ascii letter

  --// initializing variables
SET @j = 1
SET @phraseOUT = ''

  --// main iterator through each letter of input phrase
WHILE @j <= len(@phrase) BEGIN
   --// pick out letter
  SET @letter = ASCII(SUBSTRING(@phrase, @j, 1))

   -- // rotate letter 13 places in either upper or lower case ascii range and add to output variable
  SELECT @phraseOUT = @phraseOUT + CASE
        WHEN @letter BETWEEN 97 AND 122 THEN CASE
                 WHEN @letter - 13 < 97 THEN char((@letter - 13) + 122 - 96)
                 ELSE char(@letter - 13) END
        WHEN @letter BETWEEN 65 AND 90 THEN CASE
                 WHEN @letter - 13 < 65 THEN char((@letter - 13) + 90 - 64)
                 ELSE char(@letter - 13) END
        ELSE CHAR(@letter)
        END
  SET @j = @j + 1
END
RETURN @phraseOUT
END

go

Password Generator

aegix — Thu, 23 Sep 2004 16:35:00 GMT

For some reason, I've always had a hard time being creative on the spot, especially when needing to create a hardened password. It's kinda like going up to a comedian and asking them to say something funny.

Sure, I could bounce my stress-relief ball on the keyboard a few times to get something of a random set of characters, but here's my coded example.

CREATE proc sp__PassGen
@Length int, @pass varchar(15) output
AS
set nocount on
declare @CapBeg int,
  @SmallBeg int,
  @i int,
  @cnt int,
  @type int,
  @bit char(1)

set @CapBeg = 65
set @SmallBeg = 97

Set @cnt = 26

declare @t Table (
RowNum int identity(1,1),
Letter char(1)
)

Set @i=1
While @i<=@cnt Begin
Insert Into @t (Letter) Values (char(@CapBeg))
Set @i=@i+1
Set @CapBeg = @CapBeg + 1
End

Set @i=1
While @i<=@cnt Begin
Insert Into @t (Letter) Values (char(@SmallBeg))
Set @i=@i+1
Set @SmallBeg = @SmallBeg + 1
End

set @i = 1

while @i <= @Length begin
if @i = 1
  select @bit = letter from @t where RowNum = convert(int, rand()*52)
else begin
  set @type = rand()*2
  if @type = 1
   select @bit = letter from @t where RowNum = convert(int, rand()*52)
  else
   select @bit = convert(varchar, convert(int, rand()*9+1))
end

if @i = 1
select @pass = @bit
else
select @pass = @pass + @bit

set @i = @i + 1
end
go

Without question, there's some kludginess that could be cleaned up. I've used this extensively in environments where SQL authenticated logins have to have their passwords changed routinely (and then have written carefully-worded emails to the Powers That Be as to why we can't rely more on Windows users or groups) or when replacing entire servers. Here's the server password changeout:

declare @p varchar(15)

declare @login varchar(128)
set @login = (select min(name) from syslogins where name <> 'sa' and isntname = 0)
while @login is not null begin
exec sp__PassGen 6, @pass = @p output
print @login+','+@p+','+@@servername
exec sp_password null, @p, @login
set @login = (select min(name) from syslogins where name <> 'sa' and isntname = 0 and name > @login)
end

On Not Listening To Experts

aegix — Wed, 22 Sep 2004 08:25:00 GMT

I had an interesting situation the other day. I happened to bump in to my high school counselor. Seems she has given up the righteous path of helping to shape kids' futures by hocking prosthetic shoe inserts out of a 6' x 8' mall kiosk.

We talked up the basic pleasantries, each of us feeding the other what we knew about the current whereabouts of various classmates of mine.

It seems as though an inordinate number of my former friends are in prison.

“I wanted to tell you, Luke. A couple of years ago when the state was investigating me...again...[big sigh. eyes rolling] that I came across some old aptitiude scores, and yours was one of them.”

A bile-infused twinge of pain hit me.

“...and I discovered your aptitude was actually much, much lower than I had originally reported to you. I just thought you ought to know.”

Wow. Years of therapy (and thousands of dollars) fighting the ego eroding guilt of being a perinnual under-achiever that I come to find out I've been an Uber-Achiver...worthy of a whole slew of gold stars and happy faces.

The lesson learned? Even if it's an aptitude test, don't look off of someone else's answer sheet, even if the guy looks like he might be a geek.