Host on windows IIS or an Apache server

[NathanielCJ99]

Wondering how/if I can host my web interface on IIS for windows. Or an Apache server.

Thanks.

[lovasoa]

Hello !

It is definitely possible and reasonable to host sqlpage behind a reverse proxy if you don't want to expose it directly.

We have a guide about how to do so using nginx on the official website: https://sql.ophir.dev/your-first-sql-website/nginx.sql

But since you mention IIS and Apache, here are instructions for these servers.

IIS

Official guide: https://learn.microsoft.com/en-us/iis/extensions/url-rewrite-module/reverse-proxy-with-url-rewrite-v2-and-application-request-routing

1. Install IIS:

   • Open Server Manager, click on "Manage" > "Add Roles and Features."
   • Follow the wizard to install IIS.

2. Install the URL Rewrite Module:

   • Download and install the URL Rewrite Module.

3. Configure the Reverse Proxy:

   • Open IIS Manager.
   • Select your website or create a new one.
   • Open "URL Rewrite" and add a new rule.
   • Choose "Reverse Proxy" and configure it as follows:
     • Inbound rules: Create a rule to rewrite incoming requests to your SQLPage instance.
     • Server address: Enter the address of your SQLPage instance, e.g., http://localhost:8080.

   Example rule:

   <rewrite>
     <rules>
       <rule name="ReverseProxyInboundRule1" stopProcessing="true">
         <match url="(.*)" />
         <action type="Rewrite" url="http://localhost:8080/{R:1}" />
       </rule>
     </rules>
   </rewrite>

4. Save and Apply:

   • Apply the rule and ensure your site is running.

Url rewriting

If you want to let your users access /products.sql?id=XXX through the nicer-looking url /products/XXX :

<configuration>
    <system.webServer>
        <rewrite>
            <rules>
                <rule name="RewriteProductsURL" stopProcessing="true">
                    <match url="^products/([^/]+)$" />
                    <action type="Rewrite" url="/products.sql?id={R:1}" appendQueryString="true" />
                </rule>
                <rule name="ProxyToSQLPage" stopProcessing="true">
                    <match url=".*" />
                    <action type="Rewrite" url="http://localhost:8080/{R:0}" appendQueryString="true" />
                </rule>
            </rules>
        </rewrite>
    </system.webServer>
</configuration>

Apache

1. Install Apache:

   • On Ubuntu, use: sudo apt install apache2
   • On Windows, you can use tools like XAMPP or WampServer.

2. Enable Required Modules:

   • Enable the proxy modules. On Ubuntu, you can use:

     sudo a2enmod proxy
     sudo a2enmod proxy_http
     sudo systemctl restart apache2

3. Configure the Reverse Proxy:

   • Edit the Apache configuration file for your site, typically located in /etc/apache2/sites-available/your-site.conf or httpd.conf for Windows.

   Example configuration:

   <VirtualHost *:80>
       ServerName example.com
   
       ProxyPreserveHost On
       ProxyPass / http://localhost:8080/
       ProxyPassReverse / http://localhost:8080/
   
       <Location />
           Require all granted
       </Location>
   </VirtualHost>

4. Save and Apply:

   • Save the configuration file.
   • On Ubuntu, enable the site and restart Apache:

     sudo a2ensite your-site.conf
     sudo systemctl restart apache2

URL Rewriting

If you need to rewrite URLs (e.g., remove .sql extensions or change URL parameters) :

• Use RewriteEngine in your configuration:

  <VirtualHost *:80>
      ServerName example.com
  
      RewriteEngine On
      RewriteRule ^/products/([^/]+)$ /products.sql?id=$1 [L]
  
      ProxyPreserveHost On
      ProxyPass / http://localhost:8080/
      ProxyPassReverse / http://localhost:8080/
  </VirtualHost>

This will let your users access /products.sql?id=XXX through the nicer-looking url /products/XXX.

Let me know if this helps, and feel free to ask if you need more detailed instructions or further assistance!

