0

For an MSSQL database at the IP 192.168.55.55 and a server name of MYDB2, what should my database settings be in the settings.php file? (Please note, the machine has more than one database server, so I need to specify more than just the IP when I connect.)

Here is a simple PHP script showing how I can connect to the database:

$info = array('Database' => 'my-test', 'UID' => 'my-user', 'PWD' => 'the-password');
$conn = sqlsrv_connect('192.168.55.55\\MYDB2', $info);

if ($conn) {
  print "connected";
}

However, if I set the "host" in Drupal's settings to "192.168.55.55\\MYDB2", the connection fails. In other words, this doesn't work:

$databases['default']['default'] = [
  'driver' => 'sqlsrv',
  'database' => 'my-db',
  'namespace' => 'Drupal\\sqlsrv\\Driver\\Database\\sqlsrv',
  'autoload' => 'modules/contrib/sqlsrv/src/Driver/Database/sqlsrv',
  'username' => 'my-user',
  'password' => 'the-password',
  'host' => '192.168.55.55\\MYDB2',
  'port' => 1433,
  'prefix' => '',
];

With the above Drupal settings, I get...

Drupal\Core\Database\DatabaseAccessDeniedException: SQLSTATE[28000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'my-user'.

Whereas, if I use the same Drupal settings, aside from the host value, to connect to an IP that has only one db server, the connection succeeds. Meanwhile, the simple PHP script can connect to both IPs successfully, so long as I include the "\\MYDB2" in the function parameter as show above when I am connecting to the IP that has multiple database servers.

So I am wondering if there is some other key in the array I should use for the "MYDB2" server name? How can I tell Drupal which specific database server I want to connect to?


Please note, I am aware that Drupal will be dropping support for MSSQL in version 11, and am aware that MSSQL is not ideal for working with Drupal. However, in my current situation, I have absolutely no choice but to use MSSQL for the project.

5
  • I am not asking how to connect to multiple databases. I am asking how to connect to a single database that resides at an IP that has multiple database servers. The first code sample in my question shows how this can be done in a simple PHP script -- I have confirmed the script works. So it is clearly possible to do in a PHP context. I am just not sure how to express this in the Drupal settings array.
    – arnoldbird
    Feb 12 at 20:26
  • Perhaps you could expand on the technical meaning of "it doesn't work", above, in the question.
    – cilefen
    Feb 12 at 20:38
  • I edited the question to include the message I see when the connection fails.
    – arnoldbird
    Feb 12 at 21:04
  • 1
    Does your sqlsrv_connect example work if you use $conn = sqlsrv_connect('192.168.55.55\\MYDB2,1433', $info);? That DSN (with the port number appended) is what the sqlsrv module builds to connect via PDO. I don't know that they're 100% comparable. A fairer test would be to use standard PDO in your plain PHP example, as that's what Drupal uses
    – Clive
    Feb 12 at 21:08
  • That's it! If I remove the port from the Drupal settings array, Drupal can connect. My host value was correct: '192.168.55.55\\MYDB2'. Many thanks.
    – arnoldbird
    Feb 12 at 21:18

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.