Setting up the SQL external database (notes for DBA’s)

To enable these Lookups functions to operate, the DBA providing access to the SQL server type external database/s needs to provide that SQL access according to the following instructions.

Permissions

We recommend that an SQL login be created in the external database that provides read-only access to the objects (tables and views) to be used in the Lookup operations.

We also recommend that this user be given SQL Select access only to those objects that are required for the Lookup operations.

Importantly, if this approach is taken, the connection information provided in the RefTracker datasources definition screen can only be used for Lookups and so ensures that having the access information recorded in RefTracker does not jeopardise the security of the external database in any way.

Your DBA may even wish to create a Windows account under which RefTracker runs (rather than the standard ASP.Net account). This account could have read/write access to the RefTracker database and only read access to the external database. The connection string could then use integrated security – in which case there would be no need to include a username and password in the connection string at all.

Database objects view

A view named reftdbobject must exist in the external database which must return two columns;

  1. reftdbobject_id
  2. reftdbobject_desc

This view must return the name of the view in both the reftdbobject_id column and the reftdbobject_db column because the object name is used to build the query that gets the data from the external database. For example, here is the output from the reftobject view for the example Xeno database.

The reftdbobject view is used in RefTracker to present a list of objects in the external database (Source objects), from which the user can select.

At its simplest this view can simply return all objects from the sysobjects table which are of type U or V (user created tables and views).

However if the external database has a large number of possible objects we recommend that this view is coded to only return the objects that are to be used in the Lookup operations.

If the login is granted SQL Select access only to the required objects then there should be no need to modify this view. The intrinsic SQL security will restrict the objects retrieved.

Example reftdbobjectview

RefTracker is able to query the tables in the external database directly. However views should ideally be created if any of the following conditions are true;

  1. The external table has a large number of columns, in which case the view can restrict the columns being returned to only those that are required for the Lookup operation.
  2. The column names in the external table are obtuse, in which case the view can provide more meaningful names by assigning an alias.
  3. There is a need to return only a subset of the records in a given table, in which case the view can restrict the records returned.
  4. Columns are required from more than one table, in which case the view can join the required tables.


How your database will be queried is set up in the next step of this setup – Lookup definitions. More information is provided about setup in that step.


Exercise

Have your DBA set up Datasources definitions for each of the external databases that you will need to look up.As described here, for each external database that will be accessed to achieve your Lookups, the DBA needs to create an entry in the External databases table using an appropriate user in the external database.

Then they need to set up the reftdbobject view in the external database.

Utilising Linked servers

If your organisation utilises SQL Linked Servers, these views can be set up in the RefTracker database rather than in the external database. This approach removes the risk that the owner of the external database may accidentally remove the views used by RefTracker.

Setting up where LDAP is the external database

To date all customers have had security issues with allowing RefTracker to access their LDAP file directly. We continue to work with customers to find a way that direct access can be done.

In the meantime the following solution has proven to be successful.It relies on the LDAP data being copied over from LDAP to a file set up within RefTracker. This file is used for the lookups until a refresh period has elapsed, after which a refresh form LDAP is done on the next occasion that a lookup done by a RefTracker user. As the lookup occurs when the client or staff member is waiting, the refresh time should be made as large as possible.

To use this workaround LDAP solution, – advise your RefTracker support representative that you are taking this approach so that they can create, or provide instructions to create the LDAP file in RefTracker, and help you with this setup.To be able to utilise data copied form your LDAP you will need to have OLE Automation in Advanced SQL Configuration turned on.

  • set your database definition in System>Lookups>Database definitions to point to the file in RefTracker e.g. Data Source=utah9;Initial Catalog=reftracker;User ID=reftracker;Password=reftracker
  • then set the parameters as notes below
  • be sure that you also have the external setting correct as described below. 


Parameter Settings

5.4 LDAP Connection string

Supply the connection string to your LDAP server

i.e. LDAP://DomainNm/ou=AU,dc=company,dc=com

5.5 LDAP fields

Provide a comma separated list of LDAP field names that you wish returned

i.e. cn,name,displayName,mail

5.6 LDAP Refresh

When a lookup is actuated on a request form, if the LDAP data hasn’t been refreshed for a period greater the value set in parameter 5.6 then the LDAP data will be refreshed.

External Settings

RefTracker uses a TSQL Stored Procedure to query LDAP. For SQL to execute this SP ‘OLE Automation in Advanced SQL Configuration’ needs to be turned on. The SQL user needs to be sa.