Linked Server Creation fails after applying SQL Server 2005 Service Pack 2

  • Article

I came to know about an issue with Linked Server Creation:

Creation of a new linked server using the option "Not Be Made" or "Be Made without using a security context", after SQL Server 2005 SP2 application may fail with errors.

***Error for 'Not Be Made'***

 </ErrorMessage>
TITLE: Microsoft SQL Server 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)

------------------------------
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416) For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3013&EvtSrc=MSSQLServer&EvtID=7416&LinkId=20476

 </ErrorMessage>

Although Error is thrown, the linked server is created anyway. But the linked server created this way may or may not work.

***Error for 'Be Made without using a security context'****

 <ErrorMessage>
TITLE: Microsoft SQL Server 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 "TEST" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "TEST". OLE DB provider "SQLNCLI" for linked server "RESTR" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399) For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3013&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

</ErrorMessage>

Resolution: Create your linked server from TSQL script using SP_AddLinkedServer and SP_AddLinkedSrvLogin.

Please let me know if you happen to see such an issue and if the above resolution helps.