March 3, 2024 at 2:21 am
Hi all,
I have 2 SQL servers on different domains (lets call them SQL-A and SQL-B), network connected via IPSEC vpn. DNS is not yet set up between the two networks, so I have amended the hosts file on SQL-A.
On the machine running SQL-A, I can use SSMS to connect to SQL-B successfully. I an also connect successfully using PowerBI Desktop.
When I try to create a linked server on SQL-A to SQL-B, I get this error:
Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI11" for linked server "xxxx" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "xxxx" 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.". (Microsoft SQL Server, Error: 5)
I have tried SQL Native Client, as well as the MS OLE DB for SQL Server. I have also tried just creating a system DSN in ODBC and it won't connect either. I am using a SQL login that has sysadmin and db_owner rights on SQL-B.
SQL-B:
Named instance with Named Pipes and TCP/IP enabled, running on dynamic ports.
SQL Browser service is running.
Configured to allow remote connections.
Windows Firewall disabled.
Any ideas?
Thanks!
Michael
March 3, 2024 at 3:13 am
Enable RPC and RPC Out properties of the linked server and ensure that the remote user login and password are correct.
=======================================================================
March 3, 2024 at 3:38 am
Just tried enabling RPC and RPC out, but still no good.
Credentials are working in SSMS, so definitely correct.
March 4, 2024 at 5:49 pm
Have you tried connecting by IP instead of server name set up in the hosts file? I am wondering if SQL Server Engine doesn't "trust" the hosts file and is relying on DNS.
Also, probably a dumb question, but the hosts file you modified is the hosts file on SQL-A, not your local machine, correct?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 4, 2024 at 8:22 pm
Interestingly connecting by IP doesn't work at all, even in SSMS where the server name works. TCP/IP is enabled with the correct IP in there.
And yes I modified both my local machine and SQL-A.
Thanks!
March 4, 2024 at 9:04 pm
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply