Azure VM not receiving data from Externally Hosted SQL Server
I have an Azure VM that hosts a PHP web application and a MySQL server.
One of the processes required is to retrieve data from a third-party instance of MS SQL Server. To facilitate this, I have installed the version of ODBC Driver for SQL Server (5.10.1) that is appropriate for the installed version of PHP (7.4.3).
I connect to the database and retrieve data using the following code:
class mssql {
protected dbConn;
public function open_db($server, $user, $password, $database, $throwError=false)
{
global $_REGISTRY;
ini_set("max_execution_time","0");
ini_set("max_input_time","0");
ini_set("memory_limit","-1" );
$errorMsg = "MSSQL_ERROR: Cannot connect to MsSQL database!\n";
$serverName = $server;
$connectionInfo = array(
'UID' => $user,
'PWD' => $password,
'DATABASE' => $database
);
$this->dbConn = \sqlsrv_connect($serverName, $connectionInfo);
if (!$this->dbConn) {
$_REGISTRY['STATE'] = 'FATAL';
$_REGISTRY['ERROR_MESSAGE'] = 'DATABASE_CONNNECTION_ERROR';
if($throwError) throw new \ErrorException( $errorMsg, 10001, 0,__file__, __line__ );
else return $errorMsg;
}
return true;
}
public function query( $sql, $throwError=true )
{
if(!$this->dbConn ) {
$errorMsg = "MSSQL_ERROR: No database connection";
if( $throwError) throw new \ErrorException( $errorMsg, 10001, 0,__file__, __line__ );
else return $errorMsg;
}
if(!$throwError) {
\sqlsrv_configure("WarningReturnAsErrors", 0);
}
$stmt = \sqlsrv_prepare($this->dbConn, $sql, array(), array('Scrollable'=>SQLSRV_CURSOR_STATIC));
if($stmt){
$result = \sqlsrv_execute($stmt);
if($result){
return $stmt;
} else {
if( ($errors = \sqlsrv_errors()) != null ){
$queryError = "MSSQL_ERROR: \n" . print_r(\sqlsrv_errors(), true) . "\n\n<pre>$sql</pre>\n";
if ($throwError) throw new \Exception( $queryError ); else return $queryError;
}
}
} else {
if( ($errors = \sqlsrv_errors()) != null ){
$queryError = "MSSQL_ERROR: \n" . print_r(\sqlsrv_errors(), true) . "\n\n<pre>$sql</pre>\n";
if ($throwError) throw new \Exception( $queryError ); else return $queryError;
}
}
}
public function fetch_data( $sql, $throwError=true )
{
$res = $this->query( $sql, $throwError );
// Throwing an error with null data returned.
if($res) {
$queryError = 'MSSQL_ERROR: ' . \sqlsrv_errors() . "\n\n$sql\n";
file_put_contents('/var/tmp/mssql.log', "fetchData Res: $queryError\n", FILE_APPEND );
try{
if(\sqlsrv_num_rows($res)) {
$ar = array();
while( $row = \sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC ) ) {
array_push($ar, $row);
}
return $ar;
}
return false;
} catch( \Exception $e ) { throw new \Exception( $queryError );}
} else {
throw new \Exception( "Invalid response from Server" );
}
}
}
// The mssql class is used like so:
$mssqlObj = new mssql();
$mssqlObj->open_db($server_url, $user_name, $user_password, $database_name);
$results_array = $mssqlObj->fetch_data($sql);
Unfortunately, the data is not retrieved properly.
The query I have been using to test this is supposed to return a result set of 641 rows.
sqlsrv_num_rows($res)
gives the expected number, but the row retrieval:
$ar = array();
while( $row = \sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC ) ) {
array_push($ar, $row);
}
return $ar;
only returns 19 rows.
I have tried explicitly retrieving each row in the result set like so:
$ar = array();
for( $i = 0; $i < \sqlsrv_num_rows($res); $i++){
\sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC, SQLSRV_SCROLL_ABSOLUTE, $i);
array_push($ar, $row);
}
return $ar;
But that has resulted in more than half of the results being NULL
rows.
Running the same query through PHP on my local machine gives the proper result set, which tells me it is an issue with the Azure VM configuration.
The Network Settings panel has rules that allow both inbound and outbound connections to the external MS SQL Server on port 1433, and running a tcpdump
command to capture the traffic during a query shows that no packages have been dropped. But the Metrics Panel shows that only 91 KiB have been transferred in over the network, while the data in the result set is 1.7 MiB not including the data in the TCP frames.
telnet
ing to the remote database on port 1433 gives
> telnet [remote Hostname] 1433
trying [remote IP]...
Connected to [remote Hostname].
Escape character is '^]'.
while running a ping to the remote results in nothing given back:
> ping [remote Hostname]
PING [remote Hostname] ([remote IP]) 56(84) bytes of data.
^C
--- [remote Hostname] ping statistics ---
68 packets transmitted, 0 received, 100% packet loss, time 68587ms
I also do not have a ufw configured or active.
Any help to identify the cause of the issue is appreciated