Andrews Tech Notes (and other random things)

SQL Permissions - The Case of the "Token-based server access violation"

2011-12-02T14:43:00.001-05:00

I managed to create and then fix my own PEBAC issue today and I thought I'd share the resolution in case it helps someone else out there Google-ing/Bing-ing for the solution.

The problem is failed logins and in your SQL error log you'll see entries like this:
Login failed for user 'DOMAIN\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 11.

You might see these sorts of errors when you've got domain problems or kerberos issues, but that's not the case here. You can eliminate those issues if others can log in or if the broken user/users work when you grant them sysadmin server role membership.
Since granting anyone sysadmin (even on a dev box) just isn't acceptable in my world I had to dig deeper. It turns out that the real problem was that at some point I had denied CONNECT SQL to the DOMAIN\Domain Users group. DOH!
Probably not a common thing to do but this problem would manifest itself if you had done the same thing on other groups. Maybe you have a group of accountants you don't want poking around in the db, or a group of nosy developers and you revoke CONNECT to their AD group. Then the exception arises and someone needs access but they just can't connect despite you granting them permission. Look for that deny setting and you'll be all set.

Cheers!

-A

A little C++ Never Hurt Anybody

2011-11-20T19:35:00.001-05:00

As a good friend of mine would agree to, C and I had have a rocky past. I'm still not sure I like C/C++ and all it's nasty pointers and buffers, but I absolutely do respect it. I just find it convoluted and annoying to read personally and love how easy C# makes things. I'll take C# over C++ any day of the week and twice on Sundays... :p

Anyway, there comes a time in every tech geeks life when they go to use a FOSS product and realize it doesn't do quite what they need. This recently happened to me with a very cool media centre project - OpenELEC.
It's a really, really quick booting stripped down Linux based XBMC setup that's optimized for different platforms (like x64 Atom CPU and Nvidia ION graphics). The whole install is ~90MB and it'll run of a MicroSD card, USB, stick, etc. I'm very impressed with it and recommend anyone who likes XBMC check it out. I digress.

So OpenELEC is great and all, but I'm still a huge fan of using an Xbox 360 wireless remote to control things and this is where it fell down. The Linux xpad kernel driver isn't the best, and the SDL joystick code in the XBMC version that ships with OpenELEC is straight up broken when it comes to the 360 wireless controller.
This is where my old friend C and I met again. In digging through event captures, debug logs, and source code I was able to find the code that was causing my problem. Then I got rough. I cut out blocks of code that weren't called, I stubbed out procedures that weren't needed, and I hack-hack-hacked my through some changes to produce a file that not only compiled, it worked!

All in all it was a great learning experience both for working with Linux systems as well as C++ and I can honestly say I enjoyed it. And because I fixed this myself the victory is that much sweeter.
If anyone is interested I shared the changes here on the OpenELEC forums. Now excuse me, I've got some stunning HD content to watch on my now fully functional Asus 1012p EeeBox media centre. ;)

-A

Thoughts On SQL PASS 2011

2011-10-16T19:42:00.001-04:00

This year I attended SQL PASS in Seattle, Washington for the first time. The experience exceeded my expectations, and my expectations were high. If you know me at all you know that I'm typically guilty of holding exceedingly high expectations from anything training related so saying that the conference exceeded my expectations really is something.

The quality of the presentations given were all very high and the knowledge demonstrated by the speakers was beyond anything I've seen before. Without a doubt SQL PASS is where you go to meet the experts. I learned so much in-depth knowledge about advanced internals like workspace memory, IO, and residuals & predicates that the time and money it took to attend will be made back in the first week that I'm back at work. I thank everyone who gave their time and talent to SQL PASS as there's no doubt it takes a huge amount of effort to pull off something that great.

Now, I'm not saying everything was perfect. The big brother/sister program was a failure from my perspective. I never met either my big brother or anyone in my "first timer" group. I was where I was supposed to be, but so were about a thousand other people and there were rooms big enough to hold maybe half of that in a usable manner. I have no idea if my big brother even showed up - according to the group leaders I found he wasn't there. I can't say I was upset about it, but if I've got any feedback to offer for areas of improvement I think that would be it - fix that whole program or just kill it off. The first timer networking session with Don Gabor was well run though, and certainly valuable.

Overall it was a fantastic week in Seattle. I met some new people, reconnected with some others, learned a lot, and for the first time ever; felt a sense of community. I think it's the sense of community which has really left me feeling motivated. I want that feeling to continue and thus I've committed to posting up a technical series!
I've decided that not a lot of people know about the magic that is MSX - TSX SQL Agent setups and thus I'll be blogging a lot about that in the near future. It's my way of saying thanks and giving back to the SQL Server community that has given so much to me.

Moving On Up

2011-05-01T19:17:00.009-04:00

I'm officially no longer a DBA today, I'm now a "Database Developer"!
In the work-sphere of Information Technology I think this is higher than a lowly administrator, but who knows? I've seen enough TSQL code in my 5 years as a DBA to know that there are a LOT of "database developers" who would be better off using Google to find their code. I'm not kidding but I wish I was.

I'm thrilled to know that this change means no more on-call or after hours maintenance. I look forward to being able to do whatever I want to do whenever I want to do it. My wife does too ;)
I'll take a good pay cut giving up the on-call ball & chain but since time is the only finite resource we've got it's therefore the most precious and I make the trade with no reservations at all.

I'm also really looking forward to being able to be creative again. When I started in IT creativity was a great asset but leaving now I feel it's more of a liability. Creative thinkers who can see and plan beyond the basics have an upward battle whenever they try to go above and beyond. It seems that the larger IT gets, the less flexible it gets. Creative solutions become harder and harder to implement and take more and more selling/politicing/nurturing than it's worth. When one can see how a more creative solution would work better for less money and with less management required but can't implement it for "soft" reasons it's very demotivating; almost depressing. Live in an environment like that for too long and one becomes bitter.

I'm absolutely thrilled to be moving into an environment where others are willing to listen when a creative idea comes forward and can be demonstrated. The ability to honestly and openly debate technical solutions with my peers on a daily basis has me grinning from ear to ear. It's like a dream come true.

When You Can't Find a Hack, Make Your Own

2010-09-02T22:01:00.005-04:00

I recently got wind of sweet deal on a Planet Eclipse eTek3 paintball marker and I took it. The gun is great. It's well designed, light, easy to use, and it's electronic.
Being me I naturally started to look into what upgrades, customizations, and hacks were available for it. I was surprised to find there were no hacks AT ALL to be found anywhere in Google for it. I figured at least people would be doing simple things like putting in circuits to simulate rapid trigger pulling... but nope; nothing out there. I did find all sorts of other parts I could spend large sums of money on to swap out the "board" (aka single sided PCB with a single MCU on it- this isn't rocket science) to something with more blinking lights and LCD's! Oooh boy! Oh no, wait. I'm not a 13 year old kid and I'm not dazzled by blinking lights and the ability to modify the colour of my OLED screen or -GASP- 2 MIPS!
All these wickedly overpriced and overhyped electronics did get me thinking so I opened up the marker and looked at the board.
I was pleasently surprised when I found that the heart of the stock board was a PIC18F1320 MCU (10 MIPS BTW). I know the PICs well and I immediately located the ICSP traces on the PCB. After a little rummaging around in the basement I found my USB Willem with ICSP header and wired the two together. I dumped the PIC and was not terribly surprised to find that the application code and bootloader were both read and write protected. I was more surprised to find that the on-chip eeprom memory was readable and writeable.
The hex values I saw were few enough that I thought they could easily map back to the variables the gun offered (trigger de-bounce, balls per second caps, dwell, shots to trigger auto-fire, etc.) so I reset the board to defaults, dumped the hex values and repeated the process making single changes. It was pretty easy to see what was what and I quickly figured out the math behind the hex -> real world value and the checksum routine. (8bit math FTW!)
Once I was pretty confident I could make a change and set the proper checksum I modified the values and wrote back to the PIC. I fired up the gun and verified that I had indeed changed the variable correctly (the firing mode to be safe) and I gave myself a pat on the back for spending about 4 hours to do what could be done with the board still in the gun in about 30 seconds. :p
The obvious goal was to see if I could put in a value that exceeded what the stock board would allow. For example, the stock board won't allow you to go over 15.4 balls per second in the ramping mode. An aftermarket board will go up to the theoretical physical limit of the gun which is ~25bps. I made the change, flashed the settings back, and fired up the gun. I was VERY excited to see the gun flash twice in the tens indicator proving that I had indeed just set the gun to shoot at 20bps - 4.6bps faster than "allowed"... for free.
Of course, just because the little blinking lights says it'll shoot 20bps doesn't mean it will so I loaded up some reballs and tested. WOW! 20bps is stupidly fast!

Up next: tweaking the values to make a full auto mode for the big game Sept 19 :D

P.S. I thought about detailing the entire hack along with how to decode the hex values and calculate the checksum but I think it's better not to. If you're not smart enough to read this post, do the work, and figure out the math on your own it's probably better off that you don't do it.

EDIT: Added links

PAINTBALL!!

2010-07-10T18:52:00.002-04:00

I have to admit something. I may have found something more interesting than just computers and cars. It's semi-violent and most certainly testostrone fueled. It is paintball.

I don't have a long history with paintball. The first time I played was with new friends in first year university (1997!). The second time was for a friends bachelor party last year and the third was for mine this year. There's a few more after that now.

So why do I feel a little guilty about this? I'm not sure. Is it because it's kind of nerdy? Maybe. Is it because it's hyper-male? Maybe. Is it because you have the opportunity to shoot small children with semi-solid balls travelling at around 280 feet per second and then laugh about it? Possibly.

The thing is though; I don't care. I enjoy playing. I enjoy shuffling my fat ass through weeds and brush and mud. It's fun! The adrenaline flows, the heart pounds, the lungs teeter on exploding. Yeah... fun.
I've got my own equipment now so that I can aim better, shoot farther, shoot more often, and do it all easier. It wasn't free but it wasn't terribly expensive either. It's not as expensive as a new SSD for the home NAS, or a new turbo for the WRX. It also means I'm out there enjoying the sun and working off some of the fat I collect during the week via my otherwise sedentary lifestyle. Sure I get a few bruises. But perhaps -just perhaps- those bruises are a little bit like what Fight Club would be like.
Out there on the field and in the post-firefight pit talks we're all equal. The CEO and the grocery bagger. Both were armed, both were ready to fight and neither had any huge advantage. So the CEO spent $2k on his setup... the grocery bagger took him out with a headshot from 60ft with his older brothers hand-me-down pump gun. They slap high-fives and talk about how fun that last round was while both drip sweat. The ego is kept in check by raw adrenaline and the sense of comradery that follows the fight. Ego-boosted boundaries fade and new friends are made.

I'm sure my views at this point are overly idealistic, but it's what I've seen. Developer and DBA work together to take out the support rep but the titles are replaced with names... who cares what happened last week at work? We're taking fire! Sure, tempers can flare when rules are broken or bent but there's no time -and less patience- for a prima donna act. Transgressions are ultimately forgiven.
Perhaps the world might be a better place if we all spent a little time shooting paint balls at each other?

Ok. I've waxed poetic long enough. I'm not even sure where this rambling noise came from or was headed but I think it's time to stop.
At the end of the day I'm not afraid to say that I enjoy playing paintball with my friends and I look forward to the new friendships I'll build while inflicting minor bodily harm on others.

CARPE DIEM!! ...and shoot to splatter ;)

