How to scan SQLServer using SQL Adapter

book

Article ID: 160467

calendar_today

Updated On:

Products

Data Loss Prevention Network Discover

Issue/Introduction

Scanning MS SQLServer using SQL Adapter

Resolution

Relevant versions: 7.0 and up

1. Download the jdbc drivers for SQL server to the Discover server where you will be running SQLAdapter.  You can get them here:

http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en

2. Ensure that you can connect to the SQLServer DB Server from the Discover server - try a telnet over port 1433

3. Go to the SQLAdapter.lax file located in protect\bin and add the paths to the 3 jar files to the LAX.CLASS.PATH property. They are called msbase.jar, mssqlserver.jar, and msutil.jar. Save the file.

4. Go to the SQLAdapter.properties file located in protect\config and add the following lines:

 driver_class.microsoft = com.microsoft.jdbc.sqlserver.SQLServerDriver
 driver_subprotocol.microsoft = microsoft:sqlserver
 driver_table_query.microsoft = SELECT NAME FROM dbo.sysobjects WHERE xtype = 'U'

Or:

driver_table_query.sqlserver = SELECT table_schema + '.' + table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'

You can limit the number of rows returned by using

driver_row_selector.sqlserver = SELECT TOP {2.EN_US} {1.EN_US} FROM {0.EN_US}

5. In Vontu, add a custom target and point it to the Output path you'll use for the extract files (i.e. c:\drop_Discover).

6. Start the scan

7. Open a command window and change to the protect user by typing:

For Windows:

runas /user:<hostname>\protect cmd.exe 

For Linux:

 su - protect

8. From the new command window that gets opened from the previous step, go to the protect\bin directory and run the following command:

Sqladapter -alias=//<DB server>:1433;DatabaseName=<DB name> -username=<DB user> -password=<Passwd> -output=<Output path> -vendor=microsoft -rows=0

For example: 

Sqladapter -alias=//10.65.67.24:1433;DatabaseName=master -username=sa -password=xxxxxxxx -output=c:\drop_Discover -vendor=microsoft -rows=0

This should result in several .zip files getting written to the output directory, which should get picked up by the running discover scan.

Other Notes:

If you want to only scan certain tables in the DB, you should change the Select statement in step 4 to read:  SELECT 'Table1','Table2','Table3',…

One caveat is that you cannot limit the number of rows that get extracted like you can with Oracle at this time.

NOTE:   As of Symantec DLP 8.1, the recommended approach for SQL database scanning is to use the new method of setting up and running a scan on a SQL database.

Use of the SQL Adapter is no longer required or recommended, and support for it will be removed in a future release.