Spotfire Server: Resolving SQL Server connection issues during Spotfire Server installation

Description:

When setting up a new Spotfire Server environment that uses a SQL Server instance as its backend system database, it is possible to encounter connection issues while running Spotfire Server's create_databases.(bat|sh) script. This article outlines steps to resolve common connectivity problems.

Solution:

1. Enable "SQL Server Browser" service:

   • Open Windows Services control panel, locate "SQL Server Browser"  service, right-click on it and select Properties.
   • Set the startup type to Automatic and start the service.

2. Enable Named Pipes protocol:

   • Open SQL Server Configuration Manager, expand SQL Server Network Configuration and select Protocols for [INSTANCE_NAME].
   • Enable "Named Pipes" protocol. 

3. Verify TCP/IP protocol configuration:

   • In SQL Server Configuration Manager, ensure TCP/IP protocol is enabled.
   • Check that the correct port (default 1433) is configured.

4. Configure Windows Firewall:

   • Add an inbound rule for SQL Server port (typically 1433).
   • If issues persist, temporarily disable the firewall for testing.

5. Verify SQL Server authentication:

   • Ensure "mixed mode authentication" is enabled.
   • Reset and confirm the 'sa' account password.

6. Test connection using sqlcmd from the same location where you're running create_databases.bat script:

   • Open a command prompt and run:

     sqlcmd -U sa -S [SERVER_NAME]\[INSTANCE_NAME]

     For exampe:
     sqlcmd -U sa -S localhost
     sqlcmd -U sa -S <hostname of the machine>
     sqlcmd -U sa -S <hostname of the machine>\MSSQLSERVER
   • If connection succeeds, you can also run a few commands to verify this connection, for example:

     exec sp_databases

   • If successful, proceed with create_databases.bat script.

7. Review SQL Server error logs:

   • Check for any connection-related errors or configuration issues.

After implementing these steps, run the create_databases.bat script again. If issues persist, consult with your database administrator or contact Revvity Signals Support for further assistance.