Double hop via linked server works with oledb provider and not SQL Server "server type"

Question

Double hop via linked server works with oledb provider and not SQL Server "server type"

telaan

SSC Enthusiast

Points: 126
More actions
August 28, 2012 at 9:19 am

#280800

Hi,
I am working with my AD admin to get delegation set up between several servers so we can do a double hop.
We've set the SPNs and enabled delegation but I can only connect to the other server via double hop using the ole db provider and not the Server Type of SQL Server on the General page of the Linked server setup GUI. Both servers are SQL 2012.
Does anyone know what might be happening?
Thank you!
Telaan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

telaan (8/28/2012)
Hi,
I am working with my AD admin to get delegation set up between several servers so we can do a double hop.
We've set the SPNs and enabled delegation but I can only connect to the other server via double hop using the ole db provider and not the Server Type of SQL Server on the General page of the Linked server setup GUI. Both servers are SQL 2012.
Does anyone know what might be happening?
Thank you!
Telaan

First, "double-hopping" can only truly be done with Kerberos, so make sure that you have that in your enterprise and that it is set up and working correctly.

Secondly, as to why it would work for oleDb and not for SQL-Server type linkedServers, script out your two linked server definitions and post them here. Be sure to include the security settings for each.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

telaan SSC Enthusiast Points: 126 More actions · Answer 2

Kerberos is set up and I've made sure that connections are being made via kerberos.

SPNs and delegation set up (but maybe not the way the SQL Server type needs?)

The values left out are default.

*************THIS WORKS (Double Hop)***********************

EXEC master.dbo.sp_addlinkedserver @server = N'TYPE_OLEDB', @srvproduct=N'', @provider=N'SQLOLEDB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TYPE_OLEDB', @locallogin = NULL , @useself = N'True', @rmtuser = N''

*************THIS DOESN'T***********************

EXEC master.dbo.sp_addlinkedserver @server = N'TYPE_SQLSERVER', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TYPE_SQLSERVER', @locallogin = NULL , @useself = N'True', @rmtuser = N''

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

Not sure what the defaults are. Is @provstr="Integrated Security=SSPI; " mentioned?

Found in this url

Doublehop for IIS including preflight checks

If the server is part of a cluster

telaan SSC Enthusiast Points: 126 More actions · Answer 4

Thanks for the reply!

No SSPI setting on the linked server that I can tell.

Doesn't look like any of those are the issue.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

What error do you get?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

telaan SSC Enthusiast Points: 126 More actions · Answer 6

Hi,

The usual NT AUTHORITY\ANONYMOUS LOGON error. Don't get it when I do oledb. Very strange.

Thanks!