Passwords are a necessary evil and there are times when you have to hand out a password for a SQL login (because the POS application doesn’t support Windows Authentication). Traditionally I’ve done this by sending an email to the user with the login and a separate one with the password, figuring that internal security controls would be good enough to prevent anyone from accessing both emails. Recently it came to light that all emails were being siphoned off and an information security team had access to all email that traversed our Exchange servers. Now I’m not saying that I don’t trust these guys, but there’s no way in hell I would ever let them get one of my passwords.

I needed to come up with a better solution for getting passwords to users that had a pretty good level of security around it. Yes, I know that the password can easily be set to force a change at the next login, however this does not work in a lot of cases where it will be used by an application and the person doing the configuration doesn’t have the knowledge or tools to go in and change the password themselves.

I decided that I wanted to have a two-factor authentication type method that would limit the availability to a password and that would provide the information once and once only for the user so that it would never be stored for a long period of time.

First I created a table to hold the password and a unique-identifier and nothing else. I didn’t want to store a login name along with this data just for extra security purposes. This way even if someone got access to the password they wouldn’t know what login it was for, helping with additional security.

CREATE TABLE [dbo].[PwInfo]

    (

      [AuthenticationID] [uniqueidentifier] NULL ,

      [NewPwd] [varchar](128) NULL

    )

ON  [PRIMARY]

GO

ALTER TABLE [dbo].[PwInfo] ADD  DEFAULT (NEWID()) FOR [AuthenticationID]

GO

Now I needed a quick and easy way to get a password once entered. I wrote a procedure that accepts the AuthenticationID, returns the password and then deletes the entry.

CREATE PROCEDURE [dbo].[GetPwdByAuthenticationID]

    @AuthenticationID UNIQUEIDENTIFIER

AS 

    SET NOCOUNT ON

    DECLARE @NewPwd VARCHAR(128)

    SELECT  @NewPwd = NewPwd

    FROM    dbo.PwInfo

    WHERE   AuthenticationID = @AuthenticationID

    DELETE  FROM dbo.PwInfo

    WHERE   AuthenticationID = @AuthenticationID

    SELECT  @NewPwd

GO

Finally I added a proc which would accept a password, add it to the table and then return some information to later allow the password to be retrieved.

CREATE PROCEDURE [dbo].[AddNewPassword] @NewPwd VARCHAR(128)

AS 

    SET NOCOUNT ON

    DECLARE @AuthIDTbl TABLE

        (

          AuthenticationID UNIQUEIDENTIFIER

        )

    INSERT  INTO dbo.PwInfo

            ( NewPwd )

    OUTPUT  INSERTED.AuthenticationID

            INTO @AuthIDTbl

    VALUES  ( @NewPwd )

    DECLARE @AuthenticationID VARCHAR(128)

    DECLARE @Msg VARCHAR(4000)

    SELECT  @AuthenticationID = AuthenticationID

    FROM    @AuthIDTbl

    SELECT  @Msg = 'Password added. Add the users AD account to the report folder (http://ReportingServices/Reports/Pages/Folder.aspx?ItemPath=%2fDBA+Reports%2fUser+Password+Information )and remove once they have pulled the data.

Send the following to the user:

For your security purposes please visit the following URL in your browser http://ReportingServices/Reports/Pages/Report.aspx?ItemPath=%2fDBA+Reports%2fUser+Password+Information%2fGet+Password and enter the authentication ID of '

            + @AuthenticationID

            + '

This is a one time use authentication token, if you need the password information again you will need to contact the production DBA team.

'

    PRINT @Msg

GO

If you read through this code you’ll see that it outputs a message that provides a couple of links to Reporting Services. This is where the extra authentication comes in.

Within Reporting Services I created a report which called the GetPwdByAuthenticationID proc and just returned the password (nothing more). This report lives in it’s own subfolder which is only accessible by the DBA team.

Here’s how it works:

A user requests a password from us, we pull that password from our secure repository (highly encrypted) and use dbo.AddNewPassword to add this password to the table. We get back a message which we then use to email the requestor; this contains the URL and the AuthenticationID that they need to enter into SSRS to get the information out. We then go to SSRS and grant the user browser permissions in the folder, allowing them to run the report and get back the password. Once they have retrieved the password we then go and remove the user from the folder, closing down access once more.

This provides several layers of security:

• The user must be logged in to AD with their own account to be able to access the report
• The user must have the the AuthenticationID provided by the DBA team to get the password
• The password has a one time access restriction meaning the process cannot be repeated
• The login is never given along with the password and never stored by the DBA team together except in a highly encrypted password vault inaccessible to anyone but the DBAs.

I feel this a much better solution than sending passwords via email, and considering it only took an hour to put together I figure it a very worthwhile piece of my time. Sure, there are improvements that could be made around automation of access and notifications to users, but as a quick win I think this does a good job.

I’m interested to know what solutions you other folks might be using for those times when you need to provide password information to users. Please comment below.