Execute sp_password (stored procedure) from VBA when the user's SQL server password is expired

Question

I have been researching this for a while and I still haven’t been able to solve the problem. I have developed a front end (in MS Access using VBA) that allows users to easily enter, view, and interact with data stored in our SQL Server.

Everything is working well except that the user's passwords expire every 60-ish days and they have no way to update their password through the MS Access front end and I am sick and tired of manually updating passwords.

I have created some code that allows users to change their password if the password hasn’t already expired, but once the password has expired we are SOL and have to manually change it in the back end.

I think the problem is that when the password hasn’t expired the user can still successfully connect to the SQL Server and execute the stored procedure that will change their password. However, once the password has expired, they can no longer actually connect to the server to execute the stored procedure to change their password. Any ideas on how to solve this problem? All the passwords are going to expire soon and I would LOVE to not manually update all the passwords.

Here is an example of the code that I am working on. It is full of garbage and doesn’t work, but I think it may be in the right direction.

Option Compare Database

Private Sub ChangePWbutton_Click()
On Error GoTo ErrHandler

Dim cnComments As New ADODB.Connection
Dim strCS As String
Dim P As String
Dim Rsx As ADODB.Recordset

'Set up the connection string
strCS = "Provider=SQLOLEDB;" _
     & "Server=IPaddressHere\SQL_2005;" _
     & "Database=" + DBselect.Value + ";" _
     & "User ID=" + Uname.Value + ";" _
     & "Password=" + pWord.Value + ";" _
     & "MARS Connection=True;"

    cnComments.Open strCS

    If cnComments.State = adStateOpen Then
    MsgBox "it is open Place 1"
    End If

'P = "sp_password '" & pWord.Value & "', '" & NewPW.Value & "', '" & Uname.Value & "'"
'Set Rsx = cnComments.Execute(P)

ErrHandler:
Select Case Err.Number

     Case -2147467259
        If cnComments.Errors.Count > 1 Then
            Select Case cnComments.Errors(1).NativeError

                Case 18463 To 18468, 18487 To 18488

                    MsgBox "The password must be changed. Password expired."
                    If cnComments.State = adStateOpen Then
                    MsgBox "it is open place 2"
                    End If

                        'cnComments.Open strCS, bragado_l, Accenture1*
                        P = "sp_password '" & pWord.Value & "', '" & NewPW.Value & "', '" & Uname.Value & "'"
                        Set Rsx = cnComments.Execute(P)



                    'strNewPassword = ChangePassword() 'a function you create
                    'If Len(strNewPassword) Then
                        'Con.ConnectionString = strConnectionString & ";User ID=" & strUser & ";Password=" & strNewPassword & ";Old Password=" & strPassword
                        'Resume ExitProc
                    'Else
                       ' QuitProgram
                    'End If

            End Select
        End If
    Case Else
        VBA.MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
End Select
'Resume ExitProc
'Resume

End Sub

Before you do anything else, take some time to learn about SQL Injection, and then fix the glaring vulnerabilities in your code. Parameterized queries in VBA aren't that difficult. — Richard Deeming
– Richard Deeming, Commented Jul 23, 2014 at 15:45
Secondly, if your user's SQL password has expired, they will not be able to connect to your server. The only way for your code to reset their password is if it connects with a different user account. — Richard Deeming
– Richard Deeming, Commented Jul 23, 2014 at 15:47

Brad · Accepted Answer · 2014-07-23 22:17:43Z

Looks like you are using ADO so you can use something like this. It uses parameters to prevent sql injection.

Function changePassword()

    Dim username As String, yourThreshold As Integer
    username = "testUser"

    Dim conn As ADODB.Connection
    Dim expiresInDays As Integer

    Set conn = GetProjectConnection
    expiresInDays = DaysUntilExpiration(conn, username)

    Dim oldPassword As String, newPassword As String
    oldPassword = "oldpassword123"
    newPassword = "newpassword321"

    If expiresInDays <= yourThreshold Then
        ResetPassword conn, username, oldPassword, newPassword
    End If

End Function

Function ResetPassword(conn As ADODB.Connection, username As String, oldPassword As String, newPassword As String)
    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = CommandTypeEnum.adCmdStoredProc
    cmd.CommandText = "sp_password"
    cmd.Parameters.Append cmd.CreateParameter("@old", adVarWChar, adParamInput, 128, oldPassword)
    cmd.Parameters.Append cmd.CreateParameter("@new", adVarWChar, adParamInput, 128, newPassword)
    cmd.Parameters.Append cmd.CreateParameter("@loginname", adVarWChar, adParamInput, 128, username)

    Dim rs As New ADODB.Recordset
    cmd.Execute
    cmd.Parameters.Refresh
    ResetPassword = cmd.Parameters("@RETURN_VALUE").Value

End Function

Function DaysUntilExpiration(conn As ADODB.Connection, username As String) As Integer
    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = CommandTypeEnum.adCmdText
    cmd.CommandText = "SELECT [expiresInDays] = LOGINPROPERTY(@pUsername, 'DaysUntilExpiration')"
    cmd.Parameters.Append cmd.CreateParameter("@pUsername", adVarWChar, adParamInput, 255, username)
    Dim rs As New ADODB.Recordset
    rs.Open cmd
    DaysUntilExpiration = rs("expiresInDays").Value

End Function

Brad, thank you so much for such an awesome answer. I really appreciate your help!

Collectives™ on Stack Overflow

Execute sp_password (stored procedure) from VBA when the user's SQL server password is expired

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related