change replica login password? (mssql server)

[cbuckley]

As part of our security policy, we need to change login passwords. I was able to do that on the main instance but on our replica, I cannot change passwords. There is no option in the console and trying to do it using the alter login command results in an error that I do not have permission to change the password, even though I am logged in as the sqlserver user. 

Is there a workaround? this is a pretty major hole in the process

SQL Command:  ALTER LOGIN sqlserver WITH PASSWORD='xxxxx' OLD_PASSWORD='xxxx';

Error: 

Msg 15151, Level 16, State 1, Line 3
Cannot alter the login 'sqlserver', because it does not exist or you do not have permission.

screenshot below of console

[-Rhett]

Hi @cbuckley,

When using replicas on Cloud SQL, there is a known limitation where logins created after the replica is created won’t be propagated to the replica. However, you can try this workaround to manually create the logins in the replica:

1. Create a login in your primary instance using Cloud SQL or using TSQL command
2. Grant the required permissions in the database at primary instance
3. Run this TSQL query at the primary instance to get the SID of newly created login
   select sid,name from sys.syslogins where name=[*loginname*]
4. Create a login in the replica by executing this TSQL query using SQLserver or any other login which already exists in the replica and have ALTER ANY LOGIN PERMISSION.

CREATE LOGIN [*loginname*]
WITH PASSWORD = '[*password*]',
SID = [*SID_from_step_3*];

If you’re still getting the same error, try the troubleshooting steps on this Stack Overflow thread, or this thread from the Microsoft Community Hub by jose_manuel_jurado.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[cbuckley]

The login already exists. Its the sqlserver user/login that I cannot change the password to. This is a major security issue. As the super user of the server google has provided, it absolutely should have the ability to change its own password on the replica. I have already change the password on the primary and now we are out of sync

[chenry1]

For anyone else running into this issue, I was able to change the sqlserver account password by logging in to the replica using a different account with the ALTER ANY LOGIN permission. Since this was a brand new instance I used the below to create the new login, grant it the required permission, and then use it to update the sqlserver account:

--while logged in to replica as sqlserver
CREATE LOGIN [<newuser>]
WITH PASSWORD = '<newUserPassword>'
GRANT ALTER ANY LOGIN TO [<newuser>] AS CustomerDbRootRole

--while logged in to replica as new login created above
ALTER LOGIN sqlserver WITH PASSWORD = '<sqlserverPassword>'

--while logged in to replica as sqlserver
DROP LOGIN [<newuser>]