Link Servers with Delegation

I am trying to implement a linked server that uses integrated authentication on a 64 bit Wndows 2003 SP1 server. I have both Sql Server 2005 and Sql Server installed, and have successfully created database link that is able to use double hop authentiction on the Sql Server 2005 instance. I am unable to do the same usign the Sql Server 2000 instance. Does anyone know if double hop uathentication using Kerberos is supported on Sql Server 2000. The linked server on Sql Server 2005 is created using this syntax

"EXEC sp_addlinkedserver @.server=’LinkedServer’,

@.srvproduct=''",

@.provider='SQLNCLI',

@.datasrc=’SQLB’,--the data source

@.provstr="Integrated Security=SSPI; "

"exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'"

SPN's and domain accounts have been created as documented and those same accounts are used in both the Sql Server 2005 and Sql Server 2000 instances.

The error message going from a Sql Server 2000 or 2005 client, to the Sql Server 2000 instance that has the linked server using the SQLNCLI provider is

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' reported an error. Authentication failed.
[OLE/DB provider returned message: Communication link failure]
[OLE/DB provider returned message: Named Pipes Provider: No process is on the other end of the pipe.
]
[OLE/DB provider returned message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
OLE DB error trace [OLE/DB Provider 'SQLNCLI' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

If I use the Sql Server 2000 OLEDB provider when creating the link I get this error

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The same link using the SQLNCLI provider in Sql Server 2005

works and I am able to use double hop authentication.

My question is , does anyone know if double hop authentication is supported using a Sql Server 2000 linked server?

Using Kerberos as Authentication protocol and the appropiate configuration this should work even for SQL Server 2000.

http://msdn2.microsoft.com/en-us/library/aa905162(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms189580.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

link from sql2005 (64-bit) to sql 7 (32-bit).

I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"Jos" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
I get the following error:
OLE DB provider "SQLNCLI" for linked server "sql7" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "sql7". The provider supports the interface, but
returns a failure code when it is used.
How can I get these servers linked?
Regards,|||I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"Jos" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>

Lineage ID Errors

I am running an SSIS package and I keep getting this error when it gets to a the dataflow task. I have a bit of an idea of what it means, but I can't figure out how to fix it. IF anybody could explain this error and fixes for it that would be of immense help. Thank you.

Error: 0xC004701C at Load Server Security, DTS.Pipeline: input column "Server" (1434) has lineage ID 1421 that was not previously used in the Data Flow task.
Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Destination 1" (1420)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error: 0xC004700C at Load Server Security, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Load Server Security: There were errors during task validation.The first error simply says that you have a column in the data flow, Server, that isn't used.

The second error looks to me like there were database changes to that table and now the OLE DB destination needs to be updated. Double click on the OLE DB destination and select the mappings tab. If no changes need to be made, simply click OK.|||

Possible reason could be if your destination table structure or the column data type changed.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=102011&SiteID=1

Thanks

|||You should have a triangle with an exclaimation mark on it. Double click on that component and then mappings, if applicable, to fix it.