14 Replies Latest reply on Jul 29, 2020 3:21 PM by Woo Lam

    Credentials for MS SQL

      Share This:

      Hi all!

       

      I'm just getting started with discovering of MS SQL databases. I have uploaded database driver and have added the credentials. However, when I test it failes.

       

      ADDM gives this error message:

      SQL Provider: Unable to open a connection to the database: login failed for user 'xxxxxxx'. ClientConnectionId:0c6713d9-efaa-46b9-aafe-8261521ac5da

       

       

      The error message the database group find in their log is as follows:

      "Login failed for user xxxxxx. Reason: Attempting to use an NT account name with SQL Server Authentication".

       

      The database group can make a special user for me, but then they have to add it on all the servers and maintain it, so it is quite a lot of work.

       

      Is there another way to solve this problem? I am open for advice :-)

       

      I'm on version 10.0.0.2 and TKU 2015-09-1

       

       

      BR

      Inger Kristine

        • 1. Re: Credentials for MS SQL
          Andrew Waters

          This has told you exactly what the problem is.

           

          SQL Server, by default, has its own accounts. You are trying to use a Windows account to authenticate. Either the SQL server needs to be configured to accept Windows accounts or you need to use the appropriate SQL Server account.

          • 2. Re: Credentials for MS SQL
            Michael Hoover

            Andrew is correct, the DBAs would need to configure their SQL servers to use LDAP, Active Directory, or whatever authentication method you use and get a group setup in those respective CSO servers.  Or they would have to do the manual effort of giving you a special account and setting it up across all servers.

            • 3. Re: Credentials for MS SQL

              Thank you, Mike and Andrew!

              Then I know what to do.

              • 4. Re: Credentials for MS SQL

                Hi again,

                 

                The DB Group have a question for you...

                 

                They are asking how ADDM can log on to SQL server when this functionality is added and is perferred from a safety veiew.

                 

                image001.png

                 

                I'm thankful for all advice.

                 

                BR,
                Inger Kristine

                • 5. Re: Credentials for MS SQL
                  Kerryn Wood

                  Hi,

                   

                  You'll just need to add the Database Credential to ADDM, using the required Windows account which may, or may not, be the same as the credential used to access the system.

                  • 6. Re: Credentials for MS SQL

                    Hi.  I have the exact same error.  I'm VERY new to ADDM and am evaluating ADDM 10.2 Community Edition.  I don't believe database discovery is a limitation of this edition, so I'm expecting it to work if I configure it properly.

                     

                    I've tried the "Microsoft SQL Server (jTDS Driver 1.3.1)" and "Microsoft SQL Server JDBC Driver 4.1 and 4.0", but get the same results.

                     

                    The credentials I'm trying to use are an AD account...not a local SQL account.  I know my AD account has access to SQL Server as I use this same account with HP UCMDB discovery.  In HP discovery, I have to specify which type of account I'm using.  If I specify the wrong type of account, the test will fail.  In ADDM, I don't get the option to specify that I'm using an AD or local SQL account.  Maybe ADDM is smart enough to figure that out.

                     

                    Anyhow, I'm unable to connect to my Microsoft SQL Servers at this point.  Any other suggestions?

                     

                    Thanks.

                    • 7. Re: Credentials for MS SQL
                      Andrew Waters

                      To use AD credentials you need to specify useNTLMv2=true - see documentation here.

                      • 8. Re: Credentials for MS SQL

                        I should have known to search the documentation for NTLM...thanks for that find.     

                         

                        Ok, so I've updated the "Additional JDBC Parameters" by adding useNTLMv2=true.  I've also changed the "Database Driver" back to "Microsoft SQL Server (jTDS Driver 1.3.1)".  But I'm still getting the following error message when I test the credential:

                         

                        Failure - Connect to "jdbc:jtds:sqlserver://x.x.x.x:1433;useNTLMv2=true" at today at 15:29:23 requested by system

                        SQL Provider: Unable to open a connection to the database: Login failed for user 'MyDomain\MyUser'.

                         

                        And my SQL DBA send me the error from his side which indicates the logon attempt isn't trying to use NTLM.

                         

                        Login failed for user 'MyDomain\MyUser'. Reason: Attempting to use an NT account name with SQL Server Authentication.


                        It appears my credential isn't really trying NTLM yet.  Other than adding "useNTLMv2=true" in the credential, is there anything else I need to do for it to take affect?


                        Thanks,

                        Ron

                         

                        • 9. Re: Credentials for MS SQL

                          Ok, figured it out.  2 things I missed.

                           

                          #1:  I was putting the domain\user in the "Username" field when I should have only put in the username.  And I needed to add the domain=MyDomain as an additional parameter.

                           

                          #2:  When adding more than one parameter in the "Additional JDBC Parameters" field, it states to list each parameter on a new line.  However, when I executed the test, it simply bumped both parameters together with no spaces causing it to fail. I was able to get around this by putting each parameter on a single line separated by a semicolon.

                           

                          Any comments on my #2 above?

                           

                          Anyhow, my test works now.

                           

                          Thanks!

                          1 of 1 people found this helpful
                          • 10. Re: Credentials for MS SQL

                            Hi

                             

                            I am facing similar issue while SQL DB server test connection, using an AD account in this case, otherwise with local account on couple of Oracle server, things working fine. Issue is with AD account and its the same account as configured for ADDM to login into server and run discovery. I can login into server with this account and can login into SQL database also with same account but its failing test remotely from ADDM. Please suggest.

                             

                            have mentioned all values in proper way as suggested above.

                            1.User, password is proper-I can login into database directly after logging into server.

                            2.DB Instance is blank and I put it while testing

                            3.Port is blank and I put it while testing

                            4.Additional JDBC Parameters are domain=MyDomain;useNTLMv2=true;

                            5.And below are the erros I see

                             

                            Failure - Connect to "jdbc:jtds:sqlserver://XX.XXX.XXX.XXX:2437/SHAREDSQL;domain=MyDomain;useNTLMv2=true;" at today requested by system

                            SQL Provider: Unable to open a connection to the database: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

                             

                            Failure - Connect to "jdbc:sqlserver://;serverName=XX.XXX.XXX.XXX\MSSQLSERVER;port=1433;domain=MyDomain;useNTLMv2=true;" at today requested by ystem

                            SQL Provider: Unable to open a connection to the database: Login failed for user 'svc-ADDMProxy'. ClientConnectionId:470cf49d-a934-4a2c-893a-e2c67c7ab2c3

                             

                            And whats the difference between 2 option provided in drop down for SQL

                            Microsoft SQL Server (jTDS Driver 1.3.1)" and "Microsoft SQL Server JDBC Driver 4.1 and 4.0

                             

                            Also if multiple domains have trust relationship, how/can I test IPs from multiple domain in one go & success scan then in deep discovery?

                             

                            Lastly is there a way to generate a report on which SQL and Oracle credentials failed/success?

                             

                            Thanks

                            Sandeep

                            • 11. Re: Credentials for MS SQL
                              Andrew Waters

                              The difference between the options is that they are using different JDBC drivers. Per the docs you have to use JTDS if attempting to use domain authentication.

                               

                              What exactly appears in the logs?

                              1 of 1 people found this helpful
                              • 12. Re: Credentials for MS SQL

                                Hi

                                 

                                Creds working now, there was some permissions issue.

                                 

                                Still looking for "is there a way to generate a report on which SQL and Oracle credentials failed/success?

                                 

                                Thanks

                                Sandeep

                                • 13. Re: Credentials for MS SQL
                                  Woo Lam

                                  Did you find a way to generate a report on which SQL and Oracle credentials failed/success?

                                   

                                  Thanks

                                  Woo

                                  • 14. Re: Credentials for MS SQL
                                    Woo Lam

                                    I saw jtds driver for MS SQL was missing in BMC Discovery v12.0.0 and I only saw MS SQL JDBC drivers that we can upload. How can I configure MS SQL database credential to use the latest JDBC drivers to test Windows authentication? I tried putting "DOMAIN\user" in user field and provided IP address, port 1433 and database instance, I got  sqlserver failed with error com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'DOMAIN\user'. ClientConnectionId:19f4fd6b-46cc-4059-91b2-6d73598eb6f9.

                                     

                                    Thanks

                                    Woo