[NathanielCJ99]

This was helpful thank you!

[kryskool]

For IIS, it's better to use HttpPlatformHandler (when site start, IIS launch the SqlPage.exe)

Example of web.config file

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <handlers>
            <add name="httpPlatformHandler" path="*" verb="*" modules="httpPlatformHandler" resourceType="Unspecified" requireAccess="Script" />
        </handlers>
        <httpPlatform stdoutLogEnabled="true" stdoutLogFile=".\sqlpage.log" startupTimeLimit="20" processPath="C:\Program Files\SqlPage\SqlPage.exe" arguments="-c sqlpage.json">
            <environmentVariables>
                <environmentVariable name="SQLPAGE_LISTEN_ON" value="127.0.0.1:%HTTP_PLATFORM_PORT%" />
                <environmentVariable name="SQLPAGE_PORT" value="%HTTP_PLATFORM_PORT%" />
            </environmentVariables>
        </httpPlatform>
    </system.webServer>
</configuration>

https://learn.microsoft.com/en-us/iis/extensions/httpplatformhandler/httpplatformhandler-configuration-reference

[cbertelli]

It would be very useful to put at least the instructions for Apache httpd in the documentation besides nginx (https://sql-page.com/your-first-sql-website/apache.sql when it will exist ;.)).
Additionally, it would be useful to get instructions on how to reverse proxy into a directory (<location "/sqlpage">) instead of root of a dedicated host.

[cbertelli]

Thank you, this defines the interface which is enabled to listen not the host that can access it. I think I shall use the firewall for this.

[cbertelli]

I tried this configuration:

<VirtualHost *:80>
   ServerName example.com
   ProxyPreserveHost On
   ProxyPass /mydatabase http://another.host.com:8080/
   ProxyPassReverse /mydatabase http://another.host.com:8080/
   <Location />
       Require all granted
   </Location>
</VirtualHost>

I reached the site, but it was unable to load javascript and css files. The console shows these errors:

Failed to load resource: the server responded with a status of 404 (Not Found)
sqlpage.3e044654a284b0dc.js:1     Failed to load resource: the server responded with a status of 404 (Not Found)
sqlpage.745cd4232be41811.css:1     Failed to load resource: the server responded with a status of 404 (Not Found)

Any suggestions?

[lovasoa]

I'll publish a detailed example. I think what you are missing is the site_prefix configuration:

/etc/apache2/sites-available/my-site.conf

<VirtualHost *:80>
    ServerName my_website
    DocumentRoot "/var/www"

    ProxyPreserveHost On

    <Location />
        Require all granted
        Options Indexes FollowSymLinks
        AllowOverride None
    </Location>

    <Location /my_website>
        ProxyPass "http://sqlpage:8080/my_website"
        ProxyPassReverse "http://sqlpage:8080/my_website"
    </Location>
</VirtualHost> 

sqlpage.json

{
    "site_prefix": "/my_website"
}

[cbertelli]

Thank you very much, now it seems to work.
There are still some quirks:

Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'browsing-topics'.
Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'compute-pressure'.
Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'join-ad-interest-group'.
Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'private-state-token-issuance'.
Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'private-state-token-redemption'.
Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'run-ad-auction'.
Error with Permissions-Policy header: Origin trial controlled feature not enabled: 'attribution-reporting'.

Do you think I can solve them by changing my configuration?

[lovasoa]

You probably manually set a Permission-Policy with unsupported permissions somewhere.

To fix this, edit your site's config or .htaccess file and remove unsupported features from the Permissions-Policy header.

Check your Apache configuration files, such as /etc/apache2/sites-available/000-default.conf or .htaccess, for a line like this:

Header set Permissions-Policy "browsing-topics=(), compute-pressure=()"

Edit it to remove unsupported features:

Header set Permissions-Policy "geolocation=(), microphone=()"

Then restart Apache with:

sudo systemctl restart apache2

[lovasoa]

I added a SQLPAGE + apache configuration example to the examples.