0

I want to run SQL Server query on the remote machine and this is what I am doing

EXEC sp_addlinkedserver 
   N'remote-server',
   N'SQL Server';
//I don't want to do this step, and want to use trusted connection to remote server
EXEC sp_addlinkedsrvlogin 'remote-server', 'false', NULL, 'sa', 'mypassword';

select * from [remote-server].[db_test].[dbo].[test_table];

EXEC sp_droplinkedsrvlogin 'remote-server',NULL;
EXEC sp_dropserver 'remote-server', 'droplogins';

I want to use the trusted connection to remote server, Is there any way so I can skip the username and password giving step and use the trusted connection as -T switch do in bcp utility?

or any other workaround? Currenty if I skip the sp_addlinkedsrvlogin then the distributed query select * from [remote-server].[db_test].[dbo].[test_table]; gives authentication failed error.

Muhammad Ummar
  • 3,541
  • 6
  • 40
  • 71

1 Answers1

0

Try using EXEC sp_addlinkedsrvlogin 'remote-server' without other parameters

check the link http://msdn.microsoft.com/en-us/library/ms189811(v=SQL.90).aspx

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • Tried this out but it is giving error `Msg 18456, Level 14, State 1, Server remote-server, Line 1 Login failed for user 'MYDOMAIN\ummar'.` – Muhammad Ummar Jun 24 '11 at 07:30
  • Does the other server belong to the same domain? Then add the domain user to the other server and give him the appropriate permissions – niktrs Jun 24 '11 at 07:31
  • Thanks niktrs, I have not given permission to my user on other machine.. My mistake... now its working fine.. Thanks. – Muhammad Ummar Jun 24 '11 at 07:53