MDM Error when using three servers and one being on a different port
Error generated when deploying in MDM:
Message:Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.; 08001.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'CDU_DS', Name of 'CDU_DS'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Time', Name of 'Time' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Level_0_Order' attribute of the 'Time' dimension from the 'ClarityMDM' database was being processed.
Server: The operation has been cancelled.
Error is seen in event viewer and trace as well.
Issue is that SQL has trouble connecting to servers on different ports other than default.
Diagnosing the problem
This is a known MS issue, refer to MS blog site.
Resolving the problem
Creating a TCP alias in SQL Server Configuration Manager resolves the error. Create an alias with the non-default TCP port on the machine where the client application is running. Alias is an alternate name for SQL Server used to make connections which encapsulates the following elements in the connection string:
1. Server name
2. Protocol (TCP/IP or Named Pipes)
3. TCP Port number or Pipe Name.
In our case, created an alias in SQL Server Configuration Manager under SQL Native Client 10.0 Configuration 64-bit.