Easy Workaround for Oracle Linked Server Problem with TIMESTAMP(6) Column

2010-05-10T14:24:00.002-04:00

I've got a lot of SQL servers at work. And those servers link to a lot of other servers. MySQL, Oracle, SQL 2005, 2008, etc.
For the most part linked servers are frowned on from my perspective but I can't keep people from getting things done so they do get setup. Most of the time the four part naming convention works just fine although MySQL in particular seems to need OPENQUERY Oracle never has... until today.

For some reason running a query against an 11g R1 Oracle DB kept returning this:
Msg 7354, Level 16, State 1, Line 1557
The OLE DB provider "OraOLEDB.Oracle" for linked server "DBI05CNC" supplied invalid metadata for column "CREATEDDATE". The data type is not supported.

The column type in Oracle was timestamp(6) and no matter of cast or convert would avoid the problem. Selecting without the offending column still produced the error. I was about to start looking for newer OraOLEDB drivers when I thought about the MySQL issue. I also did a little checking and found this KB:
SELECT
*
FROM OPENQUERY(XYZSERVER, 'SELECT COUNT(foobar) FROM XYZSCHEMA.XYZTABLE')

Worked great; problem solved :)

I'm also going to pose the solution to the forum post below as it seems to be related...
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/fd1cb0c8-efb5-4a94-b00d-b2aeb91dd771

Subaru Part Review: GrimmSpeed Up Pipe for 02-10 WRX

2010-04-28T18:54:00.006-04:00

I got a GrimmSpeed uppipe today and I'm very impressed with the quality. I already have a GrimmSpeed crosspipe and this is right on par with the quality I expect. The welds are perfect, the flanges are thick and straight, and the coating is perfect. It's boderline artwork.
I was so impressed I decided to get some specs on it and post them up.
The inlet port to the upipe (exhaust manifold outlet) has a radius nearly 10mm larger than stock. The pipe tapers smoothly to the outlet (turbo inlet) where its radius is about 4mm larger than stock. The stock uppipe weighs in at about 7 - 7.5lbs and the GrimmSpeed piece is about 3 lbs lighter.

The lowdown:

thicker flanges
ceramic coating for to keep the heat in
mandrel bent pipe vs. stock crush bends
~20mm diameter larger inlet (94.56mm) & ~8mm larger outlet (86.76mm)
3lbs weight savings

An STI uppipe will weigh less than the stock WRX as the catalytic converter, but no one would be buying this piece for the weight savings. I'm actually replacing an STI uppipe I put in because the heat sheild on it has rusted and it's loose and banging around. I think it may also have a leak.

If you're on the fence about this part hopefully this info plus the vendor info pushes you over the edge. It's a solidly built piece and you won't be disappointed.

If you're in Canada, support Touge Tuning. I've had nothing but excellent experiences with them and from what I can tell their prices can't be beat!

MS DTC Problems With Oracle Linked Server & Windows 2008 x64 R2 Cluster (Server: Msg 7391, Level 16, State 1)

2010-04-20T14:43:00.002-04:00

If you are getting this error (Server: Msg 7391, Level 16, State 1) trying to run a transaction using a linked Oracle server then I may have the fix you're looking for. I had to troubleshoot a problem with a new Windows 2008 Enterprise R2 x64 cluster today that was a real pain.

To get things ready normally you need to at least do the following:

install the Oracle drivers & reboot
set "allow in process" on the provider in SQL
ensure the default DTC is the clustered resource
enabled DTC for network access and set correct authentication.

Normally things would work at this point. No such luck today though! No matter what I did I couldn't get a specific stored procedure a user wrote to without firing off the "unable to enlist" error. Other DTC transactions worked just fine.

The solution was simple (but tricky for non-Oracle DBAs); I had to install the "Oracle Services for Microsoft Transaction Server 11.1.0.7.0" service and reboot.
Now, I *could* have re-written the query but since it was already working on another QA box and the user is going to be re-working the whole solution shortly I didn't see much point. Plus I wanted to know HOW or IF it could be made to work on this cluster! ;)

Hope this helps someone else out there!
-A

FreeNAS Magic Sauce: Getting Transmission to play nice with Samba

2010-03-29T22:51:00.002-04:00

I love my FreeNAS setup. I can download torrents on it faster than with my Windows boxes and it's about as close to "set it and forget it" as it gets. Love it.
I did have one problem though. From some reason transmission got in the habit of downloading files with permissions that let me read them but not write or modify. It would get worse if I tried to move them around via Samba.
As I'm not keen on running scripts to constantly do the same thing (like reset permissions) over and over again with little regard for what they're doing I found another solution. Tell Samba how to behave!
Adding these commands to the "Auxillary Parameters" of the CIFS/SMB config will make all your problems go away. Is it secure? Probably not. Is my network secure? Yes. Good.

force group = wheel
force user = transmission

Bounce the service and you'll never have permission problems again! :)

Subaru Parts

2010-02-24T18:04:00.002-05:00

I like to shop around for parts. For quality I'll only go with OEM or a recognized aftermarket brand. When it comes to filters oil, fuel WIX is a well known company that makes an excellent product.
What some people may not know is that NAPA Gold parts are just re-badged WIX products.
Here's an example using some common filters on a 2002 Subaru WRX (the apply on most WRX's from the 90's - 2006 as well as SAABurus 05-06)
Oil Filter - NAPA Gold - 1365 (WIX# 51365)
Fuel Filter - NAPA Gold - 3558 (WIX #33558)
Cabin Air Filter - NAPA Gold - 4485 (WIX #24485)

So there you go, now you know you can pick up a quality WIX filter locally nearly anywhere in North America. :)

SQL Code: Object Location Within Data Files

2010-02-04T12:19:00.002-05:00

I'm doing a lot of playing around with different numbers of data files and performance right now and in doing that I'm moving data around files and filegroups alot.
One of the things I find maddening is when I *think* I've moved all the data only to find that a filegroup still isn't empty. You check tables, indexes, indexed views, heaps, all look like they're in the right spot. That's why I wrote the query below. It will show exactly what data is where in your database. It's helpful for tracking down LOB_DATA that doesn't move with the rest of your table data :)

SELECT
OBJECT_NAME(object_id) AS obj_name
,p.rows
,au.type_desc
,au.used_pages
,au.data_space_id
,mf.name AS [file_name]
,mf.physical_name
,mf.state_desc
FROM sys.partitions p
JOIN sys.allocation_units au
ON p.partition_id = au.container_id
JOIN sys.master_files mf
ON au.data_space_id = mf.data_space_id
AND mf.database_id = DB_ID()
ORDER BY obj_name, au.data_space_id;

A Word About Backups At Home

2010-01-20T19:48:00.002-05:00

I just posted this on a Subaru message forum (off topic section) because people were discussing backups and I couldn't deal with all the bad info. I thought I'd repost it here for posterity. Note that this is STRICTLY for home setups and has nothing to do with SQL backups and what you should be doing at work! ;)

1. - Know Your Limits
If you barely manage with Windows and have an ongoing problem with spyware/malware/viruses stop reading now. Go buy something from DLINK or Linksys, or whoever. You do not have the skills to build and maintain anything more robust and you'll need support. This will cost you a lot more money and you'll get less flexibility, but it'll be easy to use.
If you are an average to above average Windows or Linux user you can probably risk trying out some of the stuff I'll describe below.
If you're an expert Windows or Linux user you should already know this stuff.
If you're a mac user go overpay for something stylish then tell us all how much better it is and you are. Or suck it up and follow along below

2. - Identify Your Goals
You can waste plenty of time and money if you don't know what you NEED to do. You may WANT to backup everything you have in real time, but do you NEED to?
How much data can you afford to lose? How often is the data changing?
For example a normal computer user probably adds a few bookmarks, downloads a few shows, songs, imports some pictures from their camera, etc. each day or so. Maybe a weekly backup of a few directories/files would fit your needs. Maybe you need something to keep entire volumes in sync. Think about it, make a decision, then move on.

3. - Know Your Options
Options range from really easy & cheap, to very expensive and complex.
On the low end of the scale you've got USB attached storage. Maybe it's a HD or solid state device. Maybe it's an entry level RAID or JBOD.
In the middle you've got more advanced attached disk setups through eSATA, USB, or even network attached.
On the high end you've got custom built servers that do nothing but share files.
On the extreme high end you've got true NAS appliances from vendors like NetApp. If you know what these are you shouldn't be reading this!

4. - Know What Causes Problems
People. Hardware failures. Software glitches.
Most often at home you lose data because a hard drive dies, a CD or DVD degrades, or something else happens like your DLINK media server mainboard fries.
How do you prevent these things? REDUNDANCY!!!
That's it. Multiple copies of things in different places is the only way to guarantee it survives. I laugh at the people who throw a couple disks in a box and mirror them (RAID 1), then go on to brag about how they'll never, ever lose data. What happens when the RAID controller dies a few years down the road? What happens when you get a virus that overwrites all the data? What happens when they stop making the identical drive you need so the el-cheapo RAID controller will agree to rebuild your array? Or what about when little Johnny decides to try installing a new Linux partition over your Windows 7 setup? Yep; total data loss.
If you're doing something as simple as say once a week copying or auto-syncing say your "My Documents" and "My Pictures" folders (sorry Linux folks) to a USB drive you've just prevented total data loss for about $100 in hardware. Congrats!
That being said... if you CAN accept that you *might* lose all your data if X,Y,Z unlikely things happen then a simple RAID 1 setup could be all you need.

5. - Identify What You Want To Backup
Backing up everything is an option, but do you need to do it? Probably not. Identify all the things you can't lose and ensure they're safe. For me this is things like documents, movies, music, pictures, and code.

6. Pros & Cons
Everything you coudl possibly choose has pros and cons... I'll try to go over the most popular.
a) CDs & DVDs
-pro- cheap
-con- don't hold much data
-con- very labour intensive to use
-con- don't last too long
b)Hardware RAID 1/ RAID 10 (1+0, stripe + mirror, etc)
-pro- pretty cheap
-pro- most motherboards have this now
-pro- braindead easy to get disk level redundancy
-pro- disks last longer than CD/DVDs, but not as long as solid state devices.. solid state is still very expensive
-con- no redundancy of RAID controller (typically)
-con- typically need identical disks
-con- can lose all data to stupid mistake (blow away partition) or virus/corruption
-con- always possible driver issues
-con- might not be able to move RAID disks to new HW and have them recongized
c)Software RAID
-pro- probably pretty cheap or free with OS
-pro- very easy to use
-con- can lose all data to stupid mistake (blow away partition) or virus/corruption
-con- may not be portable/easy to upgrade
-con- performance hit
d)USB devices (single disk)
-pro- pretty cheap
-pro- very portable
-pro- no lock in to hardware, OS, manufacturer
-con- may need software to automate backups
-con- can fail pretty randomly
-con- performance is limited
d)Vendor Device (DLINK, Linksys, etc.)
-pro- very easy to use
-pro- often has HW RAID and funky GUIs, maybe even backup software
-con- often very expensive for what you get
-con- locked into the vendor
-con- when the HW fails it's unlikely you can fix it
-con- BUGS!! & hassle of firmware updates
-con- proprietary disk RAID setups
-con- often limit the size of disks you can use
-con- software included can sometimes be utter garbage
-con- may not work with all OSs
e)FreeNAS/Linux/Windows Home Server
-pro- Very robust, very flexible solution
-pro- Easy to work with (depending on skill level)
-pro- Commodity hardware can be used, purchased, upgraded
-con- Can become expensive
-con- Need decent computer skills
-con- Uses more space & power than other setups
-con- May not include anything to help you automate backups
-con- Need to stay on top of patching

Once you work through all of this you should have a good idea of what you need, what you need to backup, and hopefully have an idea of how you can back it up.
To help out a bit more I'll detail my setup...
I have two laptops, two PCs, a BlackBerry, and a wired home network + wireless access to it. I run Windows Xp, Windows 7, and Ubuntu. I want all my devices to be able to access music, pictures, and I want each device to have certain things like documents backed up. Securty of all this is a totally different matter...
My largest risk exposure is to fire. In the event of a fire I would lose everything because I'm not moving my data off site... it's too damn expensive and I don't trust online providers enough. At some point I'll look at getting a fireproof safe and then backing things up to external storage on a regular basis and putting the backups in there.
Aaaaaanyways...
My backup solution is an older P4 box with a few TB drives in it running FreeNAS. I use software RAID to protect against disk failure and RAID hardware failure and the performance is more than good enough. FreeNAS itself runs off a free 128MB USB stick I got and I've got another copy of it in case anything happens to it. If the computer did die I could easily slap the drives into any other FreeNAS box or even a linux box that reads UFS formatted disks and I'd be able to recover my data. That's really good.
On some PCs I have specific folders set to stay in constant sync with FreeNAS, while others do it on a schedule. Everything is enabled to "work offline" if FreeNAS wasn't available meaning I've got at least two distinct copies of my data. Total cost if you were to build this thing new is about $300-$600+ depending on the number and costs of disks you put in it. Go as cheap as you can on CPU.

So there y'all go. One REALLY long post about backups. You should be good-to-go now.

Cheers!

SQL Server Login Error Review & Fix for "Error: 18456, Severity: 14, State: 11."

2010-01-07T11:16:00.005-05:00

If you've got a SQL Server machine on a domain chances are you've seen error 18456 enough times to have seen the following:

Severity is always 14
State 8 is a bad password (password submitted doesn't match password stored in master)
State 9 is an invalid password (doesn't meet requirements)
States 2 & 5 are invalid usernames
State 40 means the logins default database isn't accessible to that login
State 11 means the login is valid, but something else went wrong

Now, state 11 is fun. You'll get this error and then you'll probably get a call or email from someone saying they should have access but it isn't working. Often in the logs you'll see something like this:

Login failed for user 'DOMAIN\SAMAccountName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

This is a pretty good indicator that the users login credentials are out of date which is usually a result of a laptop user trying to access the resource with cached credentials after changing their password elsewhere.
No matter what there are two simple fixes for this that have always worked for me.

Have the user lock their computer and login again.
Have the user log off and logon again.

That's it! Those two steps will ensure that NTLM and/or Kerberos tickets/tokens are refreshed by talking to a current domain controller and the problem should go away.

NOTE: If you're seeing errors about SSPI context there is a possibility that your SQL Server service account is invalid (password changed, locked out, disabled, etc.), or your domain (or specific DC) is having issues.

Cheers!

SQL Query Optimization

2009-11-03T21:30:00.003-05:00

Optimizing queries isn't always straight forward. Sometimes you think you've created a great index but the query optimizer just won't agree. You update stats, try maxdop hints, clear caches, and maybe even curse a little but nothing works.
You suck it up and admit you're going to have to make an exception to what you know is right and use an index/table/locking hint.

Well in doing that today I made extensive use of a few options most people may or may not be aware of. These are:
SET STATISITICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON;

The output of the first two options are pretty easy to understand, but the third (which is AMAZINGLY valuable!) can be tricky.
I suggest taking a look at this blog post on the subject as it gives great detail on the use of this excellent tool.
http://blogs.msdn.com/queryoptteam/archive/2006/08/29/730521.aspx

Interesting problem with SQL 2008, VCS, and MSX-TSX Servers

2009-08-21T15:25:00.002-04:00

I thought I was going nuts today when I couldn't get a TSX server to use it's MSX server. Enrollment went through without a hitch, but the TSX would never get any jobs or even probe (although the MSX said it was probed).
After a LOT of digging I eventually found the problem. The TSX is actually a Veritas/Symantec cluster with a virtual name but when the MSX was calling things like msdb.dbo.sp_sqlagent_probe_msx it would use the hostname of the cluster node running the SQL service for the value of @server_name. This was weird. Both @@SERVERNAME and SERVERPROPERTY('SERVERNAME') returned the virtual name and the TSX enrolled as the virtual name but it was always calling the damn hostname!
The fix for this was quite obscure.
I eventually happened upon a key
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT]
"Environment"

And this key was missing for this cluster, yet there for every other SQL server I've got. I took a chance and put in the data for the key that was present under the service for this instance and bounced the service. BAMN! Just like that it downloaded all it's jobs and the MSX started issuing sp_sqlagent_probe_msx calls using virtual name!

I have no idea why this happened (SQL 2008 seems 'flaky' with permissions like this) but at least the fix was easy.
Keep an eye open for this if you're running a similar setup!

Fanboys: Pros & Cons Are Everywhere. Why can't you see them?

2009-07-30T23:12:00.005-04:00

I just don't understand fanboys. These are people who take interest in things like cars, phones, computers, OS's, etc to a whole new (and scary) level.
There are people out that that would sooner spit on you than shake your hand because you use MS Windows instead of Linux. Why? I HAVE NO IDEA!
I just can't wrap my head around it. I suspect it is some sort of undiagnosed social problem and/or an intelligence issue but I'm no doctor.
I make my living supporting and architecting a Microsoft product (SQL Server), I'm posting this from Linux (Ubuntu), and I urged my parents to buy an Apple (Macbook) when I got tired of supporting them on XP ago and was given reason to fear for their information security. Why the diversity? Because everything has pros and cons. EVERYTHING.
Recently, I've been spending time digging into the pros and cons of Oracle 11g vs. SQL 2008 and I've got to say there aren't too many feature-wise. The big differences start to show up when you start to examine dealing with huge (multi-TB) databases that need very high up time numbers. MS still isn't there 100% with that sort of stuff (ever dug into an AWR report and seen what Oracle keeps track of? WOW!) but they are making major progress; no doubt.
In doing this I was also forced to recognize some of my own bias in that I have slammed Oracle over costs or complexity but ultimately; there is some serious value for that money and the complexity is a result. Of course, this point is moot unless you have DBAs that know what they're doing; really know what they're doing. As I see it the benefit of the MS solution is that it'll pretty much manage itself and tell you when something is seriously wrong; up to a point. This is a pretty common theme with all MS products and it's why Linux is still nowhere near a threat to Windows on the desktop (I won't get into that religious debate here). You'll never scale over a few hundred GB with MS SQL unless you have talented DBAs (haha! sales pitch for me *wink*) because you have to start fiddling with the knobs and actually understand the architecture. You need to be able to comprehend what all the DMVs expose and do it without going to Google/Bing/Ms Connect! Oracle lacks that touchy-feely stuff from the get-go and you won't get anywhere with it out of the box unless you have the right people.
One last thing before I move on from this Oracle/SQL rambling bable; what is up with them comparing things like Oracle RAC to SQL 2008 clustering?? How can anyone honestly claim that these two products compete in the same space? One is a simple low cost active-passive HA setup and the other is a full-blown active-active horizontally scalable massively-parallel solution that requires you to code specifically for it! 99% of all marketing is utter crap in the IT world.
In getting back to the topic, I'm forced to acknowledge that my mind just doesn't allow me to become too fanatical for any one thing; I always see pros and cons. I can't help but wonder if that is a positive or a negative. Is it evolutionarily (is that a word?) "better" to have this ability to reason and judge to such a degree, or is it better to be able to fully commit to something and then blindly follow your decision? I wonder. If I had to make a call I'd say the former is superior (wow... really high on my horse tonight, eh?) as I reason it to be a divergence from the typical tribal mentality we would have had in us many thousands of years ago. Without the ability to see fault you won't have innovation. The Model-T may have been "good enough". The steam engine "efficient enough". Nuclear power "too dangerous"...
I end with this; is the fanboy mentality an age-old human trait, a sign of a dumbing-down with society, or something else entirely? I can't help but wonder that everyime I read their comments/editorials/words. Part of me hopes the entire thing is nothing more than some elaborate marketing scheme running amok! ;)

STi Intercooler Waterspray Into 2002 WRX Wagon

2009-07-26T13:10:00.021-04:00

I found a great deal on a STi waterspray tank and switch and decided to take it. It came with the reservoir, pump motor, level switch, ~1ft of tubing, and the stock USDM switch. I picked up 20ft of tubing at a hose supply company for $15.
I did a little reading and got the wiring diagrams for the switch and pump, then went about deciding how to wire things up for my install. To compensate for the lack of time delay relay I searched my parts bin and came up with a 555 timer and then hit up Princess Auto for a $5 automotive relay & harness.
I knew that I wouldn't have the IC spray hooked up in the winter and I wanted it installed somewhere "covert" and out of the way as I actually use my trunk... a lot! I came up with the idea of removing the spare tire and putting the tank in that spot- works great after drilling a single hole in the tank mounting bracket and I can use my entire trunk as the cover/floor goes right over top like stock.
Hose and power/ground routing was done along the same routes as the stock rear washer/gas lines & wiring; along the driver side.
Here are pictures of the whole install and a little more info about wiring for anyone out there who may be interested...

Stock WRX intercooler shroud vs. STi with sprayer nozzles- not a huge difference:

Water tank bracket mounted in spare tire well. This is bolted right into the same spot as the stock spare tire hold-down. One hole needs to be drilled in the bracket where the bolt is seen (or where the blue dot is in other pictures):

Showing how the water tank bolts up to the bracket. Two 10mm nuts hold it on. You can just get at them with a wrench while the bracket it bolted down:

The water hose was run along the stock line and I brought it out through this existing bung (drilled holes though the plastic). My vacuum tube for the stock boost gauge also uses this:

The hoses run into the engine bay the same way as the stock wiper hose and wiring harness:

Then it runs along under the various wires, hoses, and what-not to come up in the same spot as the stock wiper hose and connects to the in-line check valve and the tube/splitter combo that runs to the IC sprayer nozzles:

The wiring of the relay to the stock switch and the 555 timer setup. In retrospect I should have used a pot instead of a 10k resistor for the timing as my test 12v source gave 5s but the car power gives 3.5s. With a pot I could have dialed in the spray time exactly. Oh well. Holding down the button sprays as long as you hold it :)

Wiring the switch into the car is pretty straight forward. The basic steps follow for a +12v switched relay that provides ground when "on". If you want detailed wiring instructions leave feedback on this post and I'll put them up.

Tap the existing wires for the illumination bulb & wire into the switch.
Wire up your relay to be +12v switched on.
Run a ground to the IC spray switch.
Run the output of the switch to the trigger of your 555 timer circuit (must be ground!)
Run power and ground to the 555 timer circuit. Use the same switched power source and ground as the relay and switch.
Run the output of the 555 circuit to the +12v switch of the relay.
Run the "on" output of the relay (should be ground when "on") to the IC spray pump.
Fill the spray tank, turn on the ignition, and spray away!

When winter comes just disconnect the wires @ the tank and tape-off. Cap the water hose and you're done until spring.

Hopefully this will help/encourage others out there to try this. I couldn't find any detailed instructions for this (and none for the 555 stuff) so I thought I'd share it with some pictures to help out. :)

Cheers!

Investing: The biggest known con?

2009-07-25T10:46:00.004-04:00

I admit I have very little love for the financial sector. Profit is king and money is made on the blood and sweat of the vulnerable. It's quite disgusting and at some point in time it will collapse... or be ripped apart. Hopefully a peaceful end comes around but given human history for dramatic changes, I doubt it :(

Anyways I just read an article on "high-frequency trading" and I can't help but laugh. If anyone out there seriously thinks that they can complete with multi-million dollar trading algorithms and hardware setups they are just plain wrong. The only hope you have is of somehow setting up another system to game their system. What does that lead to? Poker. Seriously. You've got multiple "players" all wanting "to win" and they're all looking at each other for various "tells"; but the players can "bluff"!
Great. Sounds like the very definition of fair, balanced, and dependable... NOT.
Given a situation like this I think the whole market is essentially done. No longer are companies actually making money through the merit of the company. They just make money because people use their stocks like poker chips. This system doesn't inspire anything positive and instead puts enormous pressure on companies to simply release sound bytes and news clips that will trigger high volume trades on their stock.
Have we learned absolutely nothing from the latest "global economic downturn"?

...oh wait; I guess we did. We learned that the tax dollars of the people will be given to the very same groups and individuals that caused the problem in the first place so that they can do it all over again. The status quo reigns when the status quo means the rich and powerful stay rich and powerful ...dancing on the backs of the bruised.

Lenovo T61 + OCZ Solid Series 64GB SSD = No Love

2009-07-21T21:33:00.003-04:00

I picked up an SSD on sale for $105 Saturday and I'll be returning it tomorrow. What a pile of crap.
First off the T61 doesn't even recognize the thing... BIOS update fixed that.
Then there are HUGE problems just trying to get the damn thing to seat properly in the HD bay and not throw a 2100 error. Finally solved that with some by not using the drive caddy and using the just the rubber ends, then wiggling just right until it seated in the SATA connections.
Then I find out that the T61 doesn't support SATA II.
Then I find out the SSD doesn't support AHCI.
Then I finally get all the tweaks done, mount the drive in another computer, track align @ the 4k boundary, format, get rolling with the XP install on the laptop and... NOTHING.
A blinking cursor in the upper left after the first reboot. Awesome.
Tried to fixboot, fixmbr, etc. No dice.

I'm done. The whole plan was to use this as a quick swap for use in the car (while tuning) to avoid issues with bumps and to ensure my original HD could be encrypted and thus used for work only; all personal use = SSD HD.
I suspect it's a partition issue and could be resolved but after all this I've got a sour taste in my mouth and this just isn't worth it. For a few hundred dollars more I could get a very decent NetBook and enjoy all the advantages that brings.

Oh well, nice idea but it just didn't work out... Shocking how many problems there were.

Designated Driver? We've been doing it wrong all along!

2009-07-06T17:38:00.004-04:00

I had to post this. Sometimes a typo is just a typo... Sometimes it's this!

Gone Fishing

2009-06-16T23:03:00.002-04:00

Trish and I bought a canoe as our first combined big ticket item for our 2 year anniversary and we spent the weekend fishing. It was great! We both got burned and totally relaxed... Without thinking about it this was by far the best anniversary ever.

As for the Fish Cam- I'll still be posting a how-to and I can saw it works. However, I need to put a tint on the lens as the B&W camera is way too sensitive to daylight and it washes out totally. I did a quick test by putting my sunglasses on it and it worked like a charm. Since the camera lens is small I may be able to get away with just putting a piece of old negative film over it. I can't wait to use it more!

Fishing has been going well. We've both caught loads of bluegill, green sunfish, and pumpkinseed. Plus various mixes of them. We've also caught smallmouth and largemouth bass, and Trish caught a rock bass at one point. I've caught the biggest fish so far with the best being a 2-3lb largemouth :)
We do have pics ;) Trish 98% landed a trophy perch. It was at least 20-30cm long and thick! She caught it while reeling in a tiny little perch- greedy cannibal perch! The wildest thing is that she was having trouble unhooking the small (10cm) perch she had on and the massive perch would just sit at the edge of the canoe and watch, then when she would put it back in the water to keep it alive the predator would strike immediately and she'd have to yank the poor little guy out again! I have NEVER seen anything like it! It actually managed to get her fish all the way in it's mouth and she had them both almost in the canoe when it finally spit out the poor little guy and took off for good. I wish we had that on video... just unbelievable and the whole thing went on for about 3 or 4 minutes.

We're thinking that we may try out carp fishing as the local pond in St. Thomas seems to have changed a lot since last year and the pan fish & bass have been largely replaced by carp, carp, and more carp. :( I hear they're tough fighters and there's no doubt they're shy (they go nuts trying to get away from the canoe when we come close) but I'm just not excited about fishing for something where it's just a throw in the line & wait scenario. Oh well, we'll see how it goes...

Steam - Not Fun When Broken

2009-05-22T23:40:00.002-04:00

I stupidly decided to opt-in to the Steam Beta client today. WHAT A MISTAKE!
After applying a patch and restarting Steam I was greated with the following error:
"Error...steam.exe (main exception): " yada, yada, yada but the best part is that the error was Error 1 or 0 "unknown error".
Awesome.

Well after about 45 minutes I got things working again.
I used the repair option given from the installer on my original Orang Box DVDs. Then, I deleted teh Registry Keys "beta" and everything under "Users" at the following location:
HKCU\Software\Valve\Steam

After that Steam started, updated, and I simply logged in again. Bingo!

Fish-Cam is GO!

2009-05-09T18:37:00.003-04:00

I recently made a nice little to-do list on my fridge with a dry erase marker I've had since university. It doesn't judge me; you shouldn't either.
..anyways I've made some very good progress on said project and have now officially kicked off my "Fish-Cam" project. It is essentially a budget/DIY version of this or this.
I decided to use a portable DVD player instead of a stand-alone mini-LCD or *gasp* a mini-CRT. The main reasons for this decision are:

