Maybe this is known among those who have to connect to Teradata stores from SQL Server but I needed to accomplish this from SQL Server 2005 and didn't find any published resources on how to do it. With a little experimentation, this is what I came up with:
1) Fetch the Teradata OLEDB driver from teradata.com and install it on the SQL Server 2005 system.
2) In SQL Management Studio (AKA Enterprise Manager) create a new Linked Server with the following configuration:
-Linked Server: choose any name (ex: ENCORE)
-Provider: OLE DB Provider for Teradata (select from dropdown list)
-Product Name: Database name (ex: databasename)