search

asifjalil / cli

Go DB2 Driver
MIT License
22 stars 3 forks source link

Issues Setting up an SSL Connection #11

Closed RPuffer closed 5 years ago

RPuffer commented 5 years ago

I'm getting some unhelpful errors when trying to connect to a DB2 Database with an SSL connection. My connection string structure is as follows:

connStr := fmt.Sprintf("DATABASE=%s; CurrentSchema=%s; HOSTNAME=%s; PORT=%d; PROTOCOL=%s; UID=%s; PWD=%s; Security=SSL; SSLServerCertificate=%s",
        database, schema, host, port, protocol, username, password, certPath)

And the error I'm seeing is:

{
    "sqlCode": "-30081",
    "err": "database/sql/driver: [IBM][CLI Driver] SQL10007N Message \"0\" could not be retrieved.  Reason code: \"3\".\n",
    "msg": "error occurred during processing",
    "sqlState": "08001"
}

I'm wondering if there is any additional configuration steps required, or if you have gotten SSL to work? Maybe my connection string parameters are incorrect? I have tried using an absolute path as well as a relative path for the SSLServerCertificate value, but no luck either way. I have also been able to connect to the DB2 Database with SSL using Node.js and the ibm_db package with the same connection string so I'm confused why it isn't working with this package. Any insight would be greatly appreciated.

asifjalil commented 5 years ago

@RPuffer I haven't tried my driver with SSL. But I will see what I can dig up. Thanks for reporting the issue.

RPuffer commented 5 years ago

@asifjalil No problem, and I appreciate the quick response! I will continue to investigate as well, but yeah let me know if you find anything

asifjalil commented 5 years ago

@RPuffer I had to configure the DB2 Instance to use SSL first. To do that I followed the steps in this article from ToadWorld. Here are my steps for creating a self-signed certificate (this is on Linux): 

$ cd ~
$ mkdir cert
$ cd cert
$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -keydb -create -db "server.kdb" -pw "Passw0rd"  -stash
$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -create -db "server.kdb" -pw "Passw0rd" -label "mylabel" -dn "CN=testcompany" -size 2048 -sigalg SHA256_WITH_RSA
$ /opt/ibm/db2/V11.1/gskit/bin/gsk8capicmd_64 -cert -extract -db "server.kdb" -pw "Passw0rd" -label "mylabel" -target "server.arm" -format ascii

I then added a new port to /etc/services file for the SSL connection:

$ sudo vi /etc/services # add db2cs_asif 50001/tcp

Then I configured the DB2 Instance to use SSL and the certificate. Please note that this requires DB2 SYSADM privilege because you will need to update the instance configuration and then recycle (DB2 Instance name in this case is called asif):

$ db2 update dbm cfg using SSL_SVR_KEYDB /home/asif/certs/server.kdb
$ db2 update dbm cfg using SSL_SVR_STASH /home/asif/certs/server.sth
$ db2 update dbm cfg using SSL_SVR_LABEL mylabel
$ db2 update dbm cfg using SSL_SVCENAME db2cs_asif
$ db2set -i asif DB2COMM=TCPIP,SSL
$ db2stop
$ db2start

Once DB2 has been configured, you can use this connection string in Go to connect to the database sample that is under DB2 Instance asif:

        var (
                database = "sample"
                schema   = "asif"
                host     = "localhost"
                port     = 50001
                protocol = "tcpip"
                username = "asif"
                password = "****"
                certFile = "/home/asif/certs/server.arm"
        )

        connStr := fmt.Sprintf(`DATABASE=%s; CurrentSchema=%s; HOSTNAME=%s; PORT=%d;
        PROTOCOL=%s; UID=%s; PWD=%s; Security=SSL;SSLServerCertificate=%s`,
                database, schema, host, port, protocol, username, password, certFile)

Please note that for the certificate file, I am using the .arm file that I created in the certificate step. Hope that helps.

RPuffer commented 5 years ago

@asifjalil Interesting, well my issue must be something with our DB2 and/or firewall setup... or I'm using the wrong certificate. Thank you for investigating! I'm working with our DB Admins to diagnose the problem so this information is very useful.

