.Net version of SQL Server PWDCompare

One of my customers has a Microsoft SQL Server database with passwords that have been secured using PWDENCRYPT and PWDCOMPARE As part of a migration plan we need to be able to validate passwords against hashes that have been generated with older versions of Microsoft SQL Server.

After some searching I found a stackoverflow post which described how to validate hashes up to SQL Server 2012 and I have added code to support more modern versions of SQL Server.

I had a chat with my boss and he approved me posting a console application wrapper for an anonymised version of the code as an aide to other developers.

This sample code is not production ready it is just to illustrate how the password hashes for older and newer versions of SQL Server can be validated in C#

//---------------------------------------------------------------------------------
// Copyright ® Feb 2018, devMobile Software
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
// based on https://stackoverflow.com/questions/43879003/how-can-i-manually-check-passwords-hashed-with-sql-server-pwdencrypt-in-c-sharp
//
// Have added implementation for more modern SQL Server boxes and built as a console application
//---------------------------------------------------------------------------------
namespace devMobile.SqlServer.PWDCompareDemo
{
   using System;
   using System.Data.SqlClient;
   using System.Linq;
   using System.Security.Cryptography;
   using System.Text;

   public class Program
   {
      private const int DatabasePasswordHashLength = 256;
      private const int HeaderLength = 2;
      private const int SaltLength = 4;
      private const int Sha1HashLength = 20;
      private const int Sha512HashLength = 64;

      public static void Main(string[] args)
      {
         if (args.Length != 3)
         {
            Console.WriteLine("Expecting ConnectionString UserID Password");
            Console.WriteLine("Press ");
            Console.ReadLine();
            return;
         }

         string connectionString = args[0];
         string userId = args[1];
         string password = args[2];

         using (SqlConnection conn = new SqlConnection(connectionString))
         {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand("SELECT Password FROM Users WHERE UserID=@UserID", conn))
            {
               cmd.Parameters.AddWithValue("@UserID", userId);

               using (SqlDataReader reader = cmd.ExecuteReader())
               {
                  if (reader.Read())
                  {
                     byte[] databasePasswordHash = new byte[DatabasePasswordHashLength];
                     reader.GetBytes(0, 0, databasePasswordHash, 0, databasePasswordHash.Length);

                     int header = BitConverter.ToChar(databasePasswordHash, 0);
                     byte[] salt = new byte[SaltLength];
                     Buffer.BlockCopy(databasePasswordHash, HeaderLength, salt, 0, salt.Length);

                     switch (header)
                     {
                        case 1: //SHA1 encryption for old SQL Server
                           byte[] sha1Hash = new byte[Sha1HashLength];
                           Buffer.BlockCopy(databasePasswordHash, HeaderLength + SaltLength, sha1Hash, 0, sha1Hash.Length);

                           HashAlgorithm sha1Hasher = SHA1.Create();
                           byte[] sha1Result = sha1Hasher.ComputeHash(Encoding.Unicode.GetBytes(password + Encoding.Unicode.GetString(salt)));
                           if (sha1Hash.SequenceEqual(sha1Result))
                           {
                              Console.WriteLine("SHA1 Password is good");
                           }
                           else
                           {
                              Console.WriteLine("SHA1 Password is bad");
                           }

                           break;

                        case 2: //SHA2-512 encryption for modern SQL Server
                           byte[] sha512Hash = new byte[Sha512HashLength];
                           Buffer.BlockCopy(databasePasswordHash, HeaderLength + SaltLength, sha512Hash, 0, sha512Hash.Length);

                           HashAlgorithm sha512Hasher = SHA512.Create();
                           byte[] sha512Result = sha512Hasher.ComputeHash(Encoding.Unicode.GetBytes(password + Encoding.Unicode.GetString(salt)));
                           if (sha512Hash.SequenceEqual(sha512Result))
                           {
                              Console.WriteLine("SHA512 Password is good");
                           }
                           else
                           {
                              Console.WriteLine("SHA512 Password is bad");
                           }

                           break;

                        default:
                           Console.WriteLine("Unknown header value something bad has happened");
                           break;
                     }
                  }
               }
            }
         }

         Console.WriteLine("Press ");
         Console.ReadLine();
      }
   }
}

Azure IoT Hub nRF24L01 Windows 10 IoT Core Field Gateway

This project is now live on Hackster.IO and github.com with sample *duino, Devduino and Netduino clients. While building the AdaFruit.IO field gateway, Azure IOT Hub field gateways and sample clients I changed the structure of the message payload and spent a bit of time removing non-core functionality and code.

The diagnostics logging code was refactored several times and after reading this reference on docs.Microsoft.com I settled on the published approach.

I considered using the built in Universal Windows Platform (UWP) application data class but this would have made configuration in the field hard for most of the targeted users school students & IT departments.

I have the application running at my house and it has proved pretty robust, last week I though it had crashed because the telemetry data stopped for about 20 minutes. I had a look at the Device portal and it was because Windows 10 IoT core had downloaded some updates, applied them and then rebooted automatically (as configured).

I put a socket on the Raspberry PI nRF24L01 Shield rather than soldering the module to the board so that I could compare the performance of the Low and High power modules. The antenna end of the high power module tends to droop so I put a small piece of plastic foam underneath to prop them up.

I had code to generate an empty JSON configuration but I removed that as it added complexity compared to putting a sample in the github repository.

I considered using a binary format (the nRF24L01 max message length is 32 bytes) but the code required to make it sufficiently flexible rapidly got out of hand and as most of my devices didn’t have a lot of sensors (battery/solar powered *duinos) and it wasn’t a major hassle to send another message so I removed it.

I need to tidy up the project and remove the unused Visual Assets and have a look at the automated update support.

Wireless field gateway protocol V2

I have now built a couple of nRF2L01P field gateways (for AdaFriut.IO & Azure IoT Hubs) which run as a background tasks on Windows 10 IoT Core on RaspberyPI). I have also written several clients which run on Arduino, devDuino, Netduino, and Seeeduino devices.

I have tried to keep the protocol simple (telemetry only) to deploy and it will be used in high school student projects in the next couple of weeks.

To make the payload smaller the first byte of the message now specifies the message type in the top nibble and the length of the device unique identifier in the bottom nibble.

0 = Echo

The message is displayed by the field gateway as text & hexadecimal.

1 = Device identifier + Comma separated values (CSV) payload

[0] – Set to 0001, XXXX   Device identifier length

[1]..[1+Device identifier length] – Unique device identifier bytes e.g. Mac address

[1+Device identifier length+1 ]..[31] – CSV payload e.g.  SensorID value, SensorID value