Completing Connection Properties for Database Sources

The SQL Server, OLE-DB Provider, Oracle, and ODBC sources share common input controls in the bottom panel of the adapter properties page.

To complete connection properties for these database sources:

1. To complete the Connection String field, click the ellipsis button (...) at the right end of the field.
   The standard Microsoft Windows Data Link Properties dialog appears.
2. Complete the required details, and the connection string is created for you.
   1. In Select or enter a server name, choose or enter a fully qualified server name (or IP address) for the server on which the database is running.
   2. Choose the authentication method for the account under which the Business Importer will access the database. Use Windows NT Integrated Security is recommended for easier maintenance over the long term; or you may use SQL authentication by choosing Use a specific user name and password, and entering the account details.
      Important: If you enter the account details, be certain to select Allow saving password so that the password can be brought into the adapter. Otherwise the password will be lost as soon as you click OK.
   3. From the Select a database on the server drop-down list, choose the database.
   4. Click Test Connection to make sure that your specifications are correct (adjusting as necessary for success).
   5. Click OK to write these connection details for this adapter into Business Adapter Studio.
3. If you choose to edit the string or need more information, the following table provides additional notes for each database type.

   SQL Server

   • If you selected Windows authentication, a typical connection string (all on one line) is of the form
     Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SourceCatalogName;
Data Source=SQLServerName

   • For SQL authentication, a typical connection string is of the form
     Password=SQLPassword;Persist Security Info=True;User ID=SQLAccount;
Initial Catalog=SourceCatalogName;Data Source=SQLServerName

   OLE-DB

   OLE-DB is a generic driver that can be used with any databases such as Microsoft Access, Ingres, Paradox, and others, provided that the corresponding OLE-DB driver has been installed and configured on the machine where the import is run.

   Note: The Business Importer is a 32-bit application, and 32-bit OLE-DB connection strings must be used on 64-bit operating systems.

   An example connection string for Microsoft Access:
   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[Path and name of the .mdb file]

   Oracle

   Oracle connections require the installation of an Oracle client provided by Oracle Corporation. The Oracle client installs the OLE-DB driver for Oracle. An example connection string:

   Password=Password;User ID=User;Data Source=OracleDataSourceName;
Persist Security Info=True

   ODBC

   ODBC is a generic driver than can be used in conjunction with the Microsoft OLE-DB Driver for ODBC Drivers. The connection string varies according to the driver used.

   An example for a connection to an Excel file using a test DSN:

   DSN=test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;

4. Enter an SQL query in the Query Text field.
   This query runs against the data source, and return a grid of data that the Business Importer brings into the compliance database in IT Asset Management. Most of the rest of the Business Adapter Studio UI focuses on mapping the data returned by this query to the appropriate properties in the compliance database.
5. In the Timeout field, enter the number of seconds to wait before giving up a query from the data source. The following values have special meaning:
   • A value of 0 means there is no limit and the Business Importer will wait indefinitely for the query to finish.
   • A value of -1 means that the default time-out determined by the source database server should be used.