Install ODBC driver on Ubuntu 20.04: How to connect gramine-sgx application with Azure SQL Database

[dukele35]

Hi team,

I'm currently facing an issue connecting my python application with the Azure SQL database inside the Gramine enclave.

A. CONTEXTS

A.1. VM settings

• Linux (Ubuntu 20.04) - Gen 2
• Size: Standard DC2ds v3 (reference)
• RAM: 16 GB
• vCPUs: 2
• VM architecture: x64
• Installed Gramine driver on the VM (reference)

A.2. Python application

• Basic endpoints
• python.manifest.dev file (reference)

# Python3 manifest

loader.entrypoint = "file:{{ gramine.libos }}"
libos.entrypoint = "{{ entrypoint }}"
loader.pal_internal_mem_size="128M"

loader.log_level = "{{ log_level }}"

loader.env.LD_LIBRARY_PATH = "/lib:/lib:{{ arch_libdir }}:/usr/{{ arch_libdir }}"

loader.argv = ["python", "app.py"]

sys.enable_sigterm_injection = true

fs.mounts = [
  { path = "/lib", uri = "file:{{ gramine.runtimedir() }}" },
  { path = "{{ arch_libdir }}", uri = "file:{{ arch_libdir }}" },
  { path = "/usr/{{ arch_libdir }}", uri = "file:/usr/{{ arch_libdir }}" },
  { path = "{{ entrypoint }}", uri = "file:{{ entrypoint }}" },
  { path = "{{ python.stdlib }}", uri = "file:{{ python.stdlib }}" },
  { path = "{{ python.distlib }}", uri = "file:{{ python.distlib }}" },
  { path = "/usr/local/lib/python3.8/dist-packages", uri = "file:/usr/local/lib/python3.8/dist-packages" },


  { type = "tmpfs", path = "/tmp" },
]

sys.stack.size = "2M"
sys.enable_extra_runtime_domain_names_conf = true

sgx.debug = false
sgx.nonpie_binary = true
sgx.enclave_size = "512M"
sgx.thread_num = 32

sgx.remote_attestation = "{{ ra_type }}"
sgx.ra_client_spid = "{{ ra_client_spid }}"
sgx.ra_client_linkable = {{ 'true' if ra_client_linkable == '1' else 'false' }}
sgx.insecure__rpc_thread_num = 10

sgx.trusted_files = [
  "file:{{ gramine.libos }}",
  "file:{{ entrypoint }}",
  "file:{{ gramine.runtimedir() }}/",
  "file:{{ arch_libdir }}/",
  "file:/usr/{{ arch_libdir }}/",
  "file:{{ python.stdlib }}/",
  "file:{{ python.distlib }}/",
  "file:/usr/local/lib/python3.8/dist-packages/",
  "file:./"
]

• Makefile file (reference)

ARCH_LIBDIR ?= /lib/$(shell $(CC) -dumpmachine)

ifeq ($(DEBUG),1)
GRAMINE_LOG_LEVEL = debug
else
GRAMINE_LOG_LEVEL = error
endif

.PHONY: all
all: python.manifest
ifeq ($(SGX),1)
all: python.manifest.sgx python.sig python.token
endif

RA_TYPE ?= none
RA_CLIENT_SPID ?=
RA_CLIENT_LINKABLE ?= 0

python.manifest: python.manifest.dev
	gramine-manifest \
		-Dlog_level=$(GRAMINE_LOG_LEVEL) \
		-Darch_libdir=$(ARCH_LIBDIR) \
		-Dentrypoint=$(realpath $(shell sh -c "command -v python3")) \
		-Dra_type=$(RA_TYPE) \
		-Dra_client_spid=$(RA_CLIENT_SPID) \
		-Dra_client_linkable=$(RA_CLIENT_LINKABLE) \
		$< >$@

# Make on Ubuntu <= 20.04 doesn't support "Rules with Grouped Targets" (`&:`),
# see the helloworld example for details on this workaround.
python.manifest.sgx python.sig: sgx_sign
	@:

