How to connect QNAP Web server to Microsoft MS SQL

Question

I'm trying to configure my QNAP TS-231P Web server to connect to a MS SQL database installed on a Windows server (MSSQL Express 2014).

In QNAP Control Panel -> Server Web, I added in the php.ini file this line

extension = mssql.so

and now running phpinfo() I see the mssql section (Library version is FreeTDS).

My web application, built on CodeIgniter 3.1.9, is unable to connect to MSSQL (it works on XAMPP ver 3.2.2 installed on Windows), I also tried this simple php code:

<?php
$connection = mssql_connect('10.10.10.100\SQLEXPRESS', 'sa', 'mypassword');
if (!$connection) {
  die('Unable to connect!');
}
if (!mssql_select_db('MY_DATABASE', $connection)) {
  die('Unable to select database!');
}
$result = mssql_query('SELECT * FROM MY_TABLE');
while ($row = mssql_fetch_array($result)) {
  var_dump($row);
}
mssql_free_result($result);
?>

but the connection fails. The question is what can I do for getting a successful connection ?

You really shouldn't be using the `sa` account for your Web Service. I (**very**) strongly suggest you create a login and user with only the permissions your website needs, and connect using that account. Having a website with full `sysadmin` privileges is a terrible idea. — Thom A, Dec 21 '18 at 14:41
You're right, but I'm using sysadmin privileges just in production, first of all I need to get a functioning connection. — Davide, Dec 22 '18 at 08:09
MS SQL Server have to have enabled remote connections. Have you set it? How? See: [Configure the remote access Server Configuration Option](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-2017) — Maciej Los, Jan 16 '19 at 21:50

score 0 · Answer 1 · answered Jan 17 '19 at 11:04

Based on information from php.net:

mssql_connect

Warning

This function was REMOVED in PHP 7.0.0.

Rather than it, you should use sqlsrv_connect:

To be able to connect to MS SQL Server, that server have to accept remote connections. See: Configure the remote access Server Configuration Option

When you use IP, you have to provide a port number on which MS SQL Server is listening remote connections.

<?php
$serverName = "serverName\\sqlexpress, 1542"; //serverName\instanceName, portNumber (default is 1433)
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

More about connection to MS SQL Server in this thread: How to use PHP to connect to sql server

Good luck!

How to connect QNAP Web server to Microsoft MS SQL

1 Answers1