In this example SERVER 1 is the server bieng linked to.
SERVER 2 is the server bieng linked from.
The first thing to set up is the SQL SERVER 1 to allow incoming connections from a non Windows Authenticated account – if not already done so.
In SQL server 2005 Management Studio, right click on the database at the top of the tree on the top left hand side of the window –> select Properties.
Select the security tab.
Under Server Authentication, select SQL Server and Windows Authentication mode. This will let the SQL Server accept connections from an SQL account.
Still on SERVER 1, expand the security tree on the left hand side of the Management Studio.
Right-Click Logins and select New Login…
Enter a Login Name.
Select SQL Server authentication and enter a password for this login.
Select the User Mapping Tabe on the Left.
Select the tables that this user will need access to. At this point you can set the permissions for the new login.
Click OK to complete the new login set up.
This is the login that will be used to connect to SERVER 1 from SERVER 2.
Open SQL Management Studio and log in to SERVER 2.
Expand Server Options from the left hand side menu.
Right-Click Linked Servers and click New Linked Server…
Enter the name of the Server that you’re connecting to in Linked Server – in this case SERVER 1.
Under Server Type, select SQL Server.
Select the security tab from the menu on the left.
For the login type, select Be made usinf this security contect
Enter the username and password set up on SERVER 1 for STEP 2.
Select the Server Options tab on the right
Set the details as follows:
Collation Compatible: FALSE
Data Access: TRUE
Rpc Out FALSE
Use Remote Collation TRUE
Connection Timeout 0
Query Timeout 0
Click OK to instantiate the linked server.
STEP 4 Selecting from the linked server:
Where you would use the following command to select from a table called DATA on SERVER 1:
select * from database.dbo.table
You would use the folloing updated command to select data from SERVER 1 while logged into SERVER 2:
select * from [SERVER 1].database.dbo.table
You can build up SQL commands in the usual way, adding the server name bevore using a table from the linked server:
select id, field1, field2
id in (select id from [SERVER 1].database.dbo.table)