Not able to scan MSSQL 2005 DB

[GoogleCodeExporter]

What steps will reproduce the problem?
1.Create a DB Scan profile with just sepcific DB to scan
2.Run the scan mentioning DB server IP and selecting created profile
3.

What is the expected output? What do you see instead?
It should scan the DB and run throught the tables reporting the findings. 
Instead it just successfully authenticates and does not scan through the 
tables. No findings.

What version of the product are you using? On what operating system?
Open DLP 0.4.3

Please provide any additional information below.
It runs well with the same profile on MSSQL 2000 DB

Original issue reported on code.google.com by softhear...@gmail.com on 10 Feb 2012 at 4:32

[GoogleCodeExporter]

Hello,

1. Are you using an OS account or a MSSQL account?

2. What account are you using?

3. Is the account privileged or unprivileged?

4. What is the output of this query:
SELECT catalog_name FROM information_schema.schemata;

Original comment by andrew.O...@gmail.com on 10 Feb 2012 at 7:11

[GoogleCodeExporter]

I am using a Domain account which has been given DB owner privilege on required 
DB.
The particular account has also been given local administrator right on the 
server.
When we run the particular query it just shows Master DB name and not other 
ones which we need to scan.

Original comment by softhear...@gmail.com on 13 Feb 2012 at 9:05

[GoogleCodeExporter]

Does it scan all of the databases if you use the Microsoft SQL server account 
"sa"?

Original comment by andrew.O...@gmail.com on 13 Feb 2012 at 11:21

[GoogleCodeExporter]

Even when using SA account the same result. It does not scan the other DBs.

Original comment by softhear...@gmail.com on 13 Feb 2012 at 2:21

[GoogleCodeExporter]

Ok, thanks for the update. I only tested OpenDLP with MSSQL 2000, so I'll grab 
a copy of MSSQL 2005 from MSDN and see what's going on.

What version are you using: Standard, Enterprise, Workgroup, Developer, or 
Everywhere?

Original comment by andrew.O...@gmail.com on 13 Feb 2012 at 2:51

[GoogleCodeExporter]

Enterprise.

Original comment by softhear...@gmail.com on 13 Feb 2012 at 3:47

[GoogleCodeExporter]

We identified this in our testing as well. It appears to be the use of:
"SELECT catalog_name FROM information_schema.schemata" which is incompatible 
with MSSQL >2000. We tried "SELECT name FROM master..sysdatabases WHERE dbid > 
4" which worked. The "WHERE dbid > 4" is to exclude system tables. Patch 
attached (note: not rigorously tested).

Original comment by cbyr...@gmail.com on 13 Feb 2012 at 4:29

Attachments:

• SQL.patch

[GoogleCodeExporter]

Thanks for the patch! I'll give it a test drive on 2000 and 2005, then I'll 
include it in my next release.

Original comment by andrew.O...@gmail.com on 13 Feb 2012 at 8:20

• Changed state: Accepted

[GoogleCodeExporter]

Thanks everyone for pulling their time and effort....will wait for the update 
release.

Original comment by softhear...@gmail.com on 14 Feb 2012 at 7:25

[GoogleCodeExporter]

Working with Christopher, I have found that the use of the "EXEC sp_helpdb" 
query is more friendly to SQL Server accounts with limited privileges. 
Hopefully this helps.  

Original comment by bh5...@gmail.com on 14 Feb 2012 at 3:20

Attachments:

• stored_procedure.sql

[GoogleCodeExporter]

This should be fixed in 0.4.4, which I released today. Thanks again for 
reporting it and for supplying a patch to fix it.

Original comment by andrew.O...@gmail.com on 22 Feb 2012 at 11:10

• Changed state: Done