One of my responsibilities as a Systems Analyst is to create service accounts for custom applications to use to access our network. In doing this I constantly need to generate a random password to assign to the service account. I’m aware that some organizations likely assign the same password to many, if not all of their service accounts but I recommend against this due to the obvious security concern. The best practice is to just keep track of all your individual service account passwords in an encrypted file should you ever need to provide the account credentials in the future.

The method I use to create unique, randomly generated passwords can easily be accomplished using the steps below. Once you’ve created the necessary infrastructure, generating the password is as simple as calling the function and passing the desired password length.

The first step is to create either a Table or a View to store a system generated unique ID. I recommend using a View for this as it requires fewer resources. The reason this is necessary is because you can not call the NEWID() system function from within a user defined function. This will generate a unique value of the data type uniqueidentifier and return it from the view.

CreatePassword View CREATE VIEW [dbo].[CreatePassword] AS SELECT NEWID() AS [UniqueID] GO 1 2 3 4 CREATE VIEW [ dbo ] . [ CreatePassword ] AS SELECT NEWID ( ) AS [ UniqueID ] GO

Once your view is created the next step is to create the function that will accept a length value and utilize the unique ID that was created in the view to generate your random password. The thing I most like about the following script it that it will give you the ability to choose your character set. Here you can specify exactly which characters can be used to generate the password. The more options you have here the better. In addition to all character and numeric values, I have also included some special character to provide additional security to the generated password.

CreatePassword Function CREATE FUNCTION [dbo].[fnCreatePassword] (@Length INT) RETURNS VARCHAR(18) AS BEGIN DECLARE @Password VARCHAR(18), @CharSet VARCHAR(75), @initialpw VARCHAR(5), @CharPick INT, @Counter INT SET @initialpw = '' SET @CharSet='AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789!@#$%&' SET @Counter = 1 SET @Password = @initialpw WHILE @Counter <= @Length BEGIN SELECT @CharPick = ABS(CAST CAST([UniqueID] AS VARBINARY) AS INT)) %LEN(@CharSet) + 2 FROM [dbo].[CreatePassword] SET @Password = @Password + SUBSTRING(@CharSet, @CharPick, 1) SET @Counter= @Counter + 1 END RETURN @Password END 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 CREATE FUNCTION [ dbo ] . [ fnCreatePassword ] ( @ Length INT ) RETURNS VARCHAR ( 18 ) AS BEGIN DECLARE @ Password VARCHAR ( 18 ) , @ CharSet VARCHAR ( 75 ) , @ initialpw VARCHAR ( 5 ) , @ CharPick INT , @ Counter INT SET @ initialpw = '' SET @ CharSet = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789!@#$%&' SET @ Counter = 1 SET @ Password = @ initialpw WHILE @ Counter <= @ Length BEGIN SELECT @ CharPick = ABS ( CAST CAST ( [ UniqueID ] AS VARBINARY ) AS INT ) ) % LEN ( @ CharSet ) + 2 FROM [ dbo ] . [ CreatePassword ] SET @ Password = @ Password + SUBSTRING ( @ CharSet , @ CharPick , 1 ) SET @ Counter = @ Counter + 1 END RETURN @ Password END

Finally you’ll want to call the function and pass it an integer. The above script is set up to create a password up to 18 character, though this can be modified if a greater length is required.

Executing the following script will generate a password with a length of 14 characters. The results of running the function call 3 times to generate 3 separate passwords is shown below.

Call CreatePasswrod Function SELECT [dbo].[fnCreatePassword] (14) 1 SELECT [ dbo ] . [ fnCreatePassword ] ( 14 )

Additional modifications can be made to the function in order to change the way the password is generated. However, if you are simply wanting a way to generate a strong random password, this method should suite your needs.