CockroachDB Using ODBC (Mac Setup)

ODBC with CockroachDB

The instructions here are for testing ODBC on a Mac that connects to a local CockroachDB cluster.

Install Postgres ODBC drivers

brew install unixodbc
brew install psqlodbc

Configure The DSN to Connect

The following command will tell you were the odbc configuration files are

odbcinst -j

Here are where the files are located:

unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/chriscasano/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Let's look at the odbcinst.ini file

$ cat /usr/local/etc/odbcinst.ini

Make sure your odbcinst.ini file looks like this:

[ODBC Drivers]
PostgreSQL Driver=Installed

[PostgreSQL Driver]
Driver=/usr/local/lib/psqlodbcw.so

Let's look at the odbc.ini file

cat /usr/local/etc/odbc.ini

Below is what the odbc.ini file should look like for both a secure and insecure connection to CockroachDB:

[CockroachInsecure]
Driver              = PostgreSQL Driver
Database            = defaultdb
Servername          = localhost
UserName            = chris
Password            = cockroach
Port                = 26257

[CockroachSecure]
Driver              =PostgreSQL Driver
Database            =defaultdb
Servername          =localhost
UserName            =root
Password            =
Port                =26257
Sslmode             =verify-full

Connect to a cluster that is insecure or secure but doesn't require TLS for SQL access (i.e. cockroach start --accept-sql-without-tls)

Here I used roachprod to create a local secure cluster but you can also create a secure single node cluster using the instruction here to a do secure cluster with the --accept-sql-without-tls option, or simply create an insecure cluster like this

roachprod create local -n 3
roachprod start local --skip-init --args="--accept-sql-without-tls" --secure
cockroach init --certs-dir=/Users/chriscasano/local/1/certs
cockroach sql --certs-dir=/Users/chriscasano/local/1/certs -e "create user chris password cockroach;"

isql -v CockroachInsecure

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Connect to a Secure Cluster

Create the cluster. Again, you can use the instructions [here](https://www.cockroachlabs.com/docs/v21.1/cockroach-start-single-node.html#start-a-single-node-cluster to create a secure single node cluster:

roachprod create local -n 3
roachprod start local --skip-init --secure
cockroach init --certs-dir=/Users/chriscasano/local/1/certs

This part is important and not well documented anywhere. To give the ODBC driver the cert paths, you use the following session variables:

export PGSSLCERT="/Users/chriscasano/local/1/certs/client.root.crt"
export PGSSLKEY="/Users/chriscasano/local/1/certs/client.root.key"
export PGSSLROOTCERT="/Users/chriscasano/local/1/certs/ca.crt"

Once the session variables are set, you should be able to connect.

isql -v CockroachSecure

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

view raw cockroach_odbc.md hosted with ❤ by GitHub