Configuration Manager can be configured with site server availability.
High availability for the site server role is a Configuration Manager-based solution to install an additional site server in passive mode. The central administration site and child primary sites can have an additional site server in passive mode. The site server in passive mode can be on-premises or cloud-based in Azure.
Recently I helped a customer fix an issue about site installation in passive mode. The issue was that passive node installation failed at Prerequisite Check indicating that SQL Server sysadmin rights failed with the following message in Site Server Installation Status window.
[Failed]: Verifies that the user account running Configuration Manager Setup has been granted sysadmin SQL Server role permissions on the SQL Server instance selected for site database installation. SQL Server sysadmin role permissions are required in order to create the site database and configure necessary database role and login permissions for Configuration Manager sites.
The customer said he had granted sysadmin security role on the instance of SQL Server to the passive node computer account. I verified what he claimed with the following query (you can also navigate to <Instance>/Security/Logins node in SSMS Object Explorer to verify the logins) –
SELECT SP.name AS LoginName,
sp.is_disabled AS IsDisabled,
type_desc AS LoginType,
Cast(create_date AS DATE) DateCreated
FROM sys.server_principals SP
inner join sys.syslogins SL
on SP.sid=SL.sid
WHERE SL.sysadmin=1
I also checked the access of the passive node to the site database on the instance of SQL Server with the following query (you can also verify this using SSMS Object Explorer and navigate to <Instance>/Databases/<Site-DB>/Security/Users) –
select SP.name as Username,
SP.create_date as Create_Date,
SP.modify_date as Modify_Date,
SP.type_desc as Type,
SP.authentication_type_desc as Authentication_Type,
SL.name as Login_Name
from sys.database_principals as SP, sys.syslogins as SL
where SP.type not in ('A', 'G', 'R', 'X')
and SP.sid is not null
and SP.name != 'guest'
and SP.sid = SL.sid
order by Username;
Both looks good. Then I turned to the critical log for site high availability troubleshooting and found the following information.
FailoverMgr.log on the active site –
SMS_FAILOVER_MANAGER ERROR: timed out waiting for service smstsvc_passive_8 to finish. The test result will be inconclusive. 6556 (0x199C) SMS_FAILOVER_MANAGER failed to find result file \\passivenode.contoso.com\admin$\smstsvc_passive_8.rst (80070002) 6556 (0x199C) SMS_FAILOVER_MANAGER Cancelling network connection to \\passivenode.contoso.com\ADMIN$. 6556 (0x199C) SMS_FAILOVER_MANAGER GetResultByRunningService failed with error (80070002) 6556 (0x199C) SMS_FAILOVER_MANAGER prereq check DDFF046F-3555-41FE-9AF9-655345067D0B has failed. 6556 (0x199C)
It points to not being able to find the rst file. This information is, in fact, a bit confusing and misleading because the status window tells us that there is something wrong with the SQL Server prerequisites. Then I looked at the smstsvc.log, which Windows uses this service to test network connectivity and permissions between servers.
smstsvc.log from the passive node –
try to open file \\passivenode.contoso.com\admin$\smstsvc_passive_8.in Enforced using sql native client to talk to SQL server. INFO: Parameter SQL Server pseudodb.contoso.com, Database SCCMDB_PS\master. SQL Tcp Port: 1433 ERROR: Failed to connect SQL Server pseudodb.contoso.com, Database SCCMDB_PS\master. INFO: SQLADMIN return 0
Voila! The passive node failed to connect to the site database. I tested the tcp connection with –
Test-NetConnection pseudodb.contoso.com -Port 1433
All good. What? The result was a surprise because I was expecting a tcp connection failure. Then I created a UDL file to test sql server connectivity. I tried with pseudodb.contoso.com\SCCMDB_PS and Use Windows NT integrated security and database Master as printed in smstsvc.log. The connection failed. Then I tested with Noticed that the site database is using pseudodb.contoso.com and pseudodb with Use Windows NT integrated security and database Master. Guess what? The connection succeeded.
Now the truth is on the verge. Notice that the site database is using named instance SCCMDB_PS, which usually requires SQL Server Brower being enabled. So, I logged onto the site database server pseudodb.contoso.com to check if SQL Server browser is enabled. Contrary to my expectation, SQL Server browser is enabled. Why the passive node still fails to connect to the named instance then? There is another possibility, which is that the passive node could not get the right port which should be returned by SQL Server browser. SQL Server browser uses port 1434 to respond with the TCP/IP port or named pipe of the requested instance to clients requesting SQL Server resources.
I downloaded PortQuery and test the connection to UDP port 1434 of the site database. The result is no surprise as below –
UDP port 1434 is filtered, meaning no response from the server. There are two possibilities: First, 1434 is not listening. This is not true because I just checked SQL Server browser is enabled and listening. Second, communication from the passive node to UDP port 1434 of the site database is blocked. I asked the customer if there is any firewall between the passive node and the site database and he said yes.
There are two ways to resolve the issue –
- Either make UDP 1434 connectable between this two machine
- Or add alias in CliConfg.exe – this is what we selected to resolve the issue
- pseudodb.contoso.com\SCCMDB_PS, 1433
- pseudodb\SCCMDB_PS, 1433
SCCM Monde 