Then, as it seems it is not an issue related to your driver I will go ahead and close this issue, thanks again for your help!

RPuffer commented 5 years ago

@asifjalil Actually, just want to make sure we're on the same page. When setting up you SSL connection, are you successfully able to use a Ping or Query functions on the *DB?

I find that my issue arrises only when I try to ping the Database, or execute a query. But, when I use sql.Open(...) that seems to work just fine.

asifjalil commented 5 years ago

@RPuffer I was able to run the following: 

        connStr := fmt.Sprintf(`DATABASE=%s; CurrentSchema=%s; HOSTNAME=%s; PORT=%d;
        PROTOCOL=%s; UID=%s; PWD=%s; Security=SSL;SSLServerCertificate=%s`,
                database, schema, host, port, protocol, username, password, certFile)

        qry := "SELECT 1 FROM syscat.tables where tabschema='ASIF' Fetch first 1 row only"

        db, err := sql.Open("cli", connStr)
        if err != nil {
                log.Fatal(err)
        }

        defer db.Close()

        log.Println("Testing QueryRow.Scan()")
        log.Println(strings.Repeat("#", 40))
        err = db.QueryRow(qry).Scan(&val)
        switch {
        case err == sql.ErrNoRows:
                fmt.Printf("%q: empty resultset\n", qry)
        case err != nil:
                log.Fatal(err)
        default:
                fmt.Printf("%q: got %d\n", qry, val)
        }

        log.Println("Testing Query")
        log.Println(strings.Repeat("#", 40))
        rows, err := db.Query(qry)
        switch {
        case err == sql.ErrNoRows:
                fmt.Printf("%q: empty resultset\n", qry)
        case err != nil:
                log.Fatalf("%q: %v\n", qry, err)
        default:
                for rows.Next() {
                        if err := rows.Scan(&val); err != nil {
                                log.Fatalf("%q: rows.Scan failed: %v\n", qry, err)
                        }
                        fmt.Printf("%q: got %d\n", qry, val)
                }
        }

Result:

2019/04/29 15:27:26 Testing QueryRow.Scan()
2019/04/29 15:27:26 ########################################
"SELECT 1 FROM syscat.tables where tabschema='ASIF' fetch first 1 row only": got 1
2019/04/29 15:27:27 Testing Query
2019/04/29 15:27:27 ########################################
"SELECT 1 FROM syscat.tables where tabschema='ASIF' fetch first 1 row only": got 1

I am using local client and database. That is, there is no firewall.

RPuffer commented 5 years ago

@asifjalil hmm yeah that's pretty much exactly what I'm doing.. I have no idea why it's not working with Go. I've tested the SSL DB connection with a Node.js app as well and that seems to have no issues. Must be a firewall or network issue I guess. Thanks again for your help.

asifjalil commented 5 years ago

@RPuffer Hmm. It is surprising that Node.js app works but this Go DB2 driver don't because both underneath uses the same ODBC/CLI driver to interact with DB2. Maybe the issue is something along this line.

RPuffer commented 5 years ago

@asifjalil yeah, it seems odd that Node.js works.. and I have some more experienced DB admins looking into potential configuration issues. I did find an interesting case doing some more testing with the Node.js side.

So, in the Node app, when i remove the Security=SSL; SSLServerCertificate=%s parameters from the connection string, I get the same error as I am seeing in Go. This makes me wonder if maybe, in the Go app, those parameters in the connection string are not getting applied for some reason?

If you remove the Security=SSL;SSLServerCertificate=%s params from your SSL example, are you still able to Open and Query the DB2 instance you set up?

asifjalil commented 5 years ago

@RPuffer If I remove Security=SSL;SSLServerCertificate=%s, then I get the following error: 

2019/04/29 19:51:12 Testing QueryRow.Scan()
2019/04/29 19:51:12 ########################################
2019/04/29 19:51:12 database/sql/driver: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "127.0.0.1".  Communication function detecting the error: "recv".  Protocol specific error code(s): "104", "*", "0".  SQLSTATE=08001

According to SQL30081N, the error means the following: 

