Problem
The requirement is to create simple way to generate a strong eight-character password.
Solution
The solution involves creating a T-SQL view and a user defined function that produces a random eight-character password each time the function is called.
- The view returns a cryptographic random number generated by the Crypto API by calling the CRYPT_GEN_RANDOM system function.
- Please note that using the view rather than coding the call to the system function directly in the function to avoid the error 443: Invalid use of a side-effecting operator ‘Crypt_Gen_Random’ within a function. Microsoft has a rule that user defined functions cannot be used to perform actions that modify the database state. The CRYPT_GEN_RANDOM is one of several functions that are assumed to change the database state, but calling it through a view from the function is allowed.
- The resulting binary data is then converted to a VARCHAR(MAX) type and truncated to eight characters that produce a password that is returned to the user.
T-SQL code for view and user defined function
Following is the code to create the view and the function.
-- create view for function call
CREATE VIEW vRandom
AS
SELECT randval = CRYPT_GEN_RANDOM (8)
GO
-- ============================================================
-- Author: Eli Leiba
-- Create date: 01-2018
-- Description: a view and a scalar UDF to generate a random
-- 8 characters password
-- ============================================================
CREATE FUNCTION [dbo].[GenPass]()
RETURNS VARCHAR(8)
AS
BEGIN
-- Declare the variables here
DECLARE @Result VARCHAR(8)
DECLARE @BinaryData VARBINARY(8)
DECLARE @CharacterData VARCHAR(8)
SELECT @BinaryData = randval
FROM vRandom
Set @CharacterData=cast ('' as xml).value ('xs:base64Binary(sql:variable("@BinaryData"))',
'varchar (max)')
SET @Result = @CharacterData
-- Return the result of the function
RETURN @Result
END
GO
Example Use
In order to generate a new password, simple call the function by executing this T-SQL code:
DECLARE @newPass VARCHAR(8)
SELECT @newPass = dbo.GenPass()
PRINT @newPass
The result is:
KhH7Xql5
For every call, we will get a new random eight-character password.
Generating and Storing Password
This function only returns a generated password and does not store it anywhere. To further enhance the process, we can write the password to a table, so we can generate a bunch of passwords.
Here is the T-SQL code for the table and stored procedure.
CREATE TABLE dbo.GenPasswords (
requestID INT IDENTITY,
TS DATETIME,
pass VARCHAR(8)
)
GO
-- =============================================
-- Author: Eli Leiba
-- Create date: 15.1.2018
-- Description: Generate password and request ID
-- =============================================
CREATE PROCEDURE dbo.usp_GenPass @pass VARCHAR(8) = NULL OUTPUT,
@reqID INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @pass = dbo.GenPass();
INSERT INTO dbo.GenPasswords
VALUES (
getDate(),
@pass
);
SELECT @reqID = IDENT_CURRENT('GenPasswords');
SET NOCOUNT OFF;
END
GO
Example Use
The below code will generate the password and store to the table. The two outputs are the password and requestID value in the table.
DECLARE @p VARCHAR (8)
DECLARE @req INT
EXEC dbo.usp_GenPass @p out, @req out
PRINT @p
PRINT @req
The result is:
omm47vHo 1
When using the procedure, you could refer to the table based on the requestID to query the password.
Next Steps
- You can create this simple function and view in your master database and use it as a general function to generate new passwords.
- The function uses the cryptographic random number generator function CRYPT_GEN_RANDOM system function that is valid since SQL Server 2008, so the solution is valid for versions SQL Server 2008 and above.
- The view, function and procedure were tested for SQL Server version: Microsoft SQL Server 2014 – 12.0.2000.8
- Check out these other tips:
