Example activation script 2

This SQL script demonstrates a simple but insecure way to activate bulk import on an iBase database.

A BULK INSERT statement is run in the security context of the user who ran this script. This method of activating Bulk Import has the following security disadvantages:

A BULK INSERT statement is in a security context with more permissions than necessary.
It requires that the TRUSTWORTHY database property is set to ON.

You can copy and paste this script into SQL Server Management Studio. Some of the values that are used in this script must be modified for your iBase database.

Note: Comments are indicated by /* and */. The parts of this script that require modification are marked with exclamation marks.

USE Your_DB;
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace all occurrences of the string 'Your_DB' with the 
SQL Server name of the iBase database for which Bulk Import 
will be activated.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
Add or update an iBase configuration setting. The 
'SQLServer:BulkImportDataFileLocation' configuration setting 
specifies a UNC path for a folder that Bulk Import can use 
for creating the temporary files.
iBase users will need to have a Windows account that has 
read/write permissions on this shared network location.
The account under which SQL Server runs requires read 
permission on this shared network location.
*/
IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportDataFileLocation') 
   UPDATE _Configuration_Text SET Data = '\\computername\sharedfolder\' 
      WHERE Item = 'SQLServer:BulkImportDataFileLocation' ;
ELSE 
	BEGIN
		INSERT INTO _Configuration_Def (Item, Encrypted) VALUES
		   ('SQLServer:BulkImportDataFileLocation', 0);
	   INSERT INTO _Configuration_Text (Item, Data) VALUES
		   ('SQLServer:BulkImportDataFileLocation',
		  '\\computername\sharedfolder\');
	END
PRINT 'Bulk Import Data File configuration setting updated.';


/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace the string '\\computername\sharedfolder\' 
with the name of your shared network location.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
Add or update iBase configuration settings: 
'SQLServer:BulkImportColumnDelimiter' and 
'SQLServer:BulkImportRowDelimiter'.
These configuration settings specify terminators to be 
used in the temporary data file that Bulk Import creates 
as part of the Bulk Import process. They must be specified 
using (escape) characters that are understood by the 
T-SQL BULK INSERT statement.
Under normal circumstances you will not need to change these 
configuration settings.
*/


DECLARE @fieldTerminator VARCHAR(20);
DECLARE @rowTerminator VARCHAR(20);
SET @fieldTerminator = '\0F\0'
-- !!! If you require a different field terminator, specify it here.!!
SET @rowTerminator = '\0R\0'
-- !!! If you require a different row terminator, specify it here.!!
IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportColumnDelimiter') 
   UPDATE _Configuration_Text SET Data = @fieldTerminator 
   WHERE Item = 'SQLServer:BulkImportColumnDelimiter' ;
ELSE 
	BEGIN
	   INSERT INTO _Configuration_Def (Item, Encrypted) 
	   VALUES ('SQLServer:BulkImportColumnDelimiter', 0);
	   INSERT INTO _Configuration_Text (Item, Data) 
	   VALUES ('SQLServer:BulkImportColumnDelimiter', @fieldTerminator);
   END
IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item = 'SQLServer:BulkImportRowDelimiter') 
   UPDATE _Configuration_Text SET Data = @rowTerminator 
   WHERE Item = 'SQLServer:BulkImportRowDelimiter' ;
ELSE 
	BEGIN
		 INSERT INTO _Configuration_Def (Item, Encrypted) 
		   VALUES
			  ('SQLServer:BulkImportRowDelimiter', 0);
		 INSERT INTO _Configuration_Text (Item, Data) 
		 VALUES
			('SQLServer:BulkImportRowDelimiter', @rowTerminator);
	  END
PRINT 'Row and column delimiter configuration settings updated.';


/* 
Add or update the iBase configuration setting: 
'SQLServer:BulkImportIdentifierSelectivityThreshold'.
This configuration setting specifies the threshold ratio 
of source to target records, after which Bulk Import 
fails because there are too many matches; i.e. the import 
identifiers are not sufficiently selective.
Under normal circumstances you will not need to change 
this configuration setting.
*/


IF EXISTS ( SELECT * FROM _Configuration_Text 
   WHERE Item =
      'SQLServer:BulkImportIdentifierSelectivityThreshold') 
   UPDATE _Configuration_Text SET Data = @rowTerminator 
   WHERE Item = 'SQLServer:BulkImportIdentifierSelectivityThreshold' ;
ELSE 
	BEGIN
		INSERT INTO _Configuration_Def (Item, Encrypted) 
		VALUES 
			('SQLServer:BulkImportIdentifierSelectivityThreshold', 0);
	   INSERT INTO _Configuration_Text (Item, Data) 
	   VALUES 
		   ('SQLServer:BulkImportIdentifierSelectivityThreshold', '10');
	END
PRINT 'Identifier Selectivity Threshold setting updated.';
USE Your_DB;
go
ALTER DATABASE Your_DB SET TRUSTWORTHY ON;
/*
Stored procedure used by Bulk Import for executing a BULK 
INSERT command.
Uses impersonation to execute as the system administrator.
*/
ALTER DATABASE Your_DB SET TRUSTWORTHY ON;
go
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = '_BulkInsert' )
   DROP PROCEDURE dbo._BulkInsert
go
CREATE PROCEDURE dbo._BulkInsert 
   @stagingTable VARCHAR(500), 
   @dataFile VARCHAR(500), 
   @formatFile VARCHAR(500), 
   @kiloBytesPerBatch VARCHAR(500), 
   @maxErrors INT 
WITH EXECUTE AS SELF
AS 
BEGIN 
   EXEC( 
      ' BULK INSERT ' + @stagingTable + 
      ' FROM ''' + @dataFile + '''' + 
      ' WITH (FORMATFILE =''' + @formatFile + 
      ''', MAXERRORS=' + @maxErrors + 
      ', KILOBYTES_PER_BATCH=' + @kiloBytesPerBatch + ')' 
   ) 
END 
PRINT '_BulkInsert stored procedure (re) created.';