#0225-SQL Server 2012-Deprecated Features-CREATE LOGIN – 16-byte password hash values: Msg 15021


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:



Other posts on SQL Server 2012 Deprecated & Discontinued features:


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



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”]

Advertisements

2 thoughts on “#0225-SQL Server 2012-Deprecated Features-CREATE LOGIN – 16-byte password hash values: Msg 15021

  1. dishdy

    Nakul,

    I don’t understand Option A. Who is the ‘vendor’? What is meant by ‘new password hash’?

    You refer to 16-bit hash values. I think you mean 16-byte hash values.

    I suppose all this is simply due to the fact the hash algorithm has changed.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s