August 24, 2020 at 3:21 pm
Hi team,
I had to update the TNSnames file under: D:\....\11.2.0\client_2(clinet_1) \network\admin, to reflect a new server name of Oracle. After doing this change, the test Linked server connection fails.
TNS ping works fine.
Telnet to the server name works as well.
Do I need to reboot the SQL server ?
Error is :
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TET".
OLE DB provider "OraOLEDB.Oracle" for linked server "TET" returned message "ORA-12170: TNS:Connect timeout occurred". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3192&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Please advise on your thoughts on this.
Thanks
August 24, 2020 at 7:57 pm
Is your linked server is pointing to the same TNSnames file that you modified (possibly a copy of the file on the SQL Server itself or maybe on a network share)? I'd check the linked server config to see if you modified the file being used by the linked server.
Are you able to re-create the linked server with a new name using the TNSnames file name? That would be my first step - see if you can make a new one or not. If you can, then you can probably drop the existing and make a new linked server.
If you can't, then there is something else going on. When you say that TNS Ping works fine, do you mean it works fine from the SQL box to the Oracle box or something else? Might not hurt to check the permissions on the TNSnames file to make sure that SQL has permissions to it. Could be that when you changed the file, the permissions got screwed up and the SQL account no longer has access.
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.
August 24, 2020 at 9:39 pm
Hi Brian, thanks for the response.
tns ping works from SQL SERVER box to the oracle server that is mentioned in the tns names file.
i think I can create a linked server with the same properties and then test.
You are right l, also could be permission issue on the admin folder that must have changed, I’ll take a look at that suggestion too.
thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply