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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy