Control Compliance Suite - External Data Integration - creating a MySQL Data Location

Article:TECH199623  |  Created: 2012-11-12  |  Updated: 2012-11-13  |  Article URL http://www.symantec.com/docs/TECH199623
Article Type
Technical Solution


Issue



Control Compliance Suite v11 can connect to and collect from external data sources to collect data. One such connection can be an ODBC connection to a MySQL database server. It has been noted that using a 32 or 64bit MySQL driver and/or the 32 or 64bit windows "ODBC Data Source Administrator" makes a difference when configuring such a connection within CCS. This article outlines what to watch out for and how it works with the current version of CCS (v11.0.546.10100, PU 2012-1, SCU 2012-3).

 


Error



No error as such but a previously configured "ODBC data source" using the windows "ODBC data source Administrator" might not show up when configuring the Data location within CCS.

 


Environment



Control Compliance Suite v11

Windows 2003, 2008, 2008R2 - x64

MySQL

 


Cause



The root cause of the unexpected behaviour is related to ODBC Data Source created DSN. The CCS console expects and only lists 32bit DSN's - the CCS data collector on an x64 (64bit) CCS manager that does the actuall data collection expects and only works with 64bit DSN's.

 


Solution



This scenario is assuming a single CCS system that contains all the components, CCS Application server, Manager and Console, all-in-one, installed on a Windows 2008 R2 (x64) system.

Step 1 - install the 32 and 64 bit MySQL ODBC drivers.

  • Install the 32bit MySQL ODBC drivers (mysql-connector-odbc-5.2.2-win32.msi or simular)
  • Install the 64bit MySQL ODBC drivers (mysql-connector-odbc-5.2.2-winx64.msi or simular)

 

Step 2 - create the 32bit DSN using the 32bit version of "ODBC data source Administrator" - this connection will be seen and used by the CCS console.

  • browse to C:\Windows\SysWOW64 and excute odbcad32.exe from this location
  • create a SYSTEM DSN to your MySQL server - name it: MySQL_DSN

click to enlarge

Fig 1. This shows the directory and two DSN's - one named "32bit" for illustration purposes only.

 

Step 3 - create the 64bit DSN using the 32bit version of "ODBC data source Administrator" - this connection will be used by the CCS Manager for data collection.

  • browse to C:\Windows\System32 and excute odbcad32.exe from this location
  • create a SYSTEM DSN to your MySQL server - name it: MySQL_DSN

click to enlarge

Fig 2. This shows the directory and two DSN's - one named "64bit" for illustration purposes only.

 

Step 4 - create a data location in CCS

  • Start the CCS console, navigate to: Settings -> General -> Data Locations
  • select "Add.." and Select "Data location type: ODBC", give it a name and select the box for the connection string with the three dots.
  • select the "Connection" tab and click on "Use data source name" to view the list, select "MySQL_DSN"

click to enlarge

Fig 2. This shows only two DSN's - only the 32bit DSN's - it does not show the 64bit DSN. Because we named the 32bit and 64bit DSN the same - the console is allowed to select a DSN with the name "MySQL_DSN" but the CCS manager will look for and use the 64bit DSN with the same name i.e. MySQL_DSN.

  •  Finish the Data Location details and test the connection - save and close.

 

Step 5. - create a Data System within CCS.

  • Within the CCS console, navigate to: Manage -> External Data Integration -> Data System Tasks -> Add Data System
  • Enter a System Name and select next,
  • Enter a connection name, select ODBC for the connection type and select the in step 4 created data location.
  • For query type, select "SQL command" (Table or View name doesn't work properly when querying MySQL)
  • Enter your SQL command on the "Table/View/SQL command" box. (something like: SELECT * from MySQLDB.MyTable )

continue with the configuration until finished - further configuation and use of External Data Integration is outside the scope of this knowledgebase article, for more information around External Data Integration please consult the CCS administration guide.

 

As you're reading this article, you might also be interested in the following articles:

MySQL External Data Integration - Error: The specified DSN contains an architecture mismatch between the Driver and Application. - http://www.symantec.com/docs/TECH199664

Control Compliance Suite - MySQL External Data Integration - Error: You have an error in your SQL syntax. - http://www.symantec.com/docs/TECH199666

 




Article URL http://www.symantec.com/docs/TECH199623


Terms of use for this information are found in Legal Notices