Alternative Method for Creating Database Tables on SQL Server

Administrators that prefer using command driven tools can use oSQL to create the necessary database and tables. A brief overview is provided below; detailed step-by-step instructions are outside the scope of this 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, or 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 machines. 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 craft the proper command line for "oSQL" to connect to the database:  

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

  2. To place the sql file ARM_DBScript_1_1.sql in the Reports folder under the Server’s installation folder, type the following:

[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.169.19.17 -U jbond -P asd123!f$s1 -i ”C:\Program Files\GlobalSCAPE\Secure FTP Server\Reports\ ARM_DBScript_1_1.sql"

  1. In the Administrator, click the Server tab. You should be connected to the server.

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

  3. In the right pane, click the Server Options tab.

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

  5. In the Host(\Instance Name) box, type the Server name or IP address.

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

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

Both the auditing component and the reporting component of the Server's ARM use ADO to communicate with the data source. The connection string (automatically configured if MSDE is chosen during install) used to connect to that data source can be anything that ADO supports to open a connection. This can be a DSN, or a DSN-less connection string. For more information on ADO connection strings, search the MSDN library at http://msdn2.microsoft.com/en-us/library/default.aspx; also see Microsoft support article 193332:
http://support.microsoft.com/?kbid=193332.