HOWTO: Connecting to a Lotus Notes / Domino application using ODBC and Linked Server

Back to Forums

Post Closed

Pedro Oliveira

Staff

Solved

HOWTO: Connecting to a Lotus Notes / Domino application using ODBC and Linked Server

Question

OS Certified

Database

How-to

👋 This post has been updated with new information on June 30, 2022.

As IBM sold the Notes and Domino assets to HCL in 2019, the previous method (we’ve kept the details below) of using an ODBC Connector to Domino is not available from IBM or HCL.

The new way is to enable REST and Domino Access Services, available since Domino 9.0, to pull data from NSF databases and expose the data as an entity within OutSystems.

Our partner T4S has built a Forge component (with documentation!) to allow any OutSystems developer to use Domino as a data source for an OutSystems application. This could be for using the Domino data in another application or could be for migration purposes.

An overview of what T4S has created is here

Find all the documentation and the connector here

An example of an OutSystems application that connects to Domino is here

A detailed webinar/walkthrough of the components is here.

<The below method is no longer supported or available>

Recently I've come across a few customers that were looking into ways to integrate new OutSystems applications with existing data from legacy Lotus Notes / Domino applications that were still being used.

Two options came up as the most promising ones

Using Interop to orchestrate the Lotus Notes Client or...
Using ODBC to expose the underlying tables of a Domino application.

This How To focuses on the second option, specifically abstracting the Platform from the ODBC connection details by creating a Linked Server in MS SQL Server (an alternative here would be to perform the ODBC work inside an extension).
I've researched a number of instructions available on the web and I've tested the steps to created a linked server but none of tutorials we I found seemed explain well enough what you need to do. Below you can find the steps I've learned through these experiments.

I've actually tested them from start to finish in clean environment to make sure they worked just fine.

Note that these steps are specifically for setting up the connection for a 64bit SQL Server. If you're using 32bit the steps will be different. All the steps below are to be performed on the machine where the Database server is running.

1. Download and install Lotus Domino Server

Go to https://www.ibm.com/developerworks/downloads/ls/lsndad/ and choose Download Trial
You'll have to login with an IBM login. Create one if you don't have it.
In the next screen choose "IBM Domino Enterprise Server V9.0.1 Social Edition Multiplatform English Trial eAssembly" and click continue
After confirming the legal stuff, you'll get to a screen where you should download the file "DOMI_SRV_9.01WIN_64_EN_FULL_-TRIAL.exe (935 MB)"
Install it. You can use the defaults for everything. Choose to configure it as a "Domino Utility Server" when asked.

2. Configure Lotus Domino Server

