Problem/Motivation
When I set up an MS SQL connection as an additional database, I am unable to connect to it from Drupal.
Steps to reproduce
I have a mssql service and have provided sqlsrv to PHP service via Lando:
services:
appserver:
build_as_root:
- apt-get update -y
- apt-get install apt-transport-https -y
- curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
- curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
- apt-get update -y
- ACCEPT_EULA=Y apt-get install -y msodbcsql18
- apt-get install unixodbc-dev -y
- pecl install sqlsrv
- pecl install pdo_sqlsrv
- docker-php-ext-enable sqlsrv
- docker-php-ext-enable pdo_sqlsrv
dest-mssql:
type: mssql
tooling:
sqlcmd:
service: dest-mssql
I've added the database via settings.php:
$lando_info = json_decode(getenv('LANDO_INFO'), TRUE)['dest-mssql'];
$databases['migrate']['default'] = [
'database' => 'my_database',
'username' => $lando_info['creds']['user'],
'password' => $lando_info['creds']['password'],
'prefix' => '',
'host' => $lando_info['internal_connection']['host'],
'port' => $lando_info['internal_connection']['port'],
'namespace' => 'Drupal\\sqlsrv\\Driver\\Database\\sqlsrv',
'autoload' => 'modules/contrib/sqlsrv/src/Driver/Database/sqlsrv',
'driver' => 'sqlsrv',
];
And I have proven the PHP connection capability from web/index.php:
$dbh = new \PDO(
"sqlsrv:Server=dest-mssql;Database=my_database;TrustServerCertificate=true",
"sa",
"he11oTHERE",
[
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
]
);
$dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$sql = "SELECT TOP 5 Title FROM ContentRecords";
$sth = $dbh->prepare($sql);
$sth->execute();
$row = $sth->fetch(\PDO::FETCH_ASSOC);
print_r($row);
die();
The above produces the expected results, proving PHP is able to connect.
However, any attempt to bring up the connection from Drupal code produces:
$ lando drush php
> $connection = Drupal\Core\Database\Database::getConnection('migrate');
= Drupal\mysql\Driver\Database\mysql\Connection {#796}
Notice the driver is MySQL.
Any attempt to query produces a Base table or view not found:
> $query = $connection->select('ContentRecords', 'c');
= Drupal\mysql\Driver\Database\mysql\Select {#10943
+alterMetaData: ? array,
+alterTags: ? array,
}
> $query->addField('c', 'Title');
= "Title"
> $query->range(0,5);
= Drupal\mysql\Driver\Database\mysql\Select {#10943
+alterMetaData: ? array,
+alterTags: ? array,
}
> $result = $query->execute();
Drupal\Core\Database\DatabaseExceptionWrapper SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database.ContentRecords' doesn't exist: SELECT "c"."Title" AS "Title"
FROM
"ContentRecords" "c"
LIMIT 5 OFFSET 0; Array
(
).
Proposed resolution
Ensure ability to connect to the MS SQL database as an additional database in Drupal.