SQL 2012 Creating a Linked Server

I am attempting to create a linked server in SQL 2012.  

The 2 x SQL Instances are on 2 x different clusters on the same network and are both running SQL Server 2012.

I have a default Service account Contoso\ServiceAccount that is  has Sysadmin permissions on both instances.

If I try to create the linked server the connection returns the following.

The test connection to the linked server failed.

Additional Information
    An exception occurred while executing a Transact-SQL statement or batch.
    (Microsoft.SqlServer.ConnectionInfo)
        Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

I can confirm that the Firewall is disabled on both clusters and across all nodes.
I have also logged onto both instances in SSMS using the Contoso\ServiceAccount service account with no problems.  

When i configure the Linked Server I   Select the SQL Server Server Type.

Under the Security page I add the Contoso\ServiceAccount and i have selected to Impersonate.

I have also selected Be made using the login's current security context which has worked for me in the past.  

Any help would be appreciated.
ProjNetAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
Most likely this is due to Kerberos not being configured correctly.  In order to impersonate across instances you will need to ensure that Kerberos is configured.

http://technet.microsoft.com/en-us/library/gg723715.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProjNetAuthor Commented:
For the correct answer you will need to know the following.

Cluster1 = CLUSTER1.contoso.com
Instance1= Instance we are trying to connect set the Linked servers from.
SVC-Instance1-DB The DB Service account on Instance 1 (Not the account we are impersonating in the linked server)

Cluster2 = CLUSTER2.contoso.com
Instance2 = Instance we are trying to connect set the Linked servers from.
SVC-Instance2-DB - The DB Service account on Instance 1 (Not the account we are impersonating in the linked server)

From a  CMD on a server on the domain you need run the following commands. for the server you are setting up the linked servers on.

1. (The below command is the instance name  and the port of the instance (done do a \ instance name all you need is the port) followed by the Database service account for the Instance that you are configuring the Linked server on.

setspn -s MSSQLSvc/Instance1.contoso.co.uk:1433 contoso\SVC-Instance1-DB

2.  (The below command is the instance name NOT THE FQDN and the port of the instance (once again do not enter in the  instance name all you need is the port) followed by the Database service account for the instance that you are configuring the Linked server on.

setspn -s MSSQLSvc/Instance1:1433 contoso\SVC-Instance1-DB

3.  (The below command is the instance name FQDN and the Name of the cluster in which the instance the you want to make the linked servers resides followed by the Database service account for the instance that you are configuring the Linked server on.

setspn -s MSSQLSvc/Cluster1.contoso.co.uk contoso\SVC-Instance1-DB

4.  (The below command is the name of the cluster the instance that you want to create the linked servers on resides name FQDN and the Name of the instance (you do not need the port this time) followed by the Database service account for the server that you are configuring the Linked server on. followed by the instance port number.

setspn -s MSSQLSvc/Cluster1.contoso:1433 \SVC-Instance1-DB

Follow the above steps for the server you want to create the Linked servers too.  Once complete you need to set Delegation on the database service accounts for the SVC-Instance1-DB and SVC-Instance2-DB.  On the DC if you have set the SPN's for these accounts as detailed above then the delegation tab will appear under AD Users and Computers.  you may need to select Advanced features to see this tab.  Once you have done this select the option Trust this user for delegation to any service (Kerberos Only)

Once completed as long as the account you want to impersonate exists and has the correct permissions on the Server you are creating the linked servers on and the Server that you are linking too.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.