lhs-panel Click here to expand

Configuring the MS SQL database for EventLog Analyzer

This page describes the various steps involved in configuring the MS SQL database in EventLog Analyzer.

How to find the build number?

Note:

This procedure to configure MS SQL will clear all existing data.

Before making changes to the database with the changeDBServer.bat file, it's crucial to ensure that the product is fully operational. Once confirmed, proceed to shut down the product. Once the product has successfully stopped, run the changeDBServer.bat file.

Here's how you can configure and run the EventLog Analyzer with MS SQL as the database.

  1. From the installed MS SQL server, copy the files bcp.exe and bcp.rll to <Eventlog Analyzer Home>\bin  folder.
  2. Note: If you are copying the above files from SQL server (Version 2012 and above) and EventLog Analyzer is installed in another machine, please install the SQL native client as per the SQL version and CPU type of the EventLog Analyzer machine.

    For MSSQL version 2012, install the native client and for the remaining versions of MSSQL, install the ODBC driver (links given below).

    MSSQL 2012

    https://www.microsoft.com/en-us/download/confirmation.aspx?id=50402

    MSSQL 2014

    https://www.microsoft.com/en-us/download/details.aspx?id=36434

    MSSQL 2016

    https://www.microsoft.com/en-us/download/details.aspx?id=50420

    MSSQL 2017

    https://www.microsoft.com/en-us/download/details.aspx?id=53339

    MSSQL 2019

    64bit link: https://go.microsoft.com/fwlink/?linkid=2137027

    32bit link: https://go.microsoft.com/fwlink/?linkid=2137028

    MSSQL 2022

    64bit link: https://go.microsoft.com/fwlink/?linkid=2249006

    32bit link: https://go.microsoft.com/fwlink/?linkid=2249005

    After installing the required Native client/ODBC Driver, you can check if you've got the right version of bcp.exe+bcp.rll files or the right version of the Native client/ODBC Driver by going to <EventLog Analyzer Home>\bin folder, opening the command prompt with admin rights and executing the following command:-

    bcp.exe -v

    If you get an error, either your bcp files are wrong or your Native Client/ODBC Driver version in the EventLog Analyzer machine is incorrect.

  3. Invoke the <EventLog Analyzer Home>\tools\changeDBServer.bat, to configure MS SQL server credentials like Server Name, Port, User Name and Password.
  4. The Database Setup Wizard will appear.
  5. In the wizard screen, choose the Server Type as SQL Server. Enter the Host Name and the port of the SQL Server. Select the instance from the available SQL Server Instances.
  6. Tips:
    • Ensure that the server browser service is enabled as it provides information about the SQL Server instances.
    • Ensure that TCP/IP are enabled under protocols in the SQL Server Configuration Manager.
  7. Select the authentication type using the "Connect Using:" options.
  8. The options are:
  9. Note: Ensure that both EventLog Analyzer server and MS SQL server are in the same domain and logged in with the same domain administrator credentials.

    Windows Authentication

    • To use SQL Windows Authentication, enter the Domain Name, Username, and Password.
    Windows Authentication

    SQL Server Authentication

    For SQL Server Authentication, enter the User Name and Password.

    SQL Authentication
    Note: The product functions even if the table compression is enabled.
    S. no. Start-up Type Required Permission(s) for Login Comments
    1 (First start)
    • Server Roles page:
      1. public
      2. dbcreator
    • User Mapping page ('Database role membership' for 'eventlog' DB):-
      1. db_datareader
      2. db_datawriter
      3. db_ddladmin
      4. db_backupoperator
    • Control privilege on the created certificate, execute following queries:-
    • 'public' is the default minimum permission
    • 'dbcreator' is required to create 'eventlog' database, else you'll get "CREATE DATABASE permission denied in database 'master' " error message
    2 Warm Start

    GRANT CONTROL ON SYMMETRIC KEY::[##MS_DatabaseMasterKey##] TO [user]; -- if not provided, user will not know if a master key exists in DB

    GRANT CONTROL ON SYMMETRIC KEY::[ZOHO_SYMM_KEY] TO [user];

    GRANT CONTROL ON CERTIFICATE::[ZOHO_CERT] TO [user];

    • 'db_backupoperator' is required only if the user wishes to back-up the 'eventlog' database
  10. Click the Test Connection button to verify the correctness of the credentials. If the test fails, recheck and enter the correct credentials.
  11. Click the Configure DB button to save the SQL Server configuration. Note that configuring the settings of the SQL Server database may take a few minutes.
  12. The product will start automatically after the configuration is complete.

If you are already using the EventLog Analyzer with PGSQL or MySQL and you want to change the database to MS SQL, please refer the Migrating EventLog Analyzer Data from PGSQL to MS SQL Database page or Migrating EventLog Analyzer Data from MySQL to MS SQL Database page respectively and follow the procedure given there.

Copyright © 2020, ZOHO Corp. All Rights Reserved.

Get download link