Resolved an interesting issue today, where a client needed to create a linked server from SQL 2005/2008 to SQL 2012.
Received the infamous login failed ‘NT AUTHORITY\ANONYMOUS’ error.
This typically means that the target SQL Server needs to have a SPN (Service Principal Name) created, or it is a Kerberos issue. In this case, we had checked that the SPN was properly created and Kerberos were not an issue, as these had been previously validated.
We retested the linked server with SQL 2012 to SQL 2012, this worked fine. Strange.
In researching the root cause, located this article: Linked Servers (Database Engine) which provided a nice graphic of the architecture and indicates that SQL 2012:
“The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL Server.”
From this it made sense that the down-level SQL versions did not have the proper SQL Native client library installed. Two ways to install the Native Client
– From the SQL 2012 install media, install the client data tools
– Found that the native client redistributable is available from this location:
In addition to installing SQL Server Native Client as part of the SQL Server installation, there is also a redistributable installation program named sqlncli.msi, which can be found on the SQL Server installation disk in the following location: %CD%\Setup\.
The linked server now works perfectly.
Alternately, you can download information about the SQL Server Native Client drivers that are used here: