Create a Data Connector data source that accesses data in a Microsoft SQL Server database through an ODBC System DSN

Products

• IT Management Suite

Issue/Introduction

 This article describes how to create and configure a Data Connector data source to access data in a Microsoft SQL Server database through an ODBC (Open Database Connectivity) System DSN (Database Source Name). 

Environment

ITMS 8.x

Resolution

A Data Connector data source is needed to access the user's data so that a Data Connector import rule can later import this into the Symantec Management Platform database. For more information on how to use Data Connector or how to use different data source types, refer to the following article:

Using Data Connector to import data into the Symantec Management Platform database
181160 

Part 1: Create an ODBC System DSN.

PLEASE NOTE: While Symantec Technical Support can help assist the customer in understanding how to use Data Connector and provide troubleshooting for it, Support is unable to create or troubleshoot an ODBC System DSN for the customer as this requires access to their database via ODBC. If the customer has difficulty getting this to work, they will want to contact their network or computer administrator for further assistance in configuring their Microsoft Windows Server. Note: Because it may be difficult to configure the ODBC System DSN, the customer may want to instead use an OLEDB data source. For more information on how to use an OLEDB data source, refer to the following article:

Create a Data Connector data source that accesses data in a database through OLEDB
181751

1. On the Symantec Management Platform server, click on the Windows Start button > Administrative Tools > Data Sources (ODBC).
2. Click on the System DSN tab.
3. Click on the Add button.
4. Click on SQL Server. (There are several other data format types that can be used, such as dBASE, however, these are not discussed in this article.)
5. Click on the Finish button.
6. In the Name field, type the name of the System DSN. For example, "Symantec_CMDB database".
7. In the Description field, type an optional description, if desired.
8. Click on the Server field drop-down list and select the Microsoft SQL Server database to use. Note: If the Microsoft SQL Server database to use is not on this list, it may not be accessible over the network to the Symantec Management Platform server. Ensure that it is "broadcasting" over the network to be accessible by other servers.
9. Click on the Next button.
10. Configure the authentication to the SQL Server. The steps for this will vary, depending on the user's SQL Server security. For example, the following may or may not work:
    
    a. In the "With Windows NT authentication using the network login ID", click on its checkbox to select it.
    b. In the "Connect to SQL Server to obtain default settings for the additional configuration options", click on its checkbox to select it.
     
11. Click on the Next button.
12. In the "Change the default database to" field, click on its checkbox to select it.
13. Click on the "Change the default database to" drop-down list and select the database to use, which by default is Symantec_CMDB.
14. Click on the Next button.
15. Click on the Finish button.
16. Click on the Test Data Source button. If "TESTS COMPLETED SUCCESSFULLY!" occurs, continue to Part 2. Otherwise, go back and re-review these steps to ensure that they are correctly configured, especially the authentication options. Note: Even though the tests may complete successfully does not guarantee that a Data Connector data source will be able to later access the database. This just means that the database was tested and able to be seen by the System DSN, not its data. Refer to Part 2, step 8, for more information on this issue.
17. Click on the next three OK buttons.

Part 2: Create a Data Connector data source.

1. In the Symantec Management Platform Console, click on the Settings button > All Settings.
2. Click on to expand the folders Notification Server > Connector > Data Sources.
3. Right click on the Data Sources folder > New > ODBC Data Source.
4. Click on the New ODBC Data Source name and change this to a new name. For example "Data source used for importing computers from a database using ODBC".
5. In the System DSN field, type the name of the System DSN as entered on Part 1, step 6.
6. In the "Import query" field, type the desired SQL script that will provide the applicable columns and records to import using the import rule. Note: This article includes two sample SQL scripts that demonstrate how to do this and therefore can be used for the import query. These can be pasted as-is into the import query field or the user can adapt/add to these as they desire.
7. If data is to be pre-processed using C# code:
   
   a. Click on the "Pre-process import data" checkbox to enable it.
   b. Click on the "Edit function" button.
   c. Type the desired C# code.
   d. Click on the "Save changes" button.
   e. Click on the "View import data" button again. If the data no longer appears as expected, correct the C# code until it does.
   
   Note: When setting up the data source, it is not recommended to immediately set up its pre-processing C# code as that may further complicate the configuration. This can be skipped and later added after the data source and import rule are configured and working. For information about how to use C# code for data source pre-processing, refer to the following article:
   
   Using C# code for Data Connector data source pre-processing
   181197
    
8. Click on the "View import data" button to view the data from the import query. If an error for the System DSN occurs, such as "Unable to get data from the selected data source.", ensure that its name is entered correctly in step 5. If an error occurs for the import query or if the data doesn't appear as expected, correct the name or custom import query until the expected data is present.
9. Click on the "Save changes" button. The data source should now look similar to the following example:
   
   
    
10. Next, create a Data Connector import rule that uses the data source created in these instructions. For more information on how to create a Data Connector import rule, refer to the following article:
    
    Create a Data Connector import rule that creates or updates resources
    181748