Setting up the Spotfire database (SQL Server with Integrated Windows authentication)

If you plan to use Microsoft SQL Server for storing the Spotfire database, on a Windows computer in a Windows domain and you also plan to use Integrated Windows authentication between Spotfire Server and the Spotfire database in SQL, follow these steps before running the Spotfire Server installer.

About this task

Note: Alternatively, you can create a Spotfire database using the CLI command create-db. For more information about this option, see Creating a Spotfire database using the command create-db.

Before you begin

You have downloaded and unzipped the Spotfire Server installation kit from the TIBCO eDelivery web site. For instructions, see Downloading installation software.
The following settings must be configured on the SQL Server:
- TCP/IP communication listening on a port (the default is 1433).
- Case-insensitive collation (at least for the Spotfire database).
  Note: If your installation of SQL Server uses a case-sensitive collation by default, or your data uses a different collation than Latin1_General_CI_AS, you must edit the create_server_db.sql script before running the create_databases_ia.bat script. See step 2 below.
- The command line database tools (sqlcmd, etc.) must be in the system path.

About this task

With this type of configuration, the Spotfire database will use Windows accounts for authentication. The current user who is running the scripts to create the database must have administrative privileges on the database server, but the Spotfire process should run as a different user when connecting at runtime. Therefore, the scripts have been designed to access the database with a different Windows account when the server is running. This user is assigned to the variable WINDOWS_LOGIN_ACCOUNT. Note that the user who ran the scripts to create the database will get database owner permissions (dbo) to the database and will be able to administer the Spotfire database using integrated authentication.

If the user assigned to the WINDOWS_LOGIN_ACCOUNT variable already exists as a login on the database server, the create_server_user_ia.sql script must be edited. The following rows should then be commented out:

use master 
GO 
CREATE LOGIN [$(WINDOWS_LOGIN_ACCOUNT)] FROM WINDOWS WITH 
DEFAULT_DATABASE=[$(SPOTFIREDB_DBNAME)],DEFAULT_LANGUAGE=[us_english] 
GO 
ALTER LOGIN [$(WINDOWS_LOGIN_ACCOUNT)] ENABLE 
GO 
DENY VIEW ANY DATABASE 
TO [$(WINDOWS_LOGIN_ACCOUNT)]

As mentioned above, the server process should connect as different user than the user that runs this script for security reasons. If you really want to use the same account then you must comment out the following lines from create_server_user_ia.sql:

CREATE USER [$(SPOTFIREDB_USERNAME)] FOR LOGIN [$(WINDOWS_LOGIN_ACCOUNT)]
GO

Procedure

Copy the <installation files dir>/scripts/mssql_install directory to a location where you can edit it.
If your installation of SQL Server uses a case-sensitive collation by default, or if you need to define a different collation, follow these steps:
1. Open the mssql_install directory, and then open the create_server_db.sql script in a text editor.
2. Locate the line --create database $ (SPOTFIREDB_DBNAME) collate Latin1_General_CI_AS;
3. Remove the leading dashes (--).
4. If needed, replaceLatin1_General_CI_AS with the name of the desired collation, but make sure it is case-insensitive (CI). See the SQL Server documentation for information about available collations.
5. Comment out the next line by inserting leading dashes (--), so that the line looks like this: --create database $(SPOTFIREDB_DBNAME)
6. Save the file and close the text editor.
On the SQL Server computer, go to the mssql_install directory, and then open create_databases_ia.bat in a text editor.

In the section under "Set these variables to reflect the local environment", edit the create_databases_ia.bat script by providing the appropriate database server details. The definitions of the variables are listed at the top of the script.

Table 1. Definitions of the variables in create_databases_ia.bat
Variable	Description
DBSERVER_CONNECTIDENTIFIER	Replace `<SERVER>` with the name of the server running the SQL Server instance, and replace `<MSSQL_INSTANCENAME>` with the name of the SQL Server instance. Note: The default installation of SQL Server creates an unnamed instance of the SQL Server. If your SQL Server is a new installation, delete the "MSSQL_INSTANCENAME" part of the line and enter only the SERVER name. The connection will be made to the unnamed instance.
WINDOWS_LOGIN_ACCOUNT	The Windows Login Account that should be created as a login on the database server. The server process must run as this user.
SPOTFIREDB_DBNAME	Name of the Spotfire database that will be created; spotfire_server is the default.
SPOTFIREDB_USERNAME	Name of the user that will be created for the Spotfire database, associated with the WINDOWS_LOGIN_ACCOUNT.

Example

This is what the create_databases_ia.bat file section might look like after modification:

rem Uncomment to set variables:
set DBSERVER_CONNECTIDENTIFIER=DBSERVER\MSSQL
set WINDOWS_LOGIN_ACCOUNT=example.com\win_user
set SPOTFIREDB_DBNAME=spotfire_server
set SPOTFIREDB_USERNAME=spotfire_user

Save the file and close the text editor.
Open a command line as an administrator and go to the directory where you placed the scripts.
Type create_databases_ia.bat and press Enter.
If the parameters are correct, a text similar to the following is displayed at the command prompt:

Results

Note: The log.txt file is created in the same directory as the create_databases_ia.bat file. Examine this file to verify that no errors occurred, and retain the log for future reference.

What to do next

Install Spotfire Server

Note: The scripts contain sensitive information so it is good practice to remove them after your Spotfire environment has been installed.