Proxy SQL Services Reporting Server with HAProxy

Oct 6, 2020

—

in devops, Linux, Open Source Software, Tip of the day!

A common issue with SQL Server Reporting Services is to proxy the server so it is not exposed on the internet. This is difficult to do with nginx, apache, and others due to NTLM authentication, although nginx offers a paid version that supports NTLM authentication. One easy fix is to use HAProxy and use TCP mode.

A simple configuration like the following works well. Note that this configuration requires an SSL certificate (+key) and terminates SSL at the haproxy service.

global
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    stats socket /var/lib/haproxy/stats

    # utilize system-wide crypto-policies
    ssl-default-bind-ciphers PROFILE=SYSTEM
    ssl-default-server-ciphers PROFILE=SYSTEM

defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

frontend main
    bind *:80
    bind *:443 ssl crt /etc/haproxy/$path_to_cert_and_key_in_one_file
    option tcplog
    mode tcp
    default_backend             ssrs

backend ssrs
    mode tcp
    server  $ssrs_hostname $ssrs_ip_address:80 check

Comments

2 responses to “Proxy SQL Services Reporting Server with HAProxy”

kris

June 19, 2023

Hi
Is this a working example? to what values does this part expand?
server $ssrs_hostname $ssrs_ip_address:80 check
$ssrs_hostname = http://www.test1.com
$ssrs_ip_address:80 = 192.168.55.190:80
after installation, the link to ssrs looks like this:
http://192.168.55.190/Reports
and on the 192.168.55.190:80 pattern given in the example, it does not work
Josh

June 20, 2023

Yes, this is a working example.

Note that on the “server” line under “backend”, the $ssrs_hostname variable can be any valid string — that is just a label. The IP address must be a valid IP address of the SSRS server. If you had more than 1 SSRS server, it would be helpful to list each one, using a different label with their associated IP address.

A good example might be:

backend ssrs mode tcp server ssrs_server1 192.168.0.55:80 check

You would then navigate to the IP or hostname of your HAProxy service, which would then proxy your requests to the SSRS server. The IP and/or hostname of the HAProxy server would likely be different, when compared to your SSRS server.

Proxy SQL Services Reporting Server with HAProxy

Comments

2 responses to “Proxy SQL Services Reporting Server with HAProxy”

Leave a Reply