SQL Server Security – Best Way to Create Login with Hashed Password

sql server

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.

Best Answer

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.

Related Question