ankur.bhargava (1/9/2009)
Hi All,I am facing some problems while accessing linked server created with windows authentication. Here are the steps I performed:
1) Login with User1 on SQL management studio.
2) Create linked server from following queries:
EXEC sp_addlinkedserver @server='ANKUR02', @srvproduct='', @provider='SQLNCLI', @datasrc='ANKUR01',@provstr='Integrated Security=SSPI;';
exec sp_addlinkedsrvlogin 'ANKUR02', 'true'
3) Running the query Select * from ankur02.smshirts.dbo.results gives the error message
Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.
Can anyonle tell me what I am doing wrong here.
Thanks in advance for any assistance.
Ankur Bhargava
Whenever u create link server with login id, it should be exist in both servers.
I think this is the ur problem why u not able to access and able to only with windows authnitcation..
pls try below qury to create link server:
USE master;
GO
EXEC sp_addlinkedserver 'p123', N'SQL Server'
GO
USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'p123', @optname=N'data access', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'p123', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dba'
GO
Note : "dba" should be exist in both servers.