sshani
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
already provided query, did you check that?
Update table set password='givenPassword'
where username='the user who provided the password'
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.
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?
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.Connec tionInfo.d ll
Microsoft.SqlServer.Smo.dl l
Microsoft.SqlServer.SqlEnu m.dll
Microsoft.SqlServer.SmoEnu m.dll
2. At the beginning of the module:
Imports Microsoft.SqlServer.Manage ment.Smo
Imports Microsoft.SqlServer.Manage ment.Commo n
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:
The first Boolean (True) unlocks the account so the user can log in. The second Boolean (False) turns off the MustChange flag.
1. Add References to your project. This is the list for SQL Server 2005; it may vary for later editions:
Microsoft.SqlServer.Connec
Microsoft.SqlServer.Smo.dl
Microsoft.SqlServer.SqlEnu
Microsoft.SqlServer.SmoEnu
2. At the beginning of the module:
Imports Microsoft.SqlServer.Manage
Imports Microsoft.SqlServer.Manage
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
The first Boolean (True) unlocks the account so the user can log in. The second Boolean (False) turns off the MustChange flag.
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.
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.
ASKER
Thanks, Brichsoft for the lead. As it turns out, this is the line I was after for the first step:
SELECT loginproperty('UserLoginGo esHere','I sMustChang e')
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.
SELECT loginproperty('UserLoginGo
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 :-)
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.