Cost. A portable DVD player is ~$80 - $180 CAD right now. Mini LCDs are $110+
Design. Portable DVD players are meant for lugging around. Mini LCDs are really meant to be mounted somewhere.
Features. A Mini-LCD is useless on it's own. A portable DVD player plays DVDs, mp3s, etc.
Availability. Wal-Mart had plenty of portable DVD players. Only a few shops have mini LCDs... and questionable return policies if things don't work!

I've also picked up a B&W (I can upgrade to colour if this works well) CMOS security camera with 6 IR LEDs on it for $29 CAD. It comes with 60ft of cable so all I need to do now is get some PVC tube and acrylic that I can mount the camera in. Then the Fish-Cam will be complete!

Review: Portable DVD Players (RCA DRC6338 & Toshiba SD-P70S)

2009-05-09T17:00:00.002-04:00

As part of my Fish-Cam project I decided that I'd look at purchasing a portable DVD player to use as the monitor. So I went over to Wal-Mart and picked out the RCA DRC6338 8" portable DVD player because it was on sale and had an 8" screen. What a mistake! Luckily upon return of that unit I picked up the Toshiba SDP70S and was very pleased.
Here are my reviews...

RCA DRC6338
Cost - $119 CAD
Features - Car charger, wall charger, built-in battery, 8" screen, audio/video inputs & outputs, remote
Review - Crap. It has a cheap feel, doesn't include the special cable you'll need for av i/o, and didn't work with my video input source at all. It also lacks the ability to output digital audio (not that I care). Even worse; you can't use the device and charge it at the same time! Really poor design.
This unit went back as soon as it failed to display my video input source.

Toshiba SD-P70S
Cost - $98 CAD
Features - Car charger, wall charger, external battery, 7" screen, a/v in & out, remote
Review - Excellent. It feels well built and the packaging is many times better than the RCA. It also INCLUDES the special a/v input cord you'll need and does do digital output. It worked right away with my video input source and I didn't even have the battery plugged in to the unit. This is great as I plan to run this 100% from a 12V battery as part of the fish-cam and I don't want to worry about battery life at all. The unit CAN charge and play at the same time.
Although it does have 1" less viewable space I'd consider this unit superior to the RCA. The fact that it includes the a/v cables makes up for the screen size IMHO.

HEAP Fragementation in SQL Server 2005

2009-05-04T20:28:00.012-04:00

I happened upon an interesting problem at work today. There's an application my company purchased that is used for project management in one way or the other. Each night a job runs that does various things including synchronizing variables like permissions and user names with Active Directory. The vendor supplied the script that does this and it's terrible. It involves dumping a lot of data into a work table, then pulling that data out piece by piece, converting it to XML, comparing the XML to some other XML, and then re-inserting into the database. Yikes.
Anyways, the performance of this sync job is fairly constant until a certain point and then it just blows up and quadruples the run time which affects other processes.
Previously this was solved when I noted the complex system we use to defrag our databases had a bug in it. I fixed the bug, defragmented things and everything went back to normal.
This time however, there is no bug and the tables that play a role didn't show any sort of fragmentation at all. In QA I just went ahead and defragmented everything with the "quick & dirty" rebuild script

 EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'

Never, ever use that on a production database :p
A test showed that the sync was indeed working properly again. Great. So all we need to do is ensure that the database has essentially zero fragmentation on all tables and indexed views to get this to work. I'd also take a pet unicorn while we're wishing.
So I did a little more digging around and I noticed that our automated index rebuilding script didn't consider index_id = 0 (HEAP) which meant I wasn't viewing any data for that while reviewing fragmentation levels. I coded up a very quick fragmentation check query

SELECT object_name(object_id)
 ,index_id
 ,partition_number
 ,page_count
 ,avg_fragmentation_in_percent
 ,index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(N'--dbname--'), NULL, NULL, NULL , 'LIMITED')
ORDER BY page_count DESC, avg_fragmentation_in_percent DESC;

What I found shocked me. There were 10+ large heaps with 99.5%+ fragmentation!!
I immediately started to dig in and see why these tables didn't have clustered indexes and you know what I found? No reason. No reason at all. There were already non-clustered and unique indexes on every table I found. For some reason the developer/vendor just never clued in that over time they would create massive fragmentation on these tables and not give us poor DBAs a chance to fix it. My assumption is that this was developed way back in the SQL 6 or 7 days where I *think* they used linked-heap table structure... don't quote me on that though as I've never touched anything older than 2000 (and oh man do I not miss 2000!)

If you're not sure how I fixed things yet I'll tell you. I converted a UQ index on each table to a clustered index (using a best guess approach) which rebuilt all the others. Fragmentation is under 1% and I'll be able to monitor the fragmentation now to see if I need to do anything else with these tables.

All this work because a vendor didn't bother to properly design their database. Shocking, eh? ;)

Roomba Scheduling App Released (SerialBandit)

2009-05-03T22:10:00.018-04:00

I spent some time writing an application that will communicate with a 5XX series Roomba (I have a 530) via RS232 (COMM Port or Serial Port) from your computer. The main goal was to let me setup my Roomba to clean at specific times (cleaning schedule). You can buy a device from iRobot that will let you do this remotely, but not for my version of Roomba (or below in the 500-series).
I couldn't find any apps that worked reliably and did what I was looking for so I wrote my own.
I'll include a few pictures of the setup, but essentially it's the standard Serial Port -> MAX232 -> Roomba setup. You can get further instructions here if you need them.
The application is up on CodePlex here. You can download the compiled app on the "Downloads" tab. :)

Picture below show...

Quick shot of the interface
The Roomba serial port connector I made (A 6 pin mini DIN with the key removed, connected to Cat5e cable)
The Roomba plugged in to the serial adapter
The PC -> Roomba Converter on breadboard
The schematic I used. 100% Credit on the schematic goes to Tod E. Kurt

Leave comments here for support.
Cheers!

**UPDATE 1**
I've posted my announcement on RobotReviews.com (here)
**UPDATE 2**
The admins at RobotReviews are wankers. Apparently 2 posts with details of this app is "spamming the boards". LOL... wankers. :p

Interesting: Infomercial Ratings

2009-04-27T22:43:00.003-04:00

I can't say I've ever bought anything from an infomercial, and I doubt I ever will. However, I am often curious about what people who do the products have to say. Well now I don't have to wonder! I give you http://www.infomercialratings.com/
It's basically a site where people review all the ~~crap~~ wonderful stuff they buy from infomercials. Cool!

...and for the record I'll come clean. When I was a little kid I talked my mom into buying the Miracle Blade knives from an infomercial. In their defense I STILL have one of them and I still use it in the kitchen despite the fact that I once used it to make speaker stands out of pine planks and doweling rods. I'm not kidding either; I didn't have a saw and it worked like a charm. Who knew?

Time keeps on slipping...

2009-04-23T22:51:00.003-04:00

Where do the days go? I can't help but realize just how far behind I'm getting with regards to SQL and Windows Server :(
I've barely cracked the exciting SQL 2008 features and already I'm getting excited for Kilimanjaro. I haven't even started to learn the ins and outs of Windows 2008 and I'm looking at Windows 7 and drooling.
Then there's the world of BI that I want to get into... yikes!

I'm really looking forward to getting away from the office to attend TechEd this year. I REALLY need to get out of the constant interruptions so that I can focus on learning again. It's sort of like a very geeky "me time" trip ;)
I'm a bit bummed out that Kimbery and Paul won't be presenting this year as I've always really enjoyed their sessions and break-outs. That reminds me... I just stumbled upon the blog of Mr. Dan Jones tonight which is one I'll have to start following. I attended a few of his sessions two years ago and I believe he's also the one who led a Katmai focus group I attended; that was a blast and it's really cool to see our feedback materialized in the form of SQL 2008 SSMS!

Ok... enough work for tonight; Trish's texts indicate she's missing me so I had better call in a quick hello.

Cheers!

Using SSL Certificates for SQL 2005 & 2008

2009-04-05T19:46:00.004-04:00

I'm in the process of going back and editing some of my older posts so that I can link to this blog "without shame" and I noticed a posting about changing the MSX Encrypt Channel option to 0. I don't do that any more. I WANT my SQL servers to have SSL certs and I actively force SSL encryption on all traffic to and from my MSX servers.
So; how does one setup a SQL server to use an SSL cert in a domain when you're running the SQL service with a low-privliged (non-admin) domain user account (you ARE doing that, right?!)?

Simple.

First off, you're going to need to get a copy of winhttpcertcfg. Download that from MS and install it on the SQL host.
Then you'll need to request a cert from your domain CA and install it in the local computer store. I won't cover that process here, but it's not overly difficult.
Ensure you get a cert that can do SSL (webserver certs work 100%).
When you request the cetificate ensure that the name is SQLHOSTNAME.FQDN For clusters use VIRTUALHOSTNAME.FQDN and ensure the private key is marked exportable. Store the certificate in the local computer store.
Once you have the certificate installed you need to grant access to the private key to the SQL service account: winhttpcertcfg.exe -c -g LOCAL_MACHINE\MY -s "SQLHOSTNAME.FQDN" -a "DOMAIN\SQLSERVICE_ACCOUNT"
Once you successfully grant access for the service account use the SQL Server Configuration Tool snap-in, and setup your protocols to use the cert. Then bounce the SQL service. You should see a note in the startup log that says something like "successfully loaded certificate for encryption" and NOT "loaded self-signed certificate for encryption".

The process is a little more involved for clusters (at least Veritas Storage Foundations clusters) and involves exporting the private key, importing it on the other nodes, and editing the registry with the certificate hash value as the SQL config tool mmc won't list the cert in its drop-down because it matches the VIRTUAL name and not the HOST name.

See this KB for all the MMC stuff I was talking about.
See this MSDN article for more info and an annotation from me.
UPDATE: Looks like I blogged about this previously as well!

Boot Disks? What DECADE is this?

2009-03-13T21:14:00.004-04:00

I hate boot disks. I hate floppy disks. I hate the fact that to install Windows on modern hardware I'm forced to use them.
I'll press F6 for a 3rd party RAID driver YOU Microsoft! >:(

Anyways, I found a cool way to transfer boot images quickly using Linux.
Assuming you already have some disk images around you can restore them to the floppy with something like this:

dd if=[image filename] of=/dev/fd0

Now just keep a bunch of those images handy and you're good to go!

Subaru Tech Post! P0457 CEL Problem & Fix

2009-02-18T19:58:00.006-05:00

The P0457 CEL (check engine light) isn't actually documented in my 2002 Subaru WRX factory repair manual. Luckily, Google does list it ;)
The problem is "Evaporative Emissions Control System - [Gross] Leak Detected"
...Sounds bad.

The good news is that it's likely one of two things.
1. Your gas cap isn't on tight enough
-Or if you're like me and just installed a new turbo inlet host (Perrin!) it's probably
2. The EVAP purge hose.

Here is what I'm talking about. Look at the blue nozzle that isn't connected to anything. It's down under the intake manifold to the left of the alternator.

The little hose down and to the right of the blue nozzle is supposed to be connected. I must have yanked it off while changing the intake.

The picture below is what it's supposed to look like when the hose is hooked up properly. If you snapped the connector (I didn't) you'll need to buy a new part.

Cheers!

Getting Rid of The Bonjour Service

2009-02-03T20:33:00.003-05:00

I hate it when applications I do want install things I don't want. A great example of this is the Bonjour Service that Apple and Adobe deploy.

Here's how to get rid of it:

1. Stop the service via either sc, net, or services.msc

2. Delete service:
sc delete "Bonjour Service"

3. Remove the socket driver:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WinSock2\Parameters\NameSpace_Catalog5\Catalog_Entries\00000000000X
Note: X is a number (usually 4) but be sure that the value for LibraryPath shows a path to "mdnsNSP.dll"

4. Change the value Endabled from 1 to 0

5. Reboot

Au'revoir Bonjour!!

Changing the default backup directory for SQL 2005

2009-01-26T16:00:00.002-05:00

I got tired of using regedit to do this (the value is in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\{your instance}\MSSQLServer\BackupDirectory) and write a quick script to do it. I found it really tedious to change this on so many servers and clusters. It'll work with all versions of SQL 2005 and Express AFAIK.

I've posted it for anyone else interested, and be aware that it does use the "undocumented" xp_instance_regread and xp_instance_regwrite extended stored procedures.


DECLARE @SmoDefaultFile nvarchar(512), @NewBackupDir nvarchar(250);
SET @NewBackupDir = N'\\dbs51ykf\SQL_Backups'; --THE NEW BACKUP DIRECTORY TO USE

--LIST THE CURRENT DIRECTORY
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@SmoDefaultFile OUTPUT;

SELECT @SmoDefaultFile as [Old Backup Directory];

--CHANGE THE DIRECTORY
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
REG_SZ,
@NewBackupDir;

--CONFIRM CHANGE
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@SmoDefaultFile OUTPUT;

SELECT @SmoDefaultFile as [New Backup Directory];

Hiding Panels in ASP.NET

2009-01-12T10:14:00.001-05:00

When you're using the AJAX controls with Visual Studio you'll probably eventually make use of the ModalPopupExtender control. I like it.
The one issue is that you'll see it flicker while loading the page unless you add the following to the panel style:

Style="display:none"

This will ensure the panel doesn't show and then disappear during a page load.

Hello Roomba my old friend...

2009-01-11T20:27:00.005-05:00

Ah Roomba. The Roomba has made my life even easier and for that I say thank you. I won't lie; I hate doing "chores". Vacumming (seriously; how do you spell this in Canadian English??) sucks a lot. It takes time, it throws dust all over despite the HEPA filter, and it's just not a task I WANT to allocate some of my finite amount of time to.
So, flush with Christmas cash I bought a Roomba. I couldn't be happier! The Roomba (530 model) does an excellent job of cleaning my apartment (all hardword/laminate and vinyl) and it does it while I lay in bed, play games, surf the 'net, pick my nose... you get the point.
< /roomba promo>

The other reason I'm so satisfied with my Roomba is the fact that I can hack it. I've already wired up an RS232 converter from Maxim and hit Sayal for parts to breadboard that via some Cat5e cable and started writing a C# app (yeah, I've dropped VB.NET... I'm a switcher!) It's working quite well and I've already been able to program my Roomba to clean on a schedule while I'm at work; can the future get any more real?! I've got a freaking robot cleaning my place while I'm at work!! AWESOME!
Anyways, I'll likely be tossing the source code up on something web-ish when it's done, and I'll certainly be helping out my friends to hack their Roombas. Here's a video until I get more done.

I would like to conclude this note by saying: I HAVE BEEN VALIDATED!!!

How To: Setup A Windows 2003 and SQL Server 2005 Veritas Cluster with a Domain CA-Issued Certificate

2008-11-27T22:39:00.003-05:00

One of the potentially most annoying things I've ever had to do with SQL 2005 is setup an instance to use a CA (certificate authority) issued certificate for SSL encryption. SQL 2005 will always encrypt the login traffic, but it'll use a self-signed certificate and you won't be able to "force encryption" without some other certificate being loaded. Since I think 99% of serious SQL installs would be part of a domain the instructions below assume that to be the case.
Here's the imaginary setup that'll be referenced in the steps below:

Windows 2003 x64 R2 SP2 (should be identical for 32bit)
SQL 2005 x64 (Std or Enterprise) SP2 Cumulative Update 9
Veritas Storage Foundations HA 4.3 MP2 is used for clustering
Server is part of a Windows 2003 Domain
The domain has a properly configured CA and certificate requests are approved automatically. CA is CA01.drew.net
Domain name is drew.net (drewnet)
SQL Server service account is a domain account: drewnet\sqlAdminX
SQL server cluster node hostnames are SQL1.drew.net and SQL2.drew.net
SQL server cluster virtual name is SQLCluster.drew.net
Domain account for Veritas Cluster Service is drewnet\sqlCsvc
drewnet\sqlAdminX is granted NOT an admin on any of the cluster nodes
You are an admin on all cluster nodes
SQL has been installed identically on both nodes and the Veritas cluster is working 100% (just without SSL certs for encrypted traffic)
Only one instance of SQL is installed and it is a default installation on port 1433

Ok, now that's out of the way let's get down to business...

Connect to SQL1.drew.net
Connect to the CA request page at http://CA01.drew.net/certsrv
Follow the path to make a custom advanced certificate request. See here for more info.
Request a "Web Server" certificate with a name field of SQLCluster.drew.net, ensure the private key is exportable, and store the certificate in the local computer store.
Start->Run: mmc
Add the Certificate snap-in for the local computer and verify there is a trusted certificate named SQLCluster.drew.net there that has a details tab that indicates "Server Authentication (1.3.6.1.5.5.7.3.1)" in the "Enhanced Usage" section.
Scroll to the bottom of the details and highlight all the text in the "Thumbprint" section. Copy this (ctrl-c).
Start->Run: Notepad
Paste the data you just copied and remove all the spaces. Save this file on your desktop.
Open the Veritas Cluster Manager GUI and connect to the cluster.
Freeze the SQL service group you're working on (temporary)
Download the WinHttpCertCfg.exe utility from Microsoft if you don't already have it.
Install the utility, open a command prompt and navigate to your installation path.
Run the following command:
winhttpcertcfg.exe -g -c LOCAL_MACHINE\MY -s "SQLCluster.drew.net" -a "drewnet\sqlAdminX"
If you got a success message, move on. Otherwise troubleshoot!
Start->Run: regedit
Navigate to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib and open the "Certificate" entry.
Copy & Paste the thumbprint value you saved earlier.
Restart MS SQL Server any way you see fit.
If your server starts properly that's a very good sign. Connect to SQL and view the current log for the line "The certificate was loaded successfully"- You've done it!!
You can now configure the various options like "force encryption" via SQL Server Configuration Manager if you want now.

But that's not all! There's another cluster node to configure!

In the certificates snap in right-click the SQLCluster.drew.net certificate and choose export.
Follow the wizard- be sure to export the private key and assign a password you can remember for a while. Save the .pfx file somewhere easy to get to.
Connect to SQL2.drew.net
Copy the exported .pfx file locally
Start->Run: mmc
Add the local computer certificate snap-in again.
Double-click the certificate copied locally, then choose "Install certificate"
Enter the password you just used when exporting the key.
Verify that the certificate shows up in the personal folder of the local system and that it's valid.
Again, run the winhttpcertcfg command from above on this node.
Back on SQL1.drew.net in the Veritas Cluster Manager GUI Unfreeze the SQL service group.
"Switch" the service group from SQL1.drew.net to SQL2.drew.net
If it starts on the other node you're done! VCS has replicated the registry change you made and the certificate was loaded successfully.
Delete the exported .pfx file and the copy you made.

That's it. There's obviously a lot to modify if you've got more than one node and/or more than one SQL instace (service group) in the cluster but these steps will work 100%. If you're having problems by all means leave a comment and I'll post a response. This was a very tricky thing to figure out how to do as the only thing the MS documentation tells you is "if SQL is configred as a failover cluster use the FQDN of the failover cluster for the certificate name" and "SQL server will attempt to locate a certificate based on the FQDN of the host". Those two things really screw you if you're trying to load your cert via the SQL Configuration Manager tool!

Some important Linux Commands I Always Forget

2008-11-12T20:20:00.007-05:00

This post is really just for myself... I'm learning to use Linux and there's so many commands I need to know that I constantly forget that I thought a quick post would be in order. If this helps anyone else then, great!

Restarting network service:
sudo /etc/init.d/networking restart

Getting Network Configuration (ipconfig):
ifconfig

Stopping or starting a network adapter:
sudo ifconfig [adapter_name] [operation: up/down]

Editing Networking Configuration with vi (set dhcp or static IP):

sudo vi /etc/network/interfaces
(iface eth0 inet dhcp = dhcp on)
(iface eth0 inet static
address 192.168.3.90
gateway 192.168.3.1
netmask 255.255.255.0
network 192.168.3.0
broadcast 192.168.3.255 = setting static IP)

Installing Samba Server for SMB Shares:

sudo apt-get install samba (or just try to share a dir and follow the prompts)
SAMBA conf is in: /etc/samba/smb.conf
Need to add yourself to sambashare group as well

btnx
-Get this to get multi-button mice working like they should!
-after changing settings run this to restart:
$ sudo /etc/init.d/btnx restart

This post has the goods on getting going with it...
This post has the goods on key bindings...

SQL 2005 Agent Error FIX: UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

2008-11-03T17:36:00.003-05:00

Occasionally you may see an error like this and be unable to start the SQL Agent (typically after someone doesn't use the SQL Config GUI to change service accounts)
UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

This is easily fixed.

Fire up regedit, navigate to HKLM\Software\Microsoft\Microsoft SQL Server\[YOUR INSTANCE NUMBER] then right-click and select "Permissions..."
Ensure that the SQLAgent Service and SQL Server Service have full control here and you should be all set. Be sure to use the local groups that SQL 2005 creates for this sort of access delegation to avoid having to do this again when you change service accounts. Note that you wouldn't want to use those groups (and instead use just the service account) if you're locking the permissions down to a point that you want to make it very difficult for an OS admin to get access to your SQL instance.

Cheers!

SQL 2005 "Listen All" TCP/IP Networking Problem Fixed

2008-10-28T10:10:00.002-04:00

I'm one of those DBAs that likes to lock down my SQL servers. This means ensuring that SQL is only listening on the TCP/IP port I want it on.
This is easy enough to configure with the SQL Server Configuration app or by editing the registry... The GUI is "cleaner". Anyways, you'll want to set a static port for the IP enable that IP, disable the others, and then disable "listen all". However, when doing this you may occasionally run into a problem where you get error messages like this when you try to start the SQL service:

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
AND/OR
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

If you know the port is available (netstat is your friend) and the IP address is correct then here's what you need to check:
Is there by any chance more than one 127.0.0.1 entry? Yes? Regedit time!
Go into the registry and navigate to HKLM\Software\Microsoft\Microsoft SQL Server\*INSTANCE NUMBER*\MSSQLServer\SuperSocketNetLib\Tcp\

Look for one of the IPx keys that has 127.0.0.1 in the IpAddress entry and delete it. After that you should be able to start the SQL service with no troubles.

Cheers!

Veritas Cluster Failes to Come Online

2008-10-20T13:45:00.002-04:00

I was working on setting up a new SQL 2005 Veritas cluster today and came upon this error:
Event Type: Error
Event Source: Had
Event Category: None
Event ID: 11306
Date: 10/20/2008
Time: 1:30:40 PM
User: N/A
Computer: XXXXXXXX
Description:
Did not receive cluster membership, manual intervention may be needed for seeding

What does that mean??

It means you need to drop to a command prompt and do this:
gabconfig -x

That'll reseed the control port and should bring the cluster node back into membership. It worked for me and it'll probably work for you too!

Cheers!

Windows Update and Error 0x8DDD0018 -FIX

2008-05-09T15:01:00.002-04:00

I was having a hell of a time trying to get a Windows 2003 R2 Standard x64 server to load the Windows Update site. I was always getting the error: 0x8DDD0018

Sadly, the troubleshooting info at MS focused on starting the BITS service which (seemingly) was working just fine.

Well, I eventually stumbled on the solution. Try this at a command prompt:
regsvr32.exe wuaueng.dll

Presto! Windows Updates should be working for you again :)

64bit (x64 and Itanium) OLEDB provider for ODBC (MSDASQL) Download!

2008-05-09T12:36:00.003-04:00

If you've ever tried to link a 64bit SQL 2005 server to something like Excel or MySQL you know that it just isn't going to happen without MSDASQL .

Well, it's finally out! Get it right from MS here: http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

Here's the description from the MS download:
The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. MSDASQL is an OLEDB provider that connects to ODBC, instead of a database. MSDASQL ships with the Windows operating system, and Windows Server 2008 & Windows Vista SP1 are the first Windows releases to include a 64-bit version of the technology.

Enjoy!!

Need to transfer logins from one SQL 2005 instance to another SQL 2005 instance? Here's how!

2008-04-01T15:28:00.002-04:00

In SQL 2000 there was a DTS package that could do this. I didn't like DTS and never used it, but it is out there. I used the Microsoft-provided sp_help_revlogin stored procedure instead to do this in 2000, but when I looked at the code I realized it would need an update for 2005.
In 2005 the sys.syslogins view exits, but you really shouldn't use it- you should use sys.server_principals (see books online). So, when I looked at that system view I realized this was really going to take some work... so naturally I Googled! ;)

Anyways, I found out that good-ol' MS had already updated the TSQL that generates sp_help_revlogin (and it's required binary stored proc) and posted it online in KB 918992. I copied the code and it worked just like it always had except that I was able to transfer the logins with SIDs and passwords from one 2005 instance to another instead of a 2000 to 2005 or 2000 to 2000 instance. :)

http://support.microsoft.com/kb/918992

Disappearing Symantec Anti Virus Tray Icon (VPTray)

2008-03-20T00:48:00.003-04:00

Maybe you want that stupid yellow shield (I think that's what it is... looks like an old mouse to me!) gone, and maybe you don't.

If you want it back and you can't seem to figure out how to get it back check out the directions here:
Symantec Support

Or just go ahead and try this:
To enable the system tray icon on unmanaged clients

Exit all open programs.
Click Start> Run. The Run dialog box appears.
Type regedit
Click OK. The Registry Editor appears.

Go to the following key:

HKEY_LOCAL_MACHINE\Software\Intel\Landesk\VirusProtect6\CurrentVersion\AdministratorOnly\General
In the right pane, double-click the following value:

ShowVPIcon
Change the value to 1.
Close the Registry Editor.
Restart the computer.

To add the registry value to the Run key

Exit all open programs.
Click Start> Run. The Run dialog box appears.
Type regedit
Click OK. The Registry Editor appears.
Navigate to the following key:

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Run
Click Edit> New> String Value.
Name the value "Vptray" and press Enter.
Double-click Vptray.
Type the path to Vptray.exe, enclosing it in quotation marks, and press Enter.

Windows 98/Me/NT/2000/XP:

"C:\Program Files\Symantec_Client_Security\Symantec AntiVirus\Vptray.exe"
or
"C:\Program Files\Navnt\Vptray.exe"
Close the Registry Editor.
Restart the computer.

Firefox: Goodbye 3rd Party Cookies!

2008-02-16T12:29:00.002-05:00

I love and hate cookies. I love the fact that I can go to certain
"safe" sites and they'll remember certain settings. Google, PageFlakes, Blogger, etc. would be good examples of this.
Then there's the other guys... I don't want everyone and their mother knowing what I browse, or when. To increase my privacy I like to disable 3rd Party cookies, but the DEVs removed the simple click-box to do this in later version of Firefox.
Here's how to add it back:

1. Type in about:config in the address bar and hit enter
2. Edit the value for network.cookie.cookieBehavior and set it to 1.
1 = Accept cookies from this site only, 0 = accept all cookies, 2 = accept no cookies, 3 = use P3P policy

...I also modified the network.cookie.lifetime.days value so that the cookies expire much sooner.
Check out this MozillaZine page for more great info.

IIS 6 ASP.NET Website Permissions Error

2008-02-14T10:21:00.003-05:00

If you're ever trying to get a website to load properly in IIS 6.0 and you keep seeing this:
"Server Application Unavailable
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur. "

In your browser, followed by an event in the application log that starts with this:
"Failed to initialize the AppDomain:/LM/W3SVC/1/Root"

And you're using the Network Service account to run your worker process here's some steps that should get you up and running...
1. Ensure that you've granted the Network Service the "Local Launch" and "Local Activation"permissions. (Component Services -> Computers -> My Computer -> DCOM Config -> IIS Admin Service -Right-Click-> Properties -> Select the Security tab, and set the permissions)

2. Locate the file system directory that you've mapped to the IIS virtual directory and edit the permissions to grant at least Read & Execute, List Folder Contents, and Read to the IIS_WPG account.

Make sure the worker process is running and then try again. With any luck your application will display in the browser properly!

Cheers!

How To: Unlock Protected Excel Worksheet with a Password - Without the Password!

2008-02-11T11:21:00.003-05:00

I went to modify a worksheet today that's used for tracking a few things, but found that I couldn't change the period of the report. The group who was responsible for posting these was behind in doing so and I really wanted to fill out the report before I forgot what I wanted to put in it.
This meant I had to find a way to unprotect the sheet without a password.
Easier done than said!

I opened the workbook, opened up the VB Editor and typed in the following code:
Sub NoProtection()
With ActiveWorkbook
ActiveSheet.Protect Password:="abc"

ActiveSheet.Unprotect Password:="abc"
End With
End Sub

That's it! The previous password is overwritten, then the sheet is unlocked.

...and that's why you never rely on Excel worksheet protection to protect anything.

Disclaimer: This was Excel 2003... Haven't tested if this still works with 2007

Backups, backups, backups!!

2007-12-04T10:38:00.000-05:00

Backups are quite possibly the most important part of a SQL server administration situation, but they're almost always overlooked.
The servers I look after have various different methods for backing up data ranging from local disk backups to UNC NAS storage, and even dedicated SAN LUNS. Pretty soon I'll also start streaming directly to tape for some of the multi-terrabyte backups I'll have on my hands but let's not worry about that.

I've got a few tips for anyone that needs to admin a SQL server:
1. Always make sure you a backup plan that will meet the needs of the business.
2. Use all the backups types available wisely. Transaction log, differential, and full can all be used to create restore plans, but keep in mind recovery time!
3. Always verify your backups! I recommend a script that does at least a checksum check (you are backing up "with checksum" on SQL 2005, right?), and more is better.
4. If possible, use dedicated NICs and VLANs for backup traffic.
For point 4 I setup a static route like this:
ROUTE -p ADD 10.xxx.xxx.xxx MASK 255.255.255.0 10.yyy.yyy.yyy METRIC 10
-xxx IP denotes the target subnet of your backups (i.e. a cifs share on a NetApp or other NAS... be sure to set the proper mask!)
-yyy IP denotes the adapter that'll be used to connect to the share (dedicated NIC)
-p makes it survive reboot

How to force Storage Foundations disk groups to show up as a resource in Microsoft Cluster Service (MSCS)

2007-12-04T08:25:00.000-05:00

I had an issue with an MSCS SQL cluster a while back where the "Volume Manager Disk Group" resource disappeared from the selection drop down! This was not cool as I needed the resource as a dependency of my SQL cluster I was about to setup.

The solution was fairly easy, but most certainly NOT obvious. Here's what you need to do:
Drop to a command prompt and type... (hit enter after each line)
cluster restype "Volume Manager Disk Group" /Delete /TYPE

regsvr32 vxresext.dll

cluster restype "Volume Manager Disk Group" /CREATE /DLL:vxres.dll /TYPE:"Volume Manager Disk Group"

cluster /regext:vxresext.dll

After that's done you should see the resource available in the drop down box. If it matters, this was done on Windows 2003 SP2 R2 x64 Enterprise Edition.

MSX - TSX SQL Server Setup Problems with SQL 2005

2007-12-03T23:02:00.001-05:00

If you can't get your TSX server to enlist then check this out:

I was trying to setup a MSX server in my domain to admin the various (and rapidly growing) SQL 2005 deployments I manage. However, the wizard kept failing with an error message along the lines of "I can't talk to that server". Now I know damn well it can so I was determined to find the problem.
After much searching and screwing around with sp_msx_enlist I finally found the problem! It was a setting in the registry called MsxEncryptChannelOptions
It turns out that the servers were attempting to communicate over SSL but there were no certs for them. Changing the default value from 2 to 0 took care of my problems. I suggest that MS gets off their ass and fixes the TERRIBLE error message you get to actually reflect the fact that it's an SSL communication error.
More info about the setting here

P.S. If you see secure channel errors in your system log while screwing with all this you need to get your certs sorted or change the setting on each instance to 0.

UPDATE:
I've added more information here about SSL Encryption. I can't honestly recommend that people don't use it with MSX servers as you may have info going across that connection you don't want people snooping.
See my post here.

Can't get VPN to work? Try this!

2007-12-03T22:22:00.000-05:00

I was attempting to connect to a friends test domain across the internet via a PPTP VPN he setup for me. The connection was being made quickly, and I got an IP from the connection, but I couldn't ping anything!
It was a little frustrating until I did a route print. Once I did that I noticed that my default gateway was being reset to the VPN connection IP.

To fix this I went to Network Connections and selected the properties of the VPN connection. Then:
-go to the networking tab
-select tcp/ip and then properties
-click the "Advanced" button
-remove the check mark that says "Use default gateway on remote network"
That's it!

When I connected the next time the route print looked much better and the connection worked properly and I was able to access both the remote VPN-connected network and it's resources as well as the internet through my local connection. Easy fix, but not totally obvious.

Adding Dynamic Disk from Storage Foundations (VSFW) and Microsoft Cluster Service (MSCS) in Windows 2003 R2 x64

2007-12-03T21:44:00.002-05:00

I recently had to fight with getting some LUNS configured and presented as dynamic disks to a SQL 2005 x64 (SP 2 Build 3159) cluster running on Windows 2004 x64 R2 SP2. It wasn't as easy as I thought it would be.
The first problem is that the quorum disk can NOT be a dynamic disk. So if your storage guys are presenting 100GB LUNS get ready to waste a lot of expensive SAN disk. If you want to run in a config that's "not supported by Microsoft" you can partition the LUN that you'll use for the quorum such that the quorum parition gets only a small percentage of the disk, and then you can use the rest for something else like logs, backups, SQL binaries, etc.

Here's the steps to getting the dynamic disks in the cluster:
1. Configure your dynamic disk group(s) any way that suits your needs but be sure to create them as a cluster disk group or you're going to fail the next part.
2. Add a "Volume Manager Dynamic Disk" resource top your SQL cluster group.
-If you created the disk groups as cluster resources then you should see them in a drop down menu at the end of the configuration for this resource. If you don't or don't even see the Volume Manager resource then you need to do this fix.
NOTE: When you add the resource the disks will go offline and then back online. Be sure you're not doing anything with them when you do this!

You now have a SQL cluster resource with dynamic disks that's ready to have SQL installed, or if it's already installed then you can migrate your db files as needed.

Dynamic disks are the way to go as you can easily grow volumes, extend stripes, add mirrors, etc. I never use basic disks when I connect a SQL server to a SAN.

Random note:
I like to setup at least one LUN for logs (ldf) and at least one more for data files (mdf)... If the server is going to be under enough load I'll make sure tempdb is on it's own LUNs as well. (System Center Operations Manager is an example of that config).

UPDATE:
See this post for a potential resolution with VCS disks not showing up.

Funny and true; a rare combination.

2007-02-09T22:36:00.003-05:00

This link says it all:
http://techdigest.tv/pcmaclinux.jpg

I admit I'm typing this from an installation of Ubuntu 6.10 ("windows fan boy linux") that's rocking beryl but really... Do you know how long I spent trying to get this to run on my dual-21" LCD's at work?? Oh- I could get dual monitor working (with fglrx, open source ati, AND binary ati drivers!) but I could never get it working with beryl (beryl with xgl... it worked with aiglx I think the name was??).
Surprisingly my xp desktop does it just fine.

Funnier still is the the fact that my p-to-v'd (I even spelled it phonetically; deal!) xp desktop image loaded faster as a vm ALONG WITH my ubuntu desktop than my xp desktop can dual booting directly into it!!

Draw your own conclusions. My statement will always be this: 'Why should we worry about hardware to run an OS?' (yeah- I just quoted myself!) The OS manages access to hardware and it should be more integrated and streamlined as time goes on- NOT more bloated and resource intensive.

Update: Newer Ubuntu versions load slower. I have Vista installed on a PC. I'm eagerly awaiting running Windows 2008 as a "workstation".
I also added the picture linked to above in case it disappears.

How to Upgrade SQL 2005 x64 from SP1 to build 2153 (KB918222)

2007-02-06T23:32:00.000-05:00

If you don't know what this topic means skip the rest of this post. If however you're here because you're trying to upgrade from 2047 (SP1) to the latest hotfix -build 2153- and you're getting nothing but "Failed" then this is for you.

First things first; read this again:
http://support.microsoft.com/kb/918222

Do things in the order they suggest because why not? Why fight with things for the sake of fighting with them? Oh what's that- you installed the client patch first? Heh... me too. It doesn't really matter. ;)

So anyways, I'm sure you want to know the fix so here goes.
First, figure out where your mssqlsystemresource and distmdl log and data files are. If they're all in the same folder as your master db then you've got a problem that I can't fix with this post. But if you (like me) got this crazy idea somewhere that logs and data should be kept on separate volumes and you happen to do that for system dbs as well because oh, I don't know; you like order, then I may just have the asdfnswer you're looking for.
This problem may or may not be a 2-step fix for you.
First, look at where your master.mdf is- are distmdl.mdf and distmdl.ldf there? No? Copy them there! Try it again- if the upgrade works you're done.

The other problem you may run into is the upgrade of the mssqlsystemresource db. If the .mdf and .ldf files are in different dirs (or more likely; volumes) then you'll have to copy the newer log (.ldf) version that it dumps in the same dir as your mssqlsystemresource.mdf over to the correct location before the upgrade will work. Of course your server needs to be down for this to happen. FYI: the newer file has a date with a timestamp of around 12:13am... I can't remember the exact date but the older version has a timestamp around 3-something am. If you don't normally keep the mssqlsystemresource.ldf file in the same dir as your mdf then you shouldn't have even read this far- go upgrade your system!!

And just in case these didn't help you here's some more advice:
You can find most problems by checking: %windir%\hotfix\sql9\SQL9_Hotfix_KB918222_sqlrun_sql.msp.log
For "value 3"... then look for something about failing to access a file (note the location) and/or any other messages that may help you out. One stupid thing is that the error will state the installer doesn't have access to a file when in reality the file isn't there. Use your head and check it out before assuming it's a permissions issue.

Fixing MOM 2005 MOMXParitioning Job Problems

2007-02-06T23:30:00.001-05:00

No, this post is not about how to spay or neutor your mother. It's about Microsoft Operations Manager and one piece of troubleshooting that will save the day for you when the daily partitioning and grooming job fails... and it WILL fail.

Here's how this one goes. There's an error that popped up with a MOM 2005 database I manage where the groom job would continuously fail with something like 'drop failed FK_EventParam18 is not a constraint'... Awesome. At this point your OnePoint database is going to keep on growing forever and ever because it can't purge old data. Too bad that it's no longer supported by MS when it hits >30gb. The solution? Fix the problem yourself of course!

I hunted around the internet for help with this one but was left with nothing. Finally I started stepping through the code of each stored procedure as it would have been executed to find what was being called and when.
I got tired of that and threw up a trace.
I eventually narrowed it down to the fact that it really wanted to drop a constraint named in the error on a table of a similar name. Now of course I'm sure there's a table somewhere that holds what to drop and what not to drop but I really didn't feel like editing that so I took the easy way out: I created a constraint with the required name on the proper table and BAM! It worked.
What was the constraint you ask? heh... 1=1 ;)

Hopefully some other DBA out there (or someone else stuck looking after a bloated and mis-managed MOM 2k5 deployment) will see this and be saved a lot of investigative work. (Legal self-help: I am not making any claim that this will work for you and/or won't harm your database. Use this advice at your own risk!)

Oh- and the other thing I did to purge my db (which was 130gb!)? I modded the sp_purge (or whatever it's called) to be able to groom and purge starting at 125 days rather than the 60 days it maxes out at via the GUI. This allowed me to purge data and shrink the db in a controlled fashion that wouldn't blast the storage system with IOPS it couldn't hope to handle.