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
Click on Security and make sure that SQL Server and Windows Authentication mode is selected. Then click OK. In the Object Explorer expand Security>Logins and then right click on the user to change the password. Change the password and confirm the change.
Setting a Value in a Transact-SQL Variable To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With