Unable to connect to Azure SQL Managed Instance

Questions
1

Silverstripe Version: 3.3.1

Question:
I am migrating my Silverstripe 3.3.1 site to AzureVM but am unable to connect to my new database on Azure SQL Managed Instance.
Current setup: Windows 2012 R2 , MS SQL server 2012
New Setup : AzureVM, Azure SQL Managed Instance

This has been a show stopper. Would really appreciate any kind advice on how to connect Silverstripe 3 to AzureSQL. I am using this connection string (not working). Thank you!

global $databaseConfig;
$databaseConfig = array(
‘type’ => ‘MSSQLAzureDatabase’,
‘server’ => ‘1234.database.windows.net’,
‘username’ => ‘tempadmin’,
‘password’ => ‘temppassword’,
‘database’ => ‘tempDB’,
‘windowsauthentication’ => ‘’,
‘path’ => ‘’
);

2

When you say the connection is “not working”, can you provide any more details? Is it timing-out, throwing an error of some kind, etc.?

This may not be a Silverstripe-specific question. If you’ve added the correct details to your config, then it would suggest that there’s no route from the web server to the database.

I’m not especially familiar with Azure. Have you set up all the routing, firewall rules, etc. to allow database access from the VM? Is the database user configured to allow access from the VM?

3

Hi DorsetDigital,

Thanks for replying. The firewall rules and necc access rights have been given to the VM to access the database.

I have used the php code below to successfully test that the VM is able to access AzureSQL database.

I set Silverstripe’s _config.php to use type “MSSQLAzureDatabase” instead of “MSSQLDatabase”.
Is this the correct type to access the database? I am not able to find any info on this.
Once again, thank you!

// PHP Data Objects(PDO) Sample Code:
try {
$conn = new PDO(“sqlsrv:server = tcp:1234.database.windows.net,1433; Database = tempDB”, “tempadmin”, “temppassword”);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
print(“Error connecting to SQL Server.”);
die(print_r($e));
}

5

Just casting a very quick eye over the Azure docs suggests that you should be able to access it as if it were MySQL. So try using MySQLPDODatabase as your class

4

I don’t think that’s going to work, no. Silverstripe supports a specific number of database drivers (see https://docs.silverstripe.org/en/3/getting_started/environment_management/#available-constants)

You need to set it to whatever AzureDB exposes itself as (eg. if it’s MySQL compatible, then you’d set it to use the MySQL driver, MSSQL compatible would use the MSSQL driver, etc.)

6

I changed it back to the previous type (MSSQLDatabase), which worked on my previous MS SQL server.
Got the following error:

[07-Nov-2019 18:01:43] Error at \framework\model\connect\DBConnector.php line 55: Uncaught SS_DatabaseException: Couldn’t run query:

USE “tempDB”

42000, 40508, [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]USE statement is not supported to switch between databases. Use a new connection to connect to a different database. (http://www.1234.com/dev/build/?flush=all)

8

Yes, tried using PDO as per your suggestion, but it is not compatible with the code.
Based on the error “Use statement is not supported to switch between databases”, I dont think Silverstripe 3 is compatible with Azure SQL managed instance for now.

7

That only makes sense is the Azure database is running as an MS SQL server. Have you tried the PDO type I suggested?