Password generator function

Question

What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions?

I would like to create a function that returns a randomly generated varchar(10) using characters from a given string like this one: "1234567890QWERTYUIOPASDFGHJKLZXCVBNM"

I have my own ideas that work but are not very natural solutions.

The idea behind the function is that if somebody requires a password reset I could do the following:

UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

A function would be easier to deploy and reuse than a separate table of preallocated passwords.

It's not alpha-numeric only, but this is the built-in password generating TSQL used when you make a CREATE script for Roles in SQL 2014. May be an interesting starting point for you. — LowlyDBA - John M, Jul 29 '15 at 14:15
Personally, I don't see why this should be a DB function at all unless you're storing plain text passwords, which is itself a pretty questionable practice. Ideally you want to store a hashed password and it's salt, and use a key derivation function to do your hashing for you. — Bacon Bits, Aug 1 '15 at 2:39

Solomon Rutzky · Accepted Answer · 2015-08-06 17:45:50Z

You have a few options (TL;DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3 !):

If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008:
```
SELECT CRYPT_GEN_RANDOM(5) AS [Hex],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS [HexStringWithout0x],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS [Translated-ASCII],
       CONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS [Translated-UCS2orUTF16]
```
returns:
```
Hex             HexStringWithout0x    Translated-ASCII    Translated-UCS2orUTF16
0x4F7D9ABBC4    0ECF378A7A            ¿"bü<Ý±Øï           튄ꆠ䤅㫘ᓟ멿ৢ폫씎䛯
```
As you can see, you need to use the format option of 2 on the CONVERT function so that it doesn't give you characters that, while more diverse, might be harder to type in, and you don't get the leading 0x. But you can also see that even with a single row, multiple calls will get different return values (just like NEWID() ).

Please note the "length" values passed into the CRYPT_GEN_RANDOM function. Since a hex byte is 2 characters, you only need to generate a 5-byte value. The CONVERT(VARCHAR... uses a "length" of 10 since each hex byte becomes a character. And the CONVERT(NVARCHAR... uses a "length" of 20 since every 2 bytes will become a character (when running this in a database with a default collation that does not end in _SC, which is most cases).

You can always create your own algorithm and pass in the value of CRYPT_GEN_RANDOM for the random aspect. AND, if you can structure your algorithm to be a single SELECT such that it can be done in an Inline Table-Valued Function / iTVF (usually using CTEs), then you technically can use a non-deterministic function (at least effectively) since the function call will pass in the expression instead of the result of that expression, like it does with Multistatement TVFs and Scalar UDFs:

USE [tempdb];
GO

CREATE FUNCTION dbo.GeneratePassword_InlineTVF(@RandomValue VARBINARY(10))
RETURNS TABLE
AS RETURN
  SELECT CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2) AS [HowRandomAmI?];
GO

CREATE FUNCTION dbo.GeneratePassword_MultistatementTVF(@RandomValue VARBINARY(10))
RETURNS @Result TABLE ([HowRandomAmI?] VARCHAR(100))
AS
BEGIN
  INSERT INTO @Result ([HowRandomAmI?])
         VALUES (CONVERT(VARCHAR(20), @RandomValue, 2) +
                 '~~' +
                 CONVERT(VARCHAR(20), @RandomValue, 2));
  RETURN;
END;
GO

CREATE FUNCTION dbo.GeneratePassword_ScalarUDF(@RandomValue VARBINARY(10))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2);
END;
GO

And then run the tests:

SELECT * FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10));
-- 27169EEC2B9CF7CC2731~~E9A95F49060BD41C0FF6

SELECT * FROM tempdb.dbo.GeneratePassword_MultistatementTVF(CRYPT_GEN_RANDOM(10));
-- 68EBC132814EA78602DE~~68EBC132814EA78602DE

SELECT tempdb.dbo.GeneratePassword_ScalarUDF(CRYPT_GEN_RANDOM(10)) AS [HowRandomAmI?];
-- 702DAF1C441C42FFBF5F~~702DAF1C441C42FFBF5F

And just to be sure about multiple rows in an iTVF:

SELECT TOP 5 rnd.[HowRandomAmI?]
FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10)) rnd
CROSS JOIN [master].[sys].[objects];

