Replicas with non-identical service accounts

SQL Servers that are replicas in Availability Groups should have identical SQL Server service accounts. If the SQL Server service accounts are not identical, it is not possible to set up SPNs correctly to linked servers pointing to the Availability Group listener.

To query the Performance Store database for replicas with non-identical service accounts, use:

declare @i int = 1
declare @numberOfRows int

create table #temp
(
    id int identity(1, 1) not null,
    server_name1 nvarchar(128) not null,
    server_name2 nvarchar(128) not null
)

insert into #temp (server_name1, server_name2)
exec dbo.s_get_server_pairs

set @numberOfRows = @@rowcount

declare @server_name1 nvarchar(128)
declare @server_name2 nvarchar(128)
declare @sql_service_account1 nvarchar(128)
declare @sql_service_account2 nvarchar(128)

create table #nonIdenticalServiceNames
(
    server_name nvarchar(128) not null,
    sql_service_account nvarchar(128) not null
)

while @i <= @numberOfRows
begin
    select @sql_service_account1 = s.sql_service_account, @server_name1 = s.server_name
    from dbo.v_servers s
    inner join #temp t on t.server_name1 = s.server_name
    where t.id = @i
    
	select @sql_service_account2 = s.sql_service_account, @server_name2 = s.server_name
    from dbo.v_servers s
    inner join #temp t on t.server_name2 = s.server_name
    where t.id = @i

    if @sql_service_account1 != @sql_service_account2
    begin
        insert into #nonIdenticalServiceNames (server_name, sql_service_account)
        select @server_name1, @sql_service_account1

        insert into #nonIdenticalServiceNames (server_name, sql_service_account)
        select @server_name2, @sql_service_account2
    end
    set @i = @i + 1
end

select t.server_name, t.sql_service_account
from #nonIdenticalServiceNames t

drop table #temp, #nonIdenticalServiceNames

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.