How to Change a SQL Login’s Password

2014-04-04 - Fundamentals, General, Security

Introduction

SQL Server implements two types of authentication: Windows Authentication and SQL Authentication. Windows Authentication is often also called Integrated Security. Which authentication type is applied is dependent on the server principal used to log on. If the server principal in SQL Server is based on a Windows account or a Windows group, windows authentication has to be used. If the login is a SQL Login, SQL Authentication is applied. With windows Authentication, SQL Server delegates the actual authentication process to windows without ever touching a password. With SQL Authentication SQL Server has to check the password itself. That means that a SQL Login always has to have an associated password stored within SQL Server (if you want to be able to use it to log on).

Today I am going to look at, how you can change that password.

How to use ALTER LOGIN to Change a Password

To change the password of a SQL Login, the ALTER LOGIN command can be used. To demonstrate, we first need to create a login:

<br />
CREATE LOGIN ALogin WITH PASSWORD = 'Passw0rd!';<br />

The syntax of the ALTER LOGIN statement is very similar to the CREATE LOGIN statement. To change the password of our login we can just replace the CREATE keyword with ALTER:


Secrets To Selecting The Correct SQL Server Service Account Video

<br />
ALTER LOGIN ALogin WITH PASSWORD = '2Secr@s';<br />

The following screenshot shows that in action:

ALTER LOGIN ... WITH PASSWORD

SQL Server stores the password as a salted hash, so we cannot just look at the stored password. However, we can see that hash value. The screenshot shows the before and after value to demonstrate that the password did indeed change.

Changing Your Own Password

To change the password of any SQL Login on the server, you need to have been granted the ALTER ANY LOGIN server privilege. However, you would expect that you could change your own password. Let us try:

<br />
EXECUTE AS LOGIN = 'ALogin';<br />
GO<br />
ALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?';<br />
GO<br />
REVERT;<br />

Executing this fails:

Alter own password failed.

The reason is that in this context SQL Server requires the old password to also be specified, like this:

<br />
ALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?' OLD_PASSWORD = '2Secr@s';<br />
 

That now leads to a successful password change:

ALTER LOGIN ... WITH PASSWORD ... OLD_PASSWORD

While this works, it feels a little odd. If I have authenticated using my password already, why do I need to re-authenticate for a password change again? The reason is that Microsoft wanted to prevent principals that just have the IMPERSONATE privilege on a login to be able to change the password for that login. In the example above I used EXECUTE AS and did not anywhere supply the password.

As every login has control permission on itself, that privilege alone cannot be enough to change a login's password. Therefore you have to specify the current password if you want to change your own password. You actually can change any login's password, as long as you have the CONTROL orALTER privilege on that login and you now its current password.

If you on the other hand have been granted the ALTER ANY LOGIN server privilege, you can change any login's password without specifying the current one, even if you change your own:

ALTER ANY LOGIN in Action.

Changing the Password for a Windows Login

In the case of a windows login, SQL Server delegates the authentication to Windows. SQL Server never gets to store or even see a password. That means you cannot change a Windows account password from within SQL Server. If you tried to use ALTER LOGIN ... WITH PASSWORD on a windows login, you would get this error message:

<br />
Msg 15080, Level 16, State 1, Line 1<br />
Cannot use parameter PASSWORD for a Windows login.<br />

To actually change the password of a windows account, you have to use any documented windows method.

Summary

To change the password of the SQL Login in SQL Server you can use the ALTER LOGIN ... WITH PASSWORD command. To be able to change the password of any SQL Login you must have been granted the ALTER ANY LOGIN server privilege. However, you always can change your own password by specifying the OLD_PASSWORD.

Categories: Fundamentals, General, Security
Tags: , , ,

2 comments
jackstollery
jackstollery

Great informative article! It seems SQL Server 2012 has changed the algorithms to encode the password. The length of password hash is much longer than that of SQL Server 2008. SQL Server Password Changer is a small utility that can reset SA password by replacing the password hash. But I can't figure out what algorithms are used by the latest version of SQL Server.

@sqlity
@sqlity moderator

@jackstollery  the algorithm is a salted SHA2_512:


DECLARE @pswd NVARCHAR(MAX) = 'APassword'; DECLARE @salt VARBINARY(32) = CRYPT_GEN_RANDOM(4);

DECLARE @hash VARBINARY(MAX) = 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@pswd AS VARBINARY(MAX)) + @salt);

SELECT @hash, PWDCOMPARE(@pswd,@hash)


However, I would be extremely careful using a tool that makes claims like the one you are referencing. If a tool directly updates a system table (which it will have to to live up to its claims) you will immediately loose any Microsoft support. SQL Server audits such updates and Microsoft looks there first on any support request.


There are plenty documented (and free) ways to recover access to a lost SA password.