11 Replies Latest reply on Nov 19, 2020 11:20 AM by sdepriest

    Please Help!!! Issues Connecting to Data Source...

    Robert Davidson New Member

      So we are having issues connecting to a particular data source at our company. We are currently going through a server upgrade, and with that, the source server is changing.. I was trying to be proactive, and change the connection string in our DEV and QA environments to align with the new server configuration...

       

      Now I must tell you, the SQL Server Instance is being upgraded from 2008 to 2019. Not sure if Informatica has had issues with their DataDirect ODBC drivers establishing connections to that version of SQL Server.. Anyways, our company is configured with Grid architecture, and I have successfully managed to create System DSN's on both application servers (nodes)... Connections tested successfully with no issues on both nodes to the new Data Source/Server...

       

      When I go to configure the relational connection in Workflow Manager, I check the property to "Use DSN" and point it to the connection string that was created on both nodes... Seems pretty straight forward, right? Well... The workflow keeps failing, and I am at a loss... I was hoping I could receive professional assistance...

       

      By the way, the service account has been granted read permissions to the database AND has been added as a "Login User"... So the permissions from the DB perspective should be setup just fine... Once again connection resolves when creating System DSN... Problem seems to be on the application/client side...

       

      Here are some of the error messages we are experiencing:

       

      Error connecting to database [

      [Informatica][ODBC SQL Server Wire Protocol driver]Socket closed.

      SQLSTATE: 08S01

      [Informatica][ODBC SQL Server Wire Protocol driver][SQL Server]Login failed for user ''.

      SQLSTATE: 28000

       

       

      Database driver error...

      Function Name : Connect

      Database driver error...

      Function Name : Connect

      Database Error: Failed to connect to database using user [pmkerberosuser] and connection string [DSA_EPSI].

        • 1. Re: Please Help!!! Issues Connecting to Data Source...
          JanLeendert Wijkhuijs Active Member

          Hi Robert,

          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.

           

          Regards,
          JanLeendert

          • 2. Re: Please Help!!! Issues Connecting to Data Source...
            Nico Heinze Guru

            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.

             

            Regards,

            Nico

            • 3. Re: Please Help!!! Issues Connecting to Data Source...
              Robert Davidson New Member

              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...

               

              • 4. Re: Please Help!!! Issues Connecting to Data Source...
                Robert Davidson New Member

                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

                • 5. Re: Please Help!!! Issues Connecting to Data Source...
                  Robert Davidson New Member

                  UPDATE:

                   

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Please Help!!! Issues Connecting to Data Source...
                    user101600 Guru

                    The error that youre getting

                    [Informatica][ODBC SQL Server Wire Protocol driver]Socket closed.

                    SQLSTATE: 08S01

                     

                    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.

                     

                    • 7. Re: Please Help!!! Issues Connecting to Data Source...
                      Sachin Kumar Guru

                      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.

                      • 8. Re: Please Help!!! Issues Connecting to Data Source...
                        Robert Davidson New Member

                        user101600,

                         

                         

                        If you read my comment above, you would have seen that this issue has already been resolved!

                        • 9. Re: Please Help!!! Issues Connecting to Data Source...
                          Robert Davidson New Member

                          Sachin Kumar,

                           

                          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."

                          • 10. Re: Please Help!!! Issues Connecting to Data Source...
                            Nico Heinze Guru

                            Hi Robert,

                             

                            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. ;-)

                             

                            Cheers,

                            Nico

                            • 11. Re: Please Help!!! Issues Connecting to Data Source...
                              sdepriest Guru

                              Robert,

                              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.

                               

                              Sarah