I have a cross server view that is currently setup on SQL2000 and SQL2005 boxes that run fine. The SELECT of the view looks like this:
However when I transfer this to a SQL2008 server I get the following error:
Any suggestions why this doesn't work?
If I use openrowset as below instead it works ok - but a solution that means having the credentials in the view isn't an option:
Many thanks for your time.
As @Kevin mentioned, you have the double hop issue as you are not using Kerberos authentication.
One option is the linked server with de default user and password (requires SQL Server authentication).
Other option is to properly setup Kerberos authentication so SQL Server can utilize it. There is post on SQLServerCentral - Configuring Kerberos Authentication.
answered Nov 13 '12 at 09:34 PM
You've hit a double hop issue, it sounds like. Instead of doing OPENDATASOURCE, you could create a linked server connection. That way, you could have a default user ID and password put into the linked server, but the credentials wouldn't be visible to anybody.
answered Nov 13 '12 at 06:03 PM