The Rinnai Control-R WiFi Module Sort of Sucks (and here's how to make it work)

At home we recently switched to a tankless water heater and we went all out on a fancy Rinnai RUR199IN condensing tankless heater with Control-R WiFi module.

The unit itself is great! Easy install, works amazingly, fantastic instructions, etc. The Control-R WiFi module though? WOW - that's some trash!

The main problem I had was getting the damn thing to stay on the WiFi network. I ended up with a blinking or solid purple light every time. I tried everything and resigned myself to the fact that I'd probably have to call support when I tried one last thing - and it worked.

Here's what didn't work:

• Shouting and cursing
• Searching for other solutions online
• Disabling my Pi-Hole
  
• Changing network settings, checking firewall ports
• Allowing full access to my network
• Creating a dedicated, unauthenticated 2.4GHz WiFi network to test
• Praying to jeebus
• Resetting the module like 100x
  

Here's what did work:

 I created a new 2.4GHz WiFi network (I'm using TP-Link APs with their EAP Controller software), applied the same policy I apply for all IoT-only networks which allows internet, but no local LAN access (other than the pi-hole DNS server), and selected "TKIP" for encryption rather than "auto" or "AES". THAT WAS IT!

Seriously - there's obviously a bug in the WiFi module that can't actually use AES encryption and it needs to be forced to a TKIP-only system. Annoying, but given that this will be the only thing on this network in my house and I can now restrict that network to just that MAC address, I'll consider it 'safe enough' to not really care about. ...frustrating though!

TL;DR

The Control-R WiFi module only works with TKIP encryption. It's stupid and Rinnai should feel bad about this.

I recommend putting it on a dedicated 2.4GHz network as I noticed they have an 'alternate setup page' that uses http (yeah, no TLS) and it asks for your WiFi password. Clearly, security isn't a priority for Rinnai :-S

Resolving "Key not valid for use in specified state" Error When Importing Registered Servers In SSMS

I love SQL Server Management Studio (SSMS). Is it the best tool out there? Debatable. But when you add SSMS Tools Pack and SQL Sentry Plan Explorer it's pretty hard to beat IMO. It's also usually pretty stable and reliable for me and since I spend most of my day with it open I can't complain. ...but then I re-imaged my work box.
It was time to upgrade to the latest OS and Visual Studio editions at work so why not start over clean, right? Right.
After backing up everything I needed I was off and running and re-loading all my beloved tools when it came time to import my old registered servers list. I thought I was clever; I backed it up "with login and password info" to make life easier. All was well until I imported the file and got the dreaded error message: "Key not valid for use in specified state"
Oh. No.

Of course, the servers then showed up but on closing and re-opening SSMS (2008 R2) my registered server list was blank. CRAP!

Luckily I knew where this info was stored and I navigated to %appdata%\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml
Upon opening the file in Notepad++ I jumped found the servers that had been setup with SQL Logins and started modifying...

1. I removed the encrypted password and uid=xxx bits from the "ConnectionStringWithEncryptedPassword" element.
2. I added "trusted_connection=true;" to the connection string modified in step 1
3. I changed "PersistLoginNameAndPassword" to "PersistLoginName" in the "CredentialPersistenceType" element.
4. I saved the file and crossed my fingers...

Success! I opened SSMS and had no error. My registered server list was intact and all was right with the world. Of course, the login info for a few servers will need to be corrected, but that's much less work than re-registering a pile of servers.

I hope this info will help others in the same boat :)

-A

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

I managed to create and then fix my own PEBKAC 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

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

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

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 one can reasonably put out. 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

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

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

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 "XYZSERVER01" 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

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)

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

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

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

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

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."

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.

1. Have the user lock their computer and login again.
2. 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

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

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!

STi Intercooler Waterspray Into 2002 WRX Wagon

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.

1. Tap the existing wires for the illumination bulb & wire into the switch.
2. Wire up your relay to be +12v switched on.
3. Run a ground to the IC spray switch.
4. Run the output of the switch to the trigger of your 555 timer circuit (must be ground!)
5. Run power and ground to the 555 timer circuit. Use the same switched power source and ground as the relay and switch.
6. Run the output of the 555 circuit to the +12v switch of the relay.
7. Run the "on" output of the relay (should be ground when "on") to the IC spray pump.
8. 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!

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

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!

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