Error Setting Up Linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Error Setting Up Linked server

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2014-12-16 : 08:57:08

I have serverone running SQL 2008 and servertwo running SQL 2012

both use windows and SQL connections both on the same domain.

logged in as Domain administrator

Both set up using
server type = SQL
security = be made using the logins current security context

It creates the link without error however if you try to query or test you get the error

TITLE: Microsoft SQL Server Management Studio
------------------------------

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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-16 : 12:24:51

It's a Kerberos issue as far as I know. You can get around it by connecting to SSMS using SQL authentication.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2014-12-16 : 13:43:09

Hi Tara

Long time no talk to.

Yes I can get around it using a SQL login.

But since all the different access levels are based upon domain logins on both servers.

That would be a problem.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-16 : 13:48:47

The term I was looking for is delegation. You'll need to fix delegation to get rid of the error. We never got it working at my last job, but it's working at my new job. I am not sure what steps we missed at the last place.

Here's an article: http://technet.microsoft.com/en-us/library/ms189580%28v=sql.105%29.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

foreverSQL
Starting Member

2 Posts

Posted - 2014-12-16 : 16:01:05

You will have to create SPNs using sql server service account to make pass through authentication work.

To create SPNs, check this link
http://technet.microsoft.com/en-us/library/bb735885.aspx

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources