Creating EDM profile from mssql server

Products

• Data Loss Prevention Enforce

Issue/Introduction

Creating an EDM  indexed profile from a  MSSQL server.

 

Note:  Symantec is not responsible for getting customer's data out of their repositories, nor responsible for any results including damages, from using third party tools and documents. These instructions are a guide but not intended to be a supported tested solution. The  Administrator guide clearly states that our Preindexer utility runs against Oracle DB only, See the DLP Admin guide About the SQL Preindexer and EMDI.

If you are using the SQL Preindexer with MSSQL databases, it is not supported, as it has not been certified nor tested. Symantec is not obligated to support untested tools, even if they are Symantec tools. Proceed at your own risk. For support on MS SQL database, please contact Microsoft technical support.

 

Cause

You would like to create an EDM for the Enforce DLP system to use, however the  SQLPreindexer  is only certified and supported on the Oracle database systems.

Resolution

Creating an EDM for MSSQL  Databases  without  using the SQLPreindexer  DLP solution:

Note: Our DLP Admin guide has the official supported instructions on how to use Exact Data Matching detection technology beginning at "Remote EMD Indexing." "Creating the Exact Match Data Identifier source file" has instructions on how to create the Source file for EDM, and it is the customer's responsibility to generate that cleaned and sanitized data file.

Creating an EDM from a datasource involves  getting the datasource in the correct format on the Enforce server,  and then  configuring a EDM data profile  to use the data source  file and create the EDM. 

On the MSSQL server,  you  can will need to create a  query  to pull the wanted data from your database.  After the query is pulling the correct data  and fields you want to use for your EDM,  you can  then export  the query results as a CSV file,  comma,  pipe, or tab  delimited.  Copy  the exported  CSV  file to the  "datafiles"  folder on the enforce system,  and then you can create your EDM using  the copied CSV file as your data source.

Note:  When creating a csv file from the MSSQL server, it will not be encrypted,  therefore it is like copying a csv file to enforce server to use to create the EDM profile.

a)  on MSSQL  create a query to pull the data you would like to use for the EDM profile.  Note:  an EDM profile is best to have at least 2 to 3 fields for better indexing.

b)  run the query and make sure the results or data is what you would like to use.  i.e  "Select FirstName, LastName, EmailAddress from Person.Contact"    using AdventureWorks test database.

c) Once query is correct,  you can either export the results to a CSV  file,  or  create a SSIS  package with  integration services to automate  the process.  Basically you are just creating a CSV  file that the enforce server can use to create the EDM profile.  You will want to get the exported CSV file to enforce sever under drive:\SymantecDLP\protect\datafiles\edm_mssql_output_csv   Note:  you can name the file whatever you want, just make sure the file is on proper place on enforce server and matches the csv outputted filename from mssql server.

d)  On Enforce server go to  Manage   -->   then  Data Profiles  --->  Exact Data     click on add  Exact Data Profile.  Give data profile a name. 

e)  Click on "Use  This File Name"   and give a name as   edm_mssql_output_csv   to match the exported CSV  file from the MSSQL database.  Number of columns   3 in our example,  choose the comma delimited option and leave the 5%  error threshold,  then click next  Note:  The filename must match exactly  the file exported from the mssql server.

f)  Col 1     =   firstname     Col 2  =  LastName    Col 3 = email    in our example.  Also,  you may check the box  Submit Indexing Job on Schedule  and choose daily or weekly etc.. on how often the EDM  profile is indexed. 

Note:  Once  Enforce server indexes the file,  in our case   the drive:\SymantecDLP\protect\datafiles\edm_mssql_output_csv  file according to schedule it will delete the source file so it will no longer be in the Vontu\Protect \datafiles   directory.   You can schedule your  SSIS  package to export the file daily @ 6:00 pm,  and Schedule the Index  Job to run @ 7:00 pm.  This way you get up to date data for your EDM profile.

 

Applies To

 

Microsoft MSSQL databases  2005,  2008, and 2012 versions.