Creating security files and a publication database

Before you begin

The names that you choose for the security file and database are used to generate the names of the SQL Server databases so you might want to agree a convention for file naming with your SQL Server administrator. See Before creating any iBase databases for details.

Procedure

1. Create a new security file:

   1. At the publisher site, create a new database folder to hold the security file and database that you create. The folder must be shared and should be on the database or application server machine.

   2. Using iBase Designer, log on as a security or system administrator and create a new security file in Microsoft Access format. You convert this file to SQL Server (upsize it) later.

   3. In the Security Manager, add user groups and users. As a minimum, add the system, database, and security administrators for the publisher and subscriber sites. At this stage, you can also define Data Access Control groups but you cannot set permissions for them yet.

   4. Back up the security file.

      Attention: You will need the original Microsoft Access security file if you want to extend the iBase system in the future by adding additional subscriber sites.

      See Managing Security for further details.

2. Distribute the security file to the subscriber sites: The security files used at the publisher and at all subscriber sites must be converted from Microsoft Access format to SQL Server (upsized) from the same security file. For background information on why this is necessary, see Importance of the security file at the publisher site.

   1. Distribute the security file, which is in Microsoft Access format, to the subscriber sites using any appropriate method, such as copying to CD, sending by email, or copying over a local or wide area network.

   2. At each subscriber site, create a shared folder and copy the supplied security file to it.

   Attention: Do not make any changes to the security data at the subscriber site. You can rename the file if required but any other changes are lost when replication is configured. For information on file names, see Before creating any iBase databases.

3. Convert the security files to SQL Server, at the publisher site and at all subscriber sites:

   You must convert (upsize) the security file from Microsoft Access format to SQL Server before you can replicate it. This process leaves a security connection file (.ids file) in the iBase database folder and create an SQL Server database with the name <file>_Sec on the designated server (where <file> is the name of the ids file).

   After you upsize the security file, you need to assign a site identifier to the security database.

   1. Using iBase Designer, log on as a security or system administrator using the Microsoft Access security file distributed by the publisher site.

   2. Click Cancel at the prompt to create or open a database.

   3. Select Tools > Upsize Security File to SQL Server.

   4. Click OK when you are informed that a backup is made. This is a backup of the original Microsoft Access security file and has the file extension .ids.bak (appended with a number, such as .ids.bak1, if there is already a file with this extension in the folder).

   5. Enter the server name.

      Note: Do not select the Local option from the Server list.

   6. Select the security mode. This is Windows Authentication unless your SQL Server administrator directs otherwise.

   7. In Identifier, enter a site identifier, up to 5 characters long. The identifiers used for the security connection file and the database at a site are generally the same but should otherwise be unique within the replicated system. For example, you might use the site identifier PUB for both the security file at the publisher site and the publication database.

   8. Click OK to validate the settings and perform the upsize, then click OK when the upsize is complete. If you want to review the connection details and ID of the security connection file, select File Security File Properties. The path of the security connection file will also be displayed in the status area with (SSE) after the file name to indicate that it is SQL Server format.

   9. Repeat these steps for each site involved in replication.

   10. Back up the connection file at each site (.ids file). If you lose the connection file, you are not able to log on.

       Attention: Do not make any changes to the upsized security file at the subscriber site. You can rename the file if required but any other changes are lost when replication is configured. For information on file names, see Before creating any iBase databases.

4. Protect the security connection file

   In Windows, protect the SQL Server security connection file by making it read-only or by setting appropriate security permissions. This allows any user in iBase Designer to view the properties of the connection file but prevents anyone, including iBase security administrators, from changing the SQL Server connection details.

   You should also ensure that the security connection file is included in any backup schedules for the database folder.

5. Create a new iBase database at the publisher site:

   1. Create a database. This database is configured by the SQL Server administrator as the publication database. The database should have the following mandatory properties:

      Field	Description
      Database type	Select SQL Server. You cannot replicate MS Access databases.
      Server	Enter the name of the server for the publisher site. (Note: Do not select the Local option from the Server list.)
      Use Windows Authentication	Turn on the Use Windows Authentication checkbox, unless your SQL Server administrator asks you to use SQL Server authentication.
      Database identifier	On the Advanced page, enter a unique site identifier, up to five characters in length. This is usually the same identifier as the security connection file used at the subscriber site. For example, the site identifier for a publication database and its security connection file might be PUB.
      Soft Delete	On the Advanced page, turn on the Soft Delete checkbox. (Attention: You must use Soft Delete in a replicated database. Users are not able to open the database if this is turned off.)

      For information on designing databases for replication, see Updating the Database Design.

      Warning: It can be a time-consuming process to modify the database schema after replication is started so it is important to take the time now to review, test, and refine your design. You are not able to change the schema after replication is configured in SQL Server without first disabling replication.

   2. Initialize the database for replication, in iBase Designer, select Tools > Feature Availability > Initialize Database for Replicationand then click Yes.

      Warning: Databases that are created from this template are not initialized for replication.

   3. Create a database template from the initialized database, and send this template to the subscriber sites.

   4. Back up the connection file (.idb file) to your publication database. If you lose the connection file, you are not able to open the publication database.

What to do next

Security connection file

Tell your SQL Server administrator whether you need to replicate the security audit log.

After the security connection file exists:

• At the publisher site, request the SQL Server administrator to configure replication for the security database. You can add user groups of any type, add users, and assign them to groups, and define permissions for SCAC groups either before or after replication is configured (but you should not change the security files at the subscriber sites).

• At each subscriber site, request the SQL Server administrator to configure replication for the security database. Do not change the security data at the subscriber sites until you are notified that replication is enabled for these.

After replication is fully configured, test the replicated security file at each site.

Publication database

Ask your SQL Server administrator to configure replication for this database and, optionally, for the audit log database.

Subscription databases

Create an iBase database at each subscriber site from the new database template. See Creating Subscription Databases for details.

Secure the publication database

Ask your Windows administrator to restrict access to the iBase database folder and to any other folders used by iBase, for example folders used when importing, exporting, and reporting. It is particularly important to restrict access to the database connection file.