How to add linked server SQL Server 2008 R2

Question

I have two SQL Server 2008 located in different cities.

I have physical access to one of them and I can exec SQL Server Management Studio there.

For second server I have following parameters:

• remote domain name
• remote port
• login
• password

With these parameters I can connect to second server with SQL Server Management Studio.

But now, I have to create cross server queries. It is possible? How? :)

Do I have to add Linked Server? How?

I see only this window, but where do I writer domain/port/login/password info?

Answer 1

Change the server type to SQL server, put the servername,port number in the Linked Server name; on the Security section, change to "Be made using this security context" and enter the user name/password, assuming they are SQL Authentication credentials.

Answer 2

Instead of adding the linked server in the GUI (as alread described in SqlACID's answer), you can also add it by executing the system procedure sp_addlinkedserver instead.

To actually query tables from the linked server, your queries need to look like this:

SELECT * FROM Server.Database.Schema.Table