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

Problem With Linked Server Expand / Collapse
Author
Message
Posted Monday, November 26, 2007 5:20 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 5, 2013 3:20 AM
Points: 610, Visits: 95
I created a Linked Server Object with Sql Authentication to connect to another server.

I am executing this some time well, but some times it giving error Like

"OLE DB provider "SQLNCLI" for linked server "sa_link" returned message "Cannot generate SSPI context".
Msg 802, Level 16, State 1, Line 0
SQL Network Interfaces: The target principal name is incorrect.
OLE DB provider "SQLNCLI" for linked server "sa_link" returned message "Invalid connection string attribute"."
Post #425680
Posted Monday, November 26, 2007 6:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 1,154, Visits: 866
Hi Balavardhan,
Check your linked server connection properties, may be you have not properly added the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'Login',@rmtpassword='PWd'
Post #425707
Posted Sunday, May 19, 2013 12:03 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:49 AM
Points: 62, Visits: 326

Hi,
server name='DINESH\TESTMSSQL'
loginname='test'
loginpassword='########'

replace in following script and use it..

EXEC master.dbo.sp_addlinkedserver @server = N'DINESH\TESTMSSQL', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DINESH\TESTMSSQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'test',@rmtpassword='########'

GO

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'rpc out', @optvalue=N'false'
GO

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

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

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

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

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

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

EXEC master.dbo.sp_serveroption @server=N'DINESH\TESTMSSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Post #1454358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse