Creating an EDM profile from an MSSQL database
search cancel

Creating an EDM profile from an MSSQL database


Article ID: 159723


Updated On:


Data Loss Prevention Enforce Data Loss Prevention


Creating an EDM-indexed profile from an MSSQL database.
This applies to any non-Oracle database.


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 online help page clearly states that our Preindexer utility runs against Oracle DB only, About the SQL Preindexer for EDM.

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 the MSSQL database, please contact Microsoft technical support.



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.


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 EDM 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 data source involves getting the data source in the correct format on the Enforce server,  and then configuring an EDM data profile to use the data source file and create the EDM. 

On the MSSQL server,  you 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 the 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 are what you would like to use.  i.e. "Select FirstName, LastName, EmailAddress from Person. or Contact" using the AdventureWorks test database.

c) Once the query is correct,  you can either export the results to a CSV file or create an 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 in the proper place on enforce server and matches the CSV outputted filename from MSSQL server.

d)  On the Enforce server go to "Manage   -->   Data Profiles  --->  Exact Data " and click on "Add Exact Data Profile".  Give the 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. Select the 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, weekly, etc. for how often the EDM profile is indexed. 

Note: Once the Enforce server indexes the file, in our case the drive:\SymantecDLP\protect\datafiles\edm_mssql_output_csv file, according to the 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 at 6:00 PM and schedule the Index job to run at 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.