Because certificates are a pain in the a **
AWS Glue is the best-in-class method for Extract, Transform, and Load jobs in your data pipeline. To connect with on-prem data resources and secure the connection, there are varying requirements. Depending on the data store you're connecting to, you may need an adapter or connector to make the request.
https://docs.aws.amazon.com/glue/latest/dg/connection-properties.html#connection-properties-jdbc
Additionally, if the on-prem data source is owned by another team or organization, you will have to use SSL encryption and a certificate handshake to make your service trusted to the on-prem database.
https://docs.aws.amazon.com/glue/latest/dg/connection-properties.html#connection-properties-SSL
Securing the Connection
For our purposes, AWS Glue will connect to the on-prem SnowMirror (Service Now data) MSSQL database through a JDBC (Java Database Connectivity)
connection. In development we previously attached a self-signed cert on the JDBC side, and that worked just fine. As development progressed, and there was an outage when the on-prem SQL team changed server nodes, we decided to install their SQL-installed certificate instead.
There are nuances with these certificates and the AWS documentation is somewhat misleading. You can configure the reference to the certificate in the JDBC connection pane as either a url or an S3 bucket location. We chose the S3 bucket location. Here is how it's described in the AWS documentation:
Formatting a Custom JDBC certificate in S3
If you have a certificate that you are currently using for SSL
communication with your on-premises or cloud databases, you can use
that certificate for SSL connections to AWS Glue data sources or
targets. Enter an Amazon Simple Storage Service (Amazon S3) location
that contains a custom root certificate. AWS Glue uses this
certificate to establish an SSL connection to the database. AWS Glue
handles only X.509 certificates. The certificate must be DER-encoded
and supplied in base64 encoding PEM format. If this field is left
blank, the default certificate is used.
Be Aware:
At first glance, it looks as if AWS is expecting the certificate to be stored in S3 as a ".der" file. While it is true that it must originate in der encoded format, the end state of this cert should be a .pem file. You can google varying open SSL commands to ensure you start and end with the right formats. I have provided below some of the commands that I learned along the way.
Best Practices versus Quick Wins:
If you're starting with a CSR ("certificate signing request") you must handle the encoding from start to finish. I recommend that you ask the client's Certificate Authority to create the csr, but doing it yourself, on your own machine, is an option (see "Creating a self-signed certificate" below.)
If you are being given a certificate by another team, (the certificate that is installed on the db, for example,) you may need to only perform these 2 steps:
Step 1: If given a file in .cer format, you can change the ".cer"
file extension to ".der" Next step is to convert it to pem.
Step 2: run the following to convert the .der to a .pem file that can now be stored in S3.
openssl x509 -inform der -in Desktop/my-cert-name.der -out Desktop/my-cert-name-convert.pem
The best way is to have the client's certificate authority (for our client, it's the PKI team) create the csr, the root cert with private key, and issue and sign a new .cer or .der file for you. You are not doing 2 way SSL, you are just telling the on-prem database that you are a friendly requestor of the data, and that you are encrypting the data.
However, for development, you can create your own self-signed cert from
start to finish (from csr to final .pem format), and you may follow these
steps using open ssl. Make sure your computer's shell environment has
the openssl client installed.
Creating a self-signed certificate:
1) In Open SSL run this command in your shell to create the certificate signing
request":
openssl req -new -sha256 -nodes -newkey rsa:4096 -keyout
Desktop/my-cert-name.key -out Desktop/my-cert-name.csr
2) You will get a series of prompts:
Country Name (2 letter code):MyCountry
State or Province Name (full name):MyState
Locality Name (eg, city): MyCity
Organization Name (eg, company):My-org
Organizational Unit Name (eg, section):MYORGNAME
Common Name (eg, fully qualified host name):example.com
Email Address:MyTeam@organization.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password: <LEAVE THIS EMPTY AND HIT ENTER>
3) Create the Cert
openssl x509 -req -days 3650 -in Desktop/my-cert-name.csr -signkey
Desktop/my-cert-name.key -out Desktop/my-cert-name.crt
4) Convert crt to pem:
openssl x509 -in Desktop/my-cert-name.crt -out Desktop/my-cert-name.pem
-outform PEM
5) Convert pem to der:
openssl x509 -outform der -in Desktop/my-cert-name.pem -out
Desktop/my-cert-name.der
6) Now convert the DER to PEM again (the right format to be used with
a Glue connection):
openssl x509 -inform der -in Desktop/my-cert-name.der -out
Desktop/my-cert-name-convert.pem
Top comments (0)