VS-Operator SQL Server on a remote computer

Problem

When the SQL server and VS-Operator Server are on different computers, setup of the SQL server instance needs special considerations, as by default the SQL server instance does not accept remote connections.

Solution

SQL server

Open Microsoft SQL Server Management Studio (freely available from Microsoft) and connect to the SQL server instance which will be used for VS-Operator

• Right click on the instance name and select 'Properties'
• Select page 'Security' and make certain that 'SQL Server and Windows Authentication' has been enabled
• Select page 'Connections' and make certain that 'Allow remote connections to this server' has been enabled
• Click OK
• Open the 'Security' tree, right click 'Logins' and select 'New Login ...'
• Select page 'General', define a new Login name, select 'SQL Server authentication' and define a password; set 'Password policy' as required, but note that if the password expires, VS-Operator Server does not have access to the SQL database anymore
• Select page 'Server Roles' and enable 'dbcreator'

SQL Server Management Studio can now be closed.
Open SQL Server Configuration Manager

• Open the tree 'SQL Server Network Configuration'
• Select the correct SQL instance (in case there are multiple instances running)
• If TCP/IP is disabled, right click it and select 'Enable'
• Right click TCP/IP again and select 'Properties'
• Select the TAB 'IP Addresses' and scroll down to 'IPAll'
• Make certain that the field 'TCP Dynamic Ports' is empty and enter the value 1433 in the field 'TCP Port'
• Click OK

The SQL server instance must be restarted to let the changes take effect.

Firewall settings for the computer running the SQL server

Define the following firewall rules:

• Inbound rule: allow TCP port 1433
• Inbound rule: allow UDP port 1434
• Inbound rule: allow program 'sqlservr.exe'
• Inbound rule: allow program 'sqlbrowser.exe'

The SQL Browser service must be running. Check in the 'Services' dialog whether this service is running and that the startup type is set to 'Automatic'

SQL Server Browser must be running

VS-Operator configuration

In the VS-Operator configuration tool

• Disable 'OS authentication'
• Enter the user name and password for the SQL user as defined in the paragraph above.