Configuring Microsoft SQL Server for Hosting Controller v10
Microsoft SQL Server serves as Hosting Controller’s backend database engine where HC stores its database, therefore it’s one of the major pre-requisites for installing HC. Because of the pivotal position that it occupies, it’s vital that SQL Server is installed and configured in a way that facilitates seamless integration and coupling with HC.
Supported Versions
Any edition of SQL Server 2008 or above can be used for the purpose of hosting HC database. SQL Server Express edition is free and can be downloaded from Microsoft website.
SQL Server configurations prior to installing Hosting Controller
- SQL Collation
SQL Server Collation must be SQL_Latin1_General_CP1_CI_AS
- SQL Authentication
SQL Server should be configured with mixed mode of Authentication i.e. SQL Server and Windows Authentication mode should be enabled prior to installing Hosting Controller.
- TCP/IP and Named Pipes
The status of both TCP/IP and Named Pipes should be enabled in SQL Server Configuration Manager.
- SQL Server Browser Service
SQL Server Browser service should be in running state.
- SA User
In SQL Server Management Studio under Security :: Logins, properties of SA must be enabled.
- SQL Port
If firewall is configured among servers then SQL port 1433 must remain open on HC DB machine for incoming TCP/IP requests from all servers added in HC cluster.
Note: 1433 is the default port for SQL Server. If you are using a custom port for SQL then replace 1433 with it.
Reasons for failure of HC installation
If after configuring SQL Server with the above settings, HC installer fails to connect to the database during install and throws the following error:
rule out the following reasons one by one:
Reason 1: SA password is incorrect
If SA user password is incorrect, HC installer will not connect to the HC database server. To debug this, verify if a successful connection can be made independently via ODBC DSN. To connect through ODBC DSN follow the below steps:
- Open Control Panel.
- Select Administrative Tools and click ODBC Data Sources icon
- Click the System DSN tab.
- Click the Add button.
- Select SQL Server from the drivers list and hit the Finish button.
If the SA password is incorrect, a "Login failed for user sa" error would be thrown. Therefore provide a valid password for SA.
Reason 2: SQL Server Instance Name is wrong
If SQL Server instance name specified in HC installer is incorrect then HC will not connect either. To debug this, the same ODBC DSN procedure may be repeated. If the instance name is incorrect, the following error would be thrown.
Reason 3: Instance Name of SQL Server is not resolvable
If SQL machine and HC Control Server are in two different networks a named instance specified in HC should look like
Hostname\SQLExpress with the full hostname plus the name.
If the full name is not specified, HC will not connect to the database. For a default instance only IP of the SQL machine should be specified.
