title: How to Generate Matching MD5 Hash: SQL Server nvarchar Field vs. ColdFusion
body: <p>I am trying to figure out how to generate a matching MD 5 hash value between SQL Server and ColdFusion. The root cause appears to be that the SQL Server field is an <code>nvarchar</code> datatype, which seems to mean I need to do something with the encoding of the string I would hash in ColdFusion or Java to make it match, but I am unable to figure it out. To be clear if this was a SQL Server varchar field, everything works. </p>
  
  <p>Here's the code I'm trying:</p>
  
  <pre><code>&lt;cfset stringToHash = "Hello world!"&gt;
  
  &lt;cfquery name="sqlserver" datasource="#mySqlServerDSN#"&gt;
      SELECT RIGHT( 
      master.dbo.fn_varbintohexstr( 
          HashBytes( 
              'MD5', 
              CAST(&lt;cfqueryparam value="#stringToHash#" cfsqltype="cf_sql_varchar"&gt;  AS nvarchar(max))      
          ) 
      ) 
     , 32) AS HASHED
  &lt;/cfquery&gt;
  
  &lt;cfoutput&gt;
  &lt;pre&gt;
  CF UFT-8:   #hash(stringToHash, 'MD5', 'UTF-8')#
  CF UFT-16:  #hash(stringToHash, 'MD5', 'UTF-16')#
  SQL Server: #sqlserver.hashed#
  &lt;/pre&gt;
  &lt;/cfoutput&gt;
  </code></pre>
  
  <p>Produces </p>
  
  <pre><code>CF UTF-8:   86FB269D190D2C85F6E0468CECA42A20
  CF UTF-16:  0C89A9720D83539E3723BB99C07D069F
  SQL Server: f9a6119c6ec37ce652960382f8b59f2c
  </code></pre>
  
  <p>So I'm guessing I need to change the final argument I'm passing to <code>hash()</code> to be a different encoding, but I can't figure it out. I've also tagged this question as Java too, because I'm more than happy to take an answer in that language as well. </p>