🤦‍♂️ I had neglected ODBCSYSINI as the final step. Sincerely appreciate the direction @boboldehampsink . For posterity, here are the general steps:

[DevArKaDiA]

🤦‍♂️ I had neglected ODBCSYSINI as the final step. Sincerely appreciate the direction @boboldehampsink . For posterity, here are the general steps:

Heroku Stack and Buildpack

ext-sqlsrv (and associated ext-pdo_sqlsrv) are not (currently) installed on the Heroku buildpack for php due to licensing and complexity requirements (see #417) .

To enable SQL Server via php, we are using multiple buildpacks, custom HEROKU_PHP_PLATFORM_REPOSITORIES, hosted (pre-compiled) .so files, and mandatory Heroku environmental variables.

Buildpack

 heroku buildpacks:add --index 1 heroku-community/apt to add an apt buildpack that allows us to install additional libraries and packages to the instance.

Buildpack added. Next release on ___ will use:
    1. heroku-community/apt
    2. heroku/php

Aptfile

The above buildpack allows us to customize additional packages for installation prior to dyno run. The Aptfile lists direct paths to the .deb packages, though an alternate approach could be used via trusting Microsoft repository keys if heroku/heroku-buildpack-apt#41 is ever merged.

Note that the environmental variable heroku config:set ACCEPT_EULA="Y" must be set or the Microsoft package will refuse to install.

# eg. Aptfile for Heroku-20 stack directly to debs without custom/forked apt buildpack
# NOTE: you MUST set ACCEPT_EULA=Y environmental variable!

https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/libodbc1_2.3.7_amd64.deb
https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/odbcinst_2.3.7_amd64.deb
https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/odbcinst1debian2_2.3.7_amd64.deb
https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/unixodbc_2.3.7_amd64.deb
https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/unixodbc-dev_2.3.7_amd64.deb
https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.6.1.1-1_amd64.deb
https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/mssql-tools/mssql-tools_17.6.1.1-1_amd64.deb

Composer and Heroku Custom Buildpacks

Heroku's php buildpack documentation is extensive. Here's what you need to know:

• Dependencies (like php libraries) are managed via composer and composer.json
• Since heroku doesn't support these php dependencies, you must self-host them as a publicly accessible customized composer repository. package.json and associated tar'd, gzip'd pre-compiled .so files accordingly; this is non-trivial... the information in the docs will help you. Your packages.json might look like:

{
	"packages": [
		[
			{
				"name": "heroku-sys/ext-sqlsrv",
				"version": "5.8.1",
				"type": "heroku-sys-php-extension",
				"require": {
					"php": "7.*",
					"heroku/installer-plugin": "^1.2.0"
				},
				"dist": {
					"type": "heroku-sys-tar",
					"url": "https://path-to-your-hosted/ext-sqlsrv-5.8.1_php-7.4.tar.gz"
				},
				"time": "2019-09-02"
			},
			{
				"name": "heroku-sys/ext-pdo_sqlsrv",
				"version": "5.8",
				"type": "heroku-sys-php-extension",
				"require": {
					"php": "7.*",
					"heroku/installer-plugin": "^1.2.0"
				},
				"dist": {
					"type": "heroku-sys-tar",
					"url": "https://path-to-your-hosted/ext-pdo_sqlsrv-5.8_php-7.4.tar.gz"
				},
				"time": "2019-09-02"
			}
		]
	]
}

... and your composer.json might look like:

{
    "require": {
        "php": "7.4.*",
        "ext-pdo": "^7.4",
        "ext-sqlsrv": "*",
        "ext-pdo_sqlsrv": "^5.8"
    }
}

• We MUST SET heroku config:set HEROKU_PHP_PLATFORM_REPOSITORIES="https://path-to-your-hosted/" to direct the buildpack where to find these custom pre-compiled modules.

ODBC

Microsoft's SQL Server uses ODBC for the majority of lifting here. The default buildpack instructs ODBC to attempt drivers at a default location of /etc/.ocbcinst.ini ... which will be empty. To direct ODBC to the driver, we must include an odbcinst.ini file with appropriate Heroku pathing and we MUST SET heroku config:set ODBCSYSINI="/app" to direct ODBC to attempt to initialze drivers from the Heroku app directory (where our odbcinst.ini file is located)

The odbcinst.ini file might look like:

# NOTE: you MUST set ODBCSYSINI=/app environmental variable
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/app/.apt/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1

Originally posted by @benpbolton in #417 (comment)