Problem
I have been in a situation where our Security compliance team requested to prove that remote access configuration is indeed mandatory to setup log shipping for a Biztalk database. As all we know this configuration option is used to execute remote stored procedures from local server or allow access from remote server to execute a local stored procedure. Of course this has to be accompanied with a linked server that provide RPC_OUT services.
Biztalk Logshipping (Quick review):
The setup of Biztalk Log shipping works in slight different way, although similar to, SQL Server log shipping. Due to a support limitation from Microsoft, we can only configure Biztalk Log shipping for Biztalk related databases because Biztalk uses MSDTC transactions whereby SQL transaction atomicity/integrity cannot be assured for distributed transactions.
Microsoft Biztalk provides a script to create all the log shipping related objects that include tables and stored procedures. Even on standalone servers these procedures are needed for backup as a normal backup strategy cannot cover the transactional atomicity after restoration.
Once all objects are created, It is important to mention that a single stored procedure, bts_ConfigureBizTalkLogShipping, can configure complete the log shipping configuration for the Biztalk databases, including the SQL Agent Jobs on the Primary and Secondary servers. On the Primary Server, It create a job normally with the name of “Backup BizTalk Server (BizTalkMgmtDb)” that takes backups to a UNC location on the secondary server. This location is provided as a parameter of the sp_BackupAllFull_Schedule procedure for full backups and of sp_MarkAll for all log backups.
On the Secondary server, It create three jobs: BTS Log Shipping - Get Backup History, BTS Log Shipping - Restore Databases and BTS Log Shipping - Restore To Mark. The names elaborate the functionality, however, to clear any confusion, the BTS Log Shipping - Restore To Mark job helps to bring databases online on the secondary server using WITH RECOVERY while recoverying databases from the available transaction logs that are backed up by the Primary SQL Server.
Both restore jobs do not connect to the Primary server as they depend on information reterived by ‘Get Backup History’ that exists on the secondary server, which connects to the Primary server and reterives the backup history information, including file name and location. This is where ‘remote access’ option have to be enabled to allow accessiblity. This job runs a stored procedure ‘bts_GetBTSBackupHistoryFromSource’ that executed ‘sp_GetBackupHistory’ from BiztalkmgmtDB database on Primary Server using Linked Server.
Query from sp_GetBackupHistory:
SELECT @sql = N'INSERT #lgs_tmp ( BackupId, BackupSetId, MarkName, DatabaseName, ServerName, BackupFileName, BackupFileLocation, BackupType, BackupDateTime, SetComplete ) EXEC [' + REPLACE(@ServerName, '''', '''''') + N'].[' + REPLACE(@DatabaseName, '''', '''''') + N'].[dbo].[sp_GetBackupHistory] @LastBackupSetId=' + CAST(@LastBackupSetId AS NVARCHAR(32));
Test to verify the Dependency
In my test environment, the Primary and Secondary servers have SQL Server 2008R2 Enterprise Edition (64bit) – [10.50.6529] installed. In order to disable the remote access configuration option, one can execute following command or uncheck the option in Instance Properties -> Connections -> “allow remote connections to this server”. Either of these must be followed by SQL Services restart.
exec Sp_configure ‘remote access’ , 0; GO Reconfigure with override; GO
The moment I disabled the remote access Get Backup History Job start failing with the error shown below.
Could not execute procedure on remote server because SQL Server is not configured for remote access. Ask your system administrator for reconfigure SQL Server to allow remote access. [SQLSTATE 42000] [Error 7201]
This is where I decided to re-enabled the configuration to allow the job to perform its usual task,as shown in screenshot below
Exec Sp_configure ‘remote access’ , 1; GO Reconfigure with override; GO
Understanding of Scope
According to MSDN (ref 1), remote access is available for backward compatibility for linked servers that are created with sp_addserver. However, this test shows that a linked server created with sp_addlinkedserver will still be dependent on remote access.
References:
