www.datasunrise.com DataSunrise Database Security 6.1.0 Administration Guide Windows DataSunrise Database Security A
Views 72 Downloads 0 File size 2MB
www.datasunrise.com
DataSunrise Database Security 6.1.0
Administration Guide Windows
DataSunrise Database Security Administration Guide (Windows) Copyright © 2015-2020, DataSunrise, Inc . All rights reserved. All brand names and product names mentioned in this document are trademarks, registered trademarks or service marks of their respective owners. No part of this document may be copied, reproduced or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, except as expressly allowed by law or permitted in writing by the copyright holder. The information in this document is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
iii
Contents Chapter 1: General Information.......................................................................... 5 Product Description.......................................................................................................................................... 5 Supported Databases and Features................................................................................................................5 DataSunrise Operation Modes.........................................................................................................................8 Sniffer Mode.......................................................................................................................................... 8 Proxy Mode........................................................................................................................................... 9 System Requirements...................................................................................................................................... 9 Useful Resources........................................................................................................................................... 10
Chapter 2: Deployment Topologies.................................................................. 11 Installing DataSunrise on a Database Server............................................................................................... 11 Proxy Mode......................................................................................................................................... 11 Sniffer Mode........................................................................................................................................ 12 Installing DataSunrise on a Separate Server................................................................................................ 12 Proxy Mode......................................................................................................................................... 12 Sniffer Mode........................................................................................................................................ 13
Chapter 3: DataSunrise Installation..................................................................14 Prerequisites...................................................................................................................................................14 DataSunrise Installation................................................................................................................................. 14 DataSunrise Installation Folder...................................................................................................................... 15 Starting DataSunrise...................................................................................................................................... 16 Running Multiple DataSunrise Instances on a Single Machine.....................................................................17 Installing a DataSunrise Service......................................................................................................... 17 Configuring the Dictionary Storage.......................................................................................... 17 Switching Between DataSunrise Instances Installed on the Same Machine...................................... 18 Removing DataSunrise Service.......................................................................................................... 18 Copying DataSunrise Settings to Another DataSunrise Instance..................................................................19 Upgrading DataSunrise.................................................................................................................................. 19 Downgrading DataSunrise............................................................................................................................. 20 Encrypting the Dictionary and the Audit Storage (PostgreSQL)....................................................................20 Restoring Access to the Web Console if the Password is Lost.................................................................... 21 DataSunrise Removal.................................................................................................................................... 21
Chapter 4: Multi-server Configuration (High Availability Mode).................... 22 Preparing Databases to be Used as a Dictionary/Audit Storage.................................................................. 22 Preparing a PostgreSQL Database.................................................................................................... 22 Preparing a MySQL Database............................................................................................................ 23 Adding a DataSunrise Server to HA Setup................................................................................................... 23 Reviewing Servers of an Existing HA Configuration..................................................................................... 25 Configuring a Remote Dictionary on AWS Using the AWS Secrets Manager.............................................. 26 Restoring the Configuration if your local_settings.db is Lost........................................................................ 27
Chapter 5: Always On........................................................................................ 28 Working with Always On Availability Group of SQL Server.......................................................................... 28 Configuring of the Firewall Inside the Azure Cloud for Maintenance of the SaaS SQL Azure.......................28
Issues with SQL Server Read-only Databases on AlwaysOn....................................................................... 29 Restoring Configuration if local_settings.db is Lost.......................................................................................31
Chapter 6: Frequently Asked Questions..........................................................33
1 General Information | 5
1 General Information 1.1 Product Description The introductory section of this chapter describes basic features, steps necessary for database protection and principles of DataSunrise operation. Protection of databases starts with selecting and configuring the database instance. In the process you also need to select the protection mode: Sniffer (passive protection) or Proxy (active database protection). You can additionally restrict access to your database(s) protected by DataSunrise web user interface using 2-factor authentication. DataSunrise’s functionality is based on a system of highly customizable and versatile policies (Rules) which control database protection. You can create rules for the following tools included in DataSunrise: • • •
DataSunrise Audit. DataSunrise logs all user actions, SQL queries and query results. DataSunrise Data Audit saves information on database users, user sessions, query code, etc. Data auditing results can be exported to an external system, such as SIEM. DataSunrise Security. DataSunrise analyzes database traffic, detects and blocks unauthorized queries and SQL injections on-the-fly. Alerts and reports on detected threats can be sent to network administrators or a security team (officer) via e-mail or instant messengers. DataSunrise Dynamic Masking. DataSunrise prevents sensitive data exposure thanks to its data masking tool. DataSunrise’s Dynamic Masking obfuscates output of sensitive data from a database by replacing it with random data or real-looking data on-the-fly.
The Static Masking feature replaces real data with a fake copy which enables you to create a fully protected testing and development environment out of your real production database. The Table Relations feature can build associations between database columns. As a result, all associated columns with sensitive data are linked and better organized. The Data Discovery tool enables you to search for database objects that contain sensitive data and quickly create Rules for these objects. The search can be done by the Lexicon, column names and data type. In addition, you can use Lua scripting. NLP (Natural Language Processing) Data Discovery enables you to search for sensitive data across database columns that contain unstructured data. For example, you can locate an email address in a text. Using the Table Relations feature you can see all the columns associated with the discovered columns. You can set up periodic task for DataSunrise to search for and protect newly added sensitive data. DataSunrise functionality allows companies to be compliant with national and international sensitive data protection regulations such as HIPAA, PCI DSS, ISO27001, CCPA, GDPR, SOX, KVKK. This is how the Compliance feature works. Databases are regularly searched for newly added sensitive data. As a result, database and sensitive data within are constantly protected. DataSunrise can generate PDF and CSV reports about audit and security events, data discovery, sessions, operation errors and system events.
1.2 Supported Databases and Features Supported database types and versions: • • • •
Amazon Aurora MySQL Amazon Aurora PostgreSQL Amazon DynamoDB Amazon Redshift
1 General Information | 6 • • • • • • • • • • • • • • • • • • • • •
Amazon S3 and other S3 protocol compatible file storage services like Minio and Aibaba OSS. Auditing and Data Masking of CSV, XML, JSON and unstructured files are supported Apache Hive 1.0+ Athena Cassandra 3.11.1- 3.11.2 (DB servers), 3.4.x ( CQL) Elasticsearch 5+ Greenplum 4.2+ IBM DB2 9.7+. Linux, Windows, UNIX and z/OS are supported Impala 2.x Informix 11+ MS SQL Server 2005+ MariaDB 5.1+ MongoDB 2.6+ MySQL 5.0+ (Xprotocol is supported too) Netezza 6.0+ Oracle Database 9.2+ Percona Server for MySQL 5.1+ PostgreSQL 7.4+ SAP HANA 1.0+ Sybase (coming soon) Teradata 13+ Vertica 7.0+
The table below lists the databases, supported by DataSunrise and features available for them. Please note that proxying both of encrypted and unencrypted traffic is supported for all types of databases. Supported features. Part 1 DB type
Database Activity Monitoring
Database Security
Dynamic Masking
Static Masking
Amazon Aurora MySQL
+
+
+
+
Amazon Aurora PostgreSQL
+
+
+
+
Amazon DynamoDB
+
+
+
Amazon Redshift
+
+
+
Amazon S3
+
Apache Hive
+
+
Athena
+
+
Cassandra
+
Elasticsearch
+
+ +
+
+
+
+
+
+
+
Greenplum
+
+
+
+
IBM DB2
+
+
+
+
Impala
+
+
+
+
Informix
+
+
+
+
MS SQL Server
+
+
+
+
MariaDB
+
+
+
+
MongoDB
+
+
+
MySQL
+
+
+
+
1 General Information | 7 DB type
Database Activity Monitoring
Database Security
Dynamic Masking
Static Masking
Netezza
+
+
+
+
Oracle Database
+
+
+
+
Percona Server for MySQL
+
+
+
+
PostgreSQL
+
+
+
+
SAP HANA
+
+
+
+
Teradata
+
+
+
+
Vertica
+
+
+
+
Sybase (coming soon)
Supported features. Part 2 DB type
Data Discovery
Authentication Proxy
Kerberos Authentication
Amazon Aurora MySQL
+
+
+
Amazon Aurora PostgreSQL +
+
+
Amazon DynamoDB
+
Amazon Redshift
+
Sniffer
Sniffing of encrypted traffic
+
Amazon S3 Apache Hive
+
+
+
Athena Cassandra
+
+
Elasticsearch
+
+
Greenplum
+
IBM DB2
+
+
+
+
+*
+
Impala
+
+
+
Informix
+
MS SQL Server
+
+
+
+
MariaDB
+
+
+
+
MongoDB
+
MySQL
+
+
+
+
Netezza
+
+
+
+
Oracle Database
+
+
+
Percona Server for MySQL
+
+
+
+
PostgreSQL
+
+
+
+
SAP HANA
+
+
+
+
+
+
+
Sybase (coming soon) Teradata
+
1 General Information | 8 DB type
Data Discovery
Authentication Proxy
Kerberos Authentication
Sniffer
Vertica
+
+
+
+
Sniffing of encrypted traffic
*Kerberos delegation is not supported
1.3 DataSunrise Operation Modes DataSunrise can be deployed in one of the following configurations: Sniffer mode or Proxy mode.
1.3.1 Sniffer Mode
When deployed in the Sniffer mode, DataSunrise is connected to a SPAN port of a network switch. Thus, it acts as a traffic analyzer capable to capture a copy of the database traffic from a mirrored port of the network switch.
Figure 1: Sniffer mode operation scheme. In this configuration, DataSunrise can be used only for "passive security" ("active security" features such as database firewall or masking are not supported in this mode). When deployed in the Sniffer mode, DataSunrise is capable to perform database activity monitoring only, because it can't modify database traffic in this configuration. Running DataSunrise in the Sniffer mode does not require any additional reconfiguring of databases or client applications. Sniffer mode can be used for data auditing purpose or for running DataSunrise in the Learning mode. Important: database traffic should not be encrypted. Check your database settings as some databases encrypt traffic by default. If you're operating an SQL Server database, do not use ephemeral ciphers. DataSunrise deployed in the sniffer mode does not support connections redirected to a random port (like Oracle). All network interfaces (the main and the one the database is redirected to) should be added to DataSunrise.
1 General Information | 9
1.3.2 Proxy Mode
When deployed in this configuration, DataSunrise works as an intermediary between a database server and its client applications. Thus it is able to process all incoming queries before redirecting them to a database server.
Figure 2: Proxy mode operation scheme. Proxy mode is for "active protection". DataSunrise intercepts SQL queries sent to a protected database by database users, checks if they comply with existing security policies, and audits, blocks or modifies the incoming queries or query results if necessary. When running in the Proxy mode, DataSunrise supports its full functionality: database activity monitoring, database firewall, both dynamic and static data masking are available. Important: We recommend to use DataSunrise in the proxy mode. It provides full protection and in this mode, DataSunrise supports processing of encrypted traffic and redirect connections (it is essential for Hana, Oracle, Vertica, MS SQL). For example, in SQL Server, redirects can occur when working with Azure SQL or AlwaysOn Listener.
1.4 System Requirements Before installing DataSunrise, make sure that your server meets the following requirements: Minimum hardware requirements: • • •
CPU: 2 cores RAM: 4 GB Available disk space: 1 GB for installation. 1+ GB for storing audit records if you're going to use local SQLite as the Audit Storage.
Recommended hardware configuration: Estimated database traffic volume
CPU cores*
RAM, GB
Up to 3000 operations/sec
2
8
Up to 8000 operations/sec
4
16
Up to 12000 operations/sec
8
32
Up to 14000 operations/sec
16
64
Up to 17000 operations/sec
40
160
*Xeon E5-2676 v3, 2.4 GHz
Software requirements: • •
Operating system: 64-bit Linux (Red Hat 6+, Debian 7/0+, CentOS 6+, Ubuntu 14/04 LTS+, Amazon Linux, Amazon Linux 2), Windows (Windows Vista+, Windows Server 2008+) Linux-compatible file system (NFS and SMB file systems are not supported).
Note that you might need to install some additional software like database drivers depending on the target database and operating system you use. For the full list of required components see the Prerequisites subsection of the corresponding Admin Guide.
1.5 Useful Resources Web resources: • • • • • •
DataSunrise official web site: https://www.datasunrise.com/ DataSunrise latest version download page: https://www.datasunrise.com/download DataSunrise Facebook page: https://www.facebook.com/datasunrise/ Frequently Asked Questions: https://www.datasunrise.com/documentation/faq/ Best practices: https://www.datasunrise.com/download-the-datasunrise-security-best-practices/ Best practices (AWS): https://www.datasunrise.com/download-the-datasunrise-aws-security-best-practices/
Documents (located in the doc folder within the DataSunrise's installation folder): • • • • • •
DataSunrise Administration Guide for Linux (DataSunrise_Database_Security_Suite_Admin_Guide_Linux.pdf). Describes installation and post-installation procedures, deployment schemes, includes troubleshooting subsection. DataSunrise Administration Guide for Windows (DataSunrise_Database_Security_Suite_Admin_Guide_Windows.pdf). Describes installation and post-installation procedures, deployment schemes, includes troubleshooting subsection DataSunrise User Guide (DataSunrise_Database_Security_Suite_User_Guide.pdf). Describes the Web Console's structure, program management, etc Command Line Interface Guide (CLI_guide.pdf). Contains the CLI commands description, use cases, etc Release Notes (Release_notes.pdf). Describes changes and enhancements made in the latest DataSunrise version, known bugs and version history EULA (DataSunrise_EULA.pdf). Contains End User License Agreement.
2 Deployment Topologies | 11
2 Deployment Topologies DataSunrise can be installed either on a database server or on a separate server. In both cases, the software can be used both in the Sniffer mode and the Proxy mode.
2.1 Installing DataSunrise on a Database Server
Figure 3: Deployment on a DB server
2.1.1 Proxy Mode
To deploy DataSunrise in the Proxy mode, use one of the following methods: a) Tweaking of database settings • • •
Configure DataSunrise to use the port which the database uses to connect to the client applications Change the database's port number (because its old port is occupied by DataSunrise now). Configure a connection between DataSunrise and the database considering changes made in the previous steps. All the aforementioned steps are not relevant to Teradata and Vertica. Vertica and Teradata use a default port that cannot be changed. If you are going to use DataSunrise proxy for a single database, this would work. But if there is one more Vertica or Teradata database, the default port cannot be used again because it is already taken. A proxy to another database should be opened on another port and database clients should be reconfigured.
Tip: You can use the installation method described above during firewall testing, but some DB clients will still retain direct access to the DB. Use a system firewall (Windows Firewall or Iptables for Linux for example) to block direct access to the DB. Important: Many operating systems reserve port numbers less than 1024 for privileged system processes. That’s why it’s preferable to use port numbers higher than 1024 to establish a proxy connection.
2 Deployment Topologies | 12 b) Reconfiguring client applications • •
Make sure that DataSunrise uses the same port number as the database Configure all client applications to connect to DataSunrise, not to the database
2.1.2 Sniffer Mode
It is not required to tweak any client applications or database settings.
2.2 Installing DataSunrise on a Separate Server 2.2.1 Proxy Mode
Figure 4: Proxy mode deployment scheme To deploy DataSunrise in the Proxy mode, perform the following: • •
Configure a connection between DataSunrise and the database. Configure all the client applications to connect to the DataSunrise's proxy instead of the database.
Important: Many operating systems reserve port numbers less than 1024 for privileged system processes, so it’s preferable to use port numbers higher than 1024.
2 Deployment Topologies | 13
2.2.2 Sniffer Mode
Figure 5: Sniffer mode deployment scheme To deploy DataSunrise in the Sniffer mode, configure your network switch for transferring mirrored traffic to DataSunrise (refer to your network switch's user guide for the description of port mirroring procedure).
3 DataSunrise Installation | 14
3 DataSunrise Installation Note: Before you begin DataSunrise installation process, please select an appropriate deployment option (subsections Installing DataSunrise on a Database Server on page 11 and Installing DataSunrise on a Separate Server on page 12) and perform all required preparations. Also make sure that the machine you want to install DataSunrise on, meets the system requirements.
3.1 Prerequisites Depending on the required DataSunrise features you might need to install some additional software components: •
•
To be able to generate PDFs with the Report Generator, to use Data Discovery and Unstructured Masking, install Java SE Runtime Environment 8: http://www.oracle.com/technetwork/java/javase/downloads/jre8downloads-2133155.html. Having installed the JRE, add the paths to jvm.dll and java.exe to the %PATH% environment variable. To run DataSunrise in the Sniffer mode, Install WinPcap library: http://www.winpcap.org/install/default.htm
Depending on your target database type, it might be necessary to install some additional software. Please note that you should use 64-bit drivers and components: •
For Oracle database, install the OCI driver: http://www.oracle.com/technetwork/database/features/instant-client/ index.html. Having installed the Oracle Instant Client, add its home directory path to the %ORACLE_HOME% environment variable and to the %PATH% environment variable.
•
• • • • • • • •
Additionally, install Visual C++ 2010 package: https://www.microsoft.com/en-us/download/details.aspx?id=14632 For Netezza, install the dedicated ODBC driver. Download it from the IBM Fix Central website: http:// www-933.ibm.com/support/fixcentral/ Note that your IBM ID should be associated with your IBM customer ID with active support and maintenance contract for the Netezza appliance. Refer to the following page for details: https://www-304.ibm.com/support/ knowledgecenter/SSULQD_7.0.3/com.ibm.nz.adm.doc/c_sysadm_client_software_packages.html For DB2, install the ODBC driver: https://www-304.ibm.com/support/docview.wss?uid=swg21418043 For SQL Server, install the ODBC driver: https://www.microsoft.com/en-us/download/details.aspx?id=53339 For Hive, install the Hortonworks ODBC driver: https://hortonworks.com/downloads/ For Vertica, install the ODBC client driver: https://my.vertica.com/download/vertica/client-drivers/ For Amazon Redshift, install the Redshift ODBC driver: https://docs.aws.amazon.com/redshift/latest/mgmt/installodbc-driver-windows.html For SAP Hana, install the Hana client: https://help.sap.com/viewer/e7e79e15f5284474b965872bf0fa3d63/2.0.02/ en-US/d41dee64bb57101490ffc61557863c06.html For Impala, install the ODBC driver: https://www.cloudera.com/documentation/other/connectors/impala-odbc/ latest/Cloudera-ODBC-Driver-for-Impala-Install-Guide.pdf For Teradata, install the ODBC driver: http://downloads.teradata.com/download/connectivity/odbc-driver/windows
3.2 DataSunrise Installation To install DataSunrise on your machine, do the following: 1. Double-click the DataSunrise installer file (DataSunrise_Suite_XXX.windows.64bit.msi)
3 DataSunrise Installation | 15 2. Follow the steps of the setup wizard 3. Configure DataSunrise multi-servers for High Availability (HA) if necessary (refer to Adding a DataSunrise Server to HA Setup on page 23)
3.3 DataSunrise Installation Folder This subsection describes DataSunrise files and installation folder structure.
Figure 6: DataSunrise files and folders 1. DataSunrise folders: Folder name
Description
audit_data
Temp files used by the Emergency Audit feature
clientbehaviour
.jar files required for operation of the Client Behavior feature
cmdline
Contains DataSunrise Command Line Interface files
dictionaryBackup
Dictionary backup files
3 DataSunrise Installation | 16 Folder name
Description
doc
Contains DataSunrise docs (User guide, Administrator's Guide, CLI guide, Release notes, EULA)
hadoop
Hadoop library required for Hive/Impala Static Masking
logs
Log files (Backend, Core, Web Console logs)
reports
DSAR and Report Gen reports
scripts
Scripts required for deployment of multiple DataSunrise instances on a single machine, mysql_metadata_procedures.sql required for creating a MariaDB/MySQL user, scripts used for uploading captured audit data to Amazon S3
teradata13MaskFunctions
Functions used for Teradata 13 masking
v2
Contains Web Console's files
2. DataSunrise files (except DLL files): File name
Description
audit.db
SQLite file to store audit data (the Audit Storage)
dictionary.db
SQLite file to store program settings, DataSunrise-specific objects such as database profiles, User profiles, Rules, etc.
event.db
SQLite file to store System Events logs
lexicon.db
SQLite file to store Lexicons needed for the Data Discovery
standart_application_queries.db
SQLIte file which contains queries used by Oracle SQL Developer (refer to Query Groups subsection of the User Guide for more information)
install_firewall_service.bat
This script installs DATA_SUNRISE_SECURITY_SUITE service (it is run by setup wizard during program installation)
remove_firewall_service.bat
This script removes DATA_SUNRISE_SECURITY_SUITE service (it is run by setup wizard during program installation)
start_firewall_service.bat
This script starts DATA_SUNRISE_SECURITY_SUITE service
stop_firewall_service.bat
This script stops DATA_SUNRISE_SECURITY_SUITE service
AppBackendService.exe
System process required for operation of the Web Console and for control of AppFirewallCore.exe
AppFirewallCore.exe
The Core process. Performs all fundamental DataSunrise functions
appfirewall.pem
SSL certificate for the Web Console
cacert.pem
SSL certificate required for online update
proxy.pem
OpenSSL keys and certificates used for proxies by default
appfirewall.reg
DataSunrise license key.
3.4 Starting DataSunrise DataSunrise needs for its operation the DATA_SUNRISE_SECURITY_SUITE service running. This service starts the DataSunrise's Backend and Core on Windows startup.
3 DataSunrise Installation | 17 •
If your DataSunrise process is stopped because of a problem of some kind, you can start the service manually via the Windows Task Manager.
3.5 Running Multiple DataSunrise Instances on a Single Machine DataSunrise enables running multiple independent DataSunrise instances on the same machine. It can be useful for eliminating a single point of failure while protecting multiple databases or it is required to use independent configurations for certain databases or sets of databases. To deploy an independent DataSunrise instance, it is necessary to create a Windows service for each instance. In this case DataSunrise uses common binary files and independent configuration files for each instance.
3.5.1 Installing a DataSunrise Service To create a new DataSunrise service, run install_instance.bat file located in the scripts subfolder of the DataSunrise installation folder with the following parameters: install_instance.bat [SERVER_NAME] [SERVER_HOST] [BACKEND_PORT] [CORE_PORT] Parameter
Description
[SERVER_NAME]
Name of the DataSunrise server. Server’s name is shown as a name of a server in the “Servers” subsection of the DataSunrise's Web Console and as a suffix of a service name: DATA_SUNRISE_SECURITY_SUITE + suffix
[SERVER_HOST]
IP address or host name of the server DataSunrise is installed on
[BACKEND_PORT]
Port number of the web server (Back end’s port number). You should use this port number when establishing a connection with the DataSunrise's Web Console
[CORE_PORT]
Port number of the DataSunrise's Core
An example of installation string: install_instance.bat SERVER1 127.0.0.1 11010 11011 3.5.1.1 Configuring the Dictionary Storage By default, DataSunrise stores its settings (the Dictionary) in the integrated SQLite database. If you need to use another database to store that data, you can edit the install_instance.bat script. This is what the editable parameters look like by default: set set set set set set
DICTIONARY_TYPE=sqlite DICTIONARY_HOST=NOT_APPLICABLE DICTIONARY_PORT=NOT_APPLICABLE DICTIONARY_DB_NAME=NOT_APPLICABLE DICTIONARY_LOGIN=NOT_APPLICABLE DICTIONARY_PASS=NOT_APPLICABLE
3 DataSunrise Installation | 18 Parameter
Description
DICTIONARY_TYPE
Type of a database to store the Dictionary in (sqlite by default). Available values are postgresql, mysql, vertica, mssql, for PostgreSQL, MySQL, Vertica and MS SQL Server respectively.
DICTIONARY_HOST
IP address or host name of the server the Dictionary database is installed on.
DICTIONARY_PORT
Port number of the Dictionary database
DICTIONARY_DB_NAME
Name of the Dictionary database
DICTIONARY_LOGIN
User name for accessing the Dictionary database
DICTIONARY_PASS
Password for accessing the Dictionary database
Example: set set set set set set
DICTIONARY_TYPE=postgresql DICTIONARY_HOST=192.168.1.51 DICTIONARY_PORT=5432 DICTIONARY_DB_NAME=dict_db DICTIONARY_LOGIN=user1 DICTIONARY_PASS=password
3.5.2 Switching Between DataSunrise Instances Installed on the Same Machine You can switch between existing DataSunrise instances installed on the same server. To do it, do the following 1. Create a file named "local_servers_config.json" with the following content: { "list": [ {"clusterName": "cluster1", "clusterURL": "https://localhost:11000"}, {"clusterName": "cluster2", "clusterURL": "https://localhost:11100"} ] } 2. Place the file into the $AF_HOME folder ("C:\Program Files\DataSunrise Database Security Suite" by default). 3. Select DataSunrise server of interest in the drop-down list located at the top of the screen.
3.5.3 Removing DataSunrise Service
3 DataSunrise Installation | 19 To remove an existing DataSunrise service created with the script, run remove_instance.bat with the following parameters: remove_instance.bat [SERVER_NAME] Parameter
Description
[SERVER_NAME]
Name of the DataSunrise server. Server’s name is shown as a name of a server in the “Servers” subsection of the DataSunrise's Web Console and as a suffix of a service name: DATA_SUNRISE_SECURITY_SUITE + suffix
An example of removal string: remove_instance.bat SERVER1 Important: Before updating DataSunrise, it is required to stop secondary services created by the script manually. Before product removal, it is necessary to delete all secondary services manually (use remove_instance.bat)
3.6 Copying DataSunrise Settings to Another DataSunrise Instance To use your DataSunrise settings for another DataSunrise instance installed on another server, do the following: 1. 2. 3. 4. 5. 6. 7.
Stop the DataSunrise system service (DATA_SUNRISE_SECURITY_SUITE) using the Windows Task Manager Copy dictionary.db, event.db and audit.db files from the source DataSunrise installation folder. Install a DataSunrise instance on the destination server. Stop the DataSunrise system service on the destination server. Paste dictionary.db, event.db and audit.db files to the new DataSunrise instance's installation folder. Start the DataSunrise system service on the destination server. Use the DataSunrise's Web Console to confirm completeness of migration of all policies and configurations.
3.7 Upgrading DataSunrise To update DataSunrise to the latest version, do the following: 1. Navigate to the System Settings → About subsection 2. Click Update. Note that this button appears only if a newer version of DataSunrise is available at the official web site. 3. Wait for the update to complete and refresh the Web Console's page by pressing Ctrl+F5. Note: You can also update the program in another way. Download the latest version of DataSunrise from the official web site and run the installation file. Follow the steps of the setup wizard to update the program.
3 DataSunrise Installation | 20
3.8 Downgrading DataSunrise During DataSunrise upgrading process, the installer creates a backup folder where all files required to roll back the installation are retained in case of any issues that would result from the upgrade. To restore a previous version of DataSunrise, do the following: 1. Stop the DataSunrise's system service (DATA_SUNRISE_SECURITY_SUITE) via the Windows Task Manager 2. Go to the DataSunrise's installation folder and locate the backup folder. It contains some folders with backup files 3. Choose a backup to restore from. Note that the higher number, the newer a backup file. 4. Move the backup files from the backup folder to the root of the DataSunrise installation folder. 5. Start the DataSunrise's system service.
3.9 Encrypting the Dictionary and the Audit Storage (PostgreSQL) DataSunrise enables you to encrypt Dictionary and Audit Storage database fields with pgcrypto (PostgreSQL only). Symmetric encryption is used with password stored in the field_crypto.pwd file. Note that you should enable the Dictionary encryption BEFORE creating your target database profile in the DataSunrise's settings. Once you enable the encryption, you will not be able to turn it off. Before enabling encryption, you should create the pgcrypto extension in your PostgreSQL database. Do the following: 1. Connect to your target database as the postgres user 2. Execute the following query: CREATE EXTENSION IF NOT EXISTS pgcrypto; Note: you can specify encryption options as described in the F.25.3.8 subsection of the following guide: https:// www.postgresql.org/docs/9.6/pgcrypto.html 3. Open the DataSunrise's Web Console. To encrypt the Audit storage (operations.sql_query and operation_data.data fields), enable the AuditFieldCryptEnabled parameter in System Settings → Additional Parameters. The AuditFieldCryptoOptions parameter enables you to specify encryption options to be used 4. To encrypt a Dictionary PostgreSQL database, run the DataSunrise's back end with the DICTIONARY_FIELD_CRYPTO_ENABLED=1 parameter. For example, you can use the following script during your DataSunrise installation process (this script creates new or modifies an existing local_settings.db file): ./AppBackendService DICTIONARY_TYPE="postgresql" DICTIONARY_HOST="127.0.0.1" DICTIONARY_PORT=5432 DICTIONARY_DB_NAME="ds1" DICTIONARY_LOGIN="ds_user" DICTIONARY_PASS="qwerty" DICTIONARY_FIELD_CRYPTO_ENABLED=1 FIREWALL_SERVER_NAME="ds" FIREWALL_SERVER_HOST="127.0.0.1"
3 DataSunrise Installation | 21 FIREWALL_SERVER_BACKEND_PORT="11000" FIREWALL_SERVER_CORE_PORT="11001" FIREWALL_SERVER_BACKEND_HTTPS=1 FIREWALL_SERVER_CORE_HTTPS=1 To specify Dictionary encryption options, use the DICTIONARY_FIELD_CRYPTO_OPTIONS back end parameter.
3.10 Restoring Access to the Web Console if the Password is Lost You can't restore a DataSunrise administrator's password if you lost it, but you can set a new one. To change the admin user password, do the following: • • •
Start Windows Command Prompt as an administrator. Use the cd command to navigate to the DataSunrise's installation folder (C:\Program Files\DataSunrise Database Security Suite by default). Run the AppBackendService.exe file with the set_admin_password parameter. Set a new password as the parameter's value: C:\Program Files\DataSunrise Database Security Suite>AppBackendService.exe SET_ADMIN_PASSWORD= AF_CONFIG="C:\ProgramData\DataSunrise Database Security Suite\."
•
Restart DataSunrise's system service for the changes to take effect. You can do it via the Windows Task manager.
3.11 DataSunrise Removal To uninstall DataSunrise, perform a standard program removal procedure (using the Control panel) or use the method described below: 1. Double-click the DataSunrise installer file (DataSunrise_Suite_XXX.windows.64bit.msi) 2. Click Remove to initiate the program removal process Note: click Repair to fix a corrupted DataSunrise installation. This removes and installs DataSunrise again. No user data, Dictionary data, etc. is impacted 3. Follow the steps of the setup wizard.
4 Multi-server Configuration (High Availability Mode) | 22
4 Multi-server Configuration (High Availability Mode) Along with running a single instance of DataSunrise, you can configure multiple servers to implement Failover and Scalability. This feature enables you to run multiple DataSunrise instances on separate servers sharing a common configuration (Dictionary). If some of the servers go offline, other servers keep working guarantying consistent traffic processing without an impact on system availability. DataSunrise also includes a built-in load balancer to distribute system load among multiple DataSunrise instances in HA mode. DataSunrise also includes Shared Sessions feature which enables performing authentication to the Web Console on one of the DataSunrise servers and all other servers will share queries inside the same session. Once a logout/session timeout is occurred on one instance, the session will be closed on all other DataSunrise instances. DataSunrise needs access to the Dictionary database to be able to load the software configuration. Thus, DataSunrise cannot start without a Dictionary. In case a Dictionary database is disabled AFTER DataSunrise has been started, DataSunrise will continue working because configuration had already been loaded. In HA mode, DataSunrise can continue working without a connection to a remote Dictionary database. Periodically (by default, in 5 minutes from the moment of the last changes made to the Dictionary), DataSunrise creates a Dictionary backup and stores it in the "AF_CONFIG/systemBackupDictionary" folder. Each DataSunrise server creates its own copy of the shared Dictionary. If DataSunrise uses a Dictionary backup for working, further backing up is not performed. The built-in SQLite database is used to create Dictionary backups. If a main Dictionary can't be accessed, DataSunrise's Backend and Core connect to a backed up Dictionary to use it. The Backend and Core access Dictionaries independently, so it's possible that they use a main and a reserve Dictionary at the same time.
4.1 Preparing Databases to be Used as a Dictionary/Audit Storage When deploying DataSunrise in multi-server configuration, a PostgreSQL or a MySQL database is used to store the common Dictionary and Audit data. First you should use a database user with sufficient privileges (admin for example) to create a database or schema to store that data and then create a user that could be used for access to that data. In general, such a user should have read/write access to your "Dictionary" schema or database.
4.1.1 Preparing a PostgreSQL Database Note that remote configuration of DataSunrise is available only for PostgreSQL 9.1-12.1 or higher. 1. Create a new PostgreSQL database user by executing the following query: CREATE USER WITH PASSWORD ; 2. Grant the required privileges to the user: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA TO ; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA TO ; GRANT CREATE, USAGE ON SCHEMA TO ;
4 Multi-server Configuration (High Availability Mode) | 23 GRANT CREATE, USAGE ON SCHEMA TO ;
4.1.2 Preparing a MySQL Database To use a MySQL (5.5.3+ for the Audit Storage and 5.7.2+ for the Dictionary, lower versions are not supported) database as the Dictionary and Audit Storage, do the following BEFORE deploying DataSunrise in HA configuration: 1. Open the my.cnf file (MySQL configuration file) and add the following lines to the [mysqld] section: log_bin_trust_function_creators = 1 local_infile = 1 2. Create a new MySQL user by executing the following command: CREATE USER IDENTIFIED with 'mysql_native_password' BY ; 3. Create two databases: the Audit Storage and the Dictionary: CREATE DATABASE character set utf8mb4 COLLATE utf8mb4_bin; CREATE DATABASE character set utf8mb4 COLLATE utf8mb4_bin; 4. Grant the required privileges to the user: USE ; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, ALTER, DROP, INDEX, REFERENCES, ALTER ROUTINE, CREATE ROUTINE ON .* TO ; USE ; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, ALTER, DROP, INDEX, REFERENCES, ALTER ROUTINE, CREATE ROUTINE ON .* TO ; grant SYSTEM_VARIABLES_ADMIN on *.* to ; grant SESSION_VARIABLES_ADMIN on *.* to ; grant all privileges on . * to with grant option; For MySQL 8.0.0+, additionally grant the following privileges: GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO ''@'%'; FLUSH PRIVILEGES;
4.2 Adding a DataSunrise Server to HA Setup A DataSunrise server to be deployed in HA configuration can be configured during the DataSunrise installation. 1. At the end of the installation process, in the Location of DataSunrise configuration tab, select Remote server. 2. In the DataSunrise Server details tab, specify details of the current DataSunrise instance.
4 Multi-server Configuration (High Availability Mode) | 24
Figure 7: Server details Field
Description
Name
Logical name of the DataSunrise Instance
Host
IP address or name of the host, current DataSunrise instance is installed on
Port
Port number of the Instance's Web Console (11000 by default)
3. In the DataSunrise Dictionary location tab, specify a database to store DataSunrise configuration (the Dictionary). All servers configured to use this database, will share common configuration (including common credentials to access the Web Console).
Figure 8: Dictionary location 4. In the Audit Storage location tab, specify a database to store DataSunrise's audit data.
Figure 9: Audit Storage location 5. After the configuring is completed, you can see all available DataSunrise servers (instances installed on separate servers and sharing a common Dictionary) at System Settings → Servers.
4 Multi-server Configuration (High Availability Mode) | 25
4.3 Reviewing Servers of an Existing HA Configuration All existing DataSunrise servers are equal, so you can access all server's settings from the Web Console of any DataSunrise instance included in the HA setup: 1. Go to System Settings → Servers. Select the required server in the list and click on its name to access the server's settings 2. Reconfigure the server, if necessary:
Figure 10: DataSunrise Server settings Interface element
Description
Main Settings Logical Name
Logical name of the DataSunrise server (instance)
4 Multi-server Configuration (High Availability Mode) | 26 Interface element
Description
Host
IP address of the server the Instance is installed on
Backend Port
DataSunrise Backend port number (used to access the Web Console)
Core Port to Start Numbering from
DataSunrise Core port number
Use HTTPS for Backend Process
Use HTTPS protocol to access the Web Console
Use HTTPS for Core Processes
Use HTTPS protocol to access the Core
Core and Backend Process Manager Actions→ Restart Core
Restart the Core process
Actions → Start Core
Start Core process (if stopped)
Actions → Stop Core
Stop running Core process
Actions → Restart Backend
Restart running Backend process
Server Info License Type
Type of license activated
License Expiration Date
License expiration date
Version
Program version
Backend Up Time
The Backend run duration
Server Time
Current server time
OS Type
Type of the server's operating system
OS Version
Version of the server's operating system
Machine
Server's hardware info
Node Name
Server's name (PC name)
Encoding
Encoding used on the server
Server
DataSunrise server's logical name
4.4 Configuring a Remote Dictionary on AWS Using the AWS Secrets Manager When using HA configuration, you can use a Dictionary located on AWS's RDS and you can store your Dictionary password using the AWS Secrets Manager: 1. Instal DataSunrise on Amazon EC2 virtual machine. 2. Configure the Secrets Manager to store the password to the Dictionary. 3. Execute the following script using the details of your database to specify the location of the database where DataSunrise Dictionary is stored: AppBackendService.exe DICTIONARY_TYPE= DICTIONARY_HOST= DICTIONARY_PORT= DICTIONARY_DB_NAME= DICTIONARY_LOGIN=
4 Multi-server Configuration (High Availability Mode) | 27 DICTIONARY_AWS_SECRET= FIREWALL_SERVER_HOST= FIREWALL_SERVER_CORE_PORT= FIREWALL_SERVER_BACKEND_HTTPS=1 FIREWALL_SERVER_CORE_HTTPS=1
4.5 Restoring the Configuration if your local_settings.db is Lost When using HA configuration, if one of the DataSunrise servers needs to be transferred to another server or the local_settings.db is lost, DataSunrise's configuration will be changed to the default one. To avoid this, do the following: 1. Navigate to System Settings → Servers. Select the server you want to restore access to. Note "id" in the web browser's address bar. The id's number corresponds to parameter's value you will be using at the following step. For example "id1" means Server number 1. 2. Stop DataSunrise system service via the Windows Task manager: 3. Run the Windows command line as an administrator. Navigate to the DataSunrise's installation folder: cd C:\Program Files\DataSunrise Database Security Suite 4. Execute the following command to register the AF_CONFIG variable: set AF_CONFIG=C:\ProgramData\DataSunrise Database Security Suite 5. Execute the following script using the details of your database to specify the location of the database where DataSunrise Dictionary is stored: .\AppBackendService.exe DICTIONARY_TYPE= DICTIONARY_HOST= DICTIONARY_PORT= DICTIONARY_DB_NAME= DICTIONARY_LOGIN= DICTIONARY_PASS= RESTORE_LOCAL_SETTINGS= Note: replace with the actual ID of the server you want to restore access to (see step 1). Note that all the parameter's values should be written without quotes. Important: if you changed your Dictionary location (IP address or host name), you can use the command from Step 5 to create a new local_settings.db file which is required to be able to work with a new Dictionary.
5 Always On | 28
5 Always On 5.1 Working with Always On Availability Group of SQL Server This subsection describes the basic principles of working with SQL Server's Always on availability: 1. A client connects and authorizes in a SQL Server database through the DataSunrise proxy. 2. SQL Server sends the client a command to reconnect to a secondary node. 3. DataSunrise intercepts the packet. •
If there is a proxy associated with the secondary node, the connection address is substituted with the address of a required proxy. • If there is no proxy associated with the secondary node, a new proxy is created and the client receives the new proxy address • A modified packet is sent to the client. 4. Having received the reconnection command, the client connects to the required proxy.
5.2 Configuring of the Firewall Inside the Azure Cloud for Maintenance of the SaaS SQL Azure Azure SQL and Always-On cluster use the same mechanism for client redirecting. When connecting to the SaaS SQL Azure, if a client is inside Azure subnet, then SQL Azure can redirect a client to service (dynamic) servers for balancing of network load. In such a case, a server right after authorization will return to the client an address and port number of the service the client is to be reconnected to. To be able to control such reconnections, at the moment when a server sends a query for reconnection, DataSunrise replaces address of the service server with the address of a proxy that services it. In such a case, a client is reconnected to the DataSunrise's proxy and not to the Azure service server. And for each unique reconnection, will be created an entry like this in the Event Monitor: Rewrite route: cd164f04fd1f.tr27.westus1-a.worker.database.windows.net:11082 -> 10.1.0.6:14033 where cd164f04fd1f.tr27.westus1-a.worker.database.windows.net:11082 is service server's address and 10.1.0.6:14033 is address of DataSunrise proxy that maintains this service server. If DataSunrise is not able to find a proxy in the current instance, then 2 scenarios are available: •
If MsSqlRedirectsDisable option is disabled (by default), a proxy will be created automatically in a current instance (and an interface if required)
5 Always On | 29 •
An entry will be added to the Event Monitor: Redirect: cd164f04fd1f.tr27.westus1-a.worker.database.windows.net:11082 For the SaaS SQL Azure it means that a client should add a proxy on this host manually (or it is done already) to make client connections controlled by DataSunrise. Otherwise, DataSunrise will lose control over the client connection. For a cluster with AlwaysOn enabled, it is possible to configure redirection to Readonly-replicas, that's why if redirecting host is already configured on DataSunrise, we will see this host in redirection notification. In both cases, a notification about redirection can be used when administering DS/AlwaysOn for diagnostics. To add a proxy to an instance, perform the following: • •
Add an interface of the target server (its cd164f04fd1f.tr27.westus1a.worker.database.windows.net:11082 in our case); Add a proxy on this interface. When using standard templates for host name (0.0.0.0 or 0:0:0:0:0:0:0:0) of such a proxy, DataSunrise will return the client an address of an available non-local interface from DataSunrise's host as an address for redirect.
5.3 Issues with SQL Server Read-only Databases on AlwaysOn The script for creating a backend with minimum set of privileges can not work on AlwaysOn cluster. When a database is maintained by a secondary replica, it works in Read-Only mode. In this case it is impossible to create a user in this database and give the user minimum privileges for getting database's metadata. To solve this problem, you can use the following script: DECLARE @DB SYSNAME DECLARE @UPDATEABILITY NVARCHAR(128) DECLARE @LOGIN NVARCHAR(MAX) DECLARE @SID NVARCHAR(MAX) DECLARE @PWD NVARCHAR(MAX) DECLARE @USER NVARCHAR(MAX) DECLARE ALLDB CURSOR FOR SELECT name, CONVERT(NVARCHAR(128), DATABASEPROPERTYEX(name, 'Updateability')) FROM [master].[dbo].[sysdatabases] SET SET SET SET
@LOGIN = 'bsa' @SID = '' @PWD = '1234' @USER = 'Backend User'
-- create login IF LEN(@SID) > 0 EXEC('USE [master] name = ''' + @LOGIN + @PWD + ''', SID = ' + ELSE EXEC('USE [master] name = ''' + @LOGIN + @PWD + '''')
IF NOT EXISTS(SELECT loginname FROM [dbo].[syslogins] WHERE ''') CREATE LOGIN [' + @LOGIN + '] WITH PASSWORD = ''' + @SID) IF NOT EXISTS(SELECT loginname FROM [dbo].[syslogins] WHERE ''') CREATE LOGIN [' + @LOGIN + '] WITH PASSWORD = ''' +
-- server permissions EXEC('USE [master] GRANT VIEW ANY DATABASE TO [' + @LOGIN + ']') EXEC('USE [master] GRANT VIEW ANY DEFINITION TO [' + @LOGIN + ']')
5 Always On | 30 OPEN ALLDB LOOP: FETCH NEXT FROM ALLDB INTO @DB, @UPDATEABILITY IF @@FETCH_STATUS = 0 BEGIN -- updateability check IF @UPDATEABILITY = NULL OR @UPDATEABILITY = 'READ_ONLY' BEGIN PRINT 'The database ''' + @DB + ''' is have not in a updatability
state.'
PRINT 'Perhaps it is available for management from another replica (in the case of AlwaysOn, for example).' PRINT 'In this case, make sure that the primary and secondary replica''s SID of login matched.' GOTO LOOP END -- create user EXEC('USE [' + @DB + '] IF NOT EXISTS(SELECT * FROM [sys]. [database_principals] WHERE [name] = ''' + @USER + ''') CREATE USER [' + @USER + '] FOR LOGIN [' + @LOGIN + '] WITH DEFAULT_SCHEMA = dbo') -- map user to login IF CHARINDEX('Microsoft SQL Server 2005', @@VERSION) != 0 EXEC('USE [' + @DB + '] EXEC sp_change_users_login ''Update_One'', ''' + @USER + ''', ''' + @LOGIN + '''') ELSE EXEC('USE [' + @DB + '] ALTER USER [' + @USER + '] WITH LOGIN = [' + @LOGIN + ']') -- master permissions IF @DB = 'master' BEGIN EXEC('USE [' + @DB + '] GRANT SELECT ON OBJECT::[sys].[databases] TO [' + @USER + ']') EXEC('USE [' + @DB + '] GRANT SELECT ON OBJECT::[sys]. [server_principals] TO [' + @USER + ']') END -- other permissions EXEC('USE [' + @DB + TO [' + @USER + ']') EXEC('USE [' + @DB + [database_permissions] TO [' EXEC('USE [' + @DB + @USER + ']') EXEC('USE [' + @DB + @USER + ']') EXEC('USE [' + @DB + @USER + ']') EXEC('USE [' + @DB + @USER + ']') EXEC('USE [' + @DB + + ']') GOTO LOOP
'] GRANT SELECT ON OBJECT::[sys].[database_principals] '] GRANT SELECT ON OBJECT::[sys]. + @USER + ']') '] GRANT SELECT ON OBJECT::[sys].[all_columns] TO [' + '] GRANT SELECT ON OBJECT::[sys].[all_views] TO [' + '] GRANT SELECT ON OBJECT::[sys].[all_objects] TO [' + '] GRANT SELECT ON OBJECT::[sys].[schemas] TO [' + '] GRANT SELECT ON OBJECT::[sys].[types] TO [' + @USER
5 Always On | 31 END CLOSE ALLDB DEALLOCATE ALLDB EXEC('USE [master] SELECT name, sid FROM sys.server_principals WHERE name = ''' + @LOGIN + '''') GO For AlwaysOn, call batch on each replica but with different parameters: • •
For the primary replica, call batch with @SID=''. The last batch's query returns a SID of an existing login. For other replicas, call batch with the SID got on the primary replica. If a server is included into several Availability Groups, you can call batch only once for each server. The main point is that logins for all servers should be created with the same SID. You need a similar SID to automatically map logins to read-only users on read-only replicas.
5.4 Restoring Configuration if local_settings.db is Lost When using HA configuration, if one of the DataSunrise servers needs to be transferred to another computer or the local_settings.db is lost, DataSunrise's configuration will be changed to the default one. To avoid this, do the following: 1. Navigate to System Settings → Servers. Select a server you want to restore access to. Note the id in the web browser's address bar. The id's number corresponds to parameter's value you will be using at the following step. For example "id1" means Server number 1. 2. Stop DataSunrise system service via the Windows Task manager: 3. Run the Windows command line as administrator. Navigate to DataSunrise installation folder. cd C:\Program Files\DataSunrise Database Security Suite 4. Execute the following command to register the AF_CONFIG variable: set AF_CONFIG=C:\ProgramData\DataSunrise Database Security Suite 5. Execute the following script using the details of your database to specify the location of the database where DataSunrise Dictionary is stored: .\AppBackendService.exe DICTIONARY_TYPE= DICTIONARY_HOST= DICTIONARY_PORT= DICTIONARY_DB_NAME= DICTIONARY_LOGIN= DICTIONARY_PASS= RESTORE_LOCAL_SETTINGS=
Note: replace with the actual ID of a server you want to restore access to (see step 1). Note that all parameter's values should be written without quotes.
6 Frequently Asked Questions | 33
6 Frequently Asked Questions This section describes the most common issues DataSunrise users face. Q. I've installed database server, client database and the firewall on the same host. I’m trying to run DataSunrise in Sniffer mode, but it is not listening for the traffic. In this case DataSunrise can’t capture traffic sent from a host machine to the same host machine. You should use DataSunrise Proxy mode only or install database server and database client on different hosts. Q. I've updated DataSunrise and I get the following error: "PROCEDURE dsproc_.initProcedures does not exist" Now DataSunrise uses a new method of getting metadata. Do the following steps again: subs. 4.5.4.2 Q. I’m trying to add a new Oracle database via the Configuration menu, but connection is failing because of the “Couldn’t load oci.dll” error. Probably you've installed a 32-bit version of the Oracle Database Instant Client or have not set system variables correctly. You need to install a 64-bit version of the Oracle Database Instant Client on the same server DataSunrise is installed on, and add its home directory path to the %ORACLE_HOME% system variable. Then you need to add the same directory path to the %PATH% system variable and reboot the server or restart DataSunrise service. For example (PATH): C:\Oracle\app\oracle\product\11.2.0\server\ Q. DataSunrise running on a host can’t capture data packets between a database client running on the same host and a database server running on an Oracle VirtualBox virtual machine. If you’re using VirtualBox 5.0.2, for instance, DataSunrise will likely fail to capture data packets between a database client running on the host and a database server running on the guest OS. This problem can occur under various network connection settings such as NAT, bridged and host-only. However, if you run the DB client on the guest OS and DB server — on the host, DataSunrise would be able to capture network packets. This issue is caused by VirtualBox's 5.0.X virtual network adapter (VirtualBox NDIS Bridged Network Driver). Try to install an older version of VirtualBox and check if DataSunrise captures data packets between the host and the guest OS. Q. I'm trying to enter the web interface after DataSunrise has been updated, but it displays "Internal System Error" message. Most likely, you kept web interface tab open in your browser while updating the firewall. Log out the web interface if necessary and press Ctrl + F5 to reload the page. Q. When I’m trying to run DataSunrise in sniffer mode, it displays the following message: “Can’t to parsing SSL connection in sniffer mode”. To run the firewall in sniffer mode, you should disable SSL support in your client application settings (SSL Mode → Disable). You can also switch application’s SSL Mode to “Allow” or “Prefer” ,but disable SSL support in database server's settings first. Q. When connecting to Aurora DB or MySQL the ODBC driver stops responding. Most probably, you're using ODBC driver version 5.3.6, which is known to cause freezes from time to time. Install MySQL ODBC driver version 5.3.4. Q. I forgot the password to the Web Console.
6 Frequently Asked Questions | 34 You can set a new administrator password. Use the Windows' CLI to run DataSunrise's appbackendService.exe file with set_admin_password parameter (Important: run the CLI as an administrator). For example: >C:\Program Files\DataSunrise Database Security Suite>AppBackendService.exe SET_ADMIN_PASSWORD= AF_CONFIG="C:\ProgramData\DataSunrise Database Security Suite\." To apply new password, restart DATA_SUNRISE_SECURITY_SUITE system service via the Windows Task Manager. Q. I'm using an MS SQL Server database. I'm creating a target database profile, but can't properly configure the database connection. In the DB connection details, specify the credentials (Default login and Password fields) used for SQL Server authentication and not for Windows authentication. To specify the database server's host (Host field), use actual DB server's IP address or host name instead of server's SPN. I'm using an MS SQL Server database. When connecting through a DataSunrise proxy, I get an error "Cannot connect to...". For example "Cannot connect to vsunrise.db.local,1435". This error disappears at the next connection. This error occurs when the database server and DataSunrise proxy are located on the same host and the client first connects to the server directly and then through the proxy. Client's CSP confuses proxy and the server and tries to restore the first SSL session through the proxy connection. But the proxy and the server operate on different processes and cannot share SSL sessions. You can disable session caching on the client side: • •
Open Registry Editor. Click Start, Run, type regedt32 and click OK Click to select the following key in the registry: [HKEY_LOCAL_MACHINE][System][CurrentControlSet][Control][SecurityProviders] [SCHANNEL]
• •
In the Edit menu, click Add Value, type "ClientCacheTime" in the Value Name box, select "REG_DWORD" for Data Type, and then click OK. Exit Registry Editor.
Having added ClientCacheTime to the registry, reboot your server. Q. When running DataSunrise in the sniffer mode, I get an error: "DS_31037E: Crypto [ha-05:1433]". Can not determine the username with Kerberos or local NTLM authentication in the Sniffer mode. Unless the parameters of the crypto provider are properly configured, we can not identify the login/user. The UNKNOWN LOGIN account will be used as the current user. Rules checks may not work correctly until this error is resolved. And its impossible to define the name of a user for NTLM if the client and the server are both installed on the same host. Q. I'm getting the following warning: "The free disk space limit for audit is reached. The current disc space amount is XXX MB. The disk space limit is 10240 MB". If you want to decrease the disk space threshold for this warning, navigate to the System Settings → Additional and change the "LogsDiscFreeSpaceLimit" parameter's value from 10240 to 1024 Mb for example. Q. I'm getting the following notification: "Reached the limit on delayed packets". This notification is displayed when a sniffer has captured a big amount of traffic on SSL sessions started before the DataSunrise service had been started. By default, the volume of captured traffic should not exceed 10 Mb (pnMsSqlDelayedPacketsLimit parameter). Sometimes this notification can be displayed if there is a huge load on the pcap driver. Thus, a sniffer can capture too much of a delayed traffic. In this case you need to increase pnMsSqlDelayedPacketsLimit parameter's value.
6 Frequently Asked Questions | 35 Q. I've installed Netezza driver but get an error: "Error code 160. Can't find the following module: NetezzaSQL, C:\Windows\System32\nsqlodbc.dl". Install Microsoft Visual C++ 2010 (msvc runtime 100).