Setting Up Linked Server

[MikeBronner]

Hi Everybody!

I have had this constant problem with linked servers: I can never get them to connect despite following all the FAQs and articles out there.

Here's the situation:
1 - Server A running on Windows 2003 R2 Server, SQL2005, mixed mode authentication.
2 - Server B running on Windows 2003 R2 Server, SQL2000, mixed mode authentication. Also have "accept remote connections" enabled.
3 - SQL user 'reports' set up in Server B.

I set up the linked server 'Server B' on Server A using the following settings:
- Linked Server: Server B
- Server Type: SQL Server
- Be made using this security context: reports / [password]
- Collation Compatible: False
- Data Access: True
- RPC: False
- RPC Out: False
- Use Remote Collation: True
- Collation Name: [blank]
- Connection Timeout: 0
- Query Timeout: 0

Is there anything that could be keeping this from working? I can't seem to find it...

Thanks guys!

Take Care,
Mike

 

[mrdenny]

What would be the error that you are getting?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MikeBronner]

OLE DB provider "SQLNCLI" for linked server "GMS01" returned message "Communication link failure".
Msg 109, Level 16, State 1, Line 0
Named Pipes Provider: The pipe has been ended.
Msg 18452, Level 14, State 1, Line 0
Login failed for user 'reports'. Reason: Not associated with a trusted SQL Server connection.

Seems fairly typical, but I could only find the same response on Google: enable mixed mode authentication. Unfortunately for me, that is already the case.

Take Care,
Mike