Creating a Linked server from SQL 2005/2008 to SQL 2012

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.

NT AUTH

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:

http://support.microsoft.com/kb/2726013

Advertisements
This entry was posted in Server 2012, SQL, SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s