背景
由于mongoDB 官方未提供JDBC驱动包,在使用 davinci 连接mongoDB 时遇到了问题;
问题
- 没有可用的驱动包;
- 使用 unityjdbc 进行连接时, 连接成功但是读取库表信息有误, 无法使用;
- 使用mongoDB官方提供的连接器连接有安全认证的数据库,再使用工具连接 连接器 对外的服务时报错;
2022-10-20T13:53:59.290+0800 E NETWORK [conn2] handshake error: ERROR 1759 (HY000): ssl is required when using cleartext authentication
2022-10-20T13:53:59.290+0800 I NETWORK [conn2] end connection 127.0.0.1:57270 (0 connections now open)
解决方案
- 使用官方提供的 bi-connector(专门为BI工具提供的连接器)对数据库进行代理;
- 报表工具通过 bi-connector暴露出来的地址进行连接(连接方式和SQL语法同Mysql);
mongoDB官方文档地址
https://www.mongodb.com/docs/bi-connector/current/reference/mongosqld/
重点参考资料: https://www.jianshu.com/p/ba656bf5df26
详细操作步骤
- 下载安装对应版本 bi-connector , 地址 https://www.mongodb.com/try/download/bi-connector , 默认安装路径在 C:\Program Files\MongoDB\Connector for BI[版本号]
- 如果数据库在本机且没有设置权限认证,直接启动 bin 目录下的 mongosqld.exe , 使用 127.0.0.1:3307 即可直接访问;如果有认证请继续往下看;
- 下载安装 openssl, 这个链接是win版本的, 其他系统可自行百度 http://slproweb.com/download/Win64OpenSSL_Light-3_0_5.exe
- 配置openssl 环境变量
新建变量(默认路径)
OPENSSL_HOME
C:\Program Files\OpenSSL-Win64\bin
加入到Path
%OPENSSL_HOME%
- 在 bi-connector 的bin 目录下执行 以下命令
1.创建证书
openssl req -nodes -newkey rsa:2048 -keyout mongodb-bi.key -out mongodb-bi.crt -x509 -days 365 -subj "/C=CN/ST=test/L=test/O=test Security/OU=IT Department/CN=jinggon.com"
2. 生成pem
copy .\mongodb-bi.key + .\mongodb-bi.crt mongodb-bi.pem
3. 启动 mongosqld 服务
mongosqld.exe --addr 192.168.101.234 --mongo-uri "mongodb://xxxx:27017/?connect=direct" --auth --mongo-username jgzn --mongo-password jg123456 --sslMode allowSSL --sslPEMKeyFile .\mongodb-bi.pem --mongo-authenticationSource admin
mongosqld.exe --addr 172.16.100.38 --mongo-uri "mongodb://xxxx:27017/?connect=direct" --auth --mongo-username [数据库username] --mongo-password [数据库password] --sslMode allowSSL --sslPEMKeyFile .\mongodb-bi.pem --mongo-authenticationSource admin
参数数释:
--addr 192.168.101.234 对外暴露ip
--mongo-uri :MongoDB数据库地址
--mongo-username:MongoDB用户名
--mongo-password:MongoDB密码
--mongo-authenticationSource:身份验证数据库名称
--sslPEMKeyFile:ssl私钥绝对路径
目录应该是这样的
启动效果
5.1 也可以使用配置文件启动,示例如下, 带配置文件启动命令为 mongosqld.exe --config [mongosqld-config.conf文件路径]
# mongosqld-config.conf
net:
bindIp: "192.168.101.234" # To bind to multiple IP addresses, enter a list of comma separated values.
port: 3308
# unixDomainSocket:
# enabled: false
# pathPrefix: "/var"
# filePermissions: "0600"
ssl:
mode: "requireSSL"
# allowInvalidCertificates: false
PEMKeyFile: 'C:\Program Files\MongoDB\Connector for BI\2\bin\mongodb-bi.pem' #这里要写成绝对路径,负责服务启动的时候会连不上
# CAFile: 'C:\opt\certs\mdbca.crt'
# PEMKeyPassword: <string>
# CAFile: <string>
# minimumTLSVersion: TLS1_1
mongodb:
# versionCompatibility: <string>
net:
uri: "mongodb://xxxxx:27017" # https://docs.mongodb.com/manual/reference/connection-string/#mongodb-uri
ssl:
enabled: false
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#mongodb-tls-ssl-options
# allowInvalidCertificates: false
# allowInvalidHostnames: false
# PEMKeyFile: <string>
# PEMKeyPassword: <string>
# CAFile: <string>
# CRLFile: <string>
# FIPSMode: false
# minimumTLSVersion: TLSv1_1
auth:
username: "xxx"
password: "xxx"
source: "admin" # This is the name of the database to authenticate against.
# mechanism: SCRAM-SHA-1
# gssapiServiceName: mongodb
# Security options - configure mongosqld's authentication (disabled by default).
## Enable security options if your MongoDB cluster requires authentication.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#security-options
security:
enabled: true
# defaultMechanism: "SCRAM-SHA-1"
# defaultSource: "admin"
# gssapi:
# hostname: <string>
# serviceName: "mongosql"
## Logging options
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#logging-options
systemLog:
## The path to the file where log output will be written to.
## Defaults to stderr.
path: 'C:\Program Files\MongoDB\Connector for BI\2\logs\log.txt' # 服务启动的时候这个要配置
quiet: false
## 0|1|2 - Verbosity of the log output, this is overridden if `quiet` is true.
verbosity: 1
# logAppend: false
logRotate: "rename" # "rename"|"reopen"
## Schema options
## These configuration options define how the mongosqld should sample your MongoDB
## data so that it can be used by the relational application.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#data-sampling-options
schema:
## If you've generated a DRDL schema file using mongodrdl, you can supply the
## path for mongosqld to use that schema DRDL file.
# path: <string>
# maxVarcharLength: <integer>
## Use the `refreshIntervalSecs` option to specify an interval in seconds for
## mongosqld to update its schema, either by resampling or by re-reading from
## the schema source. The default value for this option is 0, which means that
## mongosqld does not automatically refresh the schema after it is
## initialized.
refreshIntervalSecs: 0
stored:
mode: "auto" # "auto"|"custom"
source: "mongosqld_data" # the database where schemas are stored in stored-schema modes
name: "admin" # the named schema to read/write to in stored-schema modes
sample:
size: 1000 # The amount of random documents we sample from each collection.
namespaces: ["*.*"]
# prejoin: false
# uuidSubtype3Encoding: "old" # <[old|csharp|java]>
## Process management options
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#process-management-options
processManagement:
service:
name: "mongosql"
displayName: "MongoSQL Service"
description: "MongoSQL accesses MongoDB data with SQL"
## Runtime options
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#runtime-options
# runtime:
# memory:
# ## A value of `0` indicates there is no enforced maximum.
# maxPerStage: 0
# maxPerServer: 0
# maxPerConnection: 0
5.3 注册为windows 服务方式可参考
5.3.2 服务方式启动需要配置日志路径
5.3.3 服务方式启动需要配置pem文件为绝对路径
"C:\Program Files\MongoDB\Connector for BI\2.4\bin\mongosqld.exe" install --config "<pathToConfigFile>\mongosqld.conf"
net start mongosql
https://www.mongodb.com/docs/bi-connector/current/launch/#std-label-msqld-as-a-system-service
- 在报表工具中新建 数据源
6.1 数据库类型选择 mysql;
6.2 填写有数据库权限的账号信息;
6.3 连接串后要跟?useSsl=true示例: jdbc:mysql://172.16.100.38:3307?useSsl=true