returns:

HowRandomAmI?
---------------
1D57F0ABFDE44BCAED00~~AD57E9E2FF01768BB86F
264674BE1C9ABBC1572E~~6C75CD4D472935FDFA40
CB54CC6BB5F31F42FDEA~~B3EC7061027FCD36C9AC
44525355FC15655C1F4D~~4DDF874CD06BC4F2D7A4
0E51B6364F193F588C93~~08E2ED40ED9752267EF7

Putting all of this info into practice, we can do the following with regards to the original request:

CREATE FUNCTION dbo.GeneratePassword_Real(@RandomValue VARBINARY(30))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH base(item) AS
  (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
  ), items(item) AS
  (
    SELECT NULL
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT (
     SELECT TOP (LEN(@RandomValue))
            SUBSTRING('1234567890QWERTYUIOPASDFGHJKLZXCVBNM',
                      (CONVERT(TINYINT, SUBSTRING(@RandomValue, 1, 1)) % 36) + 1,
                      1) AS [text()]
     FROM   items
     FOR XML PATH('')
  ) AS [RandomPassword];

And then execute it as follows:

DECLARE @PasswordLength INT = 10;

SELECT * FROM tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength));

CAUTION: Please see Item # 2 below related to running this for multiple rows!

You can create a SQLCLR function that would have access to randomizing functionality. The main disadvantage here is that a Scalar function is much more likely to have its return value cached than an iTVF.

Update

Given the newly provided context for usage of this function from the Question update:
The idea behind the function is that if somebody requires a password reset I could do the following:
```
UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID
```
we can address the following concerns as expressed in a comment on this answer:
- One needs to pass a parameter to the function with a random value. This enforces that it is necessary to know one way of creating random strings in order to use the function...
  
  True. And it is admittedly slightly inconvenient. However, that does not negate this approach's ability to solve the issue at hand. It just requires some comments / documentation on how to properly use this function.
- ...the function couldn't be executed inside another function because you wouldn't be allowed to use a non deterministic function to generate random values inside the container function.
  
  True. But there is no need to call this function within another function. This doesn't need to be a scalar function in order to be used in an UPDATE:
```
USE [tempdb];
CREATE TABLE #Users (UserID INT NOT NULL PRIMARY KEY, [Password] VARCHAR(20));
INSERT INTO #Users (UserID, [Password]) VALUES (1, 'a'), (2, 'b');

DECLARE @PasswordLength INT = 10;

UPDATE tmp
SET    tmp.[Password] = pass.RandomPassword
FROM   #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
WHERE  tmp.UserID = 1;

SELECT * FROM #Users;
```
With regards to handling multiple rows (please see multi-row test in Option 2 above), functions of any type (user created, at least) -- Scalar UDF, Multistatement TVF, iTVF, SQLCLR UDF, or SQLCLR TVF -- are not guaranteed to run per row! You might need to get creative in how you convince the Query Optimizer to not cache the result, but there might be cases when nothing works. The following example shows the same iTVF being called twice. Passing in just @PasswordLength causes the output value to be cached. However, when you pass in a formula that incorporates a field from the current row, then it might behave as you are expecting it to:
```
DECLARE @PasswordLength INT = 10;
SELECT pass.RandomPassword, pass2.RandomPassword
FROM [master].[sys].[objects] sac
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM((sac.[object_id] % 1)
            + @PasswordLength)) pass2
```
returns:
```
RandomPassword    RandomPassword
--------------  --------------
KX1M1NSKE7        8I1OK6TJI3
KX1M1NSKE7        S0RA6AWKAJ
KX1M1NSKE7        MKXV9UAB0Y
KX1M1NSKE7        4RQ2K6O1RP
KX1M1NSKE7        8J8PKJP7K2
```
The point made by @BaconBits in a comment on the question, regarding it being best to not store readable passwords, is a valid and good point. However, circumstances might prevent that option from being possible, or at least possible at this point in time.

lorond · Accepted Answer · 2016-05-13 11:19:54Z

One more option is to use view.

Usage:

UPDATE Users SET Password = dbo.GeneratePassword(DEFAULT, DEFAULT, DEFAULT) WHERE UserID = @UserID

