December 13, 2012 at 7:42 am
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??
December 18, 2012 at 4:04 am
show the script to create linked servers
I Have Nine Lives You Have One Only
THINK!
December 18, 2012 at 4:06 am
Have you set up the correct SPN's
December 18, 2012 at 1:33 pm
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.
December 18, 2012 at 1:39 pm
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!
December 18, 2012 at 9:26 pm
This article helped me
http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx
I Have Nine Lives You Have One Only
THINK!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply