How to Generate Random Password in SQL Server? – Interview Question of the Week #181

July 8, 2018

Question: How to Generate Random Password in SQL Server?

Answer: This is a fantastic question and here is the script to generate a random password in SQL Server

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]

How to Generate Random Password in SQL Server? - Interview Question of the Week #181 randompassword-800x511

Well, that’s it. It will generate a password with alphabets, numerics, and special characters. If you want to change the length of the password, you can easily do so by changing the parameter @len. Please note that this function is based on the random function so you will probably never get the same password again in the near future. Here are the sample 10 passwords I generated from the above script to give you an idea what the final outcome looks like.

TUC:GR8?MHYP
6I9G2B]LG3H2
U8OO:^7DE;cb
93RW4]>VI`L9
[V?W^;M1E4Oc

Here are few another related blog post on the similar subject:

If you know any other way to generate a random password, please share it and I will publish with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Random, SQL Scripts, SQL Server, SQL Server Security

How to Check Edition Specific Features Enabled In SQL Server? – Interview Question of the Week #180

How to Convert Hex Windows Error Codes to the Meaningful Error Message – 0x80040002 and 0x80040005 and others? – Interview Question of the Week #182

13 Comments. Leave new

Chris F
July 8, 2018 8:11 pm
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.
Reply
- cartibarzo
  July 11, 2018 9:16 pm
  article should be titled “How to insecurely generate random passwords in SQL Server”
  Reply
  - Pinal Dave
    July 11, 2018 10:53 pm
    That is a good title but why would we call it insecurely?
  - Chris F
    July 12, 2018 6:41 am
    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
  - zames
    July 12, 2018 4:37 pm
    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.
zames
July 12, 2018 12:00 am
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.
Reply
- zames
  July 12, 2018 12:10 am
  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"
  Reply
Tim Cartwright
July 31, 2018 12:05 am
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
Reply
- Tim Cartwright
  July 31, 2018 3:25 am
  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
  Reply
- zames
  February 13, 2025 3:54 pm
  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.
  Reply
timcartwright
August 25, 2022 6:55 pm
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
Reply
- Pinal Dave
  August 26, 2022 8:46 am
  Thanks Tim!
  Reply
Kunjan Desai
February 12, 2025 4:22 pm
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.
Reply