Code:

CREATE VIEW dbo.RandomNoise
AS
-- helper view for accessing CRYPT_GEN_RANDOM from UDF
-- see dbo.GeneratePassword for details about 2048 value
SELECT CRYPT_GEN_RANDOM(2048) Bytes
GO

CREATE FUNCTION dbo.GeneratePassword(@letters INT = NULL, @numbers INT = NULL, @symbols INT = NULL)
RETURNS VARCHAR(256)
AS
BEGIN
    -- actual defaults
    IF @letters IS NULL SET @letters = 6
    IF @numbers IS NULL SET @numbers = 3
    IF @symbols IS NULL SET @symbols = 1

    IF @letters < 0 SET @letters = 0
    IF @numbers < 0 SET @numbers = 0
    IF @symbols < 0 SET @symbols = 0

    IF @letters > 256 SET @letters = 256
    IF @numbers > 256 SET @numbers = 256
    IF @symbols > 256 SET @symbols = 256

    DECLARE @letterSet VARCHAR(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
            @numberSet VARCHAR(10) = '0123456789',
            @symbolSet VARCHAR(20) = '!@#$%^&*()_+-=[]{}',
            @pwdLen INT = @letters + @numbers + @symbols

    IF @pwdLen > 256 SET @pwdLen = 256

    -- 256 is max possible password length
    DECLARE @pwd VARCHAR(256) = ''

    -- 2048 = (4 bytes rnd set + 4 bytes rnd position in set) * 256 max password length
    DECLARE @noise VARBINARY(2048) = (SELECT n.Bytes FROM dbo.RandomNoise n)

    DECLARE @i INT = 0

    WHILE @i < @pwdLen
    BEGIN
        SET @i = @i + 1

        -- [0,1)
        DECLARE @who FLOAT = (CAST(CAST(SUBSTRING(@noise, @i    , 4) AS INT) AS FLOAT) + 2147483648.0) / 4294967296.0,
                @pos FLOAT = (CAST(CAST(SUBSTRING(@noise, @i + 4, 4) AS INT) AS FLOAT) + 2147483648.0) / 4294967296.0

        IF @who < (@letters / CAST(@letters + @numbers + @symbols AS FLOAT))
        BEGIN
            SET @pwd = @pwd + SUBSTRING(@letterSet, CAST(@pos * LEN(@letterSet) + 1 AS INT), 1)
            SET @letters = @letters - 1
        END
        ELSE
        IF @who < ((@letters + @numbers) / CAST(@letters + @numbers + @symbols AS FLOAT))
        BEGIN
            SET @pwd = @pwd + SUBSTRING(@numberSet, CAST(@pos * LEN(@numberSet) + 1 AS INT), 1)
            SET @numbers = @numbers - 1
        END
        ELSE
        BEGIN
            SET @pwd = @pwd + SUBSTRING(@symbolSet, CAST(@pos * LEN(@symbolSet) + 1 AS INT), 1)
            SET @symbols = @symbols - 1
        END
    END

    RETURN @pwd
END
GO

Thanks for sharing lorond, this is an elegant solution with 3 lookup arrays which is based on a strong random generator, making it even trustworthy to use! — Theo Ekelmans, Feb 8 at 14:10

Quandary · Accepted Answer · 2016-11-29 10:50:58Z

I use this

DECLARE @__numberOfUpperCaseCharacters int 
DECLARE @__numberOfLowerCaseCharacters int 
DECLARE @__numberOfNumericCharacters int 
DECLARE @__numberOfSpecialCharacters int 


SET @__numberOfUpperCaseCharacters = 1  
SET @__numberOfLowerCaseCharacters = 5 
SET @__numberOfNumericCharacters = 1 
SET @__numberOfSpecialCharacters = 1 


;WITH CTE_UpperCaseCharacters AS 
(
    SELECT 
         CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomUpperCaseCharacter  
        ,1 AS cnt 
    WHERE @__numberOfUpperCaseCharacters > 0 

    UNION ALL 

    SELECT 
         CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomUpperCaseCharacter  
        ,CTE_UpperCaseCharacters.cnt + 1 AS cnt 
    FROM CTE_UpperCaseCharacters 
    WHERE CTE_UpperCaseCharacters.cnt < @__numberOfUpperCaseCharacters 

)
,CTE_LowerCaseCharacters AS 
(
    SELECT 
         CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomLowerCaseCharacter  
        ,1 AS cnt 
    WHERE @__numberOfLowerCaseCharacters > 0 

    UNION ALL 

    SELECT 
         CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomLowerCaseCharacter  
        ,CTE_LowerCaseCharacters.cnt + 1 AS cnt 
    FROM CTE_LowerCaseCharacters 
    WHERE CTE_LowerCaseCharacters.cnt < @__numberOfLowerCaseCharacters 

)
,CTE_Numbers AS 
(
    SELECT 
        CAST
        (
            (
                ABS
                (
                    CHECKSUM(NEWID())
                )
                %10
            ) 
            AS varchar(1)
        ) AS RandomInteger 
        ,1 AS cnt 
    WHERE @__numberOfNumericCharacters > 0 

    UNION ALL 

    SELECT 
        CAST
        (
            (
                ABS
                (
                    CHECKSUM(NEWID())
                )
                %10
            ) 
            AS varchar(1)
        ) AS RandomInteger 
        ,CTE_Numbers.cnt + 1 AS cnt 
    FROM CTE_Numbers 
    WHERE CTE_Numbers.cnt < @__numberOfNumericCharacters 
)
,CTE_SpecialCharacters AS 
(
    SELECT 
         CHAR(ASCII('!')+(ABS(CHECKSUM(NEWID()))%15)) AS RandomSpecialCharacter  
        ,1 AS cnt 
    WHERE @__numberOfSpecialCharacters > 0 

    UNION ALL 

    SELECT 
         CHAR(ASCII('!')+(ABS(CHECKSUM(NEWID()))%15)) AS RandomSpecialCharacter  
        ,CTE_SpecialCharacters.cnt + 1 AS cnt 
    FROM CTE_SpecialCharacters 
    WHERE CTE_SpecialCharacters.cnt < @__numberOfSpecialCharacters  

)
,CTE AS 
(
    SELECT RandomSpecialCharacter AS RandomCharacter, cnt  
    FROM CTE_SpecialCharacters 
    UNION ALL 
    SELECT RandomUpperCaseCharacter AS RandomCharacter, cnt  
    FROM CTE_UpperCaseCharacters 
    UNION ALL 
    SELECT RandomLowerCaseCharacter AS RandomCharacter, cnt  
    FROM CTE_LowerCaseCharacters 
    UNION ALL 
    SELECT RandomInteger AS RandomCharacter, cnt  
    FROM CTE_Numbers 
)
SELECT 
    (
        SELECT RandomCharacter AS [text()]
        FROM CTE 
        ORDER BY NEWID()
        FOR XML PATH(''), TYPE 
    ).value('.', 'varchar(50)') AS foo 
-- OPTION (MAXRECURSION 0)

But because SQL-Server is so wonderful, you can't use NEWID in a scalar function, so I create an additional view "dbo.random". As below:

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Random]'))
    EXECUTE('CREATE VIEW dbo.Random AS SELECT NEWID() AS [RUID]');


