Connection problem when changing password

Question

I have a software which displays some tables from our SQL database. Now I want to add a tool, where I can change the password for my "testdummy" user.

I tried to open a connection again but it didn't help. If you need some additional code or informations, just write a comment.

Notice that I'm new to programming. I'm a apprentice and currently learning programming and administer databases. I know this is not the safest solution, but it's just a little task from my instructor. This software will not be released for customers.

Like I mentioned before, I tried to open a connection again before I want to change the password.

 public void Change()
        {
            SqlConnection con = new SqlConnection();
            string connectionString = GetConnectionString();


            if (NewPassword.Password == NewPasswordAgain.Password && OldPassword.Password == GlobalData.Password)
            {
                try
                {
                    //dbMan.TryConnect(connectionString);
                    //con.Open();
                    SqlConnection.ChangePassword($"Data Source={GlobalData.ServerName};Initial Catalog={GlobalData.DBName};UID={GlobalData.Username};PWD={OldPassword}", $"{NewPassword}");
                }
                catch (SqlException ex)
                {
                    MessageBox.Show("Unable to change password. Try again!" + ex);
                }
            }
            else
            {
                // If new Password doesn't match.
                MessageBox.Show("Passwords doesn't match!");
            }
        }

I'm getting a SQL exception when I am trying to change the password.

(System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'csharptest'.

I get this at:

SqlConnection.ChangePassword($"Data Source={GlobalData.ServerName};Initial Catalog={GlobalData.DBName};UID={GlobalData.Username};PWD={OldPassword}", $"{NewPassword}");

At this point of the programm, there should be a connection to the database, because I can handle some tables and manipulate the data sets.

But when I uncomment this:

//dbMan.TryConnect(connectionString);
//con.Open();

It goes into the catch brackets there:

public bool TryConnect(string connectionString)
        {
            conn = new SqlConnection();
            conn.ConnectionString = connectionString;

            try
            {
                conn.Open();
                return true;
            }
            catch (Exception)
            {
                MessageBox.Show("Couldn't connect");
                return false;
            }
        }

and returns following exception:

System.InvalidOperationException: 'Die ConnectionString-Eigenschaft wurde nicht initialisiert.'

In english it should be something like: "the connectionstring property has not been initialized"

Edit: In the logs I'm getting this:

Login failed for user 'csharptest'. Reason: Password did not match that for the login provided.

Edit: Instead of:

SqlConnection.ChangePassword($"Data Source={GlobalData.ServerName};Initial Catalog={GlobalData.DBName};UID={GlobalData.Username};PWD={OldPassword}", $"{NewPassword}");

I did this:

string updatePassword = "USE CSHARPTEST ALTER LOGIN [" + GlobalData.Username + "] WITH PASSWORD =  '" + NewPassword + "'";
con.Open();
cmd.ExecuteNonQuery();

And now I think the only problem is the permission on the server.

Are you using a Windows Credential or a SQL Database credential? You cannot change a windows credential. — jdweng, Jun 24 '19 at 08:42
Try closing the c# application and re-open. Often I find that changes to database structure requires closing the c# before changes take affect. Often find this happens when I attach a database to the server in c#. — jdweng, Jun 24 '19 at 10:14
Am I correct in understanding this application is changing its *own* password? Yikes, that is an advanced problem, if you want it to be fault tolerant, and there are so many things that can go wrong with changing a password (complexity rules, etc) And if you are sloppy about it, you can trigger a lockout. Have you tried following [Microsoft's example](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.changepassword?view=netframework-4.8)? Looks rather involved. `for (int i = 0; i <= 1; i++)` ZOMG — John Wu, Jun 25 '19 at 07:30

score 0 · Accepted Answer · answered Jun 25 '19 at 07:12

You need to use parameters at the DbContext level. See this answer for more details, but, here's a code example (adapted from that same page):

string sql = "ALTER LOGIN @loginName WITH PASSWORD = @password";
ctx.Database.ExecuteSqlCommand(
    sql,
    new SqlParameter("loginName", loginName),
    new SqlParameter("password", password));

The purpose of using the parameters here (and everywhere) is to prevent a SQL injection attack. This is especially important given that you are writing code that changes a password.

UPDATE

The ALTER LOGIN statement won't work with variables; it must be done through dynamic SQL. Here's an example of the updated code:

string sql = @"DECLARE @sql NVARCHAR(500)
               SET @sql = 'ALTER LOGIN ' + QuoteName(@loginName) + 
                    ' WITH PASSWORD= ' + QuoteName(@password, '''') 
               EXEC @sql ";
ctx.Database.ExecuteSqlCommand(
    sql,
    new SqlParameter("loginName", loginName),
    new SqlParameter("password", password));

Note we're still using the SqlParameters to prevent SQL injection attacks. We are also using the T-SQL method QuoteName to do proper quoting in the SQL we are generating; but this method simply doubles any [ characters (in the first call) or ' characters (in the second). There are many other vectors for a SQL injection attack, so merely relying on QuoteName wouldn't be enough.

check here https://stackoverflow.com/questions/14858034/how-to-change-a-sql-login-password-with-variables — SmartestVEGA, Jun 25 '19 at 07:13
https://stackoverflow.com/questions/14858034/how-to-change-a-sql-login-password-with-variables worked for me, thanks buddy. — Dave, Jun 25 '19 at 12:30

Connection problem when changing password

1 Answers1