Add ext-sqlsrv support

[boboldehampsink]

Hi,

It's been a while since https://github.com/heroku/heroku-buildpack-php/issues/175 and a lot has changed since then. Microsoft has shipped ext-sqlsrv and it is available for ubuntu and it is stable: https://github.com/microsoft/msphpsql.

Since Heroku also offers MSSQL/SQL Server add-ons, I like to be able to use them with PHP.

[dzuelke]

There are two reasons why this hasn't happened yet:

1. we need the underlying ODBC driver libraries and in fact the entire ODBC ecosystem on all stacks first (and 20.04 isn't supported yet by Microsoft, but we have that in internal beta already);
2. https://odbceula.blob.core.windows.net/eula17/LICENSE17.TXT needs to be looked at by lawyers, and that takes time, as you might expect.

[boboldehampsink]

Yep. In the mean time I have managed to get this (and ext-pdo_sqlsrv) running by hosting the pre-built .so files for ubuntu from microsoft with a packages.json like you described in the build docs. And I use heroku-buildpack-apt with msodbcsql17 and unixodbc-dev :-)

[edgardhsl]

Hello @boboldehampsink , can you publish steps how to make ext-pdo_sqlsrv work? I'm still a newbie with Heroku.

[boboldehampsink]

I did ^^

[edgardhsl]

Ok, thank u.

[benpbolton]

Hello @boboldehampsink , I apologize for the duplicate question here. I've wired up and hosted a custom HEROKU_PHP_PLATFORM_REPOSITORIES packages.json alongside my ext-sqlsrv and ext-pdo_sqlsrv packages, hosting their tar'd gzip'd .so files appropriately... and phpinfo() believes those modules are loaded/active and happy... I've also used https://github.com/amiel/heroku-buildpack-apt#feature/support-adding-keys with a custom Aptfile to add MS's key, repo, msodbcsql17 and unixodbc-dev...

But the instance isn't loading the odbc libraries from where they reside (eg. /app/.apt/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1 on the VM) ... odbcinst -j is looking for driver pathing in /etc/.ocbc.ini... and it's not going to find anything there.

I realize this isn't a support forum, but any information or direction you can provide would be greatly appreciated!

[boboldehampsink]

HI @benpbolton, here's what I did to make it work:

1. I use Heroku's first-party apt-buildpack: https://buildpack-registry.s3.amazonaws.com/buildpacks/heroku-community/apt.tgz
2. This is my Aptfile (for Heroku 20, change to use with 18 or 16):

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

3. Put an odbcinst.ini file in the root of your project with the following contents:

[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

4. Set the following ENV vars:

ACCEPT_EULA=y
ODBCSYSINI=/app

P.S. I use PHP 7.3, I couldn't get ext-sqlsrv to work with 7.4. There is a new version coming up (5.9.0) that works with PHP 8 and might work with 7.4, but it is still in beta. https://github.com/microsoft/msphpsql/releases

[benpbolton]

🤦‍♂️ 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 https://github.com/heroku/heroku-buildpack-php/issues/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 https://github.com/heroku/heroku-buildpack-apt/pull/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

[boboldehampsink]

P.S. I use PHP 7.3, I couldn't get ext-sqlsrv to work with 7.4. There is a new version coming up (5.9.0) that works with PHP 8 and might work with 7.4, but it is still in beta. https://github.com/microsoft/msphpsql/releases

Version 5.9.0 is out and I still couldn't get it to work with anything other than PHP 7.3 on Heroku (works locally). I think this might be a bug in the buildpack @dzuelke? Here's what happens:

• I built and hosted the extension for PHP 8. I required ext-pdo_sqlsrv in composer.json and required php8 there.
• Heroku immediately fails because it also needs the 7.3 version for bootstrapping. So I also added a 7.3 version of the extension (in packages.json below the php8 version).
• Now the bootstrapping succeeds, but when using PHP 8 the extension cannot be found:

-----> PHP app detected
-----> Bootstrapping...
-----> Installing platform packages...
       - php (8.0.1)
       - ext-gd (bundled with php)
       - ext-intl (bundled with php)
       - ext-mbstring (bundled with php)
       - ext-redis (5.3.2)
       - ext-pdo_sqlsrv (5.9.0)
       - apache (2.4.46)
       - nginx (1.18.0)
       - composer (2.0.8)
PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so: cannot open shared object file: No such file or directory), /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0