.INTERMEDIATE: sgx_sign
sgx_sign: python.manifest
	gramine-sgx-sign \
		--manifest $< \
		--output $<.sgx

python.token: python.sig
	gramine-sgx-get-token --output $@ --sig $<

.PHONY: check
check: all
	./run-tests.sh > TEST_STDOUT 2> TEST_STDERR
	@grep -q "Success 1/4" TEST_STDOUT
	@grep -q "Success 2/4" TEST_STDOUT
	@grep -q "Success 3/4" TEST_STDOUT
	@grep -q "Success 4/4" TEST_STDOUT
ifeq ($(SGX),1)
	@grep -q "Success SGX quote" TEST_STDOUT
endif

.PHONY: clean
clean:
	$(RM) *.manifest *.manifest.sgx *.token *.sig OUTPUT* *.PID TEST_STDOUT TEST_STDERR
	$(RM) -r scripts/__pycache__

.PHONY: distclean
distclean: clean

B. APPLICATION WITHOUT DATABASE

• Running the python application with the following command make distclean && make SGX=1 && gramine-sgx python, everything seems fine and we have the following log

Measurement:
    891b5d69700d5be26c66932fd6873fc67baf6817da6807f357f09688f2343cd2
gramine-sgx-get-token --output python.token --sig python.sig
Attributes:
    mr_enclave:  891b5d69700d5be26c66932fd6873fc67baf6817da6807f357f09688f2343cd2
    mr_signer:   a692beec0f6c3bd16a928a00f205abb0b2bb70035df2f69278dbecbe5208aab7
    isv_prod_id: 0
    isv_svn:     0
    attr.flags:  0000000000000004
    attr.xfrm:   00000000000000e7
    mask.flags:  ffffffffffffffff
    mask.xfrm:   fffffffffff9ff1b
    misc_select: 00000000
    misc_mask:   ffffffff
    modulus:     5f8d68714b2212b961030400a95ece64...
    exponent:    3
    signature:   feaffc6c5f7c8e9c83c95e5945885b09...
    date:        2023-01-30
Gramine is starting. Parsing TOML manifest file, this may take some time...
Detected a huge manifest, preallocating 64MB of internal memory.
Detected a huge manifest, preallocating 64MB of internal memory.
 * Serving Flask app 'app'
 * Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on all addresses (0.0.0.0)
 * Running on http://127.0.0.1:5001
 * Running on http://10.0.0.6:5001
Press CTRL+C to quit

C. AZURE SQL DB & THE ODBD DRIVER ON THE UBUNTU SERVER

• Our application needs to connect with Azure SQL DB. To that end, installing Microsoft ODBC driver for the SQL server is required (reference). We did install the Microsoft ODBC 18 driver directly on our VM.
• Outside the Gramine enclave, our python application also worked well in this setting; in another word, our tables were created on Azure SQL Database and our application's endpoints were functional.

D. PROBLEMS WITH INTRODUCING ODBC IN THE GRAMINE ENCLAVE

• Updating our python.manifest.dev file to allow the enclave have ODBC's drivers and configurations as follows

# Python3 manifest

loader.entrypoint = "file:{{ gramine.libos }}"
libos.entrypoint = "{{ entrypoint }}"
loader.pal_internal_mem_size="128M"

loader.log_level = "{{ log_level }}"

loader.env.LD_LIBRARY_PATH = "/lib:/lib:{{ arch_libdir }}:/usr/{{ arch_libdir }}"

loader.argv = ["python", "app.py"]

sys.enable_sigterm_injection = true

fs.mounts = [
  { path = "/lib", uri = "file:{{ gramine.runtimedir() }}" },
  { path = "{{ arch_libdir }}", uri = "file:{{ arch_libdir }}" },
  { path = "/usr/{{ arch_libdir }}", uri = "file:/usr/{{ arch_libdir }}" },
  { path = "{{ entrypoint }}", uri = "file:{{ entrypoint }}" },
  { path = "{{ python.stdlib }}", uri = "file:{{ python.stdlib }}" },
  { path = "{{ python.distlib }}", uri = "file:{{ python.distlib }}" },
  { path = "/usr/local/lib/python3.8/dist-packages", uri = "file:/usr/local/lib/python3.8/dist-packages" },

  { path = "/opt/microsoft", uri = "file:/opt/microsoft" },
  { path = "/opt/microsoft/msodbcsql18", uri = "file:/opt/microsoft/msodbcsql18" },
  { path = "/usr/share/doc/msodbcsql18", uri = "file:/usr/share/doc/msodbcsql18" },
  { path = "/usr/lib/libmsodbcsql-18.so", uri = "file:/usr/lib/libmsodbcsql-18.so" },
  { path = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1", uri = "file:/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1" },


  { path = "/usr/include/odbcinstext.h", uri = "file:/usr/include/odbcinstext.h" },
  { path = "/usr/include/uodbc_extras.h", uri = "file:/usr/include/uodbc_extras.h" },
  { path = "/usr/include/x86_64-linux-gnu", uri = "file:/usr/include/x86_64-linux-gnu" },
  { path = "/usr/include/x86_64-linux-gnu/unixodbc_conf.h", uri = "file:/usr/include/x86_64-linux-gnu/unixodbc_conf.h" },
  { path = "/usr/include/autotest.h", uri = "file:/usr/include/autotest.h" },
  { path = "/usr/include/sql.h", uri = "file:/usr/include/sql.h" },
  { path = "/usr/include/uodbc_stats.h", uri = "file:/usr/include/uodbc_stats.h" },
  { path = "/usr/include/sqlext.h", uri = "file:/usr/include/sqlext.h" },
  { path = "/usr/include/sqlspi.h", uri = "file:/usr/include/sqlspi.h" },
  { path = "/usr/include/odbcinst.h", uri = "file:/usr/include/odbcinst.h" },
  { path = "/usr/include/sqlucode.h", uri = "file:/usr/include/sqlucode.h" },
  { path = "/usr/include/sqltypes.h", uri = "file:/usr/include/sqltypes.h" },
  { path = "/usr/lib/x86_64-linux-gnu", uri = "file:/usr/lib/x86_64-linux-gnu" },

  { path = "/etc/odbcinst.ini", uri = "file:/etc/odbcinst.ini" },
  { path = "/etc/odbc.ini", uri = "file:/etc/odbc.ini" },

  { path = "/etc/passwd", uri = "file:/etc/passwd" },




  { type = "tmpfs", path = "/tmp" },
]

sys.stack.size = "2M"
sys.enable_extra_runtime_domain_names_conf = true

sgx.debug = false
sgx.nonpie_binary = true
sgx.enclave_size = "512M"
sgx.thread_num = 32

sgx.remote_attestation = "{{ ra_type }}"
sgx.ra_client_spid = "{{ ra_client_spid }}"
sgx.ra_client_linkable = {{ 'true' if ra_client_linkable == '1' else 'false' }}
sgx.insecure__rpc_thread_num = 10

sgx.trusted_files = [
  "file:{{ gramine.libos }}",
  "file:{{ entrypoint }}",
  "file:{{ gramine.runtimedir() }}/",
  "file:{{ arch_libdir }}/",
  "file:/usr/{{ arch_libdir }}/",
  "file:{{ python.stdlib }}/",
  "file:{{ python.distlib }}/",
  "file:/usr/local/lib/python3.8/dist-packages/",

  "file:/opt/microsoft/",
  "file:/opt/microsoft/msodbcsql18/",
  "file:/usr/share/doc/msodbcsql18/",
  "file:/usr/lib/libmsodbcsql-18.so",
  "file:/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1",

  "file:/usr/include/odbcinstext.h",
  "file:/usr/include/uodbc_extras.h",
  "file:/usr/include/x86_64-linux-gnu/", 
  "file:/usr/include/x86_64-linux-gnu/unixodbc_conf.h",
  "file:/usr/include/autotest.h",
  "file:/usr/include/sql.h",
  "file:/usr/include/uodbc_stats.h", 
  "file:/usr/include/sqlext.h",
  "file:/usr/include/sqlspi.h",
  "file:/usr/include/odbcinst.h",
  "file:/usr/include/sqlucode.h", 
  "file:/usr/include/sqltypes.h",
  "file:/usr/lib/x86_64-linux-gnu/",

  "file:/etc/odbcinst.ini",
  "file:/etc/odbc.ini",

  "file:/etc/passwd",

  "file:./"
]

• Currently, we're facing this issue

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)")
(Background on this error at: https://sqlalche.me/e/14/dbapi

E. QUESTIONS

We're stuck at this stage. Could you give some suggestions regarding having extra Linux system drivers for Python application in general and ODBC in this case in particular?

[dimakuv]

I suggest two things:

1. Please change to loader.log_level = "all" in the manifest file and re-run the application. And then attach the resulting Gramine log here, so we can understand what goes inside Gramine, and what fails exactly.
2. You seem to be using Gramine v1.3.1? This version is known to not support some widely used TCP/IP and general-socket options, which may have the impact on your Azure SQL DB connection.
   • We will release Gramine v1.4 very soon (this/next week). So you'll be able to try that out.
   • Alternatively, you can build Gramine from GitHub sources (the latest master branch), and check yourself if newer Gramine fixes your issue.

[dukele35]

The problem seems to persist on my side. Out of curiosity, how can I control/define user and group in gramine manifest file? I found this https://gramine.readthedocs.io/en/stable/manifest-syntax.html#user-id-and-group-id. I already added

loader.uid = 0
loader.gid = 0

fs.mounts = [..., { path = "/etc/passwd", uri = "file:/etc/passwd" }, ...]
sgx.trusted_files = [..., "file:/etc/passwd", ...]

But it doesn't work either. The reason why user/group and /etc/passwd may be the problem becasue, for the similar error pyodbc.Error: ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)"), there are several guides talking about the user/group in the Docker context and how to resolve them. Examples are like

• ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)") mkleehammer/pyodbc#805
• "Driver's SQLAllocHandle on SQL_HANDLE_HENV failed" on Alpine Linux (via Docker) mkleehammer/pyodbc#738
• https://stackoverflow.com/questions/55474713/drivers-sqlallochandle-on-sql-handle-henv-failed-0-sqldriverconnect-when-co

For the similar context in Docker, is there any way that I can modify/customise/define the user in gramine manifest file? Thanks a lot

[dukele35]

also, I would like to provide extra context for the ODBC driver installed directly on my Ubuntu 20.04 VM.
Running the odbcinst -j command to display information about the ODBC (Open Database Connectivity) configuration on the VM. The following log is

unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/azureuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The /etc/odbcinst.ini file is

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1
Trace=Yes

The /etc/odbc.ini file, i.e. the target db, is

[db-name]
Driver=ODBC Driver 18 for SQL Server
Server=<server-name>
Port=<port-no>
Database=<db-name>
UID=<user-name>
PWD=<password>

[dimakuv]

@dukele35 For UID and GID, you would want to do like this:

• https://github.com/gramineproject/examples/blob/ce2177c9b363e9f2299ffc83016f43ed7e22ba68/scikit-learn-intelex/Makefile#L19-L20
• https://github.com/gramineproject/examples/blob/ce2177c9b363e9f2299ffc83016f43ed7e22ba68/scikit-learn-intelex/sklearnex.manifest.template#L15-L16

In other words, you need to set up the same user ID and group ID as the existing user on your system.

[dimakuv]

also, I would like to provide extra context for the ODBC driver installed directly on my Ubuntu 20.04 VM.

I'm not sure what kind of reply you're expecting from the Gramine team. AFAIK, none of us has experience with ODBC drivers.

Looking at the files you posted, seems like you need to add the corresponding directories (like /opt/microsoft/msodbcsql18) to fs.mounts and the corresponding files to sgx.trusted_files. But also I see that you did this already in your manifest file.

If you need to check whether you missed to add some files, please run your workload with loader.log_level = "all" in the manifest file -- this will give you hints which files Gramine tried to open but couldn't.