.Net version of SQL Server PWDCompare

One of my customers Hedgebook 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();
      }
   }
}

Microsoft Sync Framework timezones

Over the last few months I have been working with the Microsoft Sync Framework and the time zone issues have been a problem.

New Zealand has a 12hr standard time or 13 hr daylight savings time offset from Coordinated Universal Time (UTC) and at a glance our customer data could look ok if treated as either local or UTC.

After some experimentation I found that it was due to Windows Communication Foundation(WCF) serialisation issues (The proposed solutions looks like it might have some limitations, especially across daylight savings time transitions).

For the initial synchronisation DateTime values in the database were unchanged, but for any later incremental synchronisations the DateTime values were adjusted to the timezone of the server (Our Azure Cloud Services are UTC timezone, though I don’t understand why Microsoft by default has them set to US locale with MM/DD/YY date formats)

In our scenario having all of the DateTime values in the cloud local looked like a reasonable option and this article provided some useful insights.

In the end I found that setting the DateSetDateTime  for every DateTime column in each DataTable in the synchronisation DataSet to unspecified in the ProcessChangeBatch (our code was based on the samples) method meant that no adjustment was applied to the incremental updates

public override void ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics)
{
try
{
DbSyncContext context = changeDataRetriever as DbSyncContext;

if (context != null)
{
foreach (DataTable table in context.DataSet.Tables)
{
foreach (DataColumn column in table.Columns)
{
// Switching from UnspecifiedLocal to Unspecified is allowed even after the DataSet has rows.
if ((column.DataType == typeof(DateTime)) && (column.DateTimeMode == DataSetDateTime.UnspecifiedLocal))
{
column.DateTimeMode = DataSetDateTime.Unspecified;
}
}
}
...

Hope this helps someone else