Currently running into some errors when trying to setup External Content Types with SharePoint 2010. The goal is simple which is to connect to a SQL Server and pull information into SharePoint to an External List. The server setup is 1 SQL Server, and 1 Web Front End. I have created a Secure Store Service Target application ID and given it Windows account credentials, as well as a SQL account. Setting up the ECT in SPD connects to the database just fine with the given Target Application ID and I am able to create permissions and save the ECT. After this I create an External List and browse to it in SharePoint, it then prompts with the "Cannot connect to LobSystem" error.
External Content Type list attempts to connect to the SQL Server but fails with this message:
Cannot connect to the LobSystem (External System).
The SPN's are setup for both SQL server and each application pool account.
The SharePoint server logs the following message:
Could not open connection using 'data source=SQLServerName;initial catalog=DatabaseName;pooling=true;persist security info=false;user id=TARGETAPPID;password=**' in App Domain '/LM/W3SVC/1695757697/ROOT-1-129640094970601303'. The full exception text is: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The SQL servers port is not default, and I am wondering whether this may have something to do with the error messages I am getting. Where would the aliasing settings be? I have tried to connect with the aliased name of the SQL server; however, SPD cannot find it. Any guidance is greatly appreciated. I have read 3-4 pages in on Google with the "LobSystem error" term, none of the information is helpful.