Solved

LINKED Server in SQL 2005 with Windows Authentication fails

Posted on 2009-04-01
5
664 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now