- 1 Introduction
- 2 Configure SSL Support in MongoDB Server
- 3 Using SSL option in SSIS MongoDB Connection
MongoDB comes with SSL support which can secure your end to end communication. However setting up MongoDB SSL Connection may require some configuration on both sides (i.e. Server and Client side). In this post our goal is to secure your MongoDB Integration in SSIS.
Now lets look at steps to configure SSL in MongoDB.
Configure SSL Support in MongoDB Server
To configure MongoDB for SSL connectivity very first step is generate self signed certificate in *.pem format (This must be stored on server)
Download and Install OpenSSL
First step is to download and Install OpenSSL. Assuming you are running 64Bit Windows OS you can get Lite version of 64 Bit OpenSSL (around 3MB). If you install it using default settings then all binaries will be installed under C:\OpenSSL-Win64\bin\
Create Self Signed Certificate and *.pem file using OpenSSL
If you don’t have trust issue and you want to use self signed certificate rather than buying from 3rd party SSL Certificate Authority then you can create self signed certificate using OpenSSL Command line as below. Now lets look at how to create private key, certificate file and then finally *.pem file which is the only required file by MongoDB.
PEM file is the most common file format (container file format) which includes Certificate and Private Key in a single file.
Lets look at how to create PEM file using OpenSSL.
- First run below command. When prompted enter “passphrase” .. (in our case enter “test”).
1openssl genrsa -des3 -out server.key 2048
- Now run below command to generate private key (*.key file)
1openssl rsa -in server.key -out server.key
- After private key is created run below command to create csr file (i.e. “Certificate Signing Request”). This file is needed to generate actual certificate (Self Signed or Signed by 3rd Party such as Veritas, RapidSSL). If you omit -sub argument from below then it may ask you few questions (e.g company name, department etc) but for testing purpose lets make it easy 🙂
To enter full details or just provide CN=xxxx
123openssl req -sha256 -new -key server.key -out server.csr -subj "/C=US/ST=Georgia/L=Atlanta/O=ZappySys LLC/OU=Org/Efirstname.lastname@example.org/CN=localhost"-- or --openssl req -sha256 -new -key server.key -out server.csr -subj "/CN=localhost"
- Now lets run below command to generate certificate file (*.crt). We are setting expiration to 365 days you can adjust as per your need.
1openssl x509 -req -sha256 -days 365 -in server.csr -signkey server.key -out server.crt
- Now at last step is to combine certificate (*.crt) and private key (*.key) files and generate pem file using below command. It will generate desired *.pem file format which can be used by MongoDB server (In Unix OS you can use cat command rather than type)
1type server.crt server.key > cert.pem
Once done your cert.pem file will look like below (Open and view in notepad)
-----BEGIN RSA PRIVATE KEY-----
-----END RSA PRIVATE KEY-----
Edit MongoDB Server config file (v3.0 or higher)
Now once we have *.pem file we can use it to launch MongoDB server instance. Before we do that one more step needed. We have to specify few parameters in mongodb config file.
- Navigate to MongoDb installation directory (where mongod.exe found)
- Create empty text file call it mongo-config.txt and enter following text (assuming cer.pem, mongo-config.txt and all mongod.exe are in the same folder). Password was test when we created pem file if you used different one then change below.
12345678net:bindIp: localhostssl:mode: requireSSLPEMKeyFile: cert.pemPEMKeyPassword: testallowInvalidCertificates: trueallowInvalidHostnames: true
Start MongoDB Server using Config file
Once you create config file. We can start Mongodb instance using below command line to use new config file which has SSL mode enabled. That’s it your server is now accepting Secure connections using SSL.
mongod -f "mongod-config.txt"
Using SSL option in SSIS MongoDB Connection
Now lets look at steps to secure MongoDB connection in SSIS.
Before we look into Step-By-Step section to secure MongoDB Connection in SSIS let’s make sure you met following requirements.
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- ZappySys SSIS PowerPack installed. Click on the link to download FREE trial.
- You have basic familiarity with MongoDB.
Using SSL in SSIS MongoDB Connection Manager
To enable SSL in SSIS open MongoDB connection manager and go to SSL Tab. Check Enable SSL Option like below. Once you do this and if your MongoDB server is properly configured to use SSL then your connection Test will be green and any traffic between you and your MongoDB server will be encrypted using SSL.
Using X509 Client Authentication in SSIS MongoDB Connection Manager
Now lets look at how to authenticate using X509 Client Certificate (X509) method. For this method you must use PFX file format for certificate (do not use PEM format). To create PFX file from PEM we created earlier you can run below command. When prompted use the same password you used before to generate private key.
openssl pkcs12 -export -out cert.pfx -inkey server.key -in server.crt
To learn more about full process of how to configure MongoDB Server to use Client X509 Certificate Check this Article.
Let’s look at steps to configure SSIS MongoDB Connection manager to use X509 Client Certificate Mode.
- Open ZappySys MongoDB Connection Manager UI
- Enter User ID which you created in $external database (This UserID usually matches Subject of your Certificate). For example in our case UserID may appear like below. Leave password blank because its not used in x509 method.
- Now go to SSL Tab. Check SSL Option, You can check Ignore SSL Validation error (If its self signed certificate)
- Select PFX file path and enter certificate password if needed. If you type Path then Subject Line appears automatically (You can use it as UserID field if you were unsure in Step#1 however we still recommend to make sure UserID is exact match based on original name your System Admin created in $external DB)
- Now go to Advanced Tab and enter Below Option. By default MongoDB Connection Manager uses userid/password mode. To use X509 Auth mode you must set option like below on the Advanced Options table.
- Click Test connection to make sure its successful.