*  If "<protocol>" = TCP/IP, "<function>" = recv,
   "<rc1>" = ECONNRESET/WSAECONNRESET, AIX (73), Windows
   (10054), linux (104), SUN (131), HP (232), the connection was reset
   by the remote side executing a "hard" or "abortive" close. Possible
   causes:

   *  The connection might have been closed by the remote gateway or
      server at the TCP/IP level (eg. firewall problem, power failure,
      network failure).

   *  Client side connection pooling is enabled and is not handling
      connection failures. Code the application to retry a connection if
      a failure is received when connecting to the database and
      connection pooling is enabled.

   *  Might have been caused by a thread timeout on the host machine.
      Check the system log on the host for the presence of the IDTHTOIN
      message. Adjust the setting if applicable. If this cannot be
      adjusted, either disable connection pooling on the gateway, or
      ensure all objects are properly closed (eg. cursors with hold when
      the application completes its operation).

   *  The database agent at the server was forced off by the database
      administrator.

   *  The database agent at the server was terminated due to an abnormal
      termination of a key database manager process.

Not easy to understand but db2diag.log shows this: 

2019-04-29-19.51.12.672923+000 I7194435E493          LEVEL: Error
PID     : 6497                 TID : 140484932527872 PROC : db2sysc
INSTANCE: asif                 NODE : 000            DB   : SAMPLE
APPHDL  : 0-344
HOSTNAME: flux
EDUID   : 52                   EDUNAME: db2agent ()
FUNCTION: DB2 UDB, common communication, sqlccMapSSLErrorToDB2Error, probe:30
MESSAGE : DIA3604E The SSL function "gsk_secure_soc_init" failed with the
          return code "410" in "sqlccSSLSocketSetup".

That makes more sense because I tried to connect to DB2's SSL port without using SSL option.

Question: the DB2 instance you are trying to connect to does it have this set?

$ db2set -all [i] DB2COMM=TCPIP,SSL

RPuffer commented 5 years ago

