Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Servers Error Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 7:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 14, 2014 2:05 PM
Points: 55, Visits: 162
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
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: Wednesday, March 19, 2014 5:01 AM
Points: 1,244, Visits: 459
show the script to create linked servers
Post #1397671
Posted Tuesday, December 18, 2012 4:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
Posted Tuesday, December 18, 2012 1:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 14, 2014 2:05 PM
Points: 55, Visits: 162
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
Posted Tuesday, December 18, 2012 1:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 14, 2014 2:05 PM
Points: 55, Visits: 162
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
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: Wednesday, March 19, 2014 5:01 AM
Points: 1,244, Visits: 459
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