mssql 2019 Always on后，故障转移后孤立用户问题解决

mssql 2019 Always on后，故障转移后孤立用户问题解决

问题：

数据库配置了Always ON高可用性组。还有的可以访问到主体数据库登录一次，但转移后，就无法登录到备用服务器上的新主体数据库。 

原因：

该问题是因为每台服务器上的SQL Server登录的SID（安全标识符）不匹配。尽管对于登录的名字是相同的，但登录经由该login的SID解决。这不是Windows /域用户/组登录的问题，
因为这些登录的SID是基于域SID为用户/组创建，因此将成为给定同一用户/组相同的，无论添加到什么样的SQL Server用户/组。

 解决方案：

我们需要在secondary replica上面创建SQL Server login，不仅仅具有相同的名称，而且还具有相同SID在主服务器上创建SQL Server登录。下面相关的语句：

在主库上执行的语句

SELECT

  'create login [' +p.name + '] ' +

   case when p.type in('U','G') then 'from windows ' else '' end +

  'with ' +

  case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +

  'sid = ' + master.sys.fn_varbintohexstr(l.sid) +

  ',check_expiration = ' + case when l.is_expiration_checked >0 then 'ON, ' else 'OFF, ' end +

  'check_policy= ' + case when l.is_policy_checked> 0 then 'ON, ' else 'OFF, ' end +

   case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end

   else '' end +

   'default_database = ' + p.default_database_name+

   case when len(p.default_language_name) >0 then ',default_language = ' + p.default_language_name else '' end

FROM sys.server_principals p

LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id

LEFT JOIN sys.credentials c ON  l.credential_id= c.credential_id

WHERE p.type in('S','U','G')

AND p.name<> 'sa'

将在主服务器上面运行，并将输出的结果在副本中执行。

主库输出结果如下：

 

 

在备库上执行完后。再次故障切换后，该问题没有复现。应用用户登录db正常。