How do I get this SQL Server ODBC Connection working?

Named pipes and TCIP protocols for SQL server 2005 are disabled by default. Have you enabled them under "SQL server configuration manager"? You can find the protocols in the SQL Server Network configuration and under SQL Native client xx configuration.

The connection on the server itself works thanks to the "Shared memory" protocol.

Have you enabled the SQL Server Browser service as per How to configure SQL Server 2005 to allow remote connections?:

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS.