[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

LINKED Server in SQL 2005 with Windows Authentication fails

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
AptDev
Asked:
AptDev
2 Solutions
 
reb73Commented:
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
 
FaroFinoCommented:
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
 
FaroFinoCommented:
You will need a SQL Login to make it work.
0
 
AptDevAuthor Commented:
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
 
appariCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now