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.db
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?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Reb73,
I know what you mean. I'd like to do it anyway, though.
I'd prefer not to have to use an API to do impersonation, but I could totally see creating a "windows authentication" ODBC connection (although, wouldn't it be nice to use SQL native capability if possible?)
...
FaroFino
I tried using sp_addlinkedsrvlogin (as I mentioned in my post) but it requires a SQL authenticated user... which I don't have... and Corporate won't give me one... so I'm looking for perhaps some other creative options?
...
Anybody got any other good ideas?
try like this, as I didnt test it not 100% sure if it works or not.
create a procedure on your own expressDB using SA ID adding WITH EXECUTE AS SELF clause and give permissions to other users to execute this procedure. and try executing this procedure from other users logins.
create procedure getSalesData WITH EXECUTE AS SELF
as
begin
Select * from corpsql.division1_views.db
end
Business Accounts
Answer for Membership
by: reb73Posted on 2009-04-01 at 16:44:30ID: 24045081
I don't think you can delegate/extend your windows login related credentials to other users the way you have described.
Doing so would defeat the reason for windows credentials being extended to you as this will constitute a security loophole or vulnerability..