SQL Server 2005 Linked Server

 

In this example SERVER 1 is the server bieng linked to.
SERVER 2 is the server bieng linked from.

STEP 1:

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.

STEP 2:

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.

STEP 3:

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 FALSE
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
from
database.dbo.table
where
id in (select id from [SERVER 1].database.dbo.table)
  
 

 

Connect to MySQL database from SQL server

First of all you need to download the MySQL connection / ODBC driver.

The ODBC driver can be download free of charge from the followinglocation:
http://www.mysql.com/products/connector/odbc/

Folow the simple installation instructions.

In Microsoft SQL Server Management Studio – expand the database tree on the left hand side of the window – expand server obects – expand linked servers

Right-click linked servers – click New Linked Server…

Linked server: enter a name for the connection – this can be anything.
Server type: select Other data source
Provider: select Microsoft OLE DB Provider for ODBC Drivers
Product name: This is the name of the MySQL database connecting to

Provider String: This MUST be entered exactly as it appears below, omitting server, database, user and password with your own details.

Driver={MySQL ODBC 3.51 driver}
;Server=SERVER_IP;DataBase=DATABASE;Root=3306;UID=USER;
PWD=PASSWORD

For Example:
Driver={MySQL ODBC 3.51 driver}
;Server=123.456.789.10;DataBase=MY_DATABASE;Root=3306;
UID=ADMIN;PWD=pas32w0rd

Select the Security tab on the right
select Be made using this security cointext towards the bottomof the screen.
Enter a valid user name and password to cennect to the MySAL server.

e.g.
Remote login: Admin
with password: pass32w0rd

Select the Server Options tab on the right
Set the details as follows:

Collation Compatible: FALSE
Data Access: TRUE
Rpc TRUE
Rpc Out TRUE
Use Remote Collation TRUE
Connection Timeout 0
Query Timeout 0

Click OK to instantiate the linked server.