MSSQL Linked server to MySQL

First download the connector:
http://dev.mysql.com/downloads/connector/odbc/5.1.html
Then the MySQL GUI tools
http://dev.mysql.com/downloads/gui-tools/5.0.html
install both on the MSSQL server

Go to the MySQL server a create a user:
mssqllink
Give them permissions to access, update etc the tables you need. To make sure they have remote access from another ip or hostname, right click on the user in the administrator and "add hostname". Fill in the hostname or IP of the MSSQL server.

Go back to the MSSQL server and connect to the MySQL server via MySQL Administrator. Use the username and the password you set for that user. If you can't connect then go back and check the user details on the MySQL server. No connection here, no connection from MSSQL

Go to the windows control panel>administrative tools>ODBC datasource and click on the system DSN tab. Click 'Add' and choose the MySQL ODBC driver - fill in the details with the username and password you used to connect from MySQL Administrator. Click test and it should come back ok. Save the datasource name so you can use it below.

Open SQL server management studio and go to Server objects>linked servers
Add a new linked server, choose the microsoft OLE DB provider for ODBC drivers Provider and fill in the details.

NOTE if this isn't present then you'll need to install it, for 64 bit servers MS has released it as a hotfix:

http://blogs.msdn.com/data/archive/2008/04/07/64-bit-oledb-provider-for-odbc-msdasql-is-now-available-for-windows-server-2003.aspx

Now on to the connection string. The connection string for MySQL connector 5.1 is:
Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
more info here: connectionstrings.com

Replace the myServerAddress, myUsername and myPassword as before for MySQL. Note i found that adding the database parameter caused it to fail, so if you have problems try omitting that one ("Database=;"). Finally add the name of the system DSN you created.

Phew! click ok and it will all work as expected. if not then check all the connections and things and make sure you can connect via MySQL administrator using that username and password.

to select data from the tables on the MySQL server you must use the
select * from OPENQUERY(LINKNAME,'Select * from x')
syntax as the connector doesn't support the 4 part name access. Get around this by using views in your MSSQL db of the MySQL tables.