Application connectivity is failing using listener

• pols

  SSC Eights!

  Points: 935

  More actions

  July 27, 2022 at 9:31 am

  #4068814

  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

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  July 27, 2022 at 11:12 am

  #4068862

  Best practice is hat match the SIDS. (or you'll have to fix that with every failover )

  Easiest way to do that is do use DBATools (free)  Copy-DbaLogin

   

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me