Link to home
Start Free TrialLog in
Avatar of sshani
sshaniFlag for United States of America

asked on

Setting SQL Password through VB.net application

Experts,

I have a vb.net windows forms application that authenticates users against a database before granting access to the rest of the application. New users are "added" to the database with a generic password and are marked to be prompted to change their password upon initial log in.

Users do NOT have SQL Management Studio on their machines, so this prompt to change their password needs to be handled in the windows forms application itself. Basically, I need the ability to check the setting in the database, prompt the users to set their password, and then commit back to the database.

Is there a way to do this in vb.net?
ASKER CERTIFIED SOLUTION
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How are new users added to the database?

If you are doing this via the windows form, then you need to add an update statement.

Step 1, User is prompted to change password. That means the user is given a link to click on.

Once the user clicks the link, then a screen pops with 2 or 3 textboxes, depending on how complicated you want to make it.

First textbox asks for existing password.
Second textbox asks for new password
Third optional textbox asks to confirm new password.

Meanwhile, when you click Submit, you should have code the processes these entries, in form of UPDATE statements.

It is hard to know *how* you are doing this so far.
Avatar of sshani

ASKER

sammySeltzer,  Brichsoft

Users will be added by hand directly into SQL by an admin and set up with a generic password and marked to be prompted to change their password upon initial login. Once the users run the windows application, the app will need to check against SQL to see if this user needs to set their password. Can this be checked with a query?  what would that be?

Once the user provides a new password, the application would update the user's record in the database with the new information. What would be the query for that?
hi,

already provided query, did you check that?
Update table set password='givenPassword'
where username='the user who provided the password'

Open in new window

You just need to make sure the users usernames are unique.

Otherwise, you will have to change your WhERE clause to username and something else.

It is scary to be making these changes directly on the db.
Avatar of sshani

ASKER

Brichsoft,

Your query seems to SET the "must change" switch on the password.  I need a way to find out if a user has that setting set so I can prompt them TO change their password.  Or am I miss understanding?
The trick, as you suggested, is to figure out whether your user has the flag set. I'm not aware of a way to do that with a T-SQL query, but .NET has additional ways to get SQL information from the server and make changes.

1. Add References to your project. This is the list for SQL Server 2005; it may vary for later editions:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SmoEnum.dll

2. At the beginning of the module:
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

3. If you're not using the default instance of SQL Server on your computer, you'll need to specify the server name in "New Server" below. Otherwise:
        'Connect to the local, default instance of SQL Server.
        Dim srv As Server
        srv = New Server
        Dim lLogin As Login = srv.Logins(strUserName)
        If lLogin.MustChangePassword Then
            ' Ask for a new password 
            lLogin.ChangePassword(strNewPassword, True, False)
        End If

Open in new window


The first Boolean (True) unlocks the account so the user can log in. The second Boolean (False) turns off the MustChange flag.
Avatar of sshani

ASKER

ElrondCT,

That's what I was looking for - the code to do it. I will take a look at that and see how I make out. I'll update either today or tomorrow. Thanks.
Avatar of sshani

ASKER

Thanks, Brichsoft for the lead. As it turns out, this is the line I was after for the first step:

SELECT loginproperty('UserLoginGoesHere','IsMustChange')

This returns an integer value (0/1) which indicates if the flag to force the user to change their password is set.

Your post actually led me down the right path, so thanks for that.
happy to assist you :-)