Linked Servers

[aTekTipsUser]

I am having an issue linking a SQL Server to another SQL Server. They are both Windows NT and SQL Server 2000. When I set up the linked server, I want to use Windows Authentication. On the Security tab of the Linked Server Properties, I check 'Be made using the login's current security context'. When I try opening the table, I get "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

If I change the SQL server properties to "Use SQL Server authentication" and enter a valid Login Name and Password, I can open the tables in the Linked server.

Any idea why I can't use the Windows authentication? Thanks in advance for your help!

 

[SQLDenis]

Are they on the same domain?
and if not is a trust setup?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[aTekTipsUser]

Thanks for the quick response!

They are on the same domain.

 

[mrdenny]

Doesn't matter. It's not going to work.

You are having the same problem as people who use Intergrated authentication to connect to a web server, then try to pass the account info to the SQL Server.

Here is the MS KB that covers it.

http://support.microsoft.com/default.aspx?scid=kb;en-us;176377

The problem goes like this.
Workstation1 passes the authentication to Server2. Server2 knows that Workstation1 checked with the domain controller so the account is known as good. Server2 passes the authentication to Server3. Server3 knows that Server2 did not authenticate the user against the domain so the authentication can not be trusted.

It's the way that NTLM authentication on the domain was designed to function to guarantee that users are who they say they are.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com