Activating bulk import

Bulk import is an SQL Server only feature that allows data to be imported from a set location. Before an iBase database administrator can run bulk import, the server that holds the database must be configured for bulk import.

About this task

This method of configuring the database and server is suitable if:
  • The security context for the server is appropriate for your organization.
  • The iBase database administrator is member of the SQL Server sysadmin fixed server role.
  • The SQL Server service has permission to create and delete files in the SQL Server data folder.

After the iBase database administrator has the right SQL Server permissions, bulk import can be activated. You must reactivate Bulk Import if you move the database to a different server.

Procedure

  1. In iBase Designer, select Tools > Feature Availability > Activate Bulk Import.
    The command is only available if the database uses Microsoft SQL Server.
  2. In Connect to Server, enter the details that are required to connect to the server, as a server administrator. You can use:
    • Windows authentication, in which case you do not need to enter a user name or password - your Windows user account must have the SQL Server permissions listed above.
    • SQL Server authentication, in which case you must enter an SQL Server login, and password, that has the permissions listed above.
    Note: You can use either method of connecting to the server, regardless of the security method used in the iBase connection file.
  3. In Bulk Import Password, enter a password.
    This password is used by SQL server to:
    • Create the certificate
    • Back up the certificate (the password is required to restore the certificate from the backup)
    • As the password for the special login that provides the security context for performing BULK INSERT
  4. In Bulk Import Data Files Folder, enter the UNC path of the shared folder used by bulk import for temporary files.
    Attention: This folder should be secured to prevent unauthorized access to the temporary copy of the data. This temporary file cannot be deleted if iBase, or Scheduler, crash during a bulk import.
    Note: This folder must be shared. iBase users must have a Windows account that has read/write permissions for this folder. The account under which SQL Server runs requires read permission for this folder.
  5. Click OK.

What to do next

The database is then activated for bulk import on the specified server.

Attention: This command temporarily enables xp_cmdshell to move certificates from the primary database to the iBase database. In the unlikely event, that it fails to disable it at the end of the procedure, this message is displayed:
Cannot disable xp_cmdshell. 
Disable xp_cmdshell manually otherwise your server security might be compromised.
If this occurs, you must take immediate steps to disable xp_cmdshell yourself.

You can change the location of the bulk import data files folder using the advanced database properties, without having to reactivate the database for Bulk Import.