Pedro Oliveira
Staff
Rank: #13
9740Views
8Comments
Solved
HOWTO: Connecting to a Lotus Notes / Domino application using ODBC and Linked Server
Question
Recently I've come accross 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.
    Inline image 6
  • 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)
    Inline image 7
     
     
     

    Inline image 8
    Inline image 9
     
     
     
3. Download and install NotesSQL ODBC drivers
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 
    Inline image 3
     
     
     
  • Fill in the "Path to NOTES.INI..." as in the screenshot below, click "Display list" and then, "Add user..."
    Inline image 4
     
     
  • Set the "Location of Notes ID" as set below and click OK 
    Inline image 5
     
     
     
  • 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
    Inline image 1
     
     
     
  • Go to the "System DSN" tab and click "Add..."
     Inline image 2
     
     
     
  • 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)
    Inline image 10
     
     
     
  • 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"
    Inline image 11
     
     
     
  • Select "New Linked Server" and fill in the form as seen in the screenshot below
    Inline image 12
     
     
  • 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
    Inline image 13
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')
     
    Inline image 15
  • 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.
 
Inline image 1

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

4
0
on 2014-11-17
Copy link to comment
Pedro Oliveira
Staff
Rank: #13
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
0
0
on 2017-01-26
Copy link to comment
Fernando Matos
Rank: #0
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

5
0
on 2014-11-17
Copy link to comment
Peter Tydlacka
Rank: #82216

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

0
0
on 2017-01-25
Copy link to comment
Pedro Oliveira
Staff
Rank: #13
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

0
0
on 2017-01-26
Copy link to comment
mahmoud awny
Rank: #82216

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


 

0
0
on 2017-07-14
Copy link to comment
Pedro Moita
Rank: #923

Hi all,


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


Thanks in advance,

Pedro Moita

0
0
on 2018-05-18
Copy link to comment
Pedro Oliveira
Staff
Rank: #13

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

  1. 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
  2. Perform the linked server setup described above to connect that database to Lotus Notes
  3. 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

0
0
on 2018-05-18
Copy link to comment
Pedro Moita
Rank: #923

That should do it!

Thanks Pedro!

0
0
on 2018-05-18
Copy link to comment
Jesse De La Fuente
Rank: #82216

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.

0
0
on 2018-10-18
Copy link to comment