Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.
Although it’s rotten practice, values like dates, times, numbers, etc are often stored in columns with the wrong data type. Sometimes, it’s because the data model needs to allow for multiple types of data in the same column (sometimes even on the same row in the form of a formula or text syntax), but sometimes, it’s just plain and simple lazy.
With this type of setup, it’s only a matter of time before you run into conversion errors like this one:
Msg 245, Level 16, State 1, Line 29 Conversion failed when converting the varchar value 'xyz' to data type int.
To make things worse, these errors can be tricky to pinpoint, because they can appear to come and go without any real recognizable pattern. In this post, I’ll take a look at how this happens, how to look for these errors, and ultimately how to fix them if you can’t change the database schema.