Wells from SQL Server and Oracle

Ophiolite reads well tables from SQL Server and Oracle the way it reads them from PostgreSQL: an organisation adds the database once, each person signs in with their own database account, a project chooses a table, and the wells are imported or read at a recorded revision. Ophiolite only reads; it never writes to your database.

What is supported

	SQL Server	Oracle Database
Version checked	SQL Server 2022 (Developer, on Linux)	Oracle AI Database 26ai Free (release 23.26)
Sign-in	SQL Server sign-in: a user name and a password	A database user name and a password
Address	A server name or address and a fixed port (1433 by default)	A server name or address, a port (2484 by default) and a service name
Connection	Always encrypted (TLS); the certificate must name the server, by its alternative name	Always encrypted (TCPS); the certificate’s subject must equal CN=<server> or the subject name you state
A consistent read	The database must allow snapshot isolation	Every read runs in one read-only transaction
Not supported	Windows, Kerberos or Entra ID sign-in, named instances, unencrypted connections, writes	Wallets, TNS aliases, connect descriptors, SIDs, listener redirects, unencrypted connections, writes

Oracle connections check the certificate’s subject name; alternative names are not checked by the driver. Give the certificate’s subject name when the server is given by its address. A statement the server does not interrupt can run past the time limit.

A SQL Server database that does not allow snapshot isolation can be tested and its tables listed, but a table is not read from it: “This SQL Server database does not allow a consistent read. Ask its administrator to turn on snapshot isolation for it, or approve a view in a database that does.”

What each system keeps and loses, column by column (fraction digits of timestamps, trailing padding, empty strings in Oracle), is listed under connector support.

Add the database in the Workspace

1. On your organisation’s Connections page, choose Add a database (or Ask for a database when you may not add one; an owner decides).
2. Choose the Database type: PostgreSQL, SQL Server or Oracle. The port changes to the system’s usual one.
3. Give the Database server, the Port and the Database name (for Oracle, the Service name). For Oracle, give the Certificate subject name when the server is given by its address.
4. Sign in with your own database account. Ophiolite checks the server’s address against your organisation’s rule before anything is sent, and the certificate before your password is sent.
5. Test access lists the tables your account may read. In a project, choose the table of wells, map its columns (well identifier, name, x, y, and the coordinate system) and import them.

The same database can be approved by the deployment’s operator instead (ophiolite-ops configure connection-admin, with "kind": "sqlserver" or "kind": "oracle" on the target); it then appears in the list with the operator’s name.

Connect one on the gateway host

An operator who keeps connections in the gateway’s registry (see Connect your own source from scratch) names the type and a private sign-in file per account. A SQL Server connection:

{
  "id": "groningen-sqlserver",
  "name": "Groningen wells (SQL Server)",
  "namespace": "sqlserver.example/groningen",
  "type": "sqlserver",
  "principals": {"alice": "/etc/ophiolite/sign-ins/alice-sqlserver.json"},
  "tables": {"wells": {"schema": "dbo", "table": "groningen", "name": "Groningen wells"}}
}

Its sign-in file (mode 0600), one per account:

{"host": "mssql.example.org", "port": 1433, "database": "groningen", "user": "alice_reader", "password": "…", "cafile": "/etc/ophiolite/ca/sqlserver.pem"}

An Oracle connection and its sign-in file; dn is needed only when host is an address:

{
  "id": "groningen-oracle",
  "name": "Groningen wells (Oracle)",
  "namespace": "oracle.example/groningen",
  "type": "oracle",
  "principals": {"alice": "/etc/ophiolite/sign-ins/alice-oracle.json"},
  "tables": {"wells": {"schema": "GRONINGEN", "table": "WELLS", "name": "Groningen wells"}}
}

{"host": "oracle.example.org", "port": 2484, "service": "FREEPDB1", "user": "alice_reader", "password": "…", "cafile": "/etc/ophiolite/ca/oracle.pem", "dn": "CN=oracle.example.org"}

Read the wells in Python

Once a table is selected in your project, it is a source like any other (Read a connected source in Python). Name the sources you selected, by their ids from client.sources():

sources = {"SQL Server": "SOURCE_ID", "Oracle": "OTHER_SOURCE_ID"}

Read each one, verified against its revision:

frames = {}
for system, source_id in sources.items():
    source = client.source(source_id)
    frames[system] = source.read(expect_revision=source.revision).to_frame()
    print(system, len(frames[system]), "wells in", frames[system].attrs["crs"], "at revision", frames[system].attrs["revision"][:12])

The same table in both systems gives the same wells at the same coordinates, as stored:

first = {system: frame.set_index("well_id").loc["ALO-01", ["name", "x", "y"]].tolist() for system, frame in frames.items()}
print(first)

When something is refused

You see	What it means
“This SQL Server database does not allow a consistent read…”	Snapshot isolation is off in that database; its administrator can turn it on, or approve a view in a database that allows it
The certificate is refused	The server’s certificate does not name the server you typed (SQL Server), or its subject is not the one expected (Oracle)
The address is refused	The server’s address is outside your organisation’s rule for databases added in the browser
“The table changed while it was read; read it again”	Oracle could not keep the read consistent for its whole length; read it again