Reset SQL Logon Password thru Access Front End

feets · March 3rd, 2009, 06:40 AM

Scratching head with this one, I want to be able to reset a Users Password (use the sp_password Stored Procedure), without having to go into the SQL Backend.

However the Old Password Parameter I wish to pass is NULL, however when executing the command it says the Parameter is incorrect.

Any Ideas

[quote]
[Private Sub cmdOk_Click()
Dim strPassword1 As String, strPassword2 As String
Dim strLogonName As String
Dim strFirstName As String
Dim strLastName As String
Dim strFormName As String
Dim strCnn As String
Dim i As Integer
Dim cmd As ADODB.Command
On Error GoTo Err_error
i = 0
strCnn = "Provider = Microsoft.Access.OLEDB.10.0;"
strCnn = strCnn & "Persist Security Info=False;Data Source=" & gbl_serverName & ";"
strCnn = strCnn & "User ID=wsrs_admin;Password=ch414fd;Initial Catalog=" & gbl_dbName & ";Data Provider=SQLOLEDB.1"
strPassword1 = Me.txtPassword1
strPassword2 = Me.txtPassword2
strFirstName = Left(Form_frmInsertOfficer.txtFirstName, 1)
strFirstName = LCase(strFirstName)
strLastName = LCase(Form_frmInsertOfficer.txtLastName)
strLogonName = strLastName & strFirstName

If strPassword1 = strPassword2 Then
If Len(strPassword1) < 6 Then

MsgBox "Password Required to be more than 6 Characters", vbCritical, "Password Requirement"

Resume Err_exit

Else
Set cmd = New ADODB.Command
cmd.ActiveConnection = strCnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_Password"
cmd.Parameters.Append cmd.CreateParameter("Old", adEmpty, adParamInput, 1, Null)
cmd.Parameters.Append cmd.CreateParameter("New", adVarChar, adParamInput, 10, strPassword1)
cmd.Parameters.Append cmd.CreateParameter("LoginName", adVarChar, adParamInput, 10, strUserName)
cmd.Execute

End If

Else

MsgBox "Password does not match. Please reenter.", vbCritical, "Password Error"

Me.txtPassword1.SetFocus
Me.txtPassword1.Text = ""
Me.txtPassword2.SetFocus
Me.txtPassword2.Text = ""

Resume Err_exit

End If
strFormName = "frmResetPassword"
DoCmd.Close acForm, strFormName, acSaveNo
Err_exit:
Exit Sub
Err_error:
MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error"
Resume Err_exit
End Sub
/QUOTE]

mmcdonal · March 9th, 2009, 08:35 AM

How did you arrive at this connection object?

Code:

strCnn = "Provider = Microsoft.Access.OLEDB.10.0;"
strCnn = strCnn & "Persist Security Info=False;Data Source=" & gbl_serverName & ";"
strCnn = strCnn & "User ID=wsrs_admin;Password=ch414fd;Initial Catalog=" & gbl_dbName & ";Data Provider=SQLOLEDB.1"

This is connecting to Access, not SQL Server, it appears. Is the connection working?

Also, is it possible to set the SQL Server database to Windows NT authenitcation? This will eliminate the need to do this at all.

feets · March 11th, 2009, 05:53 AM

The Connection works fine, so I'm not concerned about that. Although I do admit looking at it now it does look a little odd. I haven't worked on this application for about six months so am having to educate myself again.

The issue with using Windows Authentication, is that I felt it removed a layer of security, and I really wanted the User to have to login.

mmcdonal · March 11th, 2009, 07:45 AM

I am not sure the reasoning there. Windows NT authentication will add a layer of security, assuming that the person logged into the computer is actually the person who has access. How often do your users hijack or spoof other user's Windows sessions? Anyway, if they can crack the Windows session, they can crack the SQL Server session.

Why are you passing a NULL for the old password? Shouldn't there be some value there?

mmcdonal · March 11th, 2009, 07:47 AM

I am finding that you send the old password as NULL when you want to change the SA password, not other users.

How about using ALTER LOGIN instead?

feets · March 11th, 2009, 09:22 AM

If I go into the SQL Backend I can run the stored procedure with the NULL for any SQL login I have created. The reason for using NULL is because the old password can not be retrieved.

Am I right in thinking ALTER LOGIN doesn't work with SQL 2000