Best way to create a login with hashed password?

Question

What is the correct way to create a login with a hashed password because .

I've tried the method explained in https://msdn.microsoft.com/en-us/library/ms189751.aspx but it dosn't work.

Query1

CREATE LOGIN administratori WITH hashed_password = '12345'
,DEFAULT_DATABASE = MDAfondation
,DEFAULT_LANGUAGE = english
,CHECK_EXPIRATION = OFF
GO 

If somebody could tell me how to write the hashed password part.

Should i do it like this , and if i do it like this when i authenticate (login) how do i tell sql it should hash the password first.

CREATE LOGIN administratori WITH PASSWORD = (HASHBYTES('SHA2_512','12345'))
,DEFAULT_DATABASE = MDAfondation
,DEFAULT_LANGUAGE = english
,CHECK_EXPIRATION = OFF
GO 

So please what would be the best way to create a login with a hashed password (preferably hashed with SHA2_512)with a query.And if anyone can tell me where the mistake is at 'Query1' i would be grateful.

Answer 1

The HASHED syntax only indicates that the specified password is already hashed. SQL Server stores all passwords as hashes, it never stores them in plain-text.

With that out of the way, the syntax for creating a login with a hashed password is:

CREATE LOGIN administratori 
WITH PASSWORD = 0x01003BA91A73BB0B9C06A647FC81F3B221EDA8E74AF33C20252F HASHED
    , DEFAULT_DATABASE = MDAfondation
    , DEFAULT_LANGUAGE = english
    , CHECK_EXPIRATION = OFF;

The password is specified as a binary value - this value is the "hashed" version of the password. The hash above was obtained by the PWDENCRYPT function, like so:

SELECT PWDENCRYPT('P@ssw0rd');

Specifying the password as a hashed value is useful for scripting logins from one server to be deployed on another server when you need to ensure the passwords for each login are precisely the same on the new server. A starting point for code that generates logins from an existing server might be:

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT N'CREATE LOGIN ' + QUOTENAME(sp.name) + N'
WITH PASSWORD = ' + COALESCE(sys.fn_varbintohexstr(
    CONVERT(VARBINARY(MAX), LOGINPROPERTY(sp.name, 'PasswordHash')
    )), '') + N' HASHED
    , SID = ' + CONVERT(VARCHAR(40), sys.fn_varbintohexstr(sp.sid), 0) + N'
    ' + CASE WHEN sp.default_database_name IS NOT NULL THEN N', DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name) ELSE N'' END + N'
    ' + CASE WHEN sp.default_language_name IS NOT NULL THEN N', DEFAULT_LANGUAGE = ' + sp.default_language_name ELSE N'' END + N'
    ' + CASE WHEN LOGINPROPERTY('SomeLogin', 'DaysUntilExpiration') IS NULL THEN N', CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF' ELSE N'' END + N'
' + CASE WHEN sp.is_disabled = 1 THEN N'ALTER LOGIN ' + sp.name + N' DISABLE;
' ELSE N'' END + N'
'
FROM sys.server_principals sp
WHERE sp.type_desc = N'SQL_LOGIN'
    AND NOT (sp.name LIKE N'##%')
    AND NOT (sp.name = N'sa')
ORDER BY sp.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (@cmd, 0, 1) WITH NOWAIT;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

I'm including that to show how the hashed password is intended to be used.

Creating new logins using a hashed password is not recommended since there is no way for SQL Server to confirm the password matches policies you've set such as minimum password length and complexity.

Answer 2

Before you go down that road, you should read the information here(Why shouldn't a pre-hashed password be used with CREATE LOGIN all the time?). In short:

HASHED Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the string entered as password is hashed before it is stored in the database. This option should only be used for migrating databases from one server to another. Do not use the HASHED option to create new logins. The HASHED option cannot be used with hashes created by SQL Server 7 or earlier.