Forum Discussion
ahamedsulthan5
Jan 09, 2024Copper Contributor
Login failed for user AG secondary replica
When i perform the restore and AG secondary replica received the following error Login failed for user 'DSPDomain\dsp_sql'. Reason: Failed to open the explicitly specified database 'PS_Data ...
Jan 09, 2024
Hello,
Note that Logins are not replicated automatically from Primary to replicas so you have to create it manually to all the replicas
do not create the login on replicas manually. use below script. you should run it on the primary and the output will be the script that has to be applied to the replicas. It will help you to transfer logins, passwords (for sql logins ( and all the hashes)
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances
Also you are referencing the account DSPDomain\gshpv-sql02$ . make sure it is created as Login on the replica after you run the scripts
Regards
Javier
Note that Logins are not replicated automatically from Primary to replicas so you have to create it manually to all the replicas
do not create the login on replicas manually. use below script. you should run it on the primary and the output will be the script that has to be applied to the replicas. It will help you to transfer logins, passwords (for sql logins ( and all the hashes)
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances
Also you are referencing the account DSPDomain\gshpv-sql02$ . make sure it is created as Login on the replica after you run the scripts
Regards
Javier
ahamedsulthan5
Jan 15, 2024Copper Contributor
Thank you for your prompt response. After adding the service account has successfully resolved the issue.