Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

LINKED Server in SQL 2005 with Windows Authentication fails

Posted on 2009-04-01
5
678 Views
Last Modified: 2012-05-06
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?



0
Comment
Question by:AptDev
5 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 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..
0
 
LVL 1

Accepted Solution

by:
FaroFino earned 400 total points
ID: 24045309
Hi,

try this...After create your linked server use sp_addlinkedsrvlogin to configure security for aways use a remote login.

sp_addlinkedsrvlogin
@rmtsrvname = N'LINKED SERVER NAME',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'REMOTE LOGIN NAME',
@rmtpassword = N'PASSWORD'

0
 
LVL 1

Expert Comment

by:FaroFino
ID: 24045589
You will need a SQL Login to make it work.
0
 

Author Comment

by:AptDev
ID: 24045596
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?
0
 
LVL 39

Assisted Solution

by:appari
appari earned 100 total points
ID: 24045647
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.dbo.sales_vw

end
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
format nvarchar field as mm/dd/yyyy 4 78
SQL Encryption question 2 61
How do I subtract date and time within a same column in SQL 4 41
Help Required 2 39
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question