Hi ,
Here is the breaf explnation about the issue we are facing.
performed db refresh from prod to dev. Taken the db backup from prod and restored in the new server. and transfered all the logins from prod to dev using sp_help_revlogin as well.
We have setup Alwayson setup for new two servers. We are connecting the SAP application using Listener. The login is 'SAPPJ1DB'
Always configuration is working fine. But when ever we are doing the HA test and application connectivity after faiover and failback the application is not able to connect.
SAP team found the issue with the login 'SAPPJ1DB' from their logs. After every failover,failback they were running below script in the db to connect the application.
declare @mySQLinstance sysname;
declare @error sysname;
set @mySQLinstance = cast(serverproperty('ServerName') as sysname);
set @error = 'This script is intended for SQL Server instance ''FRDGRHSQLPJA1'', ';
set @error = @error + 'but you are connected to ''' + @mySQLinstance + '''';
if upper(@mySQLinstance) != upper('FRDGRHSQLPJA1')
raiserror(@error ,11,1);
else begin
use [master];
-- generate random password
declare @cmd nvarchar(999);
declare @passwd sysname;
set @passwd = 'RAND-pwd.' + cast(abs(checksum(cast(getdate() as float)+ rand()*1234567)) as sysname);
set @passwd = @passwd + cast(abs(checksum(cast(getdate() as float)+ rand()*1234567)) as sysname);
-- if not exists: create SQL Login SAPPJ1DB (random password, should be set later)
set @cmd = 'create login [SAPPJ1DB] with password=''' + @passwd + ''', check_policy = off;';
if not exists (select * from sys.syslogins where name = 'SAPPJ1DB')
execute(@cmd);
alter login [SAPPJ1DB] with default_database = [PJ1], check_expiration = off, check_policy = off;
exec sp_dropsrvrolemember 'SAPPJ1DB', 'sysadmin';
use [PJ1];
-- change database owner to sa
exec sp_changedbowner [sa];
-- create user and schema SAPPJ1DB
if not exists (select * from sys.database_principals where name='SAPPJ1DB' and type = 'S')
create user [SAPPJ1DB] for login [SAPPJ1DB];
alter user [SAPPJ1DB] with default_schema = [SAPPJ1DB];
exec sp_addrolemember 'db_owner', 'SAPPJ1DB';
exec sp_droprolemember 'db_denydatareader', 'SAPPJ1DB';
exec sp_droprolemember 'db_denydatawriter', 'SAPPJ1DB';
exec sp_change_users_login 'Update_One', 'SAPPJ1DB', 'SAPPJ1DB';
if not exists (select * from sys.schemas where name = 'SAPPJ1DB')
exec('create schema [SAPPJ1DB]');
alter authorization on schema::[SAPPJ1DB] to [SAPPJ1DB];
-- end of 'check SQL instance name'
end;
I verified that login sid's of the login SAPPJ1DB in primary and secondary servers. SID's are mismatching. Is this the exact reason why sap application is not able to connect? How to avoid the below script execution for every failover/failback to connect the application? What could be the exact problem . How to fix teh issue?
Thanks,
Pols