July 5, 2023 at 8:09 am
All Pub dist and Sub servers are in AG with Sql server 2016 SP3 version and while configuring replication...while adding the distributor listener (without port number) to the publisher server I am getting below error.
OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 2, Level 16, State 1, Line 2
Named Pipes Provider: Could not open a connection to SQL Server [2].
Please note while adding listener name with port number it went fine but replication is failing with error cannot connect to dist server.
I have created the necessary linked servers and alias names . and when I am testing the connection for Repl_distributor linked server none of the places (both Pub and Dist nodes) the test connection is succeeded. And all places it is using distributor_admin login as a credentials.
July 6, 2023 at 8:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 13, 2023 at 9:30 am
Your statement:
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
could be telling you the replica SQL server needs to have a proper domain user assigned on all the SQL servers (security section) involved in the replication process with proper permissions (SQL system Admin). We had a similar error when setting up the replication process on our SQL servers.
Please ensure the network/firewall settings are correct. I would check the SQL server configuration manager settings for TCP/IP settings.
DBASupport
July 18, 2023 at 9:54 pm
I mocked this up in 2017 with AG publisher and subscribers a few years ago. I am very glad to not have needed to do that again or implement it for production.
I remember getting that error a lot, and I remember needing to manually create the linked servers replication uses on the nodes that I wasn't connected to when creating the replication. I dont remember if the linked servers are what fixed the error, but may have been?
I also did it with all Windows Authentication, I don't know how to implement for SQL auth - windows auth has to be absolutely perfectly configured. I would configure it for delegation as well at least for yourself as you will get some errors in testing that can confuse the situation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply