?
Solved

LINKED Server in SQL 2005 with Windows Authentication fails

Posted on 2009-04-01
5
Medium Priority
?
694 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 1600 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 400 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 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