How do you make a SQL Server Authentication password change take effect in SQL Backup Agent service

jlray

We use a SQL Server Authentication Login account for the SQL Backup Agent service to access a database instance.  Recently we change the password for this account.  I then executed the extended stored procedures below against master and restarted the service.   It appears that the password is not being updated in the service as running any back up job will fail with an authentication error.   I use the same account for the server within the GUI to connect to and it does connect to the database instance without issue.    So far the only way I've been able to get around this problem is to uninstall/reinstall the SQL Backup components and then supply the correct password to the service when going through the install process.  I'm thinking it shouldn't be this hard to make a password change.  Am i missing something here?  Thanks, Jerry

EXECUTE master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'

EXECUTE master..sqbsetlogin '<login goes here>', '<pwd goes here>'
EXECUTE master..sp_dropextendedproc sqbsetlogin

petey2

Try either of these:

EXEC master..sqbsetlogin 0, '<user name>', '<password>'

or 

EXEC master..sqbutility 9999, '<user name>', '<password>'

Will get the documentation corrected for the sqbsetlogin command.

squigley

Good Afternoon!

You should be able to change the SQL Server Authentication login account information from the SQL Backup GUI.

You can read how to do so from the following documentation:

https://documentation.red-gate.com/sbu/getting-started/installing/adding-sql-server-instances#AddingSQLServerinstances-EditingaSQLServerinstanceregistration

Let me know if you have any other questions.

jlray

Yes,  I was able to change password in the Server/Instance registration via the GUI but that password does not propagate to the backup service running on the server.  https://documentation.red-gate.com/sbu/permissions#Permissions-ChangingtheSQLBackupAgentservicecredentials

From the documentation:
If you are using SQL Server authentication and you change the account password, you must also apply the change to the SQL Backup Agent service, otherwise backups and restores may fail. This can be done using the sqbsetlogin stored procedure as above, but specifying the new account credentials on step 2.

I did the above and restarted the service but the service still did not use the new password.  So far, I've had to reinstall the backup service on 2 servers because the services would not recognize the password change.

jlray

Yes,  I was able to change password in the Server/Instance registration via the GUI but that password does not propagate to the backup service running on the server.  https://documentation.red-gate.com/sbu/permissions#Permissions-ChangingtheSQLBackupAgentservicecredentials

From the documentation:
If you are using SQL Server authentication and you change the account password, you must also apply the change to the SQL Backup Agent service, otherwise backups and restores may fail. This can be done using the sqbsetlogin stored procedure as above, but specifying the new account credentials on step 2.

I did the above and restarted the service but the service still did not use the new password.  So far, I've had to reinstall the backup service on 2 servers because the services would not recognize the password change.

jlray

Yes,  I was able to change password in the Server/Instance registration via the GUI but that password does not propagate to the backup service running on the server.  https://documentation.red-gate.com/sbu/permissions#Permissions-ChangingtheSQLBackupAgentservicecredentials

From the documentation:
If you are using SQL Server authentication and you change the account password, you must also apply the change to the SQL Backup Agent service, otherwise backups and restores may fail. This can be done using the sqbsetlogin stored procedure as above, but specifying the new account credentials on step 2.

I did the above and restarted the service but the service still did not use the new password.  So far, I've had to reinstall the backup service on 2 servers because the services would not recognize the password change.