I am new to SQL Server 2008 linked servers. I am trying to set up my very first linked server and not having any success. I am on a SQL Server 2005 Standard server in SSMS.
1. I created a new Linked Server named "crtestls"
2. Provider: Microsoft OLE DB Provider for SQL Server
3. Gave a correct Provider String (Tested successfully)
4. Gave Catalog name of remote database I am trying to set up connection to on a SQL Server 2005 Cluster.
I receive this message:
The OLE DB provider "SQLNCLI" for Linked Server "crtestls" reported an error. Authentication failed.
Cannot initalize the data source object of OLE DB provider "SQLNCLI" for linked server "crtestls".
(Microsoft SQL Server, Error: 7399)
I am able to successfully create an ODBC connection to the database I am trying to set the linked server up for... I don't understand why the linked server will not set up.