Connect to remote server in T-SQL (SQL server 2008)

Question

Does anyone have an example of a stored procedure which makes a connection to a remote server?

I have been searching the web and have so far discovered that it might can be done using sp_addlinkedserver and sp_addlinkedsrvlogin but I haven't found a good example and I don't understand the documentation that well.

UPDATE:

None of the two first replies help me out, the closest I can get is using this:

EXEC sp_addlinkedserver 
    @server = 'SiminnSrv', 
    @provider = 'SQLNCLI',
    @catalog = 'devel',
    @srvproduct = '',
    @provstr = 'DRIVER={SQL Server};SERVER=my.serveradr.com;UID=my_user_name;PWD=my_pass_word;'

That actually makes me connect but when I query a table I get this message:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server >connection.

Answer 1

Essentially you create a linked server to the other server, and then provide login credentials to be used for SQL calls to that linked server. e.g. this will connect to "MyOtherServer" using a DomainAccount for that server with the username & password 'DomainUserName', 'DomainPassword'

EXEC sp_addlinkedserver 'MyOtherServer', N'SQL Server'


EXEC sp_addlinkedsrvlogin 
   'MyOtherServer', 
   'false', 
   'OtherServerDomain\DomainUser', 
   'DomainUserName', 
   'DomainPassword'

More Info Here And Here

Answer 2

I managed to connect to MSSQL Server 2008 through a linked server using the "SQL Server Native Client 10" (SQLNCLI10), but I had to use sp_addlinkedsrvlogin instead of @provstr to provide the connection details. This is based on the example from this article:

EXEC master.dbo.sp_addlinkedserver 
    @server = 'MyServerConnection',
    @srvproduct = '', 
    @datasrc = 'SERVERNAME\INSTANCENAME',
    @provider = 'SQLNCLI10', 
    @provstr = ''

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'MyServerConnection',
    @useself = 'false',
    --@locallogin = 'someLocalUser' -- Use to restrict the connection to specific login
    @rmtuser = 'remoteUser',
    @rmtpassword = 'secret'

Querying this linked server:

SELECT *
FROM [MyServerConnection].[SomeDatabase].[dbo].[TableName]

Answer 3

I may be late to the party, but I found the following links worked for me:

To perform the intial link, I used

EXEC sp_addlinkedserver @server='serverLinkPseudonym',@srvproduct='',@provider='SQLOLEDB', @datasrc='192.168.1.1';

Then, as I was logging in with Windows authentication, I added the Windows user (this cured my " Not associated with a trusted SQL Server" error)

EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'MACHINENAME\windowsLogin', 'lnkSrvLogin', 'lnkSrvPswd';  

I also found that if I was going to run SQL Server Agent jobs that made calls to the LinkedServer, I had to add the following:

EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'NT AUTHORITY\SYSTEM', 'lnkSrvLogin', 'lnkSrvPswd';     

For the sake of clarity: "192.168.1.1" is the IP of the server to be linked to. "lnkSrvLogin" is a login on the server to be linked to, that has access to the database(s) that you need to access. "lnkSrvPswd" is the password of that account.

If you are connecting to the linked server, using an account from the existing server, then you just use that account name in the sp_addlinkedsrvlogin command. eg:

EXEC sp_addlinkedsrvlogin 'serverLinkPseudonym', 'false', 'thisServerLogin', 'lnkSrvLogin', 'lnkSrvPswd';  

Then test it:

SELECT * FROM [serverLinkPseudonym].[DBName].[dbo].[TableName]