Looks like after bootstrapping it tries to use the 7.3 version for 8.0?

[boboldehampsink]

@dzuelke now with the PHP bootstrapping version being bumped to 7.4, my app fails to build. I might be wrong but I think the bootstrapping PHP version should ignore the ext requirements, only the chosen php version should check/install them? would solve the problem above as well ^^

[dzuelke]

It does ignore the ext requirements. The problem must be somewhere else. It looks like your custom builds do not have the right version requirements in place for each package. Without seeing your build code, repository metadata, error logs etc, I can't help you.

[boboldehampsink]

@dzuelke what do you need, an app name? or a repo?

[dzuelke]

Your code that builds the extensions and the repository, and the resulting repository URL, @boboldehampsink

[boboldehampsink]

@dzuelke what do you mean with repository in this context?

By the way, if it helps: if I don't supply a 7.4 version of my ext, the heroku build already fails in the bootstrapping phase. That's why I guessed it is used in bootstrapping, while it shouldn't. Supplying a 7.4 version passes bootstrapping, then fails if the selected php version != 7.4

[dzuelke]

Yeah so it installs the wrong version (built for another PHP series); probably because your custom package Composer repository say this (copied verbatim from your comment further above):

				"require": {
					"php": "7.*",

That needs to be a) 7.4.* or whatever version you built it for, and b) heroku-sys/php so things are resolved correctly ;)

That's why I said I need to look at your repository for your custom extension build - that packages.json, wherever you are hosting it.

[boboldehampsink]

@dzuelke I do use 7.4.* - here is my packages file: https://raw.githubusercontent.com/robuust/heroku-php/php8-pdo_sqlsrv/packages.json

Here's the build log for that:

-----> PHP app detected
-----> Bootstrapping...
-----> Installing platform packages...
       - php (8.0.1)
       - ext-gd (bundled with php)
       - ext-intl (bundled with php)
       - ext-mbstring (bundled with php)
       - ext-redis (5.3.3)
       - ext-pdo_sqlsrv (5.9.0)
       - apache (2.4.46)
       - nginx (1.18.0)
       - composer (2.0.9)
PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so: cannot open shared object file: No such file or directory), /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so: cannot open shared object file: No such file or directory), /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0
-----> Installing dependencies...
       Composer version 2.0.9 2021-01-27 16:09:27
       PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so: cannot open shared object file: No such file or directory), /app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so (/app/.heroku/php/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0
       Installing dependencies from lock file
       Verifying lock file contents can be installed on current platform.
       Your lock file does not contain a compatible set of packages. Please run composer update.
       
         Problem 1
           - Root composer.json requires PHP extension ext-pdo_sqlsrv * but it is missing from your system. Install or enable PHP's pdo_sqlsrv extension.
       
       To enable extensions, verify that they are enabled in your .ini files:
           - /app/.heroku/php/etc/php/php.ini
           - /app/.heroku/php/etc/php/conf.d/000-heroku.ini
           - /app/.heroku/php/etc/php/conf.d/010-ext-zend_opcache.ini
           - /app/.heroku/php/etc/php/conf.d/100-ext-gd.ini
           - /app/.heroku/php/etc/php/conf.d/110-ext-intl.ini
           - /app/.heroku/php/etc/php/conf.d/120-ext-mbstring.ini
           - /app/.heroku/php/etc/php/conf.d/130-ext-redis.ini
           - /app/.heroku/php/etc/php/conf.d/140-ext-pdo_sqlsrv.ini
           - /tmp/codon/tmp/buildpacks/b87b6e4735295f86d5f7a955abc9cc75bb3dc24f/conf/php/apm-nostart-overrides/apm-nostart-overrides.ini
       You can also run `php --ini` inside terminal to see which files are used by PHP in CLI mode.
 !     ERROR: Dependency installation failed!
 !     
 !     The 'composer install' process failed with an error. The cause
 !     may be the download or installation of packages, or a pre- or
 !     post-install hook (e.g. a 'post-install-cmd' item in 'scripts')
 !     in your 'composer.json'.
 !     
 !     Typical error cases are out-of-date or missing parts of code,
 !     timeouts when making external connections, or memory limits.
 !     
 !     Check the above error output closely to determine the cause of
 !     the problem, ensure the code you're pushing is functioning
 !     properly, and that all local changes are committed correctly.
 !     
 !     For more information on builds for PHP on Heroku, refer to
 !     https://devcenter.heroku.com/articles/php-support
 !     Push rejected, failed to compile PHP app.
 !     Push failed

[dzuelke]

Right, so.

First problem:

$ curl -sL https://github.com/robuust/heroku-php/raw/pdo_sqlsrv/packages/ext-pdo_sqlsrv-5.9.0_php-7.4.tar.gz | tar tz
lib/
lib/php/
lib/php/extensions/
lib/php/extensions/._.DS_Store
lib/php/extensions/.DS_Store
lib/php/extensions/no-debug-non-zts-20190902/
lib/php/extensions/no-debug-non-zts-20190902/pdo_sqlsrv.so

$ curl -sL https://github.com/robuust/heroku-php/raw/pdo_sqlsrv/packages/ext-pdo_sqlsrv-5.9.0_php-8.0.tar.gz | tar tz
tar: Unrecognized archive format
tar: Error exit delayed from previous errors.

Second, you need "heroku-sys/php" as a requirement, not "php", in the package declaration, as I commented earlier.

You might also want "heroku-sys/heroku": "^20.0.0" for heroku-20, "heroku-sys/heroku": "^18.0.0" for heroku-18, "heroku-sys/heroku": "^16.0.0" for heroku-16 (and then build each extension version for each series for each stack as well) if the library locations or ABI versions that are installed via Apt differ between stacks.

(That's why the buildpack itself has separate repositories per stack, but you could just as well all dump them into the same repo, as long as you ensure the ordering of packages is correct).

[boboldehampsink]

Thanks checking @dzuelke. I have fixed the issue with the tar. About me using php instead of heroku-sys/php is because I looked here for an example: https://github.com/heroku/heroku-buildpack-php/blob/master/support/build/README.md#hosting-a-proprietary-extension-using-custom-tooling

I also added the platform requirement. Everything now works! Thanks again for the support.

[boboldehampsink]

I think this issue makes a great example on "hosting a proprietay extension using custom tooling", and may now be closed?

[wit3]

Thanks checking @dzuelke. I have fixed the issue with the tar. About me using php instead of heroku-sys/php is because I looked here for an example: https://github.com/heroku/heroku-buildpack-php/blob/master/support/build/README.md#hosting-a-proprietary-extension-using-custom-tooling

I also added the platform requirement. Everything now works! Thanks again for the support.

Hi @boboldehampsink . Can you provide a repository with an example worked of php with sqlsrv/sqlsrvpdo? Thanks

[boboldehampsink]

Thanks checking @dzuelke. I have fixed the issue with the tar. About me using php instead of heroku-sys/php is because I looked here for an example: https://github.com/heroku/heroku-buildpack-php/blob/master/support/build/README.md#hosting-a-proprietary-extension-using-custom-tooling I also added the platform requirement. Everything now works! Thanks again for the support.

Hi @boboldehampsink . Can you provide a repository with an example worked of php with sqlsrv/sqlsrvpdo? Thanks

https://raw.githubusercontent.com/robuust/heroku-php/pdo_sqlsrv/packages.json

[tillkruss]

@boboldehampsink: I just went through this entire ordeal to build the igbinary and phpredis extension. I'd be happy to add sqlsrv to the platform repo here: https://github.com/cachewerk/heroku-php-extensions

[boboldehampsink]

Go ahead!

[pmcgoverncw]

It is worth noting that with all of the above, when using Symfony/Doctrine (I'm currently on Symfony 5.2), the doctrine > dbal > connection configuration MUST include the server_version attribute. Without it, the deployment process will try to connect to the database to verify server version using a dyno that doesn't have the sqlsrv extension installed, which will cause the composer cache:clear step of deployment to fail with an error similar to this:

Script cache:clear returned with error code 1
       !!  
       !!  In Error.php line 45:
       !!                                                                                 
       !!    SQLSTATE [IMSSP, -49]: This extension requires the Microsoft ODBC Driver fo  
       !!    r SQL Server. Access the following URL to download the ODBC Driver for SQL   
       !!    Server for x64: https://go.microsoft.com/fwlink/?LinkId=163712

[9a24f0]

I highly appreciate this conversation as it guides me through the problem, especially of @boboldehampsink and @benpbolton . I could see the driver setup successfully as

 $ odbcinst -j

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

and

$ cat /app/odbcinst.ini 

[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

I did also find the .so file following the above path. Great result!

However I'm facing another problem as the log now returns:

'08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol] (-1) (SQLDriverConnect)'

Since I could not find any related sources in python and I'm using pyodbc to connect to the server, I had to ask here. Sorry for inconveniences might cause due to different tech. Though I believe in nice people here!

Update The SSL Error is caused by two factors: Firstly, I'm trying to connect to an old MSSQL Server (Microsoft SQL Server 2012 - 11.0.5058.0). I was provided that server and there was nothing I could do to upgrade it. On the other hand, OpenSSL version on heroku-20 stack was a bit outdated (1.1.1f published on Mar 2020 compared to the current of 1.1.1k). I was able to connect to the server on my machine which using 1.1.1k so it seems like the library needs to be updated.

Here are what I've tried to upgrade OpenSSL (in case another person face the same problem):

• Upgrade OpenSSL via buildpack.
• Run heroku run bash -a appname to enter dyno's shell. Then export LD_LIBRARY_PATH=~/openssl/lib${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH} to correct the path.
• Run echo $LD_LIBRARY_PATH and manually copy the path.
• Add config var by heroku config:set LD_LIBRARY_PATH=your-copied-path

Now everything works! Nice!

[DevArKaDiA]

@benpbolton I was able to solve the deployment in automatic, using a few various parts and it starts perfectly in each deploy

First I use the build pack https://github.com/heroku/heroku-buildpack-apt to install obc and mssql tools

aptfile contains this and the heroku env var of Aptfile section of @benpbolton guide

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

After that, I add the build pack https://github.com/niteoweb/heroku-buildpack-shell.git to execute a bash during the deploy process

Step 1: Create a .heroku folder Step 2: Into the heroku folded create run.sh file to download and move the mssql extension bin to php folder and add the ext to the php.ini config file Step 3: odbcinst.ini int the project root

[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

Example: run.sh

#!/bin/bash

#get php ext dir
ext_directory=$(php-config --extension-dir)

#donwload sqlsrv ext
sqlsrv_drivers='https://github.com/microsoft/msphpsql/releases/download/v5.9.0/Ubuntu2010-8.0.tar'
wget $sqlsrv_drivers
tar -xvf Ubuntu2010-8.0.tar Ubuntu2010-8.0
#unzip


mv "Ubuntu2010-8.0/php_sqlsrv_80_nts.so" "$ext_directory/sqlsrv.so"
mv "Ubuntu2010-8.0/php_pdo_sqlsrv_80_nts.so" "$ext_directory/pdo_sqlsrv.so"
echo "extension=sqlsrv" >> .heroku/php/etc/php/php.ini
echo "extension=pdo_sqlsrv" >> .heroku/php/etc/php/php.ini

I'm not a bash script master so I put in raw the names of the files if u are a bash master please improve this file and share it

build packs order

deploy results

-----> Building on the Heroku-20 stack
-----> Using buildpacks:
       1. heroku/php
       2. https://github.com/heroku/heroku-buildpack-apt
       3. https://github.com/niteoweb/heroku-buildpack-shell.git
-----> PHP app detected
-----> Bootstrapping...
-----> Installing platform packages...
       - php (8.0.12)
       - ext-mbstring (bundled with php)
       - composer (2.1.9)
       - apache (2.4.51)
       - nginx (1.20.1)
-----> Installing dependencies...
       Composer version 2.1.9 2021-10-05 09:47:38
       Installing dependencies from lock file
       Verifying lock file contents can be installed on current platform.
       Package operations: 71 installs, 0 updates, 0 removals
         - Installing voku/portable-ascii (1.5.6): Extracting archive
         - Installing symfony/polyfill-php80 (v1.23.1): Extracting archive
         - Installing symfony/polyfill-mbstring (v1.23.1): Extracting archive
         - Installing symfony/polyfill-ctype (v1.23.0): Extracting archive
         - Installing phpoption/phpoption (1.8.0): Extracting archive
         - Installing graham-campbell/result-type (v1.0.2): Extracting archive
         - Installing vlucas/phpdotenv (v5.3.0): Extracting archive
         - Installing symfony/css-selector (v5.3.4): Extracting archive
         - Installing tijsverkoyen/css-to-inline-styles (2.2.3): Extracting archive
         - Installing symfony/var-dumper (v5.3.7): Extracting archive
         - Installing symfony/deprecation-contracts (v2.4.0): Extracting archive
         - Installing symfony/routing (v5.3.7): Extracting archive
         - Installing symfony/process (v5.3.7): Extracting archive
         - Installing symfony/polyfill-php72 (v1.23.0): Extracting archive
         - Installing symfony/polyfill-intl-normalizer (v1.23.0): Extracting archive
         - Installing symfony/polyfill-intl-idn (v1.23.0): Extracting archive
         - Installing symfony/mime (v5.3.7): Extracting archive
         - Installing symfony/polyfill-php73 (v1.23.0): Extracting archive
         - Installing symfony/http-foundation (v5.3.7): Extracting archive
         - Installing symfony/http-client-contracts (v2.4.0): Extracting archive
         - Installing psr/event-dispatcher (1.0.0): Extracting archive
         - Installing symfony/event-dispatcher-contracts (v2.4.0): Extracting archive
         - Installing symfony/event-dispatcher (v5.3.7): Extracting archive
         - Installing psr/log (1.1.4): Extracting archive
         - Installing symfony/error-handler (v5.3.7): Extracting archive
         - Installing symfony/http-kernel (v5.3.7): Extracting archive
         - Installing symfony/finder (v5.3.7): Extracting archive
         - Installing symfony/polyfill-intl-grapheme (v1.23.1): Extracting archive
         - Installing symfony/string (v5.3.7): Extracting archive
         - Installing psr/container (1.1.1): Extracting archive
         - Installing symfony/service-contracts (v2.4.0): Extracting archive
         - Installing symfony/console (v5.3.7): Extracting archive
         - Installing symfony/polyfill-iconv (v1.23.0): Extracting archive
         - Installing doctrine/lexer (1.2.1): Extracting archive
         - Installing egulias/email-validator (2.1.25): Extracting archive
         - Installing swiftmailer/swiftmailer (v6.2.7): Extracting archive
         - Installing symfony/polyfill-php81 (v1.23.0): Extracting archive
         - Installing ramsey/collection (1.2.1): Extracting archive
         - Installing brick/math (0.9.3): Extracting archive
         - Installing ramsey/uuid (4.2.1): Extracting archive
         - Installing psr/simple-cache (1.0.1): Extracting archive
         - Installing opis/closure (3.6.2): Extracting archive
         - Installing symfony/translation-contracts (v2.4.0): Extracting archive
         - Installing symfony/translation (v5.3.7): Extracting archive
         - Installing nesbot/carbon (2.53.1): Extracting archive
         - Installing monolog/monolog (2.3.2): Extracting archive
         - Installing league/mime-type-detection (1.7.0): Extracting archive
         - Installing league/flysystem (1.1.5): Extracting archive
         - Installing nette/utils (v3.2.3): Extracting archive
         - Installing nette/schema (v1.2.1): Extracting archive
         - Installing dflydev/dot-access-data (v3.0.1): Extracting archive
         - Installing league/config (v1.1.1): Extracting archive
         - Installing league/commonmark (2.0.2): Extracting archive
         - Installing webmozart/assert (1.10.0): Extracting archive
         - Installing dragonmantank/cron-expression (v3.1.0): Extracting archive
         - Installing doctrine/inflector (2.0.3): Extracting archive
         - Installing laravel/framework (v8.60.0): Extracting archive
         - Installing brian2694/laravel-toastr (5.56): Extracting archive
         - Installing asm89/stack-cors (v2.0.3): Extracting archive
         - Installing fruitcake/laravel-cors (v2.0.4): Extracting archive
         - Installing psr/http-message (1.0.1): Extracting archive
         - Installing psr/http-client (1.0.1): Extracting archive
         - Installing ralouphie/getallheaders (3.0.3): Extracting archive
         - Installing psr/http-factory (1.0.1): Extracting archive
         - Installing guzzlehttp/psr7 (2.0.0): Extracting archive
         - Installing guzzlehttp/promises (1.4.1): Extracting archive
         - Installing guzzlehttp/guzzle (7.3.0): Extracting archive
         - Installing nikic/php-parser (v4.12.0): Extracting archive
         - Installing psy/psysh (v0.10.8): Extracting archive
         - Installing laravel/tinker (v2.6.1): Extracting archive
         - Installing laravel/ui (v3.3.0): Extracting archive
       Package sebastian/resource-operations is abandoned, you should avoid using it. No replacement was suggested.
       Generating optimized autoload files
       > Illuminate\Foundation\ComposerScripts::postAutoloadDump
       > @php artisan package:discover --ansi
       Discovered Package: brian2694/laravel-toastr
       Discovered Package: fruitcake/laravel-cors
       Discovered Package: laravel/tinker
       Discovered Package: laravel/ui
       Discovered Package: nesbot/carbon
       Package manifest generated successfully.
       48 packages you are using are looking for funding.
       Use the `composer fund` command to find out more!
-----> Preparing runtime environment...
-----> Checking for additional extensions to install...
-----> Apt app detected
-----> Reusing cache
-----> Updating apt caches
       Hit:1 http://archive.ubuntu.com/ubuntu focal InRelease
       Get:2 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [86.6 kB]
       Get:3 http://archive.ubuntu.com/ubuntu focal-security InRelease [114 kB]
       Get:4 http://archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
       Get:5 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [356 kB]
       Get:6 http://archive.ubuntu.com/ubuntu focal-security/universe amd64 Packages [805 kB]
       Get:7 http://archive.ubuntu.com/ubuntu focal-security/main amd64 Packages [1,229 kB]
       Get:8 http://archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages [1,094 kB]
       Get:9 http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [1,685 kB]
       Fetched 5,484 kB in 1s (4,660 kB/s)
       Reading package lists...
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/libodbc1_2.3.7_amd64.deb
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/odbcinst_2.3.7_amd64.deb
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/odbcinst1debian2_2.3.7_amd64.deb
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/unixodbc_2.3.7_amd64.deb
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/u/unixodbc/unixodbc-dev_2.3.7_amd64.deb
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.6.1.1-1_amd64.deb
-----> Fetching https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/mssql-tools/mssql-tools_17.6.1.1-1_amd64.deb
-----> Installing libodbc1_2.3.7_amd64.deb
-----> Installing msodbcsql17_17.6.1.1-1_amd64.deb
-----> Installing mssql-tools_17.6.1.1-1_amd64.deb
-----> Installing odbcinst1debian2_2.3.7_amd64.deb
-----> Installing odbcinst_2.3.7_amd64.deb
-----> Installing unixodbc_2.3.7_amd64.deb
-----> Installing unixodbc-dev_2.3.7_amd64.deb
-----> Writing profile script
-----> Rewrite package-config files
-----> Niteoweb Shell app detected
-----> Running .heroku/run.sh
--2021-11-10 15:54:18--  https://github.com/microsoft/msphpsql/releases/download/v5.9.0/Ubuntu2010-8.0.tar
Resolving github.com (github.com)... 140.82.114.3
Connecting to github.com (github.com)|140.82.114.3|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://github-releases.githubusercontent.com/19043988/233c7f00-6213-11eb-951a-df5633811880?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211110%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211110T155418Z&X-Amz-Expires=300&X-Amz-Signature=95476d021e282283f54ed4656ac3f66ab1d6ca1e8db4c2124e43b8eae3988739&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=19043988&response-content-disposition=attachment%3B%20filename%3DUbuntu2010-8.0.tar&response-content-type=application%2Foctet-stream [following]
--2021-11-10 15:54:18--  https://github-releases.githubusercontent.com/19043988/233c7f00-6213-11eb-951a-df5633811880?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211110%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211110T155418Z&X-Amz-Expires=300&X-Amz-Signature=95476d021e282283f54ed4656ac3f66ab1d6ca1e8db4c2124e43b8eae3988739&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=19043988&response-content-disposition=attachment%3B%20filename%3DUbuntu2010-8.0.tar&response-content-type=application%2Foctet-stream
Resolving github-releases.githubusercontent.com (github-releases.githubusercontent.com)... 185.199.111.154, 185.199.108.154, 185.199.109.154, ...
Connecting to github-releases.githubusercontent.com (github-releases.githubusercontent.com)|185.199.111.154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1637888 (1.6M) [application/octet-stream]
Saving to: ‘Ubuntu2010-8.0.tar’
     0K .......... .......... .......... .......... ..........  3% 26.5M 0s
    50K .......... .......... .......... .......... ..........  6% 37.6M 0s
   100K .......... .......... .......... .......... ..........  9% 71.3M 0s
   150K .......... .......... .......... .......... .......... 12% 48.3M 0s
   200K .......... .......... .......... .......... .......... 15% 46.9M 0s
   250K .......... .......... .......... .......... .......... 18% 75.8M 0s
   300K .......... .......... .......... .......... .......... 21% 98.3M 0s
   350K .......... .......... .......... .......... .......... 25% 51.8M 0s
   400K .......... .......... .......... .......... .......... 28%  125M 0s
   450K .......... .......... .......... .......... .......... 31% 62.2M 0s
   500K .......... .......... .......... .......... .......... 34%  123M 0s
   550K .......... .......... .......... .......... .......... 37% 36.3M 0s
   600K .......... .......... .......... .......... .......... 40% 76.3M 0s
   650K .......... .......... .......... .......... .......... 43% 95.2M 0s
   700K .......... .......... .......... .......... .......... 46% 48.6M 0s
   750K .......... .......... .......... .......... .......... 50% 99.3M 0s
   800K .......... .......... .......... .......... .......... 53% 66.3M 0s
   850K .......... .......... .......... .......... .......... 56%  117M 0s
   900K .......... .......... .......... .......... .......... 59% 42.2M 0s
   950K .......... .......... .......... .......... .......... 62%  123M 0s
  1000K .......... .......... .......... .......... .......... 65%  164M 0s
  1050K .......... .......... .......... .......... .......... 68%  133M 0s
  1100K .......... .......... .......... .......... .......... 71%  146M 0s
  1150K .......... .......... .......... .......... .......... 75%  182M 0s
  1200K .......... .......... .......... .......... .......... 78%  158M 0s
  1250K .......... .......... .......... .......... .......... 81%  161M 0s
  1300K .......... .......... .......... .......... .......... 84%  196M 0s
  1350K .......... .......... .......... .......... .......... 87%  170M 0s
  1400K .......... .......... .......... .......... .......... 90%  172M 0s
  1450K .......... .......... .......... .......... .......... 93%  212M 0s
  1500K .......... .......... .......... .......... .......... 96%  236M 0s
  1550K .......... .......... .......... .......... ......... 100%  234M=0.02s
2021-11-10 15:54:18 (79.8 MB/s) - ‘Ubuntu2010-8.0.tar’ saved [1637888/1637888]
Ubuntu2010-8.0/
Ubuntu2010-8.0/php_pdo_sqlsrv_80_nts.signature
Ubuntu2010-8.0/php_pdo_sqlsrv_80_nts.so
Ubuntu2010-8.0/php_pdo_sqlsrv_80_ts.signature
Ubuntu2010-8.0/php_pdo_sqlsrv_80_ts.so
Ubuntu2010-8.0/php_sqlsrv_80_nts.signature
Ubuntu2010-8.0/php_sqlsrv_80_nts.so
Ubuntu2010-8.0/php_sqlsrv_80_ts.signature
Ubuntu2010-8.0/php_sqlsrv_80_ts.so
-----> Discovering process types
       Procfile declares types -> web
-----> Compressing...
       Done: 36.6M
-----> Launching...
       Released v88
       https://nos-zero-stg.herokuapp.com/ deployed to Heroku

🤦‍♂️ 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