Change SQL Server Password through MS Access VBA Front-End

Question

I have been working on developing a MS Access application that will allow some coworkers to easily interact with data on our SQL Server. The program is about done, but one little thing remains- allowing a user to change their SQL Server password through the MS Access front-end. I have been googling my little heart out, but after a while everything starts to look the same (even if the answer is right in front of me!).

I found a couple links that are helpful, but I can't quite make the leap on how to apply it to my VBA program.

Change expired password without "Password Expired dialog box"

How can I change SQL Server login account password on first login via C#?

http://www.utteraccess.com/forum/Users-Change-SQL-Pass-t2006545.html&pid=2378998

My current connection string looks like this

Dim cn As ADODB.Connection
Dim strCS As String

Set cn = New ADODB.Connection

strCS = "Provider=SQLOLEDB;" _
         & "Server=IP ADDRESS GOES HERE;" _
         & "Database=" + DBselect.Value + ";" _
         & "User ID=" + Uname.Value + ";" _
         & "Password=" + pWord.Value + ";" _
         & "MARS Connection=True;"

cn.ConnectionString = strCS

cn.Open

This connection string works perfectly fine as long as the user's password hasn't expired.

How would I modify this connection string to change a users password? Any help is really appreciated!

Thanks!

No idea, but this is a well-asked question.

Eric Hauenstein
– Eric Hauenstein

2014-04-30 13:54:09 +00:00
Commented Apr 30, 2014 at 13:54 — Eric Hauenstein
– Eric Hauenstein, Commented Apr 30, 2014 at 13:54

Johnny Bones · Accepted Answer · 2014-04-30 16:00:25Z

2

Looks to me, from that 1st link, that this will work:

1) Create a new stored procedure in SQL Server called spChangeLogin. It should look like this:

CREATE procedure [dbo].[spChangeLogin]
    @UserName VarChar (50),
    @OldPass VarChar (20),
          @NewPass VarChar (20)
AS

BEGIN

ALTER LOGIN @UserName WITH 
     PASSWORD = @NewPass 
     OLD_PASSWORD = @OldPass

END
;
GO

2) Add this to your Access DB:

    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=IP ADDRESS GOES HERE;" _
         & "Database=" + DBselect.Value + ";" _
         & "User ID=" + Uname.Value + ";" _
         & "Password=" + pWord.Value + ";" _
         & "MARS Connection=True;"

        cnComments.Open strCS 

    P = "spChangeLogin '" & Me.UserName & "', '" & Me.OldPass & "', '" & Me.NewPass & "'"
    Set Rsx = cnComments.Execute(P)

3) Put 3 fields on your form; UserName, OldPass and NewPass

Requires ALTER ANY LOGIN permission.

NOTE:

My connection string looks like this:

strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=MyServerName;INITIAL CATALOG=MyDatabaseName;UID=GlobalUserID;PWD=GlobalPassword;"

You may need to adjust accordingly.

edited Apr 30, 2014 at 16:00

answered Apr 30, 2014 at 14:18

Johnny Bones

8,5027 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1238528 Over a year ago

Thanks Johnny! Instead of creating spChangeLogin, could I use the system's stored procedure spPassword? Needing ALTER ANY LOGIN permission may be an issue, and I think that spPassword doesn't require that specific permission. Thanks again for your help.

Johnny Bones Over a year ago

You could, but (according to documentation on it) that SP is not going to be in future versions of SQL Server so you'll just have to change it eventually anyway if you upgrade past SQL 2008.

user1238528 Over a year ago

Johnny, I implemented your code and it works great. I appreciate your help! One additional question that I have is if the user's password has expired the password will not update and an error saying "3704 Operation is not allowed when the object is closed." So it looks like the connection is never opened, so it isn't possible to execute P. Any ideas on how to modify this so it works when a user's password has expired? Thanks!

Johnny Bones Over a year ago

I never tested or used it, so I'm not positive. I can only propose 2 options; take a look at Section F: Unlocking A Login at the bottom of the page linked in my answer (I have no idea if it will work, unfortunately), or error trap '3704' and if it comes up then pop up a messagebox that says, "Your password has expired. You must contact an Admin to re-establish it". And then have the Admin set a temporary password.

Collectives™ on Stack Overflow

Change SQL Server Password through MS Access VBA Front-End

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related