Last week we began having an issue with a Linked Server to an Oracle database on one of our MSSQL Servers. If I use the linked server on the MSSQL server it works fine. However, if i try to use it remotely through SSMS it throws the below error. But, if i log in to the SQL Server remotely with SSMS and an SQL sysadm account it does work. Server is on mixed mode and normally we connect using window credentials.
OLE DB provider "OraOLEDB.Oracle" for linked server "*****" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "*******".
This worked fine up until last week when it just seemed to stop working.
The same server on our DR site with the same linked server works fine.
We have checked the provider settings against a working Linked Server and they are fine. We have rebooted the Server. Checked TNSNAMES is matching and correct. I am now completely out of ideas of what to try next and wondered if anybody out there has had a similar problem and has been able to resolve the issue. I could try to reinstall the Oracle ODAC but trying to avoid this.
Thanks for any help or advice you can give.