Today’s post is based on a small finding that we made when working with moving a legacy database over to SQL Server 2012. All was going good untill the configuration started. As we were moving the SQL logins from one SQL Server instance to another, creation of the logins failed with a weird error.

The legacy application comes with a script that creates the required logins using hashed password values, similar to the script below:

USE master;

GO

CREATE LOGIN SQL07

WITH PASSWORD = 0x2131214A212F26285628293328374839 HASHED,

DEFAULT_DATABASE = master;

GO

When we ran this script against SQL Server 2012, it raised the following error:

Msg 15021, Level 16, State 2, Line 1
Invalid value given for parameter PASSWORD. Specify a valid parameter value.

Root Cause

The reason behind the error is that SQL Server 2012 no longer supports passwords that use 16-byte hash values. 16-byte hash values were used when passwords were created in SQL Server 7.0 and below (gives you an idea of how old the legacy systems currently in production are!). According to Books-On-Line: “The HASHED option cannot be used with hashes created by SQL Server 7 or earlier.”

Resolution

The resolution to this issue can be one of the following:

1. Option A: Work with the vendor to get an update to the product (recommended), or at least a new password hash




• “Vendor” here refers to the 3rd party team which owns and maintains the product
  
• “New” password hash can be created by recreating the login using the plain-text password in a newer version of SQL Server


2. Option B: If you are aware of the password, you can create a new login using the plain-text password with the CHECK_POLICY clause set to OFF

Reference:

• CREATE LOGIN: http://msdn.microsoft.com/en-us/library/ms189751.aspx

Other posts on SQL Server 2012 Deprecated & Discontinued features:

Here is a recap of my posts on SQL Server 2012 deprecated and discontinued features:

• Backup & Restore database/log with password–Msg: 3032
  
• Valid compatibility levels–COMPATIBILITY_LEVEL 80 support-Msg 15048
  
• DATABASEPROPERTY replaced by DATABASEPROPERTYEX
  
• Returning result sets from triggers
  
• 32-bit systems – AWE (Address Windowing Extensions) no longer supported
  
• CREATE TRIGGER-WITH APPEND option
  
• Modify database options with sp_dboption–Msg 2812
  
• Table Hint: FASTFIRSTROW-Msg: 321
  
• sp_addserver-Remote server registration-Msg: 15663
  
• Use of OUTER operators – *= and =*; Msg: 4147
  
• Column Alias defined by a string enclosed in quotation marks
  
• sp_describe_first_result_set – replacement to FMTONLY

Until we meet next time,

Be courteous. Drive responsibly.

[EDIT: December 24, 2012; 1650hrs IST: Corrected encryption type for SQL 7 logins from 16-bit to 16-byte, described terms “vendor” and “password hash”]