Even though you will not actually use the server. You need to configure it as it it was a real server.
Double click "IBM Domino Server" on your desktop and follow the wizard (just click next until the last step and you'll be fine). You'll just need set some passwords and you can use the defaults for everything else.
Disable the Lotus Domino Services to make sure they don't start when you restart the machine (you really need to this, otherwise the odbc connection will not work)

3. Download and install NotesSQL ODBC drivers

Go to https://www14.software.ibm.com/webapp/download/nochargesearch.jsp?cat=&q0=&pf=&k=ALL&pn=&pid=&rs=&S_TACT=104CBW71&status=Active&S_CMP=&b=&sr=1&q=IBM+ODBC+Driver+for+Notes%2FDomino&ibm-search.x=0&ibm-search.y=0
Click the only option that shows on the page and, in the following page, click Continue
Here you may have to login and confirm the legal stuff again.
Download the file "ODBC_ND_9.0_W64_EN.zip (97 MB)"
Install it. You can use all defaults.

4. Configure authentication for NotesSQL

Open the "NotesSQL Authentication List Manager" by opening Windows Explorer in the NotesSQL installation folder and double clicking Nsql_ALM.exe
Fill in the "Path to NOTES.INI..." as in the screenshot below, click "Display list" and then, "Add user..."
Set the "Location of Notes ID" as set below and click OK
Click "Save List" and then "Close"

5. Change your system Path variable

Go to the "Control Panel"
Open "System"
Click "Advanced system settings" on the left
Click "Environment Variables"
In "System variables" double click "Path"
Add ";C:\Progra~1\IBM\Domino\data; C:\Progra~1\IBM\Domino;C:\ NotesSQL" to the end of the Value and click OK

6. Configure the ODBC data source pointing to a local file or to a server

Open to ODBC Data Source Administrator
Go to the "System DSN" tab and click "Add..."
Select "Lotus Notes SQL Driver (*.nsf)" and click Finish
Fill in details as seen in the screen below (in this scenario I'm connecting to a local file on the same location as the DB server)
Click OK and close the ODBC Data Source Administrator

7. Configure the Linked Server in MS SQL Server

Open SQL Server Management Studio and connect to your Database
Expand "Server Objects" and right click "Linked Servers"
Select "New Linked Server" and fill in the form as seen in the screenshot below
Click OK and your Linked Server is ready!

8. Test it

Create a new query window and test it by using, as an example SELECT * from openquery(NOTES, 'select * from Person') and you should get some nice results

9. Create Views to allow introspection with Integration Studio

You may create views by using, for example CREATE VIEW LN_Person AS SELECT * from openquery(NOTES, 'select * from Person')
This will allow you to then create introspect and create a connection using Integration Studio

One more thing... For the OutSystems applications to be able to access the data of the linked server in runtime you need to change the permissions of the OSRuntime database user. This user is the one used to access data by the running applications.

I haven't been able to figure out the exact permissions needed but, for a proof of concept phase, you can just give this user sysadmin permission and it will work fine.

Hope this helps someone out there in the community!

PS: Note to the SYS ADMINS in this forum - what would be the best set of permissions to set for the OSRuntime user in this context?
PS2: You can find in attachment the sample Lotus application I'm using in this tutorial

names.zip

17 Nov 2014

Pedro Oliveira

Staff

Solution

Hi Peter,

Actually if I recall correctly you can use the exact same mechanism to connect to remote databases.

The key difference should be in step 6 to configure a remote datasource instead of a local file.

Cheers,

Pedro

See solution in context

26 Jan 2017

Fernando Matos

Pedro,
This is a very useful tip.
As an alternative to linked servers and for option 1 I also have an example (Platform 9 only).
This sample allows you to understand "How to connect programmatically to a Lotus Note DB file with the Outsystems Platform".

Instructions:

- Install Lotus Notes Client (ex: NOTES_9.0.1_WIN_EN_EVALUATION) on OutSystems Platform Server and test it by opening a NSF file (installation folder ....\IBM\Notes\Data includes several NSF files you can use).

- Publish the application (LotusNotesInteropSample) attached into the OutSystems Platform (V9) server

- Go to MS IIS OutSystems Platform Server and create a 32 bit Application pool and move the LotusNotesInteropSample application to this application pool. When you create the 32 bit application pool you must set its pipeline mode to "Classic".

- Test the application by inserting the input parameters (NSF file path - make sure access permissions are ok, View name and View Columns to read). You can test it using DB file names.nsf (it comes with lotus client), using View My Contacts (insert some contacts first using Lotus Client) and columns like FullName, Address, PhoneNumber...

I hope this code attached is also a good help for you.

Cheers,
FM

LotusNotesInteropSample-1.0.0 (2).oap

17 Nov 2014

Peter Tydlacka

Hi Pedro,

do you know about solution how to connect to domino database that is not local?

Is there any way how to use this solution to connect non local database?

Thanks

25 Jan 2017

Pedro Oliveira

Staff

Solution

Hi Peter,

Actually if I recall correctly you can use the exact same mechanism to connect to remote databases.

The key difference should be in step 6 to configure a remote datasource instead of a local file.

Cheers,

Pedro

26 Jan 2017

mahmoud awny

Hello Pedro,

Thanks for this useful article. Now I have nsf file on my local machine and lotus notes. I installed API notessql, SQL server and management studio on my local machine. when I configured ODBC administrator for pointing to nsf file, it crashed and the below error appear :

Problem signature:
Problem Event Name: BEX
Application Name: odbcad32.exe
Application Version: 6.1.7600.16385
Application Timestamp: 4a5bcd4c
Fault Module Name: NLNVP.DLL_unloaded
Fault Module Version: 0.0.0.0
Fault Module Timestamp: 57d36c1f
Exception Offset: 51b82f4f
Exception Code: c0000005
Exception Data: 00000008
OS Version: 6.1.7601.2.1.0.256.4
Locale ID: 1033
Additional Information 1: 0a9e
Additional Information 2: 0a9e372d3b4ad19135b953a78882e789
Additional Information 3: 0a9e
Additional Information 4: 0a9e372d3b4ad19135b953a78882e789

Can you assist me in solving this issue?

Thanks

14 Jul 2017

Pedro Moita

Hi all,

Is it possible to implement option 2 (Linked Server) on a cloud environment (enterprise, not the personal) ?

Thanks in advance,

Pedro Moita

18 May 2018

Pedro Oliveira

Staff

Hi Pedro,

Option 2 should be possible but does imply an additional redirection as you cannot manipulate the database which is managed by OutSystems.

The steps should be something in the line of

Install a dedicated DB Server somewhere - ideally either in the same data center as the OutSystems servers or near the Lotus Notes datasource to minimize latency
Perform the linked server setup described above to connect that database to Lotus Notes
Configure in the Service Center of your cloud environment the DB Server above as an external database and consume the views from Integration Studio

Cheers,

Pedro

18 May 2018

Pedro Moita

That should do it!

Thanks Pedro!

18 May 2018

Jesse De La Fuente

Pedro,

Great piece of information...!

I have worked through this solution on my end and have everything in place to extract data in MS Excel using a 32 bit ODBC driver as a bridge.

I have a requirement to pull some legacy data from Lotus Notes and at the moment I am working through a 32 bit Lotus Notes Client.

From my local machine I have been able to read NSF file using 32bit Lotus Notes ODBC driver.

I have been unsuccessful to link the 32bit ODBC driver reference from SSMS based on the working 32bit ODBC references or the sample NSF file you provided.

Can you share any information on the 32bit side and if it is possible.

Many thanks in advance.

18 Oct 2018