Configuring Josytal DB Applications SharePoint

After installation on the server computer, for Josytal DB Applications SharePoint and other remote workstations (computers/users) connecting to it to function proper, you will need to:

I. On the server computer (workstation) 1. Specify the Port Number to be used by the application and get the network IP address of the server computer.
2. Specify string (parameters) for Josytal DB Applications SharePoint to connect to the database.
3. Create user account for remote workstations, specifying user's rights and privileges in the database.
4. Specify other optional parameters such as password to launch the application, auto-run the application on system start-up etc. II. On the remote workstations (client/user computers) 1. Specify the Port Number for connecting to Josytal DB Applications SharePoint via LAN (step 2 above).
2. Specify the network IP address of the server computer, on which Josytal DB Applications SharePoint has been installed and running (step 2 above).
3. Specify workstation's account ID (step 3 above).
4. Use PIN Code to protect database connection parameters page against unauthorized modifications.
Configuring remote workstations to connect to the database is treated in a separate chapter.

I. On the server computer (workstation)

If you have installed Josytal DB Applications SharePoint in the default directory and port number 55 was free on your system, then the application should be running immediately after installation. "Connected" message should be displayed on the status bar at the bottom of the main window. The first button to the left should read Stop with a green plus icon.

However, there are other vital parameters you still need to determine (or know) in other to connect remote workstations (users/computers) to the application. Namely, the server computer network IP Address and the Port Number being used, User Accounts ID with their rights and privileges in the database The first two parameters you can auto-detect, but the third you must set manually.

1. Specifying the Port Number to use for connecting to database and server computer Network IP Address

Josytal DB Applications SharePoint uses socket connection, therefore you must specify a free Port Number and connection TimeOut before you can connect to the database.

By default, Port Number for Josytal DB Applications SharePoint has been set to 55. This is a random value, so if the port is busy, then you must choose any other one that is free. If the application displays an error message "Could not bind socket. Address and port are already in use", this means on your system the default port (55) is occupied by another program. So you must specify another port number that is free, using the method described below.

Josytal DB Applications SharePoint is equipped with a built-in utility that enables you to auto-detect Computer Name, Local and Network IP Address and also scan for busy Ports. If a port is not busy, it means it is free and therefore can be used. In order to detect correct network IP address, during the process, make sure the computer is connected to and active in the local area network . Also make sure the computer is disconnected from internet, as this may lead to displaying a wrong value.

When the computer is not connected to the local network, the computer IP address will be thus: 127.0.0.1 which is the same thing as localhost. When connected to the local network, the address will be to something similar to: 269.154.104.xxx - this is called Network IP address, which you will need to connect other remote client-computers (workstations) to the server computer. So it is extremely important the server computer is active in the local area network before trying to get its IP Address.

Launch Josytal DB Applications SharePoint
From the main menu, select Tools and then either Computer IP Address or Detect busy ports sub-menu (pic.2)
In the IP-Ports tab, the computer name and its network IP address will be automatically displayed in the appropriate boxes
To auto-detect busy ports, specify minimum and maximum port numbers (range) within which to scan. By default it is 1 and 1024 respectively.
Click the Detect busy ports button
Wait while the application detects and lists busy ports.

Jot down any number that is not busy between minimum and maximum numbers. Any number that is not busy is free and therefore can be used. Also write down the (Host) IP Address - you will need them to configure remote client computers (workstations) for connecting them to the database via Josytal DB Applications SharePoint. Close the form to return to the main page.

pic.2. Auto-Detecting busy ports and IP Address

NOTE: In the example, port number 55 is listed as busy, because it is being used by Josytal DB Applications SharePoint. Detect, choose and use any other free port number only on the event, if upon launching the application error message "Could not bind socket. Address and port are already in use" is fired.

If necessary, specify the Port Number (any free port number from 1 to .... any positive number). By default it is 55. This is a random value, so if the port is busy, choose any other one that is free. You can auto-detect (scan) busy ports on any computer as described above. If you change the Port Number, then you must re-start the application for the new value to take effect.
Specify the connection TimeOut in seconds. Set TimeOut to the amount of time that should elapse before connection operation aborts under the assumption that the socket connection has failed. By default it is 30 sec.
If you want Josytal DB Applications SharePoint to automatically start each time you start the server computer or Windows, then check the Auto Run on Windows start-up box

You can now proceed to specifying Connection String to be used for connecting to the database by clicking the Settings... button.
(Much later, using the Tools >> User Accounts menu, pic.2, you can create and manage user accounts: add users/workstations' information such as names, departments, status, network IPs, database access rights and privileges etc.)

2. Specifying connection string (parameters) for Josytal DB Applications SharePoint to connect to the database

The first time when you run Josytal DB Applications SharePoint it will display an error dialog box, prompting you to set the necessary connection parameters to the database. Click the Yes button to proceed to the Connection Setting Wizard page or click No to do the settings later.

Josytal DB Applications SharePoint is capable of working with either a single-file database or database on MS SQL server. The connection string (parameters) will reflect the type of database you have chosen to use.

Using MS SQL Server DataBase
1. If you have opted for MS SQL Server, then install and configure the database server prior to installing Josytal DB Applications SharePoint
2. If you are installing the free copy of MS SQL Server 2005 Express Edition, then follow the instructions here
3. To install other versions of MS SQL Database Server, consult the server vendor Instructions and documentations
4. Launch the MS SQL Database Server
5. Open and Execute the SQL database script file (azcntmngnt_dump.sql) that comes with Josytal DB Applications SharePoint. By default, the script is located in the folder: C:\Program Files\Josytal DB SharePoint\Data. The script will create the initial Database on the MS SQL Server. After successful execution of the script, you will have a new database named "azcntmngnt" installed.

Using single-file database
1. If you are not using MS SQL Database Server (for example, you have less than 10 users), then decide your server computer, through which other workstations (users) will connect to and share the single-file database.
2. Install the Josytal DB Applications SharePoint on the server computer (if you have already done so). Note the folder where the database file is located. By default, it should be something thus: C:\Program Files\Josytal DB SharePoint\Data. The folder and all its content must have at least read, write, and modify privileges. Not just the database file, .mdb file, but the entire folder.
3. Note the name of the single-file database file. By default, it should be something thus: C:\Program Files\Josytal DB SharePoint\Data\azcntmngnt.mdb

NOTE:
If you have installed Josytal DB Applications SharePoint in the default directory and port number 55 was free on your system, then the application should be running immediately after installation, automatically establishing connection to the single-database file shipped with the application. You should see "Connected" message displayed in the status bar at the bottom of the main window. The first button to the left should read Stop with a green plus icon. In such case, you may skip the next step, as Josytal DB Applications SharePoint is already connected to the database. You can instead go to creating and managing user accounts

Connecting to the database
1. From Josytal DB Application SharePoint, press the "Settings..." button to activate the Connection Setting Wizard.

2. Select the Use Connection String option
3. Click the Build... button
4. Define OLE DB driver in [Provider] tab.
Select [Microsoft OLE DB Provider for SQL Server] for MS SQL or [Microsoft Jet 4.0 OLE DB Provider] if you are using single-file database described above.

Notes: If you must use the [Microsoft Jet 3.51 OLE DB Provider] in the above examples, you should be aware that Jet 3.51 OLEDB provider is designed to open Access 97 databases only. Jet 4.0 OLEDB provider is designed to open Access 2000 or Access 97 single-file databases.

Click the Next>> button at the bottom right of the tab

MS SQL Server Database Option
5. If you have opted for [Microsoft OLE DB Provider for SQL Server] in step 4, then you will be taken to the Database Server connection parameters tab.

Enter your server name. If you have installed MS SQL Server 2005 Express Edition and you have opted for default database name during installation, then server name should be SQLExpress
Select [Use Windows NT Integrated security] to use Windows authorization (default option), or [Use a specific user name and password] to set User name and Password
Select the azcntmngnt database from the drop-down database options.
You can check up the connection by pressing the "Test Connection" button.
Click OK button

If you have done everything correctly, then Josytal DB Application SharePoint confirmation dialog should pop-up, prompting you to save the connection parameters for future use. Click Yes button.

The settings are saved in the josytalsvr.ini file, located in Josytal DB Application SharePoint directory. For references purposes, typical minimum default connection parameters look thus :

[Options]
AutoRun=1
[Connection]
ConnectionString=Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=azcntmngnt;Data Source=SQLExpress;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=Master;Use Encryption for Data=False;Tag with column collation when possible=False
Port=55
TimeOut=30

From the Josytal DB Applications SharePoint main window, click Run button to connect the SharePoint to the database.
You should see "Connected" displayed on the status bar at the bottom of the application window.
To double-check that Josytal DB Applications SharePoint has actually been connected to the database, do the following:

From the application menu Tools, select and click User Accounts. Wait for the utility form and Users tab to load
If the table (picture below) is published with users' data, then Josytal DB Applications SharePoint is actually connected to the database

If everything is successful write down the Port Number for being used for connection and the network IP Address of the server computer - you will need them to configure remote client computers (workstations) for connecting them to the database via Josytal DB Applications SharePoint.

Single-File Database Option
6. If you have opted for [Microsoft Jet 4.0 OLE DB Provider] in step 4, then you will be taken to the tab, where you can browse your computer and choose the single-file database file.

Click on the button to browse to the folder where you have installed Josytal DB Applications SharePoint. By default it should be C:\Program Files\Josytal DB SharePoint\Data
Select and double-click the database file azcntmngnt.mdb.
If necessary, set UserName and Password to connect to the database. By default, UserName is Admin and Password is empty, meaning no authentication needed to access the database file.
You can check up the connection by pressing the "Test Connection" button.
Click OK button

If you have done everything correctly, then Josytal DB Application SharePoint confirmation dialog should pop-up, prompting you to save the connection parameters for future use. Click Yes button.

The settings are saved in the josytalsvr.ini file, located in Josytal DB Application SharePoint directory. For references purposes, typical default connection parameters look thus :

[Options]
AutoRun=1
[Connection]
ConnectionString=Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Program Files\Josytal DBA SharePoint\Data\azcntmngnt.mdb;Mode=Share Deny None;Extended Properties="";Persist Security Info=False;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False
Port=55
TimeOut=30

From the Josytal DB Applications SharePoint main window, click Run button to connect the SharePoint to the database. You should see "Connected" displayed on the status bar at the bottom of the application window.
To double-check that Josytal DB Applications SharePoint has actually been connected to the database, do the following:

From the application menu Tools, select and click User Accounts. Wait for the utility form and Users tab to load
If the table (picture below) is published with users' data, then Josytal DB Applications SharePoint is actually connected to the database

Optional Settings for Single-File Database
Even though you do not need MS Access Application installed on your system to use access database that comes with the software, if you decide to fine-tune database connection parameters, then you can do that by double-clicking on the file itself. If you have MS Access application installed on your system, it will be launched and open the file, then you can add the following optional settings to the database file

Open the database file (azcntmngnt.mdb), by double-clicking it
Go to Tools, Options, and Advanced Tab.
Set the Default Open mode to Shared and the Default Record Locking to Edited Record
While you are in the Options area, go over to the General tab and turn off Compact on Close and Name AutoCorrect
Save changes and Close the application

3. Creating user account for remote workstations, specifying user's rights and privileges in the database

After you have set connection parameters and have successfully established connection to database, you can now proceed to creating and managing user accounts: add users/workstations' information such as names, departments, status, network IPs, database access privileges etc.)

Multi-factored database security model. What is it?
According to recent Forrester study, 80 percent of data security breaches involve insiders, employees or those with internal access to an organization, putting information at risk. The big challenge for companies today is to strike the right balance between providing workers with appropriate access and protecting sensitive information as much as possible.

Traditionally, database users are assigned a database administrator (DBA) role or granted multiple system privileges. Meaning, once a user has gained access to the database s/he can manipulate (read, write, delete, export and print out) records without any further restrictions. From information security point of view and research studies, this is very, very risky. And you must be aware that this is the approach most of our competitors use!

To help ensure the safety, integrity and privacy of corporate information, Multi-User versions of our software use comprehensive, multi-factored database security approach. This approach is built on the defence-in-depth principle, which introduces multiple mechanisms to augment the traditional user and role security model. Simply put, restrictions and boundaries are set up so that even employees with database access privileges MAY NOT freely use, alter, export or print out sensitive information from the database.

How it works

1. Roles and Privileges boundaries. Administrator may assign different combinations of privileges to different users on different remote computers/workstations for manipulating records stored in the database after gaining access. For example:

User #1 - Read/Write/Delete/Print/Export

User #2 - Read/Write/Print

User #3 - Read/Print

User #4 - Read only

User #5 - Read/Delete

User #6 - Read/Write

2. Records isolation. Administrator may specify rules such that users will have access to and can manipulate only the records they have added to the database. For example, a user's tasks and schedules or his private correspondence.

Multi-factored database security model is powered by Josytal Database Applications SharePoint
that serves as a single database point of entry (server). After gaining access to the database, Josytal Database Applications SharePoint will monitor and enforce user's roles and privileges granted to him/her by the administrator.

By default, all users have administrators rights, i.e. can read, write, delete, export or/and print out information in the database, once connected. In a multi-user environment, this may not always be appropriate, partly due to security reasons. Further, consider a situation where 3 managers have added to the database different tasks and schedules, notes with reminder, contacts data. It is logical that each of the managers should have access only to the information he/she has added to the database.

Thirdly, there are certain common or system data (e.g. lookup values such as city names, country names, contact groups, telephone types etc.) creation and deletion of which should be handled by one person, preferably the administrator. Such common information is shared by all users and should neither be modified nor deleted by just anybody. Nevertheless, the administrator has the right to delegate his rights, solely or partly, to chosen users. Administrator has all rights (read, write, delete) and rights to assign rights and privileges to other users. His ID in the database is 1.

To specify users' rights and privileges, do the following:

From Josytal DB Applications SharePoint menu, select and click Tools >> User Accounts (pic.1). The utility form will be launched and Users tab will be activated (pic.2).
In the system users are identified by their unique IDs, which is automatically assigned each time a new user is added. Record with ID=1 belongs to the administrator and can not be deleted, even by the administrator
Click the Add button to create a new user account in the system. Enter all necessary information, including name, department, status (value for status by default will be User), Computer name Network IP Address, if already known.
Check or Uncheck the necessary boxes to assign database access rights and privileges to the user - pic.2.
Write down on a paper the user's ID - you will need the information to configure the copy of client application installed on the user's computer.

Table 1 below summarizes the rights and privileges that can be assigned to users, their default values and description.

Caution:
PIN Code is secret number, arbitrarily asigned to remote workstation, kept and used by system administrator to protect database connection parameters from unauthorized modifications on remote users' workstations. Leave the PIN Code information blank until you have configured all other workstations and they are functioning properly with the server computer. If you assign value (any arbitrary value) before a particular workstation has been properly configured, you may be denied access to the configuration page on the remote workstation. How to use PIN Code on users' workstations is treated in another chapter (configure remote workstations (user computers) to Access the Database)

Hints:
User's computer name and network IP can also be auto-detected from the copy of client application installed on user's computer.

Pic.1	Pic.2: Administrator's rights by default. Managing user accounts
Pic.3: a typical user's rights by default

Table1. Description of default users' rights and privileges

Name	Default Value	Description
I. Section Records
Can Access Own Records Only	Yes (true, checked)	User can retrieve, edit, delete only the records he has added to the database. Records added by other users will not be visible nor made available to him - recommended
Can Add Section Records	Yes (true, checked)	User can add records to major sections (Contacts, Companies, NotePad, Tasks, Batch Mailer, Invoices etc) of the software installed on his computer.
Can Edit Section Records	Yes (true, checked)	User can edit records in major sections (Contacts, Companies, NotePad, Tasks etc) of the software installed on his computer. When combined with Can Access Own Records Only, then user will be able to edit only the records he has added to the database
Can Delete Section Records	Yes (true, checked)	User can delete records from major sections (Contacts, Companies, NotePad, Tasks etc) of the software installed on his computer. When combined with Can Access Own Records Only, then user will be able to delete only the records he has added to the database
Can Export/Print Records	Yes (true, checked)	User can export data to various office programs (Word, Excel, Text, HTML etc.) or/and print out data from major sections (Contacts, Companies, NotePad, Tasks etc) of the software installed on his computer. When combined with Can Access Own Records Only, then user will be able to export/print only the records he has added to the database
II. Lookup tables and Records
Can Add Lookup Records	No (false, unchecked)	User can not add records to the lookup tables - highly recommended
Can Edit Lookup Records	No (false, unchecked)	User can not edit records in the lookup tables - highly recommended
Can Edit Lookup Records	No (false, unchecked)	User can not delete records from the lookup tables - highly recommended
DenyAccess	No (false, unchecked)	User can not connect to the database from his workstation. Note that in all other cases, user can connect to the database, but may be with limited rights and privileges. You can also deny access by simply editing the User's Computer Name to a wrong value, since the combination of ID and Computer Name is used to validate users' privileges before granting or denying them connection to the database via Josytal DB Application SharePoint.
III. PIN Code
PIN Code	Empty (blank)	PIN Code is secret number, assigned, kept and used by system administrator to protect database connection parameters from unauthorized modifications on remote users' workstations. Leave the PIN Code information blank until you have configured all other workstations and they are functioning properly with the server computer. If you assign value (any arbitrary value) before a particular workstation has been properly configured, you may be denied access to the configuration page on the remote workstation. How to use PIN Code on users' workstations is treated in another chapter (configure remote workstations (user computers) to Access the Database)

Hints:
If you decide to alter the default values being automatically assigned to users upon creation, then open the database (for example, C:\Program Files\Josytal DB SharePoint\Data\azcntmngnt.mdb).
Select and double-click the database file - table named Users - and alter the necessary boolean fields default values. For example, you may alter CanAccessOwnRecordsOnly field default value to False (0, No or Unchecked, depending on your system settings) instead of True (1, Yes or Checked, depending on your system settings). The new default values will be applied to all new user accounts created henceforth.

Apart from displaying user information, the table in the user tab is also used to display other useful information for the attention of the administrator: CreateDate, EditDate, LastActivityStart, LastActivityEnd, NowConnected.

Table2. Fields for the attention of the administrator

Name	Default Value	Description
CreateDate	Actual date-time	The date-time user account was created
EditDate	Actual date-time	Last date-time user account information was edited
LastActivityStart	Actual date-time	Last date-time user gracefully connected to the database from his/her remote workstation or made attempts to do so
LastActivityEnd	Actual date-time	Last date-time user gracefully disconnected from the database
NowConnected	No (false, unchecked)	Indicates whether the user is connected to the database at the moment. May be useful when you want to do database backup or repair. You should make sure no user is connected to the database before embarking on such tasks.

Done. You may now run Josytal DB Application SharePoint and go ahead to configure remote workstations (user computers) to Access the Database with assigned rights and privileges. Remember, to configure remote workstations you will need the following information:
1. The Port Number being used by Josytal DB Application SharePoint to connect to the database
2. Network IP Address of the server computer, on which Josytal DB Application SharePoint has been installed
3. ID you have assigned to the remote workstation

SPECIAL NOTES:
1. Even if you have 1 or 1000 users connecting to the database, you only need 1 copy of Josytal DB Application SharePoint to be installed on the server computer and connected to the database. Other users (workstations) only need to connect to the SharePoint in order to have access to the database.

2. You may install Josytal DB Application SharePoint on the same computer along with, for example, A-Z Contacts Manager NT application. For example, if you have only 1 user and one computer. In future other users (workstations) can also connect to the SharePoint >>Database through the computer. The computer will function as server to the rest.

3. Your database and Josytal DB Application SharePoint may be located on different computers. What is important is that you specify the correct connection string to the database for Josytal DB Application SharePoint.

4. If you encounter problems setting up Josytal DB Application SharePoint, then it will be helpful you test the connection between a workstation and the SQL Server and/or Database outside of Josytal DB Application SharePoint. If you can connect via ODBC / DSN from Windows, you will be able to connect through Josytal DB Application SharePoint. Likewise if you are unable to connect via ODBC / DSN from Windows, Josytal DB Application SharePoint is not going to successfully connect to the SQL Server either.
If you are unable to connect via ODBC / DSN from Windows, the problem is not related to Josytal DB Application SharePoint and is likely a network permission, firewall or SQL authentication issue.

5. It is recommended that you remove all restrictions before installation and configurations. Later, you can specify restriction parameters and see how things will work.