For a full explanation and step-by-step guide to setup a linked server through Sql Management Studio (SMS), check out this reference:
http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm
Here it is in a nutshell: If you are setting up a linked server for another sql server 2005/2008 box, just remember to (1) name the Linked server the same name as its network name, (2) select and provide under the security option ,"Be made using this security context" , the remote login/password combo (usually a service account living in active directory) and (3) set the server options rpc,rpdc out,data access, remote collation to True. That's it! 
No need to worry about provider, connection strings,etc.
Here's an example on how to retrieve data from a table using the linked server reference once it is established from an interactive query window in SMS:
select * from [MYSQLSERVERHOSTNAME].[DBInstanceName].[SchemaName].[TableName]