By: Eli Leiba | Updated: 2018-01-31 | Comments (1) | Security
The requirement is to create simple way to generate a strong eight-character password.
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
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:
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
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:
When using the procedure, you could refer to the table based on the requestID to query the password.
- 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:
Last Updated: 2018-01-31
About the author
View all my tips