7 Replies Latest reply on May 17, 2012 6:53 AM by Jason Creech

    Policy Compliance - SQL Controls fail to evalute

    Nitin Gopinathan Level 1

      Hi,

       

      I have been running quite a few scans to check whether my SQL Server 2000 controls work as expected. However, there is no data related to SQL Server being displayed in any of the reports. No graphs, no data, nothing. I am not able to figure out the problem despite repeated changes to the policy and scan profiles. The machine being scanned is tunning SQL Server 2000 on a Windows 2003 Server machine. Screenshot below shows the output I get from every scan.

       

      EDIT : When testing for the same values using manually created controls, things seem to be running fine. The inbuilt controls are failing without any error message. Is this a known issue?

       

      sql_policy.JPG

       

      Regards,

      Nitin Gopinathan

        • Policy Compliance - SQL Controls fail to evalute
          Jason Creech Level 3

          Hi Nitin,

           

          This looks like a setup issue.

           

          It may be that you have a good Windows authentication record but not a MSSQL Authentication record. 

           

          The policy above appears to have both Windows OS and Windows MSSQL controls. You would need to have two authentication records for scanning the target Windows systems for this policy to provide both the OS and DB level configuration content.

           

          You mention that manually created controls (User Defined Controls or UDC I am assume?) are reporting okay.  Since UDCs are only available for Windows or UNIX authentication and not database technologies, it may be that you have not setup a MSSQL authentication record but do have Windows authencation records setup.

           

          When a Windows system is scanned, the Windows authencation record is used for that technology type to gather OS values.  If it has a MSSQL database installed, you will need an MSSQL auth record as well even if the database is configured for Windows or Mixed Mode authentication.  MSSQL Auth records also have an OS level configuration option for some of the OS level database checks as well.

           

          Let me know if this helps,

           

          Jason Creech

          Qualys

          1 of 1 people found this helpful
            • Policy Compliance - SQL Controls fail to evalute
              Nitin Gopinathan Level 1

              Hi Jason, thanks for your reply. We were indeed missing an SQL record for the target server. I need to clarify one thing though. When entering details for the record, what Authentication Type should I choose? I tried a scan after choosing "Windows", but the scan failed again like earlier. Should my authentication type be "Database"?

               

              sql_record.JPG

               

              Again, thanks for your help.

                • Policy Compliance - SQL Controls fail to evalute
                  Jason Creech Level 3

                  Hi Nitin,

                   

                  Your JPG didn't come through for some reason.

                   

                  But, to answer your question, the appropriate authentication records to use will depend on the policy data you want and how your MSSQL databases are configured.

                   

                  If your MSSQL databases are configured for Windows Auth or Mixed mode, then you can use Windows option in the MSSQL auth record since the account will have rights to authenticate into the database to get confgurations values.

                   

                  But, if the MSSQL databases are configured for database authencation only, you must use database option in the MSSQL record.

                   

                  If your policy includes OS level controls listed under the Windows Server or AD technology types AND MSSQL technology controls, you will need both a Windows Authencation record AND an MSSQL Authentication record.

                   

                  If you are using the appropriate auth records and still getting failures, make sure the account you are using has appropriate admin rights. 

                   

                  In the HELP>>Resources>>Tips and Techniques areas, you will find the Trusted Scanning Guide for MSSQL, This guide has the information you will need to setup the MSSQL authentication.

                   

                  Best Regards,

                   

                  Jason Creech, Qualys

                    • Policy Compliance - SQL Controls fail to evalute
                      Nitin Gopinathan Level 1

                      Hey Jason,

                       

                      I have created the necessary authentication record. Also, after referring to the MS-SQL Server scanning Tips and Tricks, I cross checked using the following commands whether the account used for scanning had sufficient privileges

                       

                      1. selecttop 1 name permission from master.dbo.syslogins
                      2. selecttop 1 1 permission from master.dbo.sysdatabases
                      3. selecttop 1 1 permission from master.dbo.sysconfigures
                      4. selecttop 1 1 permission from master.dbo.syspermissions

                       

                      The output was as expected.

                       

                      The scans are still failing with the message "No data found". I am really at my wits end here. All the necessary inputs seem to be in place, but there is no output.

                       

                      Thanks for your help.

                        • Re: Policy Compliance - SQL Controls fail to evalute
                          Jason Creech Level 3

                          Hi Nitin,

                           

                          At this point, since it is difficult to ascertain where the issue might be with the limited information we can share in this forum, we should setup a support case for tracking purposes.  Support can rule out the usual configuration/setup issues and ascertain if the problem is a bug or if there was a step missed in the configuration of the MSSQL scans, policy creation, and/or reporting. 

                           

                          Also, if support is unable to resolve the issue, I could conduct a WebEx to take a look as well.  There are several areas where the setup or process could be off, that is usually the issue though, occasionally, there may be an issue engineering might need to address. 

                           

                          Contact support@Qualys.com and describe the issue you are seeing and the steps you have taken to resolve as well as your contact information and company name.  Support can get you a case number for tracking and, if necessary, a debug scan could be used to identify where the issue may lie.

                           

                          I am curious that after you scan the MSSQL system, does the scan even see the IP addresses, give a message about insufficient privileges or auth failed? Not in the report but in the compliance scan information view? 

                           

                          Below, you can see an example of compliance scan results from my test MSSQL system with two DB instances and the MSSQL authentication was successful, which means user/name password was correct.  And, I did not receive an "insufficient privileges" error message which means my account had appropriate access to gather the data. You can see another IP where my scan credentials did not have sufficient access to scan that IP so it threw the error message "insufficient privileges".

                           

                          Compliance Scan Results.jpg

                           

                          Let me know if you have an issues creating the support case.

                           

                          Best regards, Jason Creech

                            • Policy Compliance - SQL Controls fail to evalute
                              Nitin Gopinathan Level 1

                              Here is a screenshot of the compliance scan information window, it specifically mentions that MSSQL authentication has failed. I will try and create a support ticket and get back to you if I need help. Thanks!

                               

                              sql_auth_fail.JPG

                                • Policy Compliance - SQL Controls fail to evalute
                                  Jason Creech Level 3

                                  Hi Nitin,

                                   

                                  Thank you for the information.  I think contacting support is still the best option as they can work directly with you to figure out the issue.  Failed authentication usually means one of two things on the database scanning.

                                  1. Most simply Username/Password are incorrect which I doubt based on your responses.
                                  2. More likely, the database is not configured to allow the auth type being used (I.e., database is not configured for mixed mode or Windows auth and a Windows credential is being used.) or some variation of this.

                                   

                                   

                                  But, without having access to the system, I am guessing a bit.

                                   

                                  Please update the post if support is able to resolve the issues for you.

                                  Best regards,

                                  Jason Creech