Linked Server Problem

Question

Linked Server Problem

TriggerMe

SSChampion

Points: 11752
More actions
April 2, 2007 at 12:28 am

#75140

I have 2 SQL 2000 Servers with SP4 for SQL 2000 (SQLSrvA & SQLSrvB) on Microsoft Windows Server 2003. Both SQL Servers are configured for "SQL Server & Windows Authentication".
I have created a Linked Server on SQLSrvB that will connect to SQLSrvA.
The Linked Server uses a Local Login (Domain Name\JBloggs) and Impersonates the same login on SQLSrvA
so in other words Domain Name\JBloggs exists on both SQL Servers with the appropriate permissions to access any of the Databases.
However, whenever I connect to SQLSrvB and execute a query that uses the Linked Server, I get the famous error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
If I use SQL Server Authentication it works. When I try to use Windows Authentication I get the error message above.
The Windows User JBloggs is AD User and it definitely exists on both SQL Servers with the appropriate permissions. I have tried other Domain Users and the same error occurs.
Anyone help please?
Kindest Regards,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

I think the problem is that the Srvr A is using its service account to jump to srvrB. Should be a setting in the linked server properties to use the logged in user's account.

If you want everyone to use this, I think you need to let that account access the other server, or use a sql login.

TriggerMe SSChampion Points: 11752 More actions · Answer 2

I'm confused, what do you mean its service account on SrvA is used to connect to SRVB?

The Linked Server is resides on SRVB that is configured to see SRVA.

Kindest Regards,

samasura Grasshopper Points: 11 More actions · Answer 3

if you say that SQL authentication works why don't you then create a view in the form of

linked-server.dbname.dbo.tabletoquery and if the view is created give a windows account rights to the view?

You can then query it as you wish.

Regards

TriggerMe SSChampion Points: 11752 More actions · Answer 4

samasura,

You are way off the radar! The problem is getting SQLSrvB to pass the Windows Account to SQLSrvA to execute queries. If I create a view, how would that help when I cannot connect?

Kindest Regards,

Shaun Nelson Grasshopper Points: 13 More actions · Answer 5

If you are running Windows 2003 SP1 and non-Windows 2003 SP1, check out your DTC authentication level. SP1 allows the ability to control both inbound and outbound authentication.

If the linked servers you are trying to access are not Windows 2003 SP1 also, the only authentication level that will work is the "No Authentication Required" option.