IndySoft SQL Database Creation for On-Premise Customers

Introduction

The IndySoft client application is compatible with the following database engines: Firebird, Oracle, and Microsoft SQL Server. This document focuses specifically on a MS SQL Server instance installed at a customer’s site.

The instructions that follow will provide details on creating a clean IndySoft database, applying a preparation script, and creating logins/users to accommodate daily use of the IndySoft application as well those for administrative tasks, like performing upgrades.

Database and User Types

There are two models for users/logins included in these instructions described below. Both will create a limited login for the IndySoft Client application allowing it to function properly for daily use. This application User will be limited to roles allowing Reads, Writes, and Execution of stored procedures only. Any steps required for “Contained” database users will be flagged in these procedures that follow with “**”.

Traditional

This model is a documented, well-established method that works with older versions of MS SQL Server. It includes creating a Login for the server instance and matching it with a database User. All of the instructions below support creating this as default.

Contained

This model was introduced in MS SQL Server 2012 and involves creating database Users that are completely contained in the database being created and have no reliance on instance-level Logins. The advantage of this is portability should databases be moved or restored. There are limitations to this method, including the support for replication. Additional details about the differences can be reviewed here: Microsoft SQL Server Contained vs Traditional Databases

page1image44187008 page1image44187392 page1image44184896

Prerequisites
A clean install of Microsoft SQL Server (minimum 2008 R2) and the following:

  • ●  Mixed mode authentication enabled

  • ●  “Contained Databases” option Enabled on SQL Server Instance (only needed if using

    Contained Users)

  • ●  Windows Server or Domain Admin-level credentials or Microsoft SQL Server SA level

    credentials

  • ●  Microsoft SQL Server Management Studio (SSMS) latest edition

  • ●  IndySoft Client App installed but not launched

    Appropriate Logins for Admin vs Daily Use

    User Level Function Performed

    Server or Domain Admin Application Installation or updates

    IndySoftClient (SQL Login/User) Standard Client DB Connection set in DatabaseSettings.exe

    Database Creation Procedures

  1. Log into your MS SQL Server with SSMS using SA level privileges

  2. Navigate to the Databases folder, right-click and choose New Database

  3. In the New Database dialogue, provide the following:

    a. General Page Database Name: (ex., I ndySoftDB) b. Options Page Containment Type: None (default)

    ** Contained User: select Partial instead of None

  4. Allow database creation to complete

Server Admin, Domain Admin, SQL SA

Database Creation
Database Login, User, Role Adds, Alterations IndySoft Database Upgrades

page2image44251200 page2image44252544

  1. Open a new query window in SSMS, edit and execute one of the following scripts to create a Contained User or Traditional Login and associated database User for the application. Substitute the password for one you wish to use. The examples below assume the Database Name is IndySoftDB.

    ** Contained User Only Script

    Special Note: This script will not execute properly if the database server and database itself are not setup for “contained” use as called out in the prerequisites.

    USE IndySoftDB
    CREATE USER IndySoftClient

    WITH PASSWORD = 'StrongPassword1!' GO

    Traditional Login/User User Script

    CREATE LOGIN IndySoftClient
    WITH PASSWORD = 'StrongPassword1!'

    GO

    USE IndySoftDB
    CREATE USER IndySoftClient

    FOR LOGIN IndySoftClient GO

  2. Close the query window

  3. Open a new query window, edit and execute the following script to create a special role allowing execution of stored procedures in addition to adding the new IndySoftClient User to the three database roles for proper application functionality:

    USE IndySoftDB
    CREATE ROLE db_spexecute
    GRANT EXECUTE to db_spexecute
    ALTER ROLE db_datareader ADD MEMBER IndySoftClient ALTER ROLE db_datawriter ADD MEMBER IndySoftClient ALTER ROLE db_spexecute ADD MEMBER IndySoftClient

8. As a double check once the above step is complete, run the following in a new query window to verify that the IndySoftDB user is in the correct DB Roles; db_datareader, db_datawriter, and db_spexecute.

SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName

FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1

ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2

ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R'
ORDER BY DP1.name;

The output of this command should look like this:

db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin db_spexecute public

No members
No members IndySoftClient IndySoftClient No members
No members
No members dbo
No members IndySoftClient No members

  1. Close any open query windows

  2. In SSMS, click File, Open, and choose File and select the database preparation script called MSSQL-Unicode.sql (if Unicode is desired - if not then load MSSQL.sql) located at the following path:

    a. \Program Files\IndySoft\DBScripts

  3. Insert the following at the top of the script in the query window and then execute query:

    USE IndySoftDB

12. Using Windows Explorer, navigate to the IndySoft application root folder \(Program Files\IndySoft) and execute the file DatabaseSettings.exe, then apply the following settings:

  1. Click Test Connection to make sure the connection parameters, including the User and Password, are correct. If successful, click OK and exit the DatabaseSettings.exe app.

  2. Launch the IndySoft client application. It should successfully connect to the database, indicate that this is a new installation that requires registration, and will offer to launch the IndySoft Registration Utility. Follow the instructions for obtaining a license key from IndySoft and register the application accordingly.

  3. Upon first use of the IndySoft client application, default settings will be applied to the database and then the application will prompt you for initial company settings.

page5image38065856

Database Upgrades

If the IndySoft application detects that the database version you’re running is out of date compared to the application, follow this process to update the database.

  1. Log into the Windows server with Admin level privileges

  2. Open Windows Explorer and navigate to the \Program Files\Indysoft folder and

    launch DatabaseSettings.exe

  3. Change the User and Password your site’s SA level database user “or” toggle the

    connection dialogue to use Windows authentication and enter a valid Windows Server or

    Domain user with admin privileges.

  4. Click Test Connection to verify the parameters are correct, then click OK.

  5. While still in Windows Explorer and in the IndySoft folder, launch isupdb.exe

  6. This application will show the current version and target version for the upgrade. Follow

    the instructions and complete the upgrade.

  7. Exit the isupdb.exe application, and again launch DatabaseSettings.exe

  8. Change the User and Password back to the IndySoftClient created earlier in this

    document and click Test Connection. Then click OK to accept.

  9. Exit the DatabaseSettings.exe application.