An AyaNova user has kindly past on his information when setting up AyaNova for his remote computers that connect through a VPN. Normally remote computers would use a Data Portal connection, but this AyaNova user wanted to do a direct connection, and has past on this information:
Initially when a remote computer attempting to connect through the VPN to the SQL Express server when running AyaNova, they received the error message:
Error details:
Unhandled Exception: Exception has been thrown by the target of an invocation. System.Reflection.TargetInvocationException
Inner exception: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified/
The remote computers were using the exact same config.txt that the local area networked computers were using - for example, the connection string in the config.txt was:
<DataBaseConnectionString>Server=SERVER01\SQLExpress;initial catalog=AyaNova;User Id=sa; Password=xxxxx;</DataBaseConnectionString>
As VPN does not broadcast named instances you must attach to an sql database by IP rather than name. So had the config.txt edited with the ip address of the server. For example, as the SQL server’s internal ip address is 192.168.1.51, edited the config.txt to:
<DataBaseConnectionString>Server=192.168.1.51\SQLExpress;initial catalog=AyaNova;User Id=sa; Password=xxxxx;</DataBaseConnectionString>
He also made sure that Remote Connections in SQL were enabled, and configured a port for SQL
1. Enable Remote Connections in MSSQL Server 2005 Express
- Click Start, point to Programs, point to Microsoft SQL Server
2005, point to Configuration Tools, and then click SQL Server
Surface Area Configuration. - On the SQL Server 2005 Surface Area Configuration page, click
Surface Area Configuration for Services and Connections. - On the Surface Area Configuration for Services and Connections
page, expand Database Engine, click Remote Connections, click Local
and remote connections, click the appropriate protocol to enable for
your environment, and then click Apply.
(Note : Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you
restart the Database Engine service.)
- On the Surface Area Configuration for Services and Connections
page, expand Database Engine, click Service, click Restart, wait
until the MSSQLSERVER service stops, and then to restart the
MSSQLSERVER service.
2. Configure a port for MSSQL Server 2005 Express
- Click Start > All Programs > Microsoft SQL Server 2005 >
Configuration Tools > SQL Server Configuration Manager. - In the left pane of the SQL Server Configuration Manager
window, expand SQL Server 2005 Network Configuration and then
click Protocols for SQLEXPRESS. - In the right pane of the SQL Server Configuration Manager
window, right-click TCP/IP and click Enable. Click OK to
acknowledge the warning message.