SQL 2012 ORDER BY list of RANGE window frame cannot contain expressions of LOB type

I ran into this error when preparing some data for a report in SQL Server Reporting Services 2012. I was trying to partition data showing construction progress for work areas by week number using the updated ORDER BY clause for Windowing functions with SQL Server 2012.

This is the query:
SELECT Area, SubArea,
COUNT(SubArea) OVER
(PARTITION BY MAX(DATEPART(wk, PlannedComplete))
ORDER BY SubArea)

And this is the error I was getting:

“ORDER BY list of RANGE window frame cannot contain expressions of LOB type.”

Let’s break down the error message.

ORDER BY list
Something is wrong with the ORDER BY
RANGE window frame
Identifies the section after the Partition command
cannot contain expressions of LOB type
LOB or Large Object are Column definitions inc. Text, varchar(MAX), Image etc

Answer:
In this case it turned out that the application developers had specified that the SubArea column specification type was varchar(MAX)

Changing the SubArea column type to a more appropriate varchar(30) (Max length submission from the application is 30) resolves the issue.

Searching online for the exact error failed to bring up anything immediately useful to me so I’m putting this here for my reference, I hope it helps.

Posted in Microsoft, SQL Server | 2 Comments

Password management with KeyPass

Password management sounds really dull, and to be honest it is; right up until you have a password disaster or security breach!  In the recent past people had just a few passwords perhaps an email password and maybe one or two online shop passwords and their account password.  Most people can remember four or five different passwords, especially if they use them regularly during the week.

The problem comes when you require more and more passwords for online services.  This can lead to slipping into bad password habits.

Bad password habits

1.    Password re-use.
2.    Short passwords.
3.    Dictionary passwords.
4.    Never changing passwords.
5.    Storing passwords in an insecure manner.

Password disaster scenarios

Friend posts as you on Facebook after reading passwords.txt on your laptop
Hopefully not such a big deal as you gave them your laptop to use, but it could have been worse if someone who wasn’t your friend found the file, like password hunting malware…

Home Wi-Fi compromised after short dictionary password brute forced
Having a weak Wi-Fi password can open your connection up to all sorts of misuse, all in your name!  Routers generally don’t block incorrect attempts to register a new client so make good targets for huge dictionary attacks…

Site hacked, password stolen and email account wiped using same password
News stories about big sites such as ABC Australia, Sony PS3 network, LinkedIn, eHarmony, Yahoo and Gawker getting hacked is worrying regular.  If you use the same password across all sites then once your account at one site is compromised then ALL your online accounts are compromised, it’s just a matter of when they are accessed.  Plus given the attackers will have your email address what’s the first thing they are going to try?  If you only do one thing, please, change your email password to be something unique.

KeePass offers a solution

KeePass is a digital password safe, it’s not the only one of its kind but it is free and open source.  Open source is good because it’s open for public scrutiny of the code and the implementation of security features and does not follow the danger of security via obscurity.

Securely storing your passwords inside KeePass you can overcome bad password habits.

1.    Unique passwords for every site / service.
2.    Longer passwords with higher entropy.
3.    Non dictionary passwords (You don’t need to remember them).
4.    Expiring passwords requiring you to change them.
5.    Stored inside a secure container with 2 step authentication.

Securing KeePass with multi factor authentication

Given that KeePass now holds the details to your digital life online I recommend using its multi factor authentication methods similar to Google’s 2-step verification.
Your options for securing your KeePass vault are a mix of:

  • A Master Password. (Hopefully the only one you really need to remember!)
  • A Key file.
  • A Windows User account.

Personally I would recommend using both a Master Password and a Key file.  Make sure you use a strong master password that is of a suitable length and choose a suitable key file that contains lots of random data.  Details on the choices can be found on the KeePass site here: http://KeePass.info/help/base/keys.html

By separating your Key file on a USB flash drive from the KeePass database file you create an additional action that an attacker must achieve to gain access to your passwords.  Given that you will keep your USB flash drive containing the Key file offline, in a safe environment, it is extremely unlikely that an attacker online could guess / re-create or otherwise obtain your Key file AND guess your password.

Multi factor authentication does require an additional step from you to access your passwords but adds massive increases the difficulty for attackers.  Here is a diagram explaining how best to employ multi factor authentication easily for day-to-day life.

KeePass multi factor authentication

Backing up your KeePass Password and Key

Now you have created a valuable KeePass resource to assist you with better password practices you need to keep a backup of both the KeePass database and the keys you use to access it.

  • KeePass database file – Keep an up to date copy in SkyDrive / Google Drive and as part of your larger backup set.
  • Master Password – Record it somewhere safe offline, if you really must.
  • Key file – Use another, separate, USB flash drive, or as part of an encrypted backup.

Never, ever store your ‘Key’ file in the same location as your KeePass database file!  This effectively nullifies the security benefit of having multi factor authentication

Good password management is the start of better security

Right now go download KeePass, setup your Key file and Master Password then next time you log into a service change your password and store the details in KeePass.  Before long you’ll have updated all your passwords and be much more secure online as a result.

Take a minute to explore the other features of KeePass like password generation, set expiry dates for passwords to remind you to change them and make regular backups of your KeePass file; you can even sync to your backup KeePass file via the App.

Posted in Security | Leave a comment