13 Replies Latest reply on Feb 19, 2020 3:53 AM by Federico Fanalista

    SQL Server connection with AD user

    Federico Fanalista New Member

      Hello everybody

      I would ask you how I can use a SQL Server connection with AD User to access business data.

      My problem is this:

      I have two AD user:

      1. svc_infpowcenord: AD user which runs the Informatica service and used to connect other services (PC Repository Services) to SQL Server Database
      2. svc_infpowcenbusprd: AD user used in Relational Connection for connect into business database for target tables

      DBA gave grants to access business DB (where my ETL writes data) only to svc_infpowcenbusprd.

      At the first lunch I received this error:

       

      Database driver error...

      CMN_1022 [

      Windows authentication is used to access the Microsoft SQL Server 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 'MYAD\svc_infpowcenprd'.

      SQLSTATE: 28000

      Database driver error...

      Function Name : Connect

      Database driver error...

      Function Name : Connect

      Database Error: Failed to connect to database using user [svc_infpowcenbusprd] and connection string [PRDSQLVMKIP].]

       

      I was surprise to saw that svc_infpowcenprd wanted try to connect to the DB.

      I had assumed that this user should never connect to business data.

      Later I tried to switch grants between 2 users and my flow worked.

      But I need to avoid to give grants to this user (becuse of police company).

      So could you help me to allow only svc_infpowcenbusprd to access to business data?

      Thank you so much

      Federico

        • 1. Re: SQL Server connection with AD user
          Nico Heinze Guru

          Could you please double-check that the business user and not the pwc user is named in the session?

          To me this looks as if the relational connection names the wrong user.

           

          Regards,

          Nico

          • 2. Re: SQL Server connection with AD user
            Sachin Kumar Guru

            Please give grants to user svc_infpowcenbusprd to access database and ensure to use the same user in relational connection in workflow manager.

            • 3. Re: SQL Server connection with AD user
              Federico Fanalista New Member

              Thank you Nico Heinze for your kindly reply.

              My sessions use a Connection Variable and I don't know if there is some option for setting the user in the session.

              In relational connetion I use the right user:

               

              Thank you so much

              Federico

              • 4. Re: SQL Server connection with AD user
                Federico Fanalista New Member

                Thank you Sachin Kumar for your kindly reply.

                svc_infpowcenbusprd have already grants to access to database.

                As you can see above the user is the same.

                 

                thanks

                Federico

                • 5. Re: SQL Server connection with AD user
                  Nico Heinze Guru

                  Then please double-check that the correct connection variable has been named in the parameter file which you have to use for this session.

                   

                  Regards,

                  Nico

                  • 6. Re: SQL Server connection with AD user
                    Federico Fanalista New Member

                    I'm pretty sure is correct becouse I have only two connections: one for source (oracle) and one for target (sql server).

                     

                    Regards

                    Federico

                    • 7. Re: SQL Server connection with AD user
                      Nico Heinze Guru

                      In theory, the Oracle source database may have a database link (e.g. via Golden Gate) pointing to the repository database in SQL Server.

                      So if the source connection names an object which is linked to the repository DB in SQL Server, then this may be the only reason I can currently see why the wrong database is sourced.

                       

                      If that's not the case, please open a service request with Informatica Global Customer Support to have this investigated. I don't see any other potential issues by now.

                       

                      You did check in the session log that the correct connections were used, did you?

                      No offense intended, I've made such mistakes myself in the past.

                       

                      Regards,

                      Nico

                      • 8. Re: SQL Server connection with AD user
                        Federico Fanalista New Member

                        Hello Nico

                        thanks again.

                         

                        I tried again: DBA disabled svc_infpowcenprd from business db and enabled svc_infpowcenbusprd.

                        This is the log:

                         

                        ERROR 18/02/2020 12:13:49 DisNodePrd01 PRE-SESS CMN_1022 Database driver error...

                        CMN_1022 [

                        Windows authentication is used to access the Microsoft SQL Server 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 'MYAD\svc_infpowcenprd'. Reason: The account is disabled.

                        SQLSTATE: S1000

                         

                        Database driver error...

                        Function Name : Connect

                        Database driver error...

                        Function Name : Connect

                        Database Error: Failed to connect to database using user [svc_infpowcenbusprd] and connection string [PRDSQLVMKIP].]

                         

                         

                        Really I don't understand why svc_infpowcenprd try to connect. This is only a user for informatica domain and services.

                        And the other error: svc_infpowcenbusprd have all permission to access to the sql server machine and this particular database.

                         

                        I'm desperate

                         

                        thanks

                        federico

                        • 9. Re: SQL Server connection with AD user
                          Nico Heinze Guru

                          Unfortunately that's not my primary area of expertise. I do know that using AD users for DB access is possible, we're doing that all the time at my current customer site. But the setup is a little tricky, in particular when it comes to access management.

                          Please open a service request at Informatica Global Customer Support (GCS), they can tell you exactly what needs to be set up how and what gotchas are waiting for you (such as in your case). I simply don't know all these details.

                           

                          All the best and please let us know how you proceed,

                          Nico

                          • 10. Re: SQL Server connection with AD user
                            Federico Fanalista New Member

                            hello again Nico

                             

                            I'm following your advise and I'm trying to open a GCS but I can't see the eSupport button on my page...

                            I asked for help.

                             

                            thank you

                            Federico

                            • 11. Re: SQL Server connection with AD user
                              Nico Heinze Guru

                              If you don't see that button. then you probably are not listed as a Read/Write Contact for your organisation's GCS "project". Please ask your INFA administrator to be enrolled as such, or that this person opens a case and names you as the primary person to contact (then you don't have to be a Read/Write Contact).

                              Otherwise just give GCS a phone call, they can tell you how to proceed.

                               

                              Good luck and regards,

                              Nico

                              • 12. Re: SQL Server connection with AD user
                                Harish Kumar Dola Active Member

                                On Windows, trusted connection uses service user account as designed. From documentation :

                                 

                                Use Trusted Connection

                                If selected, the Integration Service uses Windows authentication to access the Microsoft SQL Server database. The user name that starts the Integration Service must be a valid Windows user with access to the Microsoft SQL Server database. 

                                 

                                 

                                On a trusted connection, we can't use any other account.

                                 

                                Thanks,

                                Harish

                                1 of 1 people found this helpful
                                • 13. Re: SQL Server connection with AD user
                                  Federico Fanalista New Member

                                  Hello Harish

                                  thank you for your help.

                                   

                                  So let's see if I understand.

                                  I need to uncheck "use trusted connection", like this:

                                   

                                  But now Informatica treat the user as a MSSQL Server user or a domain user?

                                  How can I say that this is a domain user?

                                   

                                  thank you

                                  Federico