Setting SQL Password through application


I have a 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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bhavesh ShahLead AnalysistCommented:

you can do it by running queries.




USE [master]




Open in new window

Courtesy :

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day 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.
sshaniAuthor Commented:
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?
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Bhavesh ShahLead AnalysistCommented:

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.
sshaniAuthor Commented:

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:

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.
sshaniAuthor Commented:

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.
sshaniAuthor Commented:
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.
Bhavesh ShahLead AnalysistCommented:
happy to assist you :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.