@asifjalil interesting, so yeah that's the error I'm getting - I will have to double check with our admins if the DB2 instance contains that set (as i don't have personal access, which is making troubleshooting a lot more difficult haha).

Well.. I just had a successful local run. I've been dealing with a security issue where our antivirus has been blocking any attempts to run local Go binaries so I've been spinning up a Docker container every-time I want to test the application. The antivirus will eventually allow for execution of the binary after 30min - 1 hour as long as it's cleared as 'not malware'. But since this process takes forever I haven't actually run the binary outside of a Docker container in a while.

So, now, the issue seems to be with the Container I'm spinning up. Do you know if there's any ports specific to the DB communication I would need to expose? Or maybe the odbc-cli package I'm using in the Docker container is insufficient .

I'm pretty sure the Node.js package I'm using ibm_db installs some sort of DB2 CLI client as well so maybe that is why it just worked on my local machine?

I will do some more investigation tomorrow.

asifjalil commented 5 years ago

@RPuffer yes odbc-cli package could be the issue. I only tested this Go driver using DB2 CLI driver. Try the DB2 driver from here. Also, someone contributed this docker file for this driver. Maybe that will help.

RPuffer commented 5 years ago

Well, I got it working in the Docker image! It seemed to be a combination of referencing the contributed Docker file for configuration items I might have missed, and changing of the base image. I'm still not sure exactly what was going wrong before but it looks like I'm up and running for now. Thanks again for all your help!

EDIT After further review of my changes (and in case anyone else has issues in the future) - it appears that there was just a few extra configurations necessary on my docker image to make the odbc-cli package work correctly. Sharing my Dockerfile used (updates indicated with comments) in case anyone else runs into this issue.

FINAL EDIT Turns out our firewall team just messed up my request to open the SSL ports for the DB calls šŸ¤¦ā€ā™‚. They must have silently fixed it which is why I got the successful run randomly. Anyways, I've updated the Dockerfile below as a good example of how to use this cli package for a go-module project without needing the db2 driver tar file in the repository. Another benefit of this Dockerfile is it is very lightweight due to using the debian:slim-stretch base image. The odbc-cli download link is the same as used in the Node.js package ibm_db so it should be very reliable. Also included an example makefile to show the build env vars. @asifjalil feel free to use this as an example for a go-module project Dockerfile with a dynamic install of the odbc-cli driver.

Example Dockerfile

FROM golang:1.11-stretch AS builder

ENV DB2DIR="$HOME/sqllib"
WORKDIR /tmp/app

# IF root certs needed for go mod download
RUN curl \
    -o "/usr/share/ca-certificates/ā–ˆā–ˆā–ˆā–ˆā–ˆ" "ā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆ" \
    -o "/usr/share/ca-certificates/ā–ˆā–ˆā–ˆā–ˆā–ˆ" "ā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆ" \
    && echo "ā–ˆā–ˆā–ˆā–ˆā–ˆ" >> /etc/ca-certificates.conf \
    && echo "ā–ˆā–ˆā–ˆā–ˆā–ˆ" >> /etc/ca-certificates.conf
RUN update-ca-certificates

# Download Driver for compilation
RUN apt-get update \
    && apt-get install -y git gcc libxml2 \
    && curl -o "db2clidriver.tgz" "https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/linuxx64_odbc_cli.tar.gz" \
    && tar xf "db2clidriver.tgz" \
    && mv "clidriver" "${DB2DIR}" \
    && rm -r *.tgz \
    && ln -sv ${DB2DIR}/lib/* /usr/lib/ \
    && ln -sv ${DB2DIR}/include/* /usr/include/

# Download dependencies & copy app
COPY go.mod go.sum ./
RUN go mod download
COPY ./ ./

# See example makefile for build env vars
RUN make <project-name>

CMD ["./<project-name>"]

# Use barebones base image to restrict container size
FROM debian:stretch-slim AS runner

ENV DB2DIR="$HOME/sqllib"
RUN apt-get update \
    && apt-get install -y curl libxml2 \
    && curl -o "db2clidriver.tgz" "https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/linuxx64_odbc_cli.tar.gz" \
    && tar xf "db2clidriver.tgz" \
    && mv "clidriver" "${DB2DIR}" \
    && rm -r *.tgz \
    && ln -sv ${DB2DIR}/lib/* /usr/lib/ \
    && ln -sv ${DB2DIR}/include/* /usr/include/ \
    && mkdir -p /tmp/app/certs \
    && curl \
        -o "/tmp/app/certs/ā–ˆā–ˆā–ˆā–ˆā–ˆ" "ā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆ" \
        -o "/tmp/app/certs/ā–ˆā–ˆā–ˆā–ˆā–ˆ" "ā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆā–ˆ" \
    && apt-get purge -y curl \
    && apt-get autoremove -y

# Load binary from builder
COPY --from=builder /tmp/app/<project-name> "/tmp/app/<project-name>"

# Only copy necessary files from builder ex: config directories & SQL files
COPY --from=builder /tmp/app/config "/tmp/app/config"
COPY --from=builder /tmp/app/sql "/tmp/app/sql"

CMD ["/tmp/app/<project-name>"]

Example Makefile

DB2DIR ?= $(HOME)/sqllib
DYLD_LIBRARY_PATH ?= $(DB2DIR)/lib
GO_CFLAGS ?= -I$(DB2DIR)/include
GO_LDFLAGS ?= -L$(DB2DIR)/lib

coverage_data = coverage/data.out
coverage_webpage = coverage/index.html

build_env_vars = CGO_CFLAGS="$(GO_CFLAGS)" CGO_LDFLAGS="$(GO_LDFLAGS)" DYLD_LIBRARY_PATH="$(DYLD_LIBRARY_PATH)"

.PHONY: <project-name> fmt test test-cov test-html vet %.go

<project-name>: %.go
    $(build_env_vars) go build ./cmd/<project-name>/

test:
    $(build_env_vars) go test ./...

test-cov:
    $(build_env_vars) go test ./... -cover

$(coverage_data): %.go
    $(build_env_vars) go test ./... -coverprofile=$@

$(coverage_webpage): $(coverage_data)
    go tool cover -html=$< -o $@

test-html: $(coverage_webpage)