The process could not connect to Subscriber

> Transactional Replication is configured between 2 managed instances.

-> More details on how to configure transactional replication between 2 managed instance can be understood from this article.

-> Distribution agent fails with below error,

The process could not connect to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'.

Disconnected from Azure Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata' with OS result code: 2250.

Starting agent.
Error messages:

The process could not connect to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084

Windows logins are not supported in this version of SQL Server. (Source: MSSQLServer, Error number: 40607)
Get help: http://help/40607
Date		8/13/2020 7:58:29 PM
Log		Job History (jbmi-publisher.37b2ca-JBREPL_Sub-TranProducts-JBSWIKI-MI-DR.37B2CA9-3)

Step ID		2
Server		JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
Job Name		jbmi-publisher.37b2ca-JBREPL_Sub-TranProducts-JBSWIKI-MI-DR.37B2CA9-3
Step Name		Run agent.
Duration		00:10:52
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
2020-08-13 14:39:13.505 Copyright (c) 2016 Microsoft Corporation
2020-08-13 14:39:13.505 Microsoft SQL Server Replication Agent: distrib
2020-08-13 14:39:13.505 
2020-08-13 14:39:13.505 The timestamps prepended to the output lines are expressed in terms of UTC time.
2020-08-13 14:39:13.505 User-specified agent parameter values:
			-Subscriber JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET
			-SubscriberDB JBREPL_Sub
			-Publisher jbmi-publisher.37b2ca9fee0a.database.windows.net
			-Distributor jbmi-publisher.37b2ca9fee0a.database.windows.net
			-DistributorSecurityMode 1
			-Publication TranProducts
			-PublisherDB JBREPL_Sub
			-Continuous
			-XJOBID 0x1F311561B8AC2A498D5487323D49323D
			-XJOBNAME jbmi-publisher.37b2ca-JBREPL_Sub-TranProducts-JBSWIKI-MI-DR.37B2CA9-3
			-XSTEPID 2
			-XSUBSYSTEM Distribution
			-XSERVER jbmi-publisher.37b2ca9fee0a.database.windows.net
			-XCMDLINE 1
			-XDB
			-XHOST np:\\.\pipe\DB4C.0-A9190D899828\sql\query
			-XCancelEventHandle 0000000000001B8C
			-XParentProcessHandle 0000000000001B24
2020-08-13 14:39:13.505 Startup Delay: 5810 (msecs)
2020-08-13 14:39:19.354 Connecting to Distributor 'jbmi-publisher.37b2ca9fee0a.database.windows.net'
2020-08-13 14:39:19.447 Obtained Azure Storage Connection String for jbmirepldata
2020-08-13 14:39:19.447 Connecting to Azure Files Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata'
2020-08-13 14:39:21.872 Parameter values obtained from agent profile:
			-bcpbatchsize 0
			-commitbatchsize 100
			-commitbatchthreshold 1000
			-historyverboselevel 1
			-keepalivemessageinterval 300
			-logintimeout 15
			-maxbcpthreads 1
			-maxdeliveredtransactions 0
			-pollinginterval 5000
			-querytimeout 1800
			-skiperrors 
			-transactionsperhistory 100
2020-08-13 14:39:21.872 Connecting to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'
2020-08-13 14:39:21.934 Disconnected from Azure Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata' with OS result code: 0.
2020-08-13 14:39:21.950 Agent message code 20084. The process could not connect to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'.
2020-08-13 14:39:21.981 Category:NULL
Source:  Microsoft OLE DB Driver for SQL Server
Number:  40607
Message: Windows logins are not supported in this version of SQL Server.

-> Azure SQL Managed Instance is connected using a SQL login. But the error states that windows logins are not supported. It seems like we have not keyed in the SQL logins properly while configuring the replication.

-> Open subscription properties by performing below actions,

-> Start the distribution agent and it will complete fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Transactional Replication – Failed to connect to Azure Storage ” with OS error: 55.

-> Transactional Replication is configured between 2 managed instances.

-> More details on how to configure transactional replication between 2 managed instance can be understood from this article.

-> Snapshot agent fails with below error,

Failed to connect to Azure Storage '' with OS error: 55.

Connecting to Azure Files Storage '\\jbmirepldata.file.core.windows.net/jbmirepldata'

Obtained Azure Storage Connection String for jbmirepldata

Connecting to Distributor 'JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET'

Initializing
Date		8/13/2020 7:16:09 PM
Log		Job History (JBMI-PUBLISHER.37B2CA9FEE0A.-JBREPL_Sub-TranProducts-1)

Step ID		2
Server		JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
Job Name		JBMI-PUBLISHER.37B2CA9FEE0A.-JBREPL_Sub-TranProducts-1
Step Name		Run agent.
Duration		00:00:02
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
2020-08-13 13:46:09.98 Microsoft (R) SQL Server Snapshot Agent
2020-08-13 13:46:09.98 [Assembly Version = 15.0.0.0, File Version = 15.0.2000.1929]
2020-08-13 13:46:09.98 Copyright (c) 2016 Microsoft Corporation.
2020-08-13 13:46:09.98 The timestamps prepended to the output lines are expressed in terms of UTC time.
2020-08-13 13:46:09.98 User-specified agent parameter values:
2020-08-13 13:46:09.98 --------------------------------------
2020-08-13 13:46:09.98 -Publisher JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
2020-08-13 13:46:09.98 -PublisherDB JBREPL_Sub
2020-08-13 13:46:09.98 -Publication TranProducts
2020-08-13 13:46:09.98 -Distributor JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
2020-08-13 13:46:09.98 -DistributorSecurityMode 1
2020-08-13 13:46:09.98 -XJOBID 0x82079C6C631CFB438ABF0DC5E99DC328
2020-08-13 13:46:09.98 -XDB 
2020-08-13 13:46:09.98 -XHOST np:\\.\pipe\DB4C.0-A9190D899828\sql\query
2020-08-13 13:46:09.98 --------------------------------------
2020-08-13 13:46:09.99 Connecting to Distributor pipe 'np:\\.\pipe\DB4C.0-A9190D899828\sql\query'
2020-08-13 13:46:10.09 Connecting to Distributor 'JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET'
2020-08-13 13:46:10.27 Parameter values obtained from agent profile:
2020-08-13 13:46:10.27 ---------------------------------------------
2020-08-13 13:46:10.27 -BcpBatchSize 100000
2020-08-13 13:46:10.27 -HistoryVerboseLevel 2
2020-08-13 13:46:10.27 -LoginTimeout 15
2020-08-13 13:46:10.27 -QueryTimeout 1800
2020-08-13 13:46:10.27 ---------------------------------------------
2020-08-13 13:46:10.27 Validating Publisher
2020-08-13 13:46:10.29 Obtained Azure Storage Connection String for jbmirepldata
2020-08-13 13:46:10.29 Connecting to Azure Files Storage '\\jbmirepldata.file.core.windows.net/jbmirepldata'
2020-08-13 13:46:10.30 Failed to connect to Azure Storage '' with OS error: 55.

-> The main error is as below,

2020-08-13 13:46:10.29 Connecting to Azure Files Storage ‘\\jbmirepldata.file.core.windows.net/jbmirepldata
2020-08-13 13:46:10.30 Failed to connect to Azure Storage ” with OS error: 55.

-> If you look at the path closely, we are able to see a “/” instead of “\” and this is causing the issue.

-> Execute below code on the Distributor to correct the REPLDATA or Working Directory to correct value “\jbmirepldata.file.core.windows.net\jbmirepldata”

exec sp_changedistpublisher @publisher = 'jbmi-publisher.37b2ca9fee0a.database.windows.net', @property = N'Working_Directory', @value = N'\\jbmirepldata.file.core.windows.net\jbmirepldata' 	 

-> Snapshot agent will complete fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Configure Transactional replication between two Azure SQL Managed Instances

Environment

Prerequisites

-> Below 2 Azure SQL Managed Instance will be used,

-> jbmi-publisher acts as Distributor and Publisher.

-> jbswiki-mi-dr acts as Subscriber.

-> SQL Login jvivek2k1 will be used to connect to both Managed instances.

-> I have created a database called JBREPL_SUB on both managed instances.

-> I have created a table called Table1 under database JBREPL_SUB in Managed Instance jbmi-publisher.

-> Create a storage account as below for using it for REPLDATA folder,

  • Create a storage account and name it as jbmirepldata.
  • Once created, open the storage account and click on “File Shares”.
  • On “Overview” tab, click on + “File share”,
  • Create a file share with name jbmirepldata as shown below,
  • Once the file share is created, open it and click on properties,
  • From properties, note down the URL as shown below,
  • Now, change that URL as below,

https://jbmirepldata.file.core.windows.net/jbmirepldata Should be changed to as below,
\\jbmirepldata.file.core.windows.net\jbmirepldata

  • Click on the storage account jbmirepldata again and go to “Access keys”,
  • In Access Keys, copy the Connection String for Key 1, which looks like below,

DefaultEndpointsProtocol=https;AccountName=jbmirepldata;AccountKey=oVJue1Ui+kDJ4DMkMJHUALsoisKEkLsESViS567STNIVcVugYagCFdlNHou16Pf0lrb/Tx+s9BMZD9stCrP7AB4eO+T4A==;EndpointSuffix=core.windows.net

Perform below for setting up replication

-> Login to Publisher Managed Instance and execute below query,

Use [master]
Go
EXEC sp_adddistributor 
@distributor = 'jbmi-publisher.37b2ca9fee0a.database.windows.net'
, @password = '<Strong_Password>'

EXEC sp_adddistributiondb @database = N'distribution'

-> Execute below query to use storage account and file share for Repldata or @working_directory folder,

Make sure you change the @working_directory and @storage_connection_string to correct values as it was created/copied after the storage account creation,


EXEC sp_adddistpublisher @publisher = 'jbmi-publisher.37b2ca9fee0a.database.windows.net', -- primary publisher
     @distribution_db = N'distribution',
     @security_mode = 0,
     @login = N'jvivek2k1',
     @password = N'<Strong_Password>',
     @working_directory = N'\\jbmirepldata.file.core.windows.net\jbmirepldata',
     @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=jbmirepldata;AccountKey=oVJue1Ui+kDJ4DMkMJHUAToiKEkLESViS5STNIVcVugZagCFdlNHou16Of0lrb/Tx+s9BMY9stCrP7AB4O+T4A==;EndpointSuffix=core.windows.net'

-> Using storage account for REPLDATA folder is the recommended approach. But you can also use a UNC path that is present in On-premise or IaaS Azure virtual machine using below query. Please make sure you either use this or above approach,

EXEC sp_adddistpublisher @publisher = 'jbmi-publisher.37b2ca9fee0a.database.windows.net', -- primary publisher
     @distribution_db = N'distribution',
     @security_mode = 0,
     @login = N'jvivek2k1',
     @password = N'<Strong_Password>',
     @working_directory = N'\\10.0.1.4\Repl',
     @storage_connection_string = N'AccountName=JBS\jvivek2k1;AccountKey=<Strong_Password>' --This account should have access to On-premise or Azure virtual machine UNC path.

Above query can be used to create Repldata folder with a share\UNC path either in On-premise or IaaS Virtual machine.

-> Create publication using below query,

use [JBREPL_Sub]
exec sp_replicationdboption 
@dbname = N'JBREPL_Sub'
, @optname = N'publish'
, @value = N'true'
GO
use [JBREPL_Sub]
exec [JBREPL_Sub].sys.sp_addlogreader_agent 
@job_login = N'jvivek2k1'
, @job_password = '<Strong_Password>'
, @publisher_security_mode = 0
, @publisher_login = N'jvivek2k1'
, @publisher_password = N'<Strong_Password>'
, @job_name = null
GO
use [JBREPL_Sub]
exec sp_addpublication 
@publication = N'TranProducts'
, @sync_method = N'concurrent'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'true'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot 
@publication = N'TranProducts'
, @frequency_type = 1
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 8
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = N'jvivek2k1'
, @job_password = '<Strong_Password>'
, @publisher_security_mode = 0
, @publisher_login = N'jvivek2k1'
, @publisher_password = '<Strong_Password>'
use [JBREPL_Sub]
exec sp_addarticle 
@publication = N'TranProducts'
, @article = N'Table1'
, @source_owner = N'dbo'
, @source_object = N'Table1'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'Table1'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboTable1'
, @del_cmd = N'CALL sp_MSdel_dboTable1'
, @upd_cmd = N'SCALL sp_MSupd_dboTable1'
GO

-> Create Subscription using below queries,

use JBREPL_Sub
go
exec sp_addsubscription
@publication = N'TranProducts',
@subscriber = N'jbswiki-mi-dr.37b2ca9fee0a.database.windows.net', 
@destination_db = N'JBREPL_Sub',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

exec sp_addpushsubscription_agent
@publication = N'TranProducts',
@subscriber = N'jbswiki-mi-dr.37b2ca9fee0a.database.windows.net', 
@subscriber_db = N'JBREPL_Sub',
@job_login = N'jvivek2k1',
@job_password = '<Strong_Password>',
@subscriber_security_mode = 0,
@subscriber_login = N'jvivek2k1',
@subscriber_password = '<Strong_Password>',
@dts_package_location = N'Distributor'
GO

-> Once replication is setup. Open replication monitor and check if everything works fine.

-> When I did it for the first time using storage account, I got below error while executing snapshot agent,

Failed to connect to Azure Storage '' with OS error: 53.

Connecting to Azure Files Storage '\jbmirepldata.file.core.windows.net\jbmirepldata'

Obtained Azure Storage Connection String for jbmirepldata

Connecting to Distributor 'JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET'

Initializing

Date		8/13/2020 6:21:08 PM
Log		Job History (JBMI-PUBLISHER.37B2CA9FEE0A.-JBREPL_Sub-TranProducts-1)

Step ID		2
Server		JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
Job Name		JBMI-PUBLISHER.37B2CA9FEE0A.-JBREPL_Sub-TranProducts-1
Step Name		Run agent.
Duration		00:00:02
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
2020-08-13 12:51:08.96 Microsoft (R) SQL Server Snapshot Agent
2020-08-13 12:51:08.96 [Assembly Version = 15.0.0.0, File Version = 15.0.2000.1929]
2020-08-13 12:51:08.96 Copyright (c) 2016 Microsoft Corporation.
2020-08-13 12:51:08.96 The timestamps prepended to the output lines are expressed in terms of UTC time.
2020-08-13 12:51:08.97 User-specified agent parameter values:
2020-08-13 12:51:08.97 --------------------------------------
2020-08-13 12:51:08.97 -Publisher JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
2020-08-13 12:51:08.97 -PublisherDB JBREPL_Sub
2020-08-13 12:51:08.97 -Publication TranProducts
2020-08-13 12:51:08.97 -Distributor JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
2020-08-13 12:51:08.97 -DistributorSecurityMode 1
2020-08-13 12:51:08.97 -XJOBID 0x3077B4849683E349BD566ADA529B5385
2020-08-13 12:51:08.97 -XDB 
2020-08-13 12:51:08.97 -XHOST np:\\.\pipe\DB4C.0-A9190D899828\sql\query
2020-08-13 12:51:08.97 --------------------------------------
2020-08-13 12:51:08.97 Connecting to Distributor pipe 'np:\\.\pipe\DB4C.0-A9190D899828\sql\query'
2020-08-13 12:51:09.07 Connecting to Distributor 'JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET'
2020-08-13 12:51:09.25 Parameter values obtained from agent profile:
2020-08-13 12:51:09.25 ---------------------------------------------
2020-08-13 12:51:09.25 -BcpBatchSize 100000
2020-08-13 12:51:09.25 -HistoryVerboseLevel 2
2020-08-13 12:51:09.25 -LoginTimeout 15
2020-08-13 12:51:09.25 -QueryTimeout 1800
2020-08-13 12:51:09.25 ---------------------------------------------
2020-08-13 12:51:09.25 Validating Publisher
2020-08-13 12:51:09.25 Obtained Azure Storage Connection String for jbmirepldata
2020-08-13 12:51:09.27 Connecting to Azure Files Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata'
2020-08-13 12:51:09.27 Failed to connect to Azure Storage '' with OS error: 53.

-> This issue happens when port 445 is not open.

-> I added a rule to open port 445 in Publisher managed instance Network Security Group to solve this issue,

-> Verify data is flowing to subscriber. You can also use Tracer Token to verify the latency and also to understand if replication is working fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.