Thank you for the reply Kshitij.

You are right. There are firewalls installed between both hosts and both were in different domains. The appropriate network configurations were taken care of and network connectivity is fine when I got this error message.

I was able to fix the issue by changing the Remote connections to use both tcp/ip and named pipes under SQL Server 2005 Surface area configuration > Surface area configuration for services and connections > Remote connections. [By default remote connections are disabled in SQL Server 2005]

After making this change, created a linked server [Choose SQL Server instead of other data sources]. Under security, choose the option "Be made using this security context" for Local server login to remote server login mappings.

The query works a charm 🙂 :smooooth: