Metainformationen zur Seite
  •  

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
server:dwb [2024/12/09 09:39] – [Firewallregeln] walbaumserver:dwb [2025/10/23 10:51] (aktuell) – [Externe Verbindungseinstellungen für Endbenutzer (z.B. über DiversityCollection / Client): smns.diversityworkbench.de Port 7878] schuhmann
Zeile 1: Zeile 1:
 +====== Externe Verbindungseinstellungen für Endbenutzer (z.B. über DiversityCollection / Client): smns.diversityworkbench.de Port 7878 ======
 +Download der Clients: https://www.diversityworkbench.de/manual/dwb_latest/modules/index.html
 +
 ====== DWB im SMNS ====== ====== DWB im SMNS ======
  
-  ; OS : Linux Debian 12  +; ; OS : : Linux Debian 12 ; ; Hostname : : lserver-dwb01 ; ; IP DMZ : : 172.31.13.31 ; ; User : : dwbdebby ; ; Passwort : : <decrypt>U2FsdGVkX18LX4h/SQIKwZiSgSlkAeYHqb8K04oLOPNUb25lXhHeawJHgS1bBrLK</decrypt> 
-  ; Hostname : lserver-dwb01  + 
-  ; IP DMZ : 172.31.13.31 +===== Systemsetup ===== 
-  ; User : dwbdebby  + 
-  ; Passwort : <decrypt>U2FsdGVkX18LX4h/SQIKwZiSgSlkAeYHqb8K04oLOPNUb25lXhHeawJHgS1bBrLK</decrypt>+Zwei redundante Server (Komponenten doppelt aufgeführt):\\ 
 +{{:server:dwb-hardware.png}}
  
 ===== Festplatte/Partitionierung ===== ===== Festplatte/Partitionierung =====
  
-Installation Debian auf 8TB (7,7) NVMe nvme0n1, Bilder liegen in /var/log/installer/.\\+Installation Ubuntu auf 8TB (7,7) NVMe nvme0n1, Bilder liegen in /var/log/installer/.\\
 LVM (Logical Volume Manager) mit Verschlüsselung.\\ LVM (Logical Volume Manager) mit Verschlüsselung.\\
 \\ \\
Zeile 202: Zeile 206:
   mssql-network:   mssql-network:
     driver: bridge     driver: bridge
 +
 </code> </code>
  
Zeile 308: Zeile 313:
  
 <WRAP round box 100% center> <WRAP round box 100% center>
 +
 <code> <code>
 #Bypass an der DMZ vorbei #Bypass an der DMZ vorbei
Zeile 321: Zeile 327:
     gateway 172.31.13.254     gateway 172.31.13.254
     dns-nameservers 172.31.13.254     dns-nameservers 172.31.13.254
 +
 </code> </code>
 +
 </WRAP> </WRAP>
  
Zeile 328: Zeile 336:
 <code> <code>
 systemctl restart networking systemctl restart networking
 +
 </code> </code>
  
Zeile 334: Zeile 343:
 <code> <code>
 ip link set dev eno2np1 up ip link set dev eno2np1 up
 +
 </code> </code>
  
 IP hinzufügen: IP hinzufügen:
 +
 <code> <code>
 ip address add 172.31.13.31/24 dev enp129s0f1np1 ip address add 172.31.13.31/24 dev enp129s0f1np1
 +
 </code> </code>
  
 IP entfernen: IP entfernen:
 +
 <code> <code>
 ip address del 172.31.13.31/24 dev enp129s0f1np1 ip address del 172.31.13.31/24 dev enp129s0f1np1
 +
 </code> </code>
  
Zeile 350: Zeile 364:
 <code> <code>
 ip addr show ip addr show
 +
 </code> </code>
  
 <WRAP center round box 100%> <WRAP center round box 100%>
 +
 <code> <code>
 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
Zeile 402: Zeile 418:
     inet6 fe80::646e:ccff:feb4:cfee/64 scope link     inet6 fe80::646e:ccff:feb4:cfee/64 scope link
        valid_lft forever preferred_lft forever        valid_lft forever preferred_lft forever
 +
 </code> </code>
 +
 </WRAP> </WRAP>
  
 ==== Firewallregeln ==== ==== Firewallregeln ====
  
-<code>sudo iptables -S</code>+<code> 
 +sudo iptables -S 
 + 
 +</code>
  
 <WRAP center round box 100%> <WRAP center round box 100%>
 +
 <code> <code>
--P INPUT ACCEPT +*filter 
--P FORWARD DROP +:INPUT ACCEPT [597730509:430440880807] 
--P OUTPUT ACCEPT +:FORWARD DROP [245:12128] 
--N DOCKER +:OUTPUT ACCEPT [175230482:78194972432] 
--N DOCKER-ISOLATION-STAGE-1 +:DOCKER - [0:0] 
--N DOCKER-ISOLATION-STAGE-2 +:DOCKER-ISOLATION-STAGE-1 - [0:0] 
--N DOCKER-USER +:DOCKER-ISOLATION-STAGE-2 - [0:0] 
--N f2b-sshd+:DOCKER-USER - [0:0] 
 +:f2b-sshd - [0:0]
 -A INPUT -p tcp -m tcp --dport 7878 -j LOG --log-prefix "[netfilter] " -A INPUT -p tcp -m tcp --dport 7878 -j LOG --log-prefix "[netfilter] "
 -A INPUT -s 45.134.26.0/24 -j DROP -A INPUT -s 45.134.26.0/24 -j DROP
Zeile 425: Zeile 448:
 -A INPUT -p tcp -m tcp --dport 7878 -j ACCEPT -A INPUT -p tcp -m tcp --dport 7878 -j ACCEPT
 -A INPUT -p tcp -m tcp --dport 9175 -m conntrack --ctstate NEW,ESTABLISHED -m comment --comment "Allow Prometheus Node Exporter scraping" -j ACCEPT -A INPUT -p tcp -m tcp --dport 9175 -m conntrack --ctstate NEW,ESTABLISHED -m comment --comment "Allow Prometheus Node Exporter scraping" -j ACCEPT
 +-A INPUT -s 108.181.2.0/24 -j DROP
 +-A INPUT -s 108.181.24.0/24 -j DROP
 +-A INPUT -s 108.181.3.0/24 -j DROP
 +-A INPUT -s 185.11.61.0/24 -j DROP
 +-A INPUT -s 188.127.242.0/24 -j DROP
 +-A INPUT -s 193.143.1.0/24 -j DROP
 +-A INPUT -s 198.144.158.0/24 -j DROP
 +-A INPUT -s 198.144.159.0/24 -j DROP
 +-A INPUT -s 199.167.138.0/24 -j DROP
 +-A INPUT -s 199.19.95.0/24 -j DROP
 +-A INPUT -s 208.87.242.0/24 -j DROP
 +-A INPUT -s 45.134.26.0/24 -j DROP
 +-A INPUT -s 45.135.232.0/24 -j DROP
 +-A INPUT -s 45.140.17.0/24 -j DROP
 +-A INPUT -s 45.148.121.0/24 -j DROP
 +-A INPUT -s 45.93.201.0/24 -j DROP
 +-A INPUT -s 80.66.76.0/24 -j DROP
 +-A INPUT -s 80.66.88.0/24 -j DROP
 +-A INPUT -s 85.209.11.0/24 -j DROP
 +-A INPUT -s 87.251.67.0/24 -j DROP
 +-A INPUT -s 87.251.75.0/24 -j DROP
 +-A INPUT -s 89.248.165.0/24 -j DROP
 +-A INPUT -s 185.242.162.125/32 -j DROP
 +-A INPUT -s 193.143.1.36/32 -j DROP
 +-A INPUT -s 45.140.17.52/32 -j DROP
 +-A INPUT -s 45.130.145.28/32 -j DROP
 -A FORWARD -p tcp -m tcp --dport 7878 -j LOG --log-prefix "[netfilter] " -A FORWARD -p tcp -m tcp --dport 7878 -j LOG --log-prefix "[netfilter] "
 -A FORWARD -j DOCKER-USER -A FORWARD -j DOCKER-USER
Zeile 466: Zeile 515:
 -A f2b-sshd -j RETURN -A f2b-sshd -j RETURN
  
-:PREROUTING ACCEPT [2214751:328875041] +*nat 
-:INPUT ACCEPT [2652476:345973188] +:PREROUTING ACCEPT [4717263:449324127] 
-:OUTPUT ACCEPT [735876:44381116] +:INPUT ACCEPT [10647614:782359454] 
-:POSTROUTING ACCEPT [359643:20612160]+:OUTPUT ACCEPT [6379770:383273186] 
 +:POSTROUTING ACCEPT [4549362:269602758]
 :DOCKER - [0:0] :DOCKER - [0:0]
 -A PREROUTING -p tcp -m tcp --dport 7879 -m comment --comment "Make Diversity Workbench SQL Server accessible from outside" -j DNAT --to-destination 172.32.23.31:5433 -A PREROUTING -p tcp -m tcp --dport 7879 -m comment --comment "Make Diversity Workbench SQL Server accessible from outside" -j DNAT --to-destination 172.32.23.31:5433
Zeile 485: Zeile 535:
 -A DOCKER -i br-b3f2654f0e35 -j RETURN -A DOCKER -i br-b3f2654f0e35 -j RETURN
 -A DOCKER ! -i br-e51ea62201df -p tcp -m tcp --dport 5432 -j DNAT --to-destination 172.18.0.2:1433 -A DOCKER ! -i br-e51ea62201df -p tcp -m tcp --dport 5432 -j DNAT --to-destination 172.18.0.2:1433
-COMMIT+
 </code> </code>
 +
 </WRAP> </WRAP>
  
Zeile 492: Zeile 543:
  
 Regel hinzufügen, z.B.: Regel hinzufügen, z.B.:
 +
 <code> <code>
 iptables -A INPUT -i eno2np1 -p tcp --dport 7878 -j ACCEPT iptables -A INPUT -i eno2np1 -p tcp --dport 7878 -j ACCEPT
 +
 </code> </code>
  
 Speicherbar machen: Speicherbar machen:
 +
 <code> <code>
 sudo apt-get update sudo apt-get update
 sudo apt-get install iptables-persistent sudo apt-get install iptables-persistent
 +
 </code> </code>
  
 Backup Existing Rules: Backup Existing Rules:
 +
 <code> <code>
-sudo iptables-save > ~/iptables_backup.txt+sudo iptables-save> ~/iptables_backup.txt 
 </code> </code>
  
-Mehr dazu: https://wiki.ubuntuusers.de/iptables/ +Mehr dazu: [[https://wiki.ubuntuusers.de/iptables/|https://wiki.ubuntuusers.de/iptables/]]\\
 === IP Blacklist aus München === === IP Blacklist aus München ===
- 
 <code> <code>
 +
 sudo iptables -A INPUT -s 108.181.2.0/24 -j DROP sudo iptables -A INPUT -s 108.181.2.0/24 -j DROP
 sudo iptables -A INPUT -s 108.181.24.0/24 -j DROP sudo iptables -A INPUT -s 108.181.24.0/24 -j DROP
Zeile 538: Zeile 594:
 sudo iptables -A INPUT -s 45.140.17.52 -j DROP sudo iptables -A INPUT -s 45.140.17.52 -j DROP
 sudo iptables -A INPUT -s 45.130.145.28 -j DROP sudo iptables -A INPUT -s 45.130.145.28 -j DROP
 +
 </code> </code>
-===== fail2ban =====+ 
 +===== fail2ban - läuft noch nicht richtig =====
  
 <code> <code>
Zeile 607: Zeile 665:
 </code> </code>
  
-===== Backups einspielen =====+===== Backups in die DWB einspielen =====
  
 Backups auf den Server kopieren: Backups auf den Server kopieren:
Zeile 715: Zeile 773:
  
 In /opt/dwb-backup läuft backup_script.sh und erstellt jede Nacht um 0:00 Uhr ein Komplettabbild der Datenbank in /mnt/mssql_data/backup/full/, jede volle Stunde zwischen 7:00 und 18:00 Uhr ein inkrementelles Abbild in /mnt/mssql_data/backup/diff/. Für dieses Skript müssen auf dem Server mssql-tools installiert werden! In /opt/dwb-backup läuft backup_script.sh und erstellt jede Nacht um 0:00 Uhr ein Komplettabbild der Datenbank in /mnt/mssql_data/backup/full/, jede volle Stunde zwischen 7:00 und 18:00 Uhr ein inkrementelles Abbild in /mnt/mssql_data/backup/diff/. Für dieses Skript müssen auf dem Server mssql-tools installiert werden!
 +
 <code> <code>
 #!/bin/bash #!/bin/bash
Zeile 730: Zeile 789:
  
     IGNORE_DB="tempdb model"     IGNORE_DB="tempdb model"
-    DB_LIST=$(sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -h -1 -Q 'SET NOCOUNT ON;SELECT name FROM sys.databases;')+    DB_LIST=$(sqlcmd -S 172.31.13.31,5432 -U BackupAdmin -P 'StrongPassword!123' -h -1 -Q 'SET NOCOUNT ON;SELECT name FROM sys.databases;')
     for db in $DB_LIST     for db in $DB_LIST
     do     do
Zeile 743: Zeile 802:
         if [ "$skipdb" = "-1" ]; then         if [ "$skipdb" = "-1" ]; then
             SQL_FILE="${db}_full_${NOW}"             SQL_FILE="${db}_full_${NOW}"
-            sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -Q "BACKUP DATABASE [${db}] TO DISK=N'${BACKUP_PATH}/full/${SQL_FILE}.bak' WITH NAME='Full backup of ${db}',INIT,COMPRESSION,CHECKSUM,FORMAT"+            sqlcmd -S 172.31.13.31,5432 -U BackupAdmin -P 'StrongPassword!123' -Q "BACKUP DATABASE [${db}] TO DISK=N'${BACKUP_PATH}/full/${SQL_FILE}.bak' WITH NAME='Full backup of ${db}',INIT,COMPRESSION,CHECKSUM,FORMAT"
             if [ $? -eq 0 ]             if [ $? -eq 0 ]
             then             then
-                printf "${SQL_FILE}.bak written" >> $LOG_FILE+                printf "${SQL_FILE}.bak written">> $LOG_FILE
                 chmod g+r "${BACKUP_PATH_HOST}/full/${SQL_FILE}.bak"                 chmod g+r "${BACKUP_PATH_HOST}/full/${SQL_FILE}.bak"
             else             else
-                printf "WARNING: An error occured while attempting to write {$db}" >> $LOG_FILE+                printf "WARNING: An error occured while attempting to write {$db}">> $LOG_FILE
             fi             fi
         fi         fi
Zeile 763: Zeile 822:
  
     IGNORE_DB="tempdb master model"     IGNORE_DB="tempdb master model"
-    DB_LIST=$(sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -h -1 -Q 'SET NOCOUNT ON;SELECT name FROM sys.databases;')+    DB_LIST=$(sqlcmd -S 172.31.13.31,5432 -U BackupAdmin -P 'StrongPassword!123' -h -1 -Q 'SET NOCOUNT ON;SELECT name FROM sys.databases;')
     for db in $DB_LIST     for db in $DB_LIST
     do     do
Zeile 776: Zeile 835:
         if [ "$skipdb" = "-1" ]; then         if [ "$skipdb" = "-1" ]; then
             SQL_FILE="${db}_diff_${NOW}"             SQL_FILE="${db}_diff_${NOW}"
-            sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -Q "BACKUP DATABASE [${db}] TO DISK=N'${BACKUP_PATH}/diff/${SQL_FILE}.bak' WITH NAME='diff backup of ${db}',DIFFERENTIAL"+            sqlcmd -S 172.31.13.31,5432 -U BackupAdmin -P 'StrongPassword!123' -Q "BACKUP DATABASE [${db}] TO DISK=N'${BACKUP_PATH}/diff/${SQL_FILE}.bak' WITH NAME='diff backup of ${db}',DIFFERENTIAL"
             if [ $? -eq 0 ]             if [ $? -eq 0 ]
             then             then
-                printf "${db}.bak written" >> $LOG_FILE+                printf "${db}.bak written">> $LOG_FILE
                 chmod g+r "${BACKUP_PATH_HOST}/diff/${SQL_FILE}.bak"                 chmod g+r "${BACKUP_PATH_HOST}/diff/${SQL_FILE}.bak"
             else             else
-                printf "WARNING: An error occured while attempting to write ${SQL_FILE}" >> $LOG_FILE+                printf "WARNING: An error occured while attempting to write ${SQL_FILE}">> $LOG_FILE
             fi             fi
         fi         fi
Zeile 789: Zeile 848:
     rm "${BACKUP_PATH_HOST}/diff/.in_process"     rm "${BACKUP_PATH_HOST}/diff/.in_process"
 } }
- 
  
 if [ "$1" = "full"  ]; then if [ "$1" = "full"  ]; then
     LOG_FILE="${BACKUP_PATH_HOST}/full/backup-db_${NOW}.log"     LOG_FILE="${BACKUP_PATH_HOST}/full/backup-db_${NOW}.log"
-    printf "Start full backup of MSSQL Server lserver-dwb01: $(date "+%F %T")" > $LOG_FILE+    printf "Start full backup of MSSQL Server lserver-dwb01: $(date "+%F %T")"> $LOG_FILE
     sqlserver_dwb_full     sqlserver_dwb_full
-    printf "Finish backup utility: $(date "+%F %T")\n\n" >> "$LOG_FILE"+    printf "Finish backup utility: $(date "+%F %T")\n\n">> "$LOG_FILE"
 fi fi
 if [ "$1" = "diff"  ]; then if [ "$1" = "diff"  ]; then
     LOG_FILE="${BACKUP_PATH_HOST}/diff/backup-db_`date "+%u"`.log"     LOG_FILE="${BACKUP_PATH_HOST}/diff/backup-db_`date "+%u"`.log"
-    echo "Start differential backup of MSSQL Server lserver-dwb01: $(date "+%F %T")" >> $LOG_FILE+    echo "Start differential backup of MSSQL Server lserver-dwb01: $(date "+%F %T")">> $LOG_FILE
     sqlserver_dwb_diff     sqlserver_dwb_diff
-    printf "Finish backup utility: $(date "+%F %T")\n\n" >> "$LOG_FILE"+    printf "Finish backup utility: $(date "+%F %T")\n\n">> "$LOG_FILE"
 fi fi
 +
 </code> </code>
  
 Crontab (crontab -e) mit PATH, sonst ist 'sqlcmd' nicht bekannt: Crontab (crontab -e) mit PATH, sonst ist 'sqlcmd' nicht bekannt:
 <code> <code>
 +
 PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/opt/mssql-tools/bin PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/opt/mssql-tools/bin
  
-0 0 * * * /opt/dwb-backup/backup_script.sh full > /var/log/backup_script_full.log 2>&1 +0 0 * * * /opt/dwb-backup/backup_script.sh full> /var/log/backup_script_full.log 2>&1 
-0 7-18 * * * /opt/dwb-backup/backup_script.sh diff > /var/log/backup_script_diff.log 2>&1+0 7-18 * * * /opt/dwb-backup/backup_script.sh diff> /var/log/backup_script_diff.log 2>&1 
 </code> </code>
  
 ===== Prometheus Daten ===== ===== Prometheus Daten =====
  
-Die Healthdaten des Servers werden via [[server:nodeex|Node Exporter]] an [[server:prometheus|Prometheus]] auf Hetzner freigegeben.+Die Healthdaten des Servers werden via [[:server:nodeex|Node Exporter]] an [[:server:prometheus|Prometheus]] auf Hetzner freigegeben. 
 +