I have a question about the DSN and the repository connection.
How were they configured? Using windows authentication or SQL server authentication?
Because I see two different accounts in the error messages.
Second we need to know what INFA version you're using.
Also, did you examine the PAM (Product Availability Matrix) for your current version before trying to connect to the new DB server?
SQL Server 2008 and 2019 are quite a bit afar from each other in terms of years, and those PowerCenter versions which are supported with 2008 are too old to be supported for 2019. And vice versa. With 10.4.1 you cannot connect to 2008 any longer.
In regards to your question about the System DSN's and repository connection, please see below:
The ODBC connections on both application servers (nodes) were configured with Kerberos as the Authentication Method. I was under the impression that one would use this if and when trying to connect to a database using Windows Authentication (since there is not an option/selection for that particular connection method).
After the DSN's were created, then I went to PowerCenter WF Manager to create the shared relational object. This was created with the sub-type of ODBC.. The username and password fields are mandatory/required in this DB configuration, even though we want to use Kerberos/Windows Authentication Method... This is why you see 2 different accounts in the session log error...
In response to you Nico, we are on version Informatica 10.2.0 (Hotfix).
I am unaware of the PAM (Product Availability Matrix), but this would have been nice to have / nice to know!
I realize that the DB versions vary quite a bit, which is why I implicitly asked in my post if this could be an ODBC issue between the two instances of SQL Server (2008 is becoming more and more obsolete, but the issue is connecting to the 2019, NOT the 2008. The 2008 connection is already established)...
I do appreciate the heads-up on 10.4.1 not supporting the older version though
1 of 1 people found this helpful
This issue has been resolved!!!
Turns out, the solution was VERY simplistic (seems to be the case in most instances LOL)...
There were actually 2 problems here:
1) The service accounts were NOT permitted as Login Users to the Source System database. I know I had stated in my forum that they were granted access, but I was just relaying information that the DBA had told me... Everyone makes mistakes
2) The Source System is on a completely different domain, and therefore IP range... I was using the DNS server alias, but NOT using the Fully Qualified Domain Name (FQDN). The server name was short-handed before and didn't include the domain extension... When I tried configuring the connection with physical server name, it worked just fine! This hinted it was an FQDN problem.
The error that youre getting
[Informatica][ODBC SQL Server Wire Protocol driver]Socket closed.
A "socket closed" error means that the connection has been terminated, however, no specific reason was provided/returned. The "socket closed" error indicates that the connection was lost outside of the control or awareness of the Driver.
there are multiple reasons that this can happen.
a few common ones are
1- The DB requires SSL or kerberos authentication and you haven't configured these parm in the ODBC driver.
2- The login credentials are in correct or youre using the wrong account.
3- SOmething on the network like a firewall is killing the connection or not allowing to go through.
I would trace the connection on the SQL server side and check the mssql server logs for any errors.
Please note that support to SQLServer 2019 has been introduced with PowerCenter 10.4.
SQLServer 2019 is not supported with PowerCenter 10.2 or any of its hotfix.
You can open a case with GCS and get the PAM of versions 10.2 and 10.4.
If you read my comment above, you would have seen that this issue has already been resolved!
Well, I can tell you that we are on Informatica PowerCenter 10.2.0 (Hotfix), and the database we are connecting to is SQL Server 2019... Everything tested just fine! Workflow was successful, session initiated the mapping, and a flat-file was produced to the $PMRoot file system...
So this kind of contradicts your statement here: "SQLServer 2019 is not supported with PowerCenter 10.2 or any of its hotfix."
regarding user101600's response, don't take that so seriously. Many of these guys don't go to the web site to read posts and respond to them; instead they get an email everytime a new thread is opened or a new response to an existing thread comes in, and they respond to this email instead of going to the web site.
Unfortunately this has the disadvantage that sometimes they miss a response by hours and seem to repeat it. But well, that's life.
Sometimes this bothers me to the bone, but we can't do anything about it. We just have to live with this. ;-)
I've found this also... the version of PowerCenter will connect and read/write to database versions that are not "supported" by Informatica. I've figured that if the underlying issue relates to the actual database version, then they will not support you; won't do further troubleshooting.
But that is not official in any respects, just my opinion.