8 Replies Latest reply on Oct 18, 2018 8:14 AM by JoeDrwiega

    Using Windows Authentication for database connection

    wynnb Specialist

      We are attempting a new install of IEM 2017.3, but can't get past the screen where we set the database connection (server, DB, credentials...). On that screen, when we test the connection if fails. We are using Windows authentication for the DB connection, rather than SQL. My DBA says the logs on the SQL server indicate that the installer is trying to connect with SQL creds, even though we specified a domain account on that screen in the installer. There is no place in the install process to select Windows authentication vs. SQL authentication.

       

      Is there a way around this? It's hard to believe that it is unable to use Windows authentication.

        • 1. Re: Using Windows Authentication for database connection
          phoffmann SupportEmployee

          You are required to use SQL authentication.

           

          NT-authentication will not work.

           

          NT-authentication comes with a LOT of headaches (such as not really being able to do impersonation all that well). There's also various other issues with NT authentication. Long story short ... see above - no, you're required to use SQL authentication. That's it.

           

          We handle the security stuff (i.e. "Well - BOB over there isn't allowed into the Console, so we won't let him log in") via RBA ourselves.

           

          We used to have a semi-hack of NT authentication available in the early 2000's ... but if I remember correctly, that (A) caused usually far more grief than it was worth anyway and (B) went the way of the Dodo around version 8.0 release or so around 2003 or so, I want to say.

           

          So yeah - please follow the install guide. They specifically do call for setting up SQL in mixed mode and using SQL authentication (see here -- How to install Microsoft SQL Server 2012 for LDMS ).

           

          We could argue the benefits / drawbacks of either approach for a while, but it's not going to change that SQL authentication is a must & will continue to be so. So - your DBA will need to switch the SQL Server over to mixed mode.

           

          Hope that helps.

          • 2. Re: Using Windows Authentication for database connection
            wynnb Specialist

            It does help to know that this is simply a design issue in the application. It's really disappointing that Ivanti doesn't consider NIST security hardening recommendations when developing their products. There should be an option for choosing the authentication type, just like there is in the Service Manager application.

             

            Thanks for the info/confirmation. I guess we'll just have to move forward with a security exception for this application.

            • 3. Re: Using Windows Authentication for database connection
              phoffmann SupportEmployee

              We do consider their recommendations. It's just that when you get involved with more of the details of things, NT-authentication can cause a LOT more issues for applications such as ours than it benefits. There's enough other ways of securing things along the way without risking to break a whole bunch of other things ...

               

              (one issue for NT authentication on SQL is that you CANNOT authenticate as "someone else" -- so suddenly you need to run ALL of your services & processes that talk to the DB as specific users and so on ... it's NOT a simple matter for complex products. There's good reasons why we disabled it as an option (well - more of a hack) eons ago.

              • 4. Re: Using Windows Authentication for database connection
                pjsnkk Rookie

                hello

                 

                i understand you do take care of the application security and the data inside, but how the SQL server security is reached as there is no encryption used in case of usage of local SQL logins?

                 

                BR

                • 5. Re: Using Windows Authentication for database connection
                  phoffmann SupportEmployee

                  Heh - talk about a bit of thread necromancy .

                   

                  Urm - so can you please provide a bit more information as to what you're on about / exactly concerned with? Ideally, with links to actual tech-articles to give more background.

                   

                  From the brief mention you listed above, the only thing that I could find so far is something like this -- security - SQL Server 2005: How Secure is SQL Server Authentication? - Stack Overflow -- .

                   

                  Having a clearer understanding of what your concern is & more detail / context would be helpful.

                  • 6. Re: Using Windows Authentication for database connection
                    pjsnkk Rookie

                    example - https://sqlstudies.com/2017/04/26/why-is-a-windows-authenticated-login-more-secure-than-a-sql-authenticated-one/

                     

                    but also direct Microsoft link: Choose an Authentication Mode | Microsoft Docs

                     

                    • If a user is a Windows domain user who has
                      a login and password for Windows, he must still provide another ( SQL
                      Server) login and password to connect. Keeping track of multiple names
                      and passwords is difficult for many users. Having to provide  SQL Server
                      credentials every time that one connects to the database can be
                      annoying. 
                    • SQL Server Authentication cannot use Kerberos security protocol. 
                    • Windows offers additional password policies that are not available for  SQL Server logins. 
                    • The encrypted  SQL Server Authentication login password, must be
                      passed over the network at the time of the connection. Some applications
                      that connect automatically will store the password at the client. These
                      are additional attack points. 
                    • 7. Re: Using Windows Authentication for database connection
                      phoffmann SupportEmployee

                      OK - that answers some of it - but doesn't answer "what aspect are you coming from".

                       

                      Are we just covering this from a "those are attack-points" perspective?

                       

                      Do you need to talk with a DBA and/or a security person, to explain why those design choices have been made?

                       

                      Are you trying to improve against MITM attacks / "people running Wireshark in your network estate" or some such ...?

                       

                      This is why I was asking for context.