Menu
Can anyone guide me around to linking a server to another in SQL Server 2008 R2? I am getting the following error when trying to do so in Management Studio.
The linked server has been created but failed a connection test. Do you want to keep the linked server?
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The OLE DB provider "SQLNCLI" for linked server "CDSPM1" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "CDSPM1". OLE DB provider "SQLNCLI" for linked server "CDSPM1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
640
Linked servers enable the SQL Server database engine and Azure SQL Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server.
Creating a SQL Server Linked Server. In SQL Server Management Studio, in the Object Explorer pane, expand the "Server Objects" section, right click on "Linked Servers" and choose "New Linked Server…" from the menu.
Seems like Authentication problem.
Test by creating the linked Server using "Server Type" as "Sql Server". Then go into "Security" and set your user mapping. As a test, create a SQL login on the remote system and specify that on the "Be made using this security context"
Be sure that you can PING the "Linked Server" name first. HTH
191
Use drop down and choose
SQL Server Native Client
instead of
SQL Server
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
