SQL: simple password generator

There are a few ways to generate random passwords in MS-SQL, but most involve creating a function.

Here are two inline ways to create passwords with 10 printable ASCII characters 40 to 122, which includes uppercase and lowercase letters, numbers and 21 special characters.

SELECT CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82) + CHAR(40 + RAND() * 82)

SELECT CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799) + CHAR(40 + CAST(CAST(CRYPT_GEN_RANDOM(2) AS int) AS float) / 799)

The first one is quite simple but it uses the RAND() function which produces pseudo-random data which may not be sufficiently secure.

The second one uses CRYPT_GEN_RANDOM(). This function is available since SQL 2008 and produces cryptographically strong data.