How to change sql login password with variables

Question

I am trying to update a 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; 

It fails. Adding [] brackets to the variable seems to resolve this in the SSMS query editor, however, using this program, writing the request in C #, it does not work, as indicated above, with the same error (syntax error in PASSWORD)

Code inside a C # application

 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 also tried to hash the password (assuming this is a problem with a variable), but the syntax is not accepted here

 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 someone help me figure out how to update the password for logging into sql using a variable instead of a fixed value?

early

Update

Based on Charlie's suggestion, 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; } } 

It still throws sqlException The wrong syntax for the new "@password". If I bind 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; } } 

Then I create a sqlException The wrong syntax is near PASSWORD.

Update2

Using updated sentences from Charlie, I tried 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)); 

As long as the query string appears to be correctly formed, the following SQLException is raised * The name 'ALTER LOGIN [newuser10] WITH PASSWORD =' t # P @ ssw0rd '' is not a valid identifier.

EDIT

After some more errors, the error was generated using a syntax error, as a result of which @sql allows the query to be executed without errors

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

Side note: just building a line 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 implementing this code minimizes the potential for sql injections, this is not the most desirable approach.

-Thanks

Answer 1

You need to use the parameters at the DbContext level. See this answer for more details, but here is a sample code (adapted from the 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 parameters here (and everywhere) is to prevent an SQL injection attack. This is especially important given that you write code that changes the password.

UPDATE

The ALTER LOGIN statement will not work with variables; this should be done through dynamic SQL. Here is 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 that we are still using SqlParameters to prevent SQL injection attacks. We also use the T-SQL QuoteName method to perform the correct quotation in the SQL that we generate; but this method simply doubles any characters [ (in the first call) or ' characters (in the second). There are many other vectors for SQL injection attacks, so just relying on QuoteName will not be enough.

Answer 2

I use the above answer with Azure SQL and I get an "invalid identifier" error until I replace "EXEC @sql" with "EXEC (@sql)". See Msg 203, Level 16, State 2, is not a valid identifier.

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

Answer 3

After preparing the SQL query string and executing with the C # SQL command, I always got an Invalid Identifier error. This is because QuoteName must be executed before executing the sql password change statements. So I created a stored procedure using the solutions described above, and then called the procedure from C #, and 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();