How to change a sql login password with variables

Question

I am trying to update the password for an existing SQL login using Alter LOGIN

I know the following works

ALTER LOGIN [username1] WITH PASSWORD = 'somenewpassword123';

However when I try to use a local variable

DECLARE @newpass nvarchar(max);
SET @newpass = 'P@ssw0rd12345';
ALTER LOGIN [username1] WITH PASSWORD = @newpass;

This fails. Adding [] braces to the variable seems to resolve this within the SSMS query editor however using this programmaticlly by writing out the query in C# it fails as the above statement with the same error ( syntax error at PASSWORD)

Code within c# app

public static int UpdateSqlLoginPassword(DbContext context, string loginName, string password)
{
 try
 {
  string updatePassword =
         @"  SET NOCOUNT ON
          DECLARE @loginName AS nvarchar(max) = {0}
          DECLARE @password AS nvarchar(max) = {1}
          EXEC('
          USE master
          ALTER LOGIN ['+ @loginName + '] WITH PASSWORD = ['+ @password + ']
          ')";
  return context.Database.ExecuteSqlCommand(updatePassword, loginName, password);
 }
 catch (Exception)
 {  
  return -2;
 }
}

I have also tried to hash the password (thinking that was the issue with the variable) but the syntax here is not being accepted

DECLARE @newpass nvarchar(max);
SET @newpass = 'P@ssw0rd12345';
DECLARE @hashedpass varbinary(max);
SET @hashedpass = HASHBYTES('SHA1', CONVERT(nvarchar(max),@newpass));

ALTER LOGIN [newuser10] WITH PASSWORD = @hashedpass HASHED;
SELECT @hashedpass;

Can anyone help me understand how to update a login's password in sql using a variable instead of a fixed value?

thanks in advance

Update

Based upon a suggestion from Charlie I also tried the following

public static int UpdateSqlLoginPassword(DbContext context, string loginName, string password)
        {
            try
            {
                string updatePassword =
                    @"ALTER LOGIN [' + @loginName +'] WITH PASSWORD =  @password ";
                return context.Database.ExecuteSqlCommand(updatePassword, new SqlParameter("loginName", loginName), new SqlParameter("password", password));
            }
            catch (Exception)
            {  
               return -2;
            }
        }

This still generates a sqlException Incorrect Syntax new '@password'. If I brace the parameter

public static int UpdateSqlLoginPassword(DbContext context, string loginName, string password)
        {
            try
            {
                string updatePassword =
                    @"ALTER LOGIN [' + @loginName +'] WITH PASSWORD =  [' + @password +']";
                return context.Database.ExecuteSqlCommand(updatePassword, new SqlParameter("loginName", loginName), new SqlParameter("password", password));
            }
            catch (Exception)
            {  
               return -2;
            }
        }

I then generate a sqlException Incorrect syntax near PASSWORD.

Update2

Using the updated suggestions from Charlie I attempted to use the QuoteName function

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

While it appears that the query string is properly formed the following SQLException is thrown *The name 'ALTER LOGIN [newuser10] WITH PASSWORD = 't#P@ssw0rd'' is not a valid identifier.

EDIT

After some more reading the error was generated by a syntax error wrapping the @sql allows the query to execute with no errors

 string sql = @"DECLARE @sql NVARCHAR(500)
                  SET @sql = 'ALTER LOGIN ' + QuoteName(@loginName) +
                    ' WITH PASSWORD = ' + QuoteName(@password, '''') 
                    EXEC(@sql)";

On a side note: by simply building the string and running it as

string updatePassword = "USE MASTER ALTER LOGIN [" + loginName + "] WITH PASSWORD =  '" + password + "'";
return context.Database.ExecuteSqlCommand(updatePassword);

the above is also a workaround and updates the sql login. While the implementation of this code minimizes the potential for sql injections this is not the most desirable approach.

-Thanks

Answer 1

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.

Answer 2

I'm using the above answer with Azure SQL and I was getting the "not a valid identifier" error until I surrounded replaced "EXEC @sql" with "EXEC (@sql)". See Msg 203, Level 16, State 2, is not a valid identifier

Additionally, I had to use "ALTER USER" instead of "ALTER LOGIN"

Answer 3

After Preparing SQL query string and executing using c# SQL Command, I was always getting Invalid Identifier error. It was because QuoteName should get executed before executing change password sql statements. So I created stored procedure using above solutions then called procedure from c#, it worked for me.

Create procedure usp_updateSqlUsers(@loginName nVarchar(100), @pwd nvarchar(100))
as
 begin
DECLARE @sql NVARCHAR(500) 
set @sql='Alter LOGIN  '+QUOTENAME(@loginName)+' WITH 
  password=N'+ QUOTENAME(@pwd,'''')
  exec sp_sqlexec @sql

 end

Then execute from C#

SqlCommand cmd = new SqlCommand("usp_updateSqlUsers", con) {CommandType = 
 CommandType.StoredProcedure};
 var passwordParam = new SqlParameter("@pwd", password);
 var sqlLoginParameter = new SqlParameter("@loginName", "SqlLoginName");
 cmd.Parameters.Add(passwordParam);
 cmd.Parameters.Add(sqlLoginParameter);
 cmd.ExecuteNonQuery();