Question MS SQL Server Instance on Remote Server

All about webserver configurations, PHP and databases.

Moderator: Thorsten

Post Reply
Marco23
Posts: 7
Joined: Fri Aug 23, 2013 2:51 pm

Question MS SQL Server Instance on Remote Server

Post by Marco23 »

Hi,

I have an MS SQL 2008 R2 installation on a remote server <server name>, the SQL server instance is not the default instance, i.e.
<server name>\<instance name>
When I enter this under "Database Host", I get an error that PHPmyFAQ cannot connect to the DB.
Is this not a supported setup or am missing something obvious here? There are several SQL server instances on the server so I cannot just enter the hostname. Also the port is not the standard MS SQL server port.

Thanks for your help.

Regards,

Marco
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Question MS SQL Server Instance on Remote Server

Post by Thorsten »

Hi,

never tried this configuration. For the port, currently only the standard port is supported. We could try to install it manually if you like.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Marco23
Posts: 7
Joined: Fri Aug 23, 2013 2:51 pm

Re: Question MS SQL Server Instance on Remote Server

Post by Marco23 »

Hi Thorsten,

First of all thanks for your help.

"Never tried this configuration" means

1) Never tried with SQL on a remote server (this is a classic setup with web server on server A and DB on server B)?
or
2) Never tried with a named instance <server name>\<instance name>?
or
3) Never tried with a non-standard port?
or
4) all of the above?

If I want to do a manual install, what do I need to do?

Thanks,

Regards,

Marco
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Question MS SQL Server Instance on Remote Server

Post by Thorsten »

Hi,

2) & 3) is currently not possible with the setup script. 1) is quite common and works.

The easiest "manual" installation would be an installation on a "simple" MS SQL server without any instance and the normal port, then dump the DB and restore it in your machine and changing the content of config/database.php

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Marco23
Posts: 7
Joined: Fri Aug 23, 2013 2:51 pm

Re: Question MS SQL Server Instance on Remote Server

Post by Marco23 »

Hello Thorsten,

Thanks. I will try this.

Regards,

Marco
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Question MS SQL Server Instance on Remote Server

Post by Thorsten »

HI,

you can also check install/setup.php, the installation is quite straight forward.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Marco23
Posts: 7
Joined: Fri Aug 23, 2013 2:51 pm

Re: Question MS SQL Server Instance on Remote Server

Post by Marco23 »

Hi,

After some trying I got it working but there is one thing which is bad but probably simple to fix:

I am getting this error:
Do you know that your Internet Explorer is out of date?
Please use Internet Explorer 8+, Mozilla Firefox 4+, Google Chrome, Apple Safari 5+ or Opera 11+


I am actually using IE 9 (!). Any ideas? I cannot use the FAQ as the layout is totally screwed. I am sure this is something small.

Some more tips if anybody tries this kind of setup:
What you need to configure the DB config with is: Hostname\Instancename,Portname
Actually this is just according to the documentation of sqlsrv_connect and mssql_pconnect.

One thing took me a long time to figure out and that was the error message when connecting to the DB even after I had set the configuration correctly.
The error message of the database server: Array.
Well, I wrote a script and found that the complete error message is:

Connection could not be established.
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49

Code: Select all

 => -49 [2] => This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 [/i]

I installed the client and I could connect to the DB.

Somehow your error handler does not seem to be displaying the entire message. I was really puzzling for a long time.

I still find it strange that I had to install this client as I had installed the MS DLL for MS SQL server and had included that in the PHP.INI. phpinfo was als displaying the
sqlsvr extension as installed. Weird.

My setup:

Host A: SQL Server 2008 R2 on MS 2008 R2 cluster
Host B: IIS 7.5 on Windows 2008 R2 Standard, PHP 5.5.2, Microsoft SQL Server 2012 Native Client

Regards,

Marco
Marco23
Posts: 7
Joined: Fri Aug 23, 2013 2:51 pm

Re: Question MS SQL Server Instance on Remote Server

Post by Marco23 »

Hi,

I have fixed that too.

Cause: I did an install with everything on the same machine (SQL and IIS) because I couldn't install on the "complex" setup I have.
And then just detached and reattached the DB to my production DB.
I found that in the table faqconfig the field main.referenceURL still contained a reference to my old setup.
I changed that and layout is now ok and no more mumbles about the IE version.

I hope everything is OK now and I will start testing. Thanks for your help so far.

Regards,

Marco
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Question MS SQL Server Instance on Remote Server

Post by Thorsten »

Hi,

the hint with the IE version occurs if you run IE in a compatibility mode. Great that it's working now, I'll fix the error handling.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Compuman
Posts: 17
Joined: Wed Feb 04, 2009 11:58 am
Location: Germany-Essen

Re: Question MS SQL Server Instance on Remote Server

Post by Compuman »

Hallo zusammen,

Ich versuche grade die Version 2.8.2 neu zu installieren. Ein Update der vorhandenen Installation möchte ich bewusst nicht machen.

Die phpmyfaq läuft auf einem Windows Server 2008R2 Enterprise.
Php5.3.25 und IIS 6 sind installiert.

Als Datenbank soll ein Separater Server mit SQL2012 genutzt werden

Wenn ich nun die Webseite öffne bekomme ich das Fenster zum Einrichten der Datenbank und das Setup für MyFAQ.
Unter Database Server habe ich aber nur die Einträge :
My SQL 5.x Maria DB 5.x (ext/mysqli)
My SQL 5.x (ext/mysql, Deprecated)
Postgre SQL 8.x
SQLite 3 (only PHP5.3+, Experiment)

Wenn ich einen dieser Einträge nutze, bekomme ich folgende Fehlermeldung:
The connection to the database server could not be established.
The error message of the database server:
2002: No connection could be made because the target machine actively refused it.



In dieser Liste vermisse ich aber den MSSQL Eintrag. Die Erweiterung von Microsoft zum Verbinden von PHP mit der SQL2012 habe ich auf dem Server installiert und in die PHP.ini eingetragen:
[PHP_EXIF]
extension=php_exif.dll
extension=php_pdo_sqlsrv_53_ts.dll

register_globals = off
safe_mode = off (recommended)
memory_limit = 64M
file_upload = on

Kannst du mir einen Tip geben, wie ich weiter komme?

Die Datenbankanbindung habe ich mit einer QDBC Verbindung getestet. Ich komme mit dem entspr. User und den Datenbankangaben auf die Datenbank darauf.

Vielen Dank im voraus.
Es grüßt freundlich

Markus
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Question MS SQL Server Instance on Remote Server

Post by Thorsten »

Hi,

du brauchst php_sqlsrv.dll im PHP, dann klappt es.

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Compuman
Posts: 17
Joined: Wed Feb 04, 2009 11:58 am
Location: Germany-Essen

Re: Question MS SQL Server Instance on Remote Server

Post by Compuman »

Vielen Dank.
Der Punkt hat jetzt geklappt.
So einfach kann es sein... :oops:
Es grüßt freundlich

Markus
Post Reply