Question: How can we generate a random password in SQL Server?
Answer: If you need to generate one in T-SQL, use a cryptographic source such as CRYPT_GEN_RANDOM. The loop is straightforward: obtain random bytes, map them to an allowed alphabet, and check the completed candidate against the required character rules.
Here is a 20-character example containing uppercase letters, lowercase letters, digits and at least one of ! or @. It returns a candidate string; it does not create or change any login.
DECLARE @alphabet varchar(64) =
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@';
DECLARE @password varchar(20), @position int, @byte int;
WHILE 1 = 1
BEGIN
SET @password = '';
SET @position = 0;
WHILE @position < 20
BEGIN
SET @byte = CONVERT(int, CRYPT_GEN_RANDOM(1));
SET @password += SUBSTRING(@alphabet, (@byte % 64) + 1, 1);
SET @position += 1;
END;
IF @password COLLATE Latin1_General_100_BIN2 LIKE '%[A-Z]%'
AND @password COLLATE Latin1_General_100_BIN2 LIKE '%[a-z]%'
AND @password COLLATE Latin1_General_100_BIN2 LIKE '%[0-9]%'
AND @password COLLATE Latin1_General_100_BIN2 LIKE '%[!@]%'
BREAK;
END;
SELECT @password AS GeneratedPassword;The alphabet has exactly 64 characters. Each random byte can have 256 values, and 256 divides evenly by 64, so this mapping does not favor some characters over others. If you change the alphabet to a different length, revisit the mapping rather than blindly changing the divisor.
The outer loop retries a candidate that lacks a required character class. The binary collation makes the uppercase and lowercase checks distinct even when the database’s default collation is case-insensitive. Changing the length also means changing the variable’s declared size and considering whether the required classes can fit.
My earlier example used RAND() with an ASCII range. That is useful for demonstrating a loop, but it is not an appropriate source for a secret. Its multiplier also meant several claimed characters could never be selected. This version uses the cryptographic function and an explicit alphabet. Random generation does not promise that repetition is impossible.
Use the actual account or application’s password requirements when accepting a candidate. This example’s four character classes are not a guarantee that it satisfies Windows policy or any application’s complete rules. Handle the generated value as a secret, rather than saving a production password in a query history, screenshot or shared script.
Related questions:
- Enforce Windows password policy for SQL Server logins
- MySQL: password does not satisfy policy requirements
- SQL Server service account password management
- Change the sa password in Management Studio
- When was a domain account password changed?
- Forgot the sa password
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
14 Comments. Leave new
Use CRYPT_GEN_RANDOM to get random binary and then cast to XML to use it’s base 64 function to convert it to an alphanumeric password.
article should be titled “How to insecurely generate random passwords in SQL Server”
That is a good title but why would we call it insecurely?
For those playing at home, a cryptographically secure random password:
DECLARE @randombinary varbinary(max), @password varchar(max), @length int
SET @length = 10
SET @randombinary = CRYPT_GEN_RANDOM(@length)
SELECT @password = CAST (” as xml).value(‘xs:base64Binary(sql:variable(“@randombinary”))’,’varchar(max)’)
SELECT LEFT(@password, @length) — Base64 will always be longer and this removes = / == from end as well
Not really, Chris.
While using a slightly more random random-number generator is good, it will have particularly little effect on the overall security of the password. What is really important is the diversity of the characters used in it.
In Pinal’s original version (with bug), used a range of 51 characters to create the password.
Yours uses a range of 64 characters.
My fix to Pinal’s (given in a different comment) uses 75 characters.
DECLARE @char CHAR = ”
DECLARE @charI INT = 0
DECLARE @password VARCHAR(100) = ”
DECLARE @len INT = 12 — Length of Password
WHILE @len > 0
BEGIN
SET @charI = ROUND(RAND()*75,0)+48
SET @char = CHAR(@charI)
SET @password += @char
SET @len = @len – 1
END
SELECT @password [PassWord]
The line “IF @charI > 48 AND @charI 47 AND @charI < 123, BUT as originally written, charI would only be in the range 0-99 anyway, so the upper limit is pointless, and you're then throwing out half the values generated.
As corrected above, the value produced for @charI is always in the proper range, so the IF becomes superfluous.
Hmm… Somethig went wrong and a bit of my text was dropped there.
The line “IF @charI > 48 AND @charI 47 AND @charI 48 AND @charI < 122" has a number of problems. First of all, character #48 ('0') and #122 ('z') are good characters, so it should be "IF @charI 47 AND @charI < 123," ….
Also, the first step should be "Reconsider your need to generate a password in SQL as this is quite out of scope for what an data server is designed to do, and there are far better tools to do it on your host computer"
Pinal, here is an alternative way to generate your password:
DECLARE @len INT = 30,
@pwd NVARCHAR(512) = N”
SELECT TOP (@len) @pwd += CHAR(fn.rnd) –, fn.rnd
FROM master.dbo.spt_values sv
CROSS APPLY (
SELECT [rnd] = (ABS(CHECKSUM(NewId())) % 122)
) fn
WHERE fn.rnd > 48 AND fn.rnd < 122
SELECT @pwd
Turned it into a proc with a little bit more flexibility in choosing which characters show up in the password.
IF OBJECT_ID (N’tempdb..#GenerateRandPwd1′) IS NOT NULL BEGIN
DROP PROC #GenerateRandPwd1
END
GO
CREATE PROCEDURE #GenerateRandPwd1 (@length int = 20, @allowAtoZ BIT = 1, @allow0to9 BIT = 1, @allowSpecials1 BIT = 0, @allowSpecials2 BIT = 0)
AS
BEGIN
DECLARE @pwd VARCHAR(512) = ”
SELECT TOP (@length) @pwd += CHAR(fn.rnd)
FROM master.dbo.spt_values sv
CROSS APPLY (
— (max – min) + min
SELECT [rnd] = (ABS(CHECKSUM(NEWID())) % (123 – 48)) + 48
) fn
WHERE (@allowAtoZ = 1 AND ((fn.rnd >= 65 AND fn.rnd = 97 AND fn.rnd = 48 AND fn.rnd = 33 AND fn.rnd = 58 AND fn.rnd <= 64))
SELECT @pwd
END
GO
EXEC #GenerateRandPwd1 @length = 30, @allowSpecials1 = 0, @allowSpecials2 = 0
EXEC #GenerateRandPwd1 @length = 30, @allow0to9 = 0, @allowSpecials1 = 1, @allowSpecials2 = 1
I just stumbled upon this (Kunjan’s new comment triggered a notification).
Anyway, the key part of your revision is replacing RAND() with (ABS(CHECKSUM(NEWID())). I’m not seeing the logic for that. RAND() is generating a 64-bit random float. NEWID is generating a 128-bit number whose primary feature is being universally unique, but not necessarily random. You then take that number, and perform another high time cost operation on it (CHECKSUM) merely to convert it to a 32-bit number. In other words, you are doing much more work, for a lesser outcome.
I know this is old, but I had written a gist that is a lot better than the proc I commented before: https://gist.github.com/tcartwright/c27a45a4a211f7cc79466140fb80a56d
Thanks Tim!
DECLARE @char CHAR = ”
DECLARE @charI INT = 0
DECLARE @password VARCHAR(100) = ”
DECLARE @len INT = 12 — Length of Password
WHILE @len > 0
BEGIN
SET @charI = ROUND(RAND()*100,0)
SET @char = CHAR(@charI)
IF @charI > 48 AND @charI < 122
BEGIN
SET @password += @char
SET @len = @len – 1
END
END
SELECT @password [PassWord]
==============================
I have used above script with only change length to 14 instead of 12 and generated password for new SQL Server User and that password has semicolon (;)
When that create new SQL Server user script gets executed it works perfectly including on production server but when the same password copied to appsettings.json for dotnet web app, there was error and the web app stopped working due to semicolon.
Please modify your script as many users follow similar practice and they end up into trouble due to this.
Please help me SET @password += @char
how this works in sql