12 Replies Latest reply on Jul 27, 2017 1:08 PM by GreggSmith

    Windows Account for SQL DB Access

    RobLent Specialist

      I am trying to get Xtraction working with a windows account for the database access.

       

      No matter what I try I keep getting a Failed to logon error when using the windows account.

       

      If I use a local SQL account with exactly the same permissions on the database then it works fine but as soon as I change this to a windows account it fails to logon.

       

      Any ideas what I could be doing wrong?

        • 1. Re: Windows Account for SQL DB Access
          Alex.Miller Rookie

          Are Windows accounts allowed to authenticate to that database?

           

          Maybe this will help: Change Server Authentication Mode | Microsoft Docs

          • 2. Re: Windows Account for SQL DB Access
            RobLent Specialist

            Yes.  we already have our Windows accounts authenticating to the databases anyway.

             

            The SQL instance is set for mixed mode.

             

            I have tried this in my lab as well as live and get the same issue.

             

            Even tried it with a full DB admin windows account with the same error.

            • 3. Re: Windows Account for SQL DB Access
              Henk Hillaert Employee

              In XtractionSettings you define the access to the database. In your console you define the roles.

               

              Are you talking about the user entered in XtractionSettings? I wouldn't use sa though

               

              XtractionSettings.JPG

               

              Henk

              • 4. Re: Windows Account for SQL DB Access
                RobLent Specialist

                Yes Henk.  That is where I am trying to use a Windows Account.

                 

                Local SQL user works fine but not a Windows Account.

                • 5. Re: Windows Account for SQL DB Access
                  Henk Hillaert Employee

                  Unfortunately I can't get it to work right now. In the Windows Event Log I find:

                   

                   

                  Login failed for user 'LDLAB\xtractionDBuser'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>]

                   

                   

                  Windows authentication is enabled on my server. Therefore I suggest to create a case, the documentation states this should be possible.

                  1 of 1 people found this helpful
                  • 6. Re: Windows Account for SQL DB Access
                    RobLent Specialist

                    Thanks.

                     

                    I will get a call raised.

                    • 7. Re: Windows Account for SQL DB Access
                      GreggSmith SupportEmployee

                      Based on the error message "Login failed for user 'LDLAB\xtractionDBuser'. Reason: Attempting to use an NT account name with SQL Server Authentication.", it appears you are incorrectly putting an AD username in the Connection settings dialog.

                       

                      Having just looked at the Installation Guide, it admittedly isn't the clearest set of instructions for setting up AD authentication for accessing databases, but it is there.

                       

                      Here is a set of steps that should get you on the right track:

                       

                      1. In IIS Admin, configure the Xtraction application pool to use the AD account as it's Identity
                      2. If you want Xtraction to access the Xtraction database via the AD account configured in IIS, you need to set the following in the database connection information of the Xtraction Settings utility:
                        1. Leave the User Id and Password fields blank
                        2. Check the Integrated Security checkbox
                      3. If you want Xtraction to access the databases that you report against via the AD account configured in IIS, you need to set the following in the database connection information of the Data Model Editor:
                        1. Leave the User Id and Password fields blank
                        2. Check the Integrated Security checkbox
                      4. In Windows Explorer, bring up the Security tab of the Properties dialog for the Xtraction\Software\Xtraction folder
                        1. Add the AD account with Modify rights to the security of the Xtraction folder
                      5. Configure the 2 Xtraction services to run under the same AD account
                      6. Restart both Xtraction services so they pick up the account change
                      7. Restart IIS so it picks up the account change

                       

                      In the past, we seen some Windows security issues with this, so if you get errors, you may have to grant the AD account Modify rights to the "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files" folder, as well.

                       

                      Keep in mind that Xtraction can only use ONE Windows user (AD or local user), and/or multiple SQL accounts for accessing MSSQL databases.  If you are going to configure Xtraction to access multiple databases via AD account, all of those accesses must be via the SAME AD account (the one configured in the Xtraction IIS application pool).

                       

                      If you have any questions, just let me know.

                       

                      Thanks,

                      Gregg

                      1 of 1 people found this helpful
                      • 8. Re: Windows Account for SQL DB Access
                        RobLent Specialist

                        Perfect.

                         

                        Thanks Gregg.

                         

                        Following these instructions it worked first time.

                         

                        Thanks very much.

                         

                        No to try and get to grips with how it all works. 

                        • 9. Re: Windows Account for SQL DB Access
                          RobLent Specialist

                          Hi GreggSmith,

                           

                          Well maybe I spoke too soon.

                           

                          I think this worked in my LAB as the account I was logged onto the server with has db access anyway.

                           

                          However when I tried this in the live environment I find that it is trying to use the account I am logged onto the server with and not the one I put in IIS.

                           

                          I have done a restart of IIS and the application pools and also the services that the account is using but it still tries to use my logged on account when ticking the integrated security box.

                           

                          Is there something else I need to do?

                          • 10. Re: Windows Account for SQL DB Access
                            Waldy Employee

                            The Xtraction Settings program is a Windows application and does not use the application pool.  When you click the test button using integrated security, it uses the currently logged on user.

                             

                            The actual Xtraction web application uses the application pool, so if you have set the app pool up with an AD account, it will use that account for integrated security database connections.

                            • 11. Re: Windows Account for SQL DB Access
                              Waldy Employee

                              That error occurs when you enter a valid AD login for your SQL Server in the User ID field, but have not checked the Integrated Security option.

                               

                              As Greg said:

                              • Leave the User Id and Password fields blank
                              • Check the Integrated Security checkbox
                              • 12. Re: Windows Account for SQL DB Access
                                GreggSmith SupportEmployee

                                No, that's the way it works (and another reason why using SQL accounts is just easier).

                                 

                                Waldy is correct.  Integrated Security means it uses the context of the current Windows user.  When the Identity is set in the IIS application pool, then Xtraction will access the databases via that user.  When you run the Settings utility or the Data Model Editor on the server, they will use the Windows user with which you are currently logged in.

                                 

                                If you are using Integrated Security, just skip using the Test button.  The Test button is not required, and not a valid test until you either log in as the account you are trying to test or launch the Settings utility with Run As to run the utility with the account you are trying to test.