Example activation script 3

This SQL script demonstrates the most secure method for activating bulk import. A certificate-signed stored procedure is used for running the BULK INSERT statement.

A BULK INSERT statement is run in the security context of a special user that can run BULK INSERT and nothing else. The iBase database can have the TRUSTWORTHY database property set to OFF.

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 configuration setting 
      updated.';
USE master;
/*
Configure server for xp_cmdshell.
*/
EXEC sp_configure 'xp_cmdshell', 1; 
RECONFIGURE; 
USE Your_DB;


/*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
If you do not want xp_cmdshell to be permanently
enabled you can disable it using the following SQL:
EXEC sp_configure 'xp_cmdshell', 0; 
RECONFIGURE; 
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
Delete _BulkInsert stored procedure.
*/
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = 'dbo' 
   AND SPECIFIC_NAME = '_BulkInsert' )
BEGIN
    DROP PROCEDURE dbo._BulkInsert
    PRINT '_BulkInsert stored procedure deleted.'
END
ELSE
BEGIN
    PRINT '_BulkInsert stored procedure not found.'
END
/*
Delete server authenticator login
*/
USE master; 
IF EXISTS (SELECT * FROM sys.server_principals
   WHERE name = N'i2iBaseServerAuthenticator_Your_DB')
BEGIN 
   DROP LOGIN i2iBaseServerAuthenticator_Your_DB 
   PRINT 'Server authenticator login deleted.' 
END
ELSE
BEGIN
   PRINT 'Server authenticator login not found.'
END


/*
Delete master certificate
*/
USE master; 
IF EXISTS (SELECT * FROM sys.certificates 
   WHERE [name]=N'i2iBaseBulkInsertCertificate_Your_DB') 
BEGIN 
   DROP CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB;
   PRINT 'Bulk Insert certificate deleted from mater database.'
END 
ELSE
BEGIN
   PRINT 'Bulk Insert certificate not found in master database.'
END;
USE Your_DB; 
/*
Delete database certificate
*/
IF EXISTS (SELECT * FROM sys.certificates 
   WHERE [name]=N'i2iBaseBulkInsertCertificate_Your_DB') 
BEGIN 
   DROP CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB;
   PRINT 'Bulk Insert certificate deleted from iBase database.';
END 
ELSE
BEGIN
   PRINT 'Bulk Insert certificate not found in iBase database.'
END
go
/*
Delete bulk insert user
*/
IF EXISTS (SELECT * FROM sys.database_principals 
   WHERE name = N'i2iBaseBulkInsertUser_Your_DB') 
BEGIN
   DROP USER i2iBaseBulkInsertUser_Your_DB;
   PRINT 'Bulk Insert user deleted.'
END
ELSE
BEGIN
   PRINT 'Bulk Insert user not found.'
END
go

/*
Delete bulk insert login
*/
USE master; 
IF EXISTS (SELECT * FROM sys.server_principals 
   WHERE name = N'i2iBaseBulkInsertLogin_Your_DB')
BEGIN 
   DROP LOGIN i2iBaseBulkInsertLogin_Your_DB 
   PRINT 'Bulk insert login deleted.' 
END
ELSE
BEGIN
   PRINT 'Bulk Insert login not found.'
END
USE master; 
/*


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace all occurrences of the following strings with a strong 
password:
'#BulkInsertL0ginPa$$word#'
'#Server C3rtificate Passw0rd#'
'#C3rtificate Backup Passw0rd#'
'#iBase Database C3rtificate Passw0rd#'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
Create certificate in master database
*/
CREATE CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB 
   ENCRYPTION BY PASSWORD = '#Server C3rtificate Passw0rd#'
   WITH SUBJECT = 'For ADMINISTER BULK OPERATIONS permission', 
   START_DATE = '20020101', EXPIRY_DATE = '20300101' 
PRINT 'Bulk Insert certificate created in master database.'
BACKUP CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB 
   TO FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\i2iBaseBulkInsertCertificate.cer' 
WITH PRIVATE KEY (
FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\i2iBaseBulkInsertCertificate.pvk' , 
ENCRYPTION BY PASSWORD = '#C3rtificate Backup Passw0rd#', 
DECRYPTION BY PASSWORD = '#Server C3rtificate Passw0rd#'
);
PRINT 'Master Bulk insert certificate exported to file.';


/*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace all occurrences of the following string: 
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\i2iBaseBulkInsertCertificate.cer
with a complete path, including file name, of the file in which the 
certificate is to be saved.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Replace all occurrences of the following string: 
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\i2iBaseBulkInsertCertificate.pvk
with a complete path, including file name, of the file in which the 
certificate key file is to be saved.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
/*
A Login with permission to perform BULK INSERT 
(ADMINISTER BULK OPERATIONS permission)
*/
USE master;
CREATE LOGIN i2iBaseBulkInsertLogin_Your_DB
   WITH PASSWORD = '#BulkInsertL0ginPa$$word#' ;
REVOKE CONNECT SQL FROM i2iBaseBulkInsertLogin_Your_DB; 
GRANT ADMINISTER BULK OPERATIONS TO i2iBaseBulkInsertLogin_Your_DB ;
PRINT 'i2iBaseBulkInsertLogin created.';
USE master;
 
/*
A login with AUTHENTICATE SERVER permission, able to authenticate 
the server-level permission to run BULK INSERT.
*/


CREATE LOGIN i2iBaseServerAuthenticator_Your_DB 
FROM CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB; 
PRINT 'Server authenticator login created.'
REVOKE CONNECT SQL FROM i2iBaseServerAuthenticator_Your_DB; 
GRANT AUTHENTICATE SERVER TO i2iBaseServerAuthenticator_Your_DB; 
PRINT 'Server authenticator granted AUTHENTICATE SERVER permission 
         and revoked CONNECT SQL permission.'
/*
A user for the i2iBaseBulkInsertLogin_Your_DB login.
Used as the security context for performing a BULK INSERT.
*/
USE Your_DB; 
CREATE USER i2iBaseBulkInsertUser_Your_DB 
   FOR LOGIN i2iBaseBulkInsertLogin_Your_DB ;
PRINT 'Bulk Insert User created.';
/*
Stored procedure used by Bulk Import for executing a 
BULK INSERT command.
Uses impersonation to execute in the context of the 
i2iBaseBulkInsertUser.
*/
go
CREATE PROCEDURE dbo._BulkInsert 
   @stagingTable VARCHAR(500), 
   @dataFile VARCHAR(500), 
   @formatFile VARCHAR(500), 
   @kiloBytesPerBatch VARCHAR(500), 
   @maxErrors INT 
WITH EXECUTE AS 'i2iBaseBulkInsertUser_Your_DB' 
AS 
BEGIN 
   EXEC( 
      ' BULK INSERT ' + @stagingTable + 
      ' FROM ''' + @dataFile + '''' + 
      ' WITH (FORMATFILE =''' + @formatFile + 
      ''', MAXERRORS=' + @maxErrors + 
      ', KILOBYTES_PER_BATCH=' + @kiloBytesPerBatch + ')' 
   ) 
END 
PRINT '_BulkInsert stored procedure (re) created.';
go
USE Your_DB;


/*
Create a certificate in the iBase database that is a copy of 
the certificate in the master database.
*/
CREATE CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB 
   FROM FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\i2iBaseBulkInsertCertificate.cer' 
WITH PRIVATE KEY (
FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\i2iBaseBulkInsertCertificate.pvk', 
DECRYPTION BY PASSWORD = '#C3rtificate Backup Passw0rd#', 
ENCRYPTION BY PASSWORD = '#iBase Database C3rtificate Passw0rd#' 
)
PRINT 'Bulk Insert certificate created in iBase database.';
/*
The backup of the certificate and its key file are deleted. 
*/
EXEC master..xp_cmdshell
'DEL "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\i2iBaseBulkInsertCertificate.*"'
PRINT 'Bulk Insert certificate backup files deleted.';
/*
Sign the stored procedure so that it has permission to perform a 
BULK INSERT command.
*/
USE Your_DB; 
ADD SIGNATURE TO dbo._BulkInsert 
BY CERTIFICATE i2iBaseBulkInsertCertificate_Your_DB
WITH PASSWORD = '#iBase Database C3rtificate Passw0rd#';
PRINT '_BulkInsert stored procedure signed 
      with Bulk Insert certificate';


/* 
Disable xp_cmdshell.
*/
USE master; 
EXEC sp_configure 'xp_cmdshell', 0; 
RECONFIGURE;


/*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
If you want xp_cmdshell to be permanently enabled
then comment out the three lines above
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/