sp_addlinkedserver syntax

Question

I would like to make a copy of what's on my remote server at xxx.safesecureweb.com. How do I connect to it from my local SQL Server?

Is the syntax something like:

sp_addlinkedserver 
@server='PRODUCTION', 
@provider='xxx.safesecureweb.com',
@Username='myUsername', 
@Password='myPassword'

What I'm thinking of doing is writing a bunch of insert statements like:

INSERT INTO Test.Table
SELECT * FROM Production.Table

Answer 1

Provider should be the client provider you want to use to connect. @datasrc is the servername to connect to.

sp_addlinkedserver  
@server='PRODUCTION',  
@provider='SQLNCLI', -- sql native client.
@Username='myUsername',  
@Password='myPassword',
@dataSrc = 'xxx.safesecureweb.com' 

Then, when querying, I believe you need to use a four part name fot the table:

INSERT INTO Test.Table
SELECT * FROM Production.mydatabase.dbo.Table

http://msdn.microsoft.com/en-us/library/ms190479.aspx

Answer 2

You do not specify the remote user and password in the linked server definition. Linked servers have a separate object that mapps the login of users connected to the local server (the 'locallogin') with remote logins. See Security for Linked Servers. For example, the following maps all local logins on the linked server to the specified MyUserName SQL Login:

exec sp_addlinkedserver 'xxx.safesecureweb.com';
exec sp_addlinkedsrvlogin 'xxx.safesecureweb.com'
    , 'FALSE', NULL, 'myUserName', 'myPassword';

You can only map with user/password a remote SQL login. Remote Windows logins (trusted authentication) must use integrated authentication and configure the server for constrained delegation.