Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Linked Servers Error
Add to briefcase

Linked Servers Error Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
MSSQL_NOOB
Posted Thursday, December 13, 2012 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38, Visits: 111
MSSQL Server A: MSSQL2000 - set to Windows Authentication mode
MSSQL Server B: MSSQL2000 - set to SQL Server & Windows Authentication mode.

My client machine: running MS SSMS2008
My domain login: SysAdmin privileges on both machines.

Linked Servers connection from Server A to Server B

When I issue command

SELECT * FROM SERVERB.DBNAME.DBO.TABLENAME

I get the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Why is that? Is it because Server A is only set to Windows Authentication mode??
Post #1396194
handkot
Posted Tuesday, December 18, 2012 4:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:19 PM
Points: 1,017, Visits: 433
show the script to create linked servers
Post #1397671
anthony.green
Posted Tuesday, December 18, 2012 4:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831
Have you set up the correct SPN's


Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1397672
MSSQL_NOOB
Posted Tuesday, December 18, 2012 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38, Visits: 111
It's an existing LinkedServer. When I right click, Script to New Query Editor Window, I get the following: ***server name changed for obvious reasons ***


/****** Object: LinkedServer [ABC] Script Date: 12/18/2012 14:29:56 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ABC', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ABC',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ABC', @optname=N'use remote collation', @optvalue=N'true'
GO


I have SysAdmin rights to both databases with my domain account. I am wondering if the domain account is the reason.
Post #1398015
MSSQL_NOOB
Posted Tuesday, December 18, 2012 1:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38, Visits: 111
anthony.green (12/18/2012)
Have you set up the correct SPN's


That could be it. Not sure what is SPN ... but from the looks of this http://technet.microsoft.com/en-us/library/bb735885.aspx

this may very well be the reason. I did not do any SPN configuration on my account.

Thanks!
Post #1398019
handkot
Posted Tuesday, December 18, 2012 9:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:19 PM
Points: 1,017, Visits: 433
This article helped me
http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx
Post #1398138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.