I've got two SQL Severs (2005) and I need to link data from each into a single VIEW.
I own one of the SQL Servers (2005 Express.) I am the SA, and I can do anything I want with it.
The other SQL Server is owned by corporate. They have given me Windows Authenticated userID "TUSER" which I can use to get to my relevant tables on their server, but I can't touch or configure their SQL Server it in any way.
So... on my SQL server... I created a LINKED SERVER using the sp_addlinkedserver function, and as long as I am logged into a computer using TUSER as the windows authenticated user, it doesn't matter how I log onto my SQL Server (as SA, or whatever) I can open any query window, and run a query against corporate's SQL Server, and it works great! For example:
Select * from corpsql.division1_views.dbo.sales_vw
But when I log in to any computer, with any other userid (like all of my end users will be doing) that query fails...
ERROR: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
So I started playing around with sp_addlinkedsrvlogin, but apparently that function is for logging in with SQL Server Authentication... and I need to pass a Windows Authentication username and password.
Is there a way to link local and remote data... (preferably in a single view)... passing WINDOWS Authenticated username and password to the remote SQL Server... without being logged onto my workstation as that user?