Configuring the ARM Database in SQL Server

This procedure is used ONLY if you did not configure ARM in the installer. All tables are installed regardless of version/features/modules (including the SAT module).

During installation, the installer places scripts in the Application Data folder under \EFT Server Enterprise\SQL Server. There are now several scripts that must be run in order. The script names are prefixed with numbers in the order you should run them.

• The OSQL utility allows you to enter Transact-SQL statements, system procedures, and script files for maintaining the database.

• For additional information on the oSQL utility, including common script samples, refer to osql Utility on microsoft.com.

The instructions below use Microsoft SQL Server Management Studio Pro, but you can use express or other methods.

To configure SQL Server

Reminder: As stated above, the necessary scripts are installed in the Application Data folder under \EFT Server Enterprise\SQL Server during installation.

1. Point EFT Server to the SQL Server.

2. On SQL Server, launch Microsoft SQL Server Management Studio or equivalent, and provide your administrator login credentials when prompted.

3. In the left pane, expand the Security node, right-click Logins, then click New Login. The New Login dialog box appears.
   
   

4. Create a new user called eftuser and click SQL Server Authentication.

If SQL Server Authentication is not available as a choice, verify that the SQL Server has been set up to support mixed mode.

5. In the Password and Confirm password boxes, provide a complex password consisting of an alphanumeric and symbol mix of at least 8 characters.

6. Select the Enforce Password Policy check box.

7. Leave the Default database as master.

8. In Default language, click the list to select English, then click OK.

   The user account that is doing the auditing for ARM must be set to English.

   

9. In the left pane, right-click Databases, then click New Database.
   
   

10. In the dialog box that appears, name the database eftdb.

If you name the database something other than eftdb, be sure to use the name you chose throughout the procedure.

11. In the Owner field, provide the login name you just created (eftuser).

12. In the Database files table, change the Initial size value to 10 MB for the eftdb logical name (first row). Leave the eftdb_log row as is and click OK.
    
    

13. In the left pane under Databases, click the newly created eftdb database, then on the Query menu, click New Query. A blank screen appears in the right pane in which you can type in a SQL query.

14. For each script (in the order specified by the file names):

Reminder: As stated above, the necessary scripts are installed in the Application Data folder under \EFT Server Enterprise\SQL Server during installation.

1. Load/copy the entire script into management studio.

2. Add the following lines to the top of the script:

 USE EFTDB

 GO

3. Execute the script.

A message appears indicating whether the query was able to complete successfully.

15. Expand Databases, then eftdb, then Tables. Verify that the database has populated correctly. (The tables defined in the script should have been created.)

Test your connection

1. Create a test connection with your FTP client to EFT Server and upload and download a few files.

2. Switch back to SQL Server and select the dbo.tbl_ProtocolCommands table under the eftdb database icon. It should return several rows with the commands issued by your client from the test connection.

3. You can now pull reports directly from EFT Server against data audited to SQL Server.

Using oSQL to Create EFT Server Database Tables on SQL Server

Administrators that prefer using command-line tools can use oSQL to create the auditing database and tables. A brief overview is provided below. Detailed instructions are outside the scope of this documentation; refer to your SQL Server documentation.

Gather the following information prior to calling the oSQL command line tool:

1. The SQL Server Host Name or address.

2. The authentication scheme. You will need to know the authentication mechanism allowed on that SQL Server. It may be Windows Authentication only, Windows Authentication, or SQL Server Authentication.

3. The allowed connection protocols. This can be Named Pipes, which is required for Windows Authentication, or TCP/IP, which is used by the SQL Server Authentication.

4. The PORT, if TCP/IP. If TCP/IP is the connection of choice, you need to know the port on which the SQL Server is listening for connections. The default port is 1433. It also supports a way that a client can dynamically determine the port, but this requires that the SQL Server have UDP port 1434 accessible by remote computers. If this port is blocked by a firewall, you will NOT be able to use the "dynamically determine port" feature. Typically, SQL Server installations use the default port 1433 (TCP) for connections.

Once you have acquired all of the above information, then you can construct the proper command line for "oSQL" to connect to the database.

You must use this procedure for each of the scripts installed in the Application Data folder under \EFT Server Enterprise\SQL Server during installation.

1. Open a command prompt. (Click Start > Run, type cmd, then press ENTER.)

2. Type the following to place the sql file in the Reports folder  (e.g., C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Server Enterprise\Reports):  

[path to oSQL]\oSQL.exe -S [server address] -U [username] -P [password] -i "[c:\path\to\ARM_DBScript_1_1.sql]\ARM_DBScript_1_1.sql"

For example, type:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\oSQL.exe" -S 192.168.19.17 -U jbond -P asd123!f$s1 -i "C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Server Enterprise\Reports\ARM_DBScript_1_1.sql"

3. In the Administrator, connect to EFT Server and click the Server tab.

4. In the left pane, click the Server you want to configure.

5. In the right pane, click the Logs tab.

6. Under Database Audit Settings, select the Enable Auditing and Reporting check box.

7. In the Server Name box, type EFT Server name or IP address.

8. Type the Database Name, a valid user name, and a password.

9. To test the connection to the database, click Test Connection.