Solved

LINKED Server in SQL 2005 with Windows Authentication fails

Posted on 2009-04-01
5
683 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
[X]
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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 6 82
SQL Backup skipping a few tables 7 62
TSQL mapping detailed records to group records 9 67
How can I find this data? 3 40
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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 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