A Security Nugget

Posted on by Oliver Asmus in Security | Leave a comment

Security is always a massive topic to discuss within the SQL Server realm.  There a literally hundreds of articles, books, and webinars on the subject and each medium gets you deeper and deeper into the SQL security underground.  Many of these outlets provide detailed information on SQL Server’s wide array of security mechanisms (i.e. certificates, keys, TDE, and so on).  However, over the last couple of weeks I found myself in need of something more development/programming based than what is traditionally delivered in SQL Server Enterprise edition, for example.  To that end, I had to do some digging.

For my task, I had to create an encrypted identifier.  Programmatically, this is something that you usually do in a Comp Sci 101 with a random number generator library.  However, outside of the classroom nothing is that straight forward.  In the past, I would just take a GUID, cast it as a string, and store it in an “encrypted” column.

Simple and effective, right?  It would seem so in databases which are not internet facing.  The NEWID() function, which uses a combination of the server’s network MAC address and the system date-time stamp, would ensure no duplicates exist and return a long, complicated-looking series of letters and numbers.  Problem solved, you say?   Not for me.

My main issue with the above solution is that although the result may “appear” encrypted, it most certainly is not.  It really is nothing more than a reasonable facsimile of what an encryption output could look like.  I wanted something that was truly encrypted and could tie back to the original data in some foreign way.

To meet this challenge, I began looking at the simple HASHBYTES() function which is a standard function within SQL Server.  Given an encryption algorithm (MD5, SHA1, etc.) and an input, it will encrypt your input using the requested algorithm and return the encrypted result.

This would seem to solve all my problems with one big exception: the output was in varbinary – I need it in varchar.  A simple CAST or CONVERT proves ineffective as there is no explicit conversion for binary objects.  Seems like I’m going back to the drawing board, right?  Not quite!

Remember the digging I was doing earlier?  Well I uncovered a little, relatively unknown system function that is delivered in the SQL Server master database that you can use to convert varbinary objects to string objects:

sys.fn_varbintohexstr

I thought I hit the lottery!  After doing a quick test:

EUREKA!! Not only did this solution use an expressly designated encryption algorithm, but it also converted the resultant binary object into a string object to allow me to insert it into a string field.  Finding these atypical, odd functions is something akin to finding a $20 bill in the pocket of a pair of pants you just washed – priceless.

P.S. – There is also a sys.fn_varbintohexsubstring function that does the exact same thing as the one above except it will substring the output.

Goals for 2015

Posted on by Oliver Asmus in Personal | Leave a comment

I’m not too keen on setting “resolutions” because it never really made sense to me.  Since I work in an environment that is heavily goal-oriented, I thought it best to set goals for 2015 instead.  This list is not all encompassing, but putting a small list out here might keep me better accountable for sticking to them.

So for 2015, here are my goals:

  • Contribute more to my local SQL Server and Database communities (NY/NJ)
  • Put more into my website (If you are a longtime follower, you will notice I’ve started over)
  • Earn some IT credentials
  • Get married!

Happy New Years!  Here’s to a great 2015!

Getting Started!

Posted on by Oliver Asmus in Personal | Leave a comment

Hi there!

Thanks for checking out the site.  Still getting things together, so check back later for more content.