Resolution:
As per the documentation at https://technet.microsoft.com/en-us/library/aa258233%28v=sql.80%29.aspx, CS in the collation name specifies case-sensitive which results in MDM to fail since it assumes the column names in the tables to be case insensitive. An error similar to the following will be thrown:

SQL-8202: Failed while executing SQL statement. SQL state S0001. Database specific error code (if any) was 207. Database error message (if any) was: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'active'.

The solution is to recreate the Database with *_CI_* collation.