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:
CREATE LOGIN SQL07
WITH PASSWORD = 0x2131214A212F26285628293328374839 HASHED,
DEFAULT_DATABASE = master;
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.
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.”
The resolution to this issue can be one of the following:
- 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
- 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”]
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.
@dishdy: Thanks for drawing my attention. Edits have been made accordingly.