GO 



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_GenerateRandomPassword]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_GenerateRandomPassword]
GO




-- ========================================================================
-- Author:        ME
-- Create date:   29.11.2016
-- Last modified: 29.11.2016
-- Description:   Erstellen eines zufälligen Passworts
-- ========================================================================

-- PRE:   Anzahl Buchstaben in spezifizierter Kombination 
-- POST:  Generiere Liste mit jeweiliger Anzahl Buchstaben. 
--        Gibt string(shuffle(array(lower + upper + numeric + special))) zurück 
-- PRINT dbo.fu_GenerateRandomPassword ( 31, 12, 2010)
CREATE  FUNCTION [dbo].[fu_GenerateRandomPassword](@__numberOfLowerCaseCharacters as int, @__numberOfUpperCaseCharacters as int, @__numberOfNumericCharacters as int, @__numberOfSpecialCharacters as int) 
    RETURNS varchar(50) 
AS
BEGIN 
    DECLARE @__retValue varchar(50); 


    --DECLARE @__numberOfLowerCaseCharacters int; 
    --SET @__numberOfLowerCaseCharacters = 5; 

    --DECLARE @__numberOfUpperCaseCharacters int; 
    --SET @__numberOfUpperCaseCharacters = 1; 

    --DECLARE @__numberOfNumericCharacters int; 
    --SET @__numberOfNumericCharacters = 1; 

    --DECLARE @__numberOfSpecialCharacters int; 
    --SET @__numberOfSpecialCharacters = 1; 


    WITH CTE_UpperCaseCharacters AS 
    (
        SELECT 
             CHAR(ASCII('A')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomUpperCaseCharacter  
            ,1 AS cnt 
        WHERE @__numberOfUpperCaseCharacters > 0 

        UNION ALL 

        SELECT 
             CHAR(ASCII('A')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomUpperCaseCharacter  
            ,CTE_UpperCaseCharacters.cnt + 1 AS cnt 
        FROM CTE_UpperCaseCharacters 
        WHERE CTE_UpperCaseCharacters.cnt < @__numberOfUpperCaseCharacters 

    )
    ,CTE_LowerCaseCharacters AS 
    (
        SELECT 
             CHAR(ASCII('a')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomLowerCaseCharacter  
            ,1 AS cnt 
        WHERE @__numberOfLowerCaseCharacters > 0 

        UNION ALL 

        SELECT 
             CHAR(ASCII('a')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomLowerCaseCharacter  
            ,CTE_LowerCaseCharacters.cnt + 1 AS cnt 
        FROM CTE_LowerCaseCharacters 
        WHERE CTE_LowerCaseCharacters.cnt < @__numberOfLowerCaseCharacters 

    )
    ,CTE_Numbers AS 
    (
        SELECT 
            CAST
            (
                (
                    ABS
                    (
                        CHECKSUM((SELECT RUID FROM Random))
                    )
                    %10
                ) 
                AS varchar(1)
            ) AS RandomInteger 
            ,1 AS cnt 
        WHERE @__numberOfNumericCharacters > 0 

        UNION ALL 

        SELECT 
            CAST
            (
                (
                    ABS
                    (
                        CHECKSUM((SELECT RUID FROM Random))
                    )
                    %10
                ) 
                AS varchar(1)
            ) AS RandomInteger 
            ,CTE_Numbers.cnt + 1 AS cnt 
        FROM CTE_Numbers 
        WHERE CTE_Numbers.cnt < @__numberOfNumericCharacters 
    )
    ,CTE_SpecialCharacters AS 
    (
        SELECT 
             CHAR(ASCII('!')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%15)) AS RandomSpecialCharacter  
            ,1 AS cnt 
        WHERE @__numberOfSpecialCharacters > 0 

        UNION ALL 

        SELECT 
             CHAR(ASCII('!')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%15)) AS RandomSpecialCharacter  
            ,CTE_SpecialCharacters.cnt + 1 AS cnt 
        FROM CTE_SpecialCharacters 
        WHERE CTE_SpecialCharacters.cnt < @__numberOfSpecialCharacters  

    )
    ,CTE AS 
    (
        SELECT RandomSpecialCharacter AS RandomCharacter, cnt  
        FROM CTE_SpecialCharacters 
        UNION ALL 
        SELECT RandomUpperCaseCharacter AS RandomCharacter, cnt  
        FROM CTE_UpperCaseCharacters 
        UNION ALL 
        SELECT RandomLowerCaseCharacter AS RandomCharacter, cnt  
        FROM CTE_LowerCaseCharacters 
        UNION ALL 
        SELECT RandomInteger AS RandomCharacter, cnt  
        FROM CTE_Numbers 
    )

    SELECT 
        @__retValue = (
            SELECT RandomCharacter AS [text()]
            FROM CTE 
            ORDER BY (SELECT RUID FROM Random)
            FOR XML PATH(''), TYPE 
        ).value('.', 'varchar(50)') 
    -- OPTION (MAXRECURSION 0) 
    ;

    RETURN @__retValue; 
END


GO

Stack Exchange Network

current community

your communities

more stack exchange communities

Password generator function

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server functions password or ask your own question.

Hot Network Questions

Password generator function

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server functions password or ask your own question.

Related

Hot Network Questions