AGRIS Client Settings for accessing AGRIS MSSQL datasets

Add Database Type=SQL to the client machines AGRIS.ini (syntax and spacing are very important)

Example:

[Paths]
Database Type=SQL
Data Path=\\SERVER\APPS\AGRIS\DATASETS
Support Path=
Userprog Path=
Work Path=c:\apps\agris\temp
Component Server=
Locking Server=
GEOData Path=
Notification URL01=

Copy the servers dbconnections.config file from the servers AGRIS\Bin folder to the clients AGRIS\Bin folder
If the server has a firewall enabled, adjust the firewall rules to accommodate client connections
Create Inbound rules to open UDP ports 1433 and 1434 and add a custom rule for the SQL Server (AGRIS) service

An error similar to the following is encountered when launching AGRIS from a client if the appropriate ports are not opened:

Unable to connect to MS SQL Server = SERVERNAME\MSSQL Instance
Please confirm MS SQL Server name and login credentials in C:\Apps\AGRIS\Bin\dbconnections.config
For additrional information, please review the Application Event Log on this machine.

Below are some event viewer errors associated with the above error report from AGRIS.
--------------------------------------------------------------------
CAUSE: Using server name only without DB name (event viewer: event id 1)
ERROR MESSAGE: The VB Application identified by the event source logged this Application SysMenu: Thread ID: 6540 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server TRAPEZE-M414T2J. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception: The system cannot find the file specified
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectio

CAUSE: No DBConnections.config file exists (may be that the filename is misspelled):
ERROR MESSAGE: The VB Application identified by the event source logged this Application SysMenu: Thread ID: 23596 ,Logged: Microsoft.SqlServer.Management.Common.InvalidArgumentException: Cannot apply value ‘null’ to property Login: Value cannot be null.. ---> Microsoft.SqlServer.Management.Common.InvalidPropertyValueException: Cannot apply value ‘null’ to property Login: Value cannot be null..

CAUSE: Using IP address with DB name instead of SERVERNAME\DBNAME:
ERROR MESSAGE: The VB Application identified by the event source logged this Application SysMenu: Thread ID: 10192 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server 192.168.0.104\SQL2014. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception: The user name or password is incorrect
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConne

CAUSE: MS SQL ports (UDP 1433, 1434) blocked by firewall

ERROR MESSAGE: The VB Application identified by the event source logged this Application SysMenu: Thread ID: 7500 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server trapeze-m414t2j\SQL2014. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.Crea

CAUSE: MS SQL ports blocked by firewall (different error)
ERROR MESSAGE: <Data>Application SysMenu: Thread ID: 7892 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server AGRISSERVER. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.)

CAUSE: Correct server\DB instance, wrong username (not using database server security)
ERROR MESSAGE: The VB Application identified by the event source logged this Application SysMenu: Thread ID: 6412 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server trapeze-m414t2j\SQL2014. ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) ---> System.ComponentModel.Win32Exception: No process is on the other end of the pipe
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnec

<Data>Application SysMenu: Thread ID: 7892 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server AGPA-AgrisEast,1455. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.)

CAUSE: Correct server\DB instance, correct username, wrong password (not using database server security)
ERROR MESSAGE: The VB Application identified by the event source logged this Application SysMenu: Thread ID: 1120 ,Logged: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server trapeze-m414t2j\SQL2014. ---> System.Data.SqlClient.SqlException: Login failed for user 'sa'.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, D

NOTE: If the username/password for the MSSQL database should be kept hidden or encrypted from end users, then configure the DBCONNECTION file correctly to access the server and then go do Customize > Database Server Security and configure a username/password that will be protected. Then all DBCONNECTION files can be configured to be blank for username\password or otherwise populated, but will be completely ignored by the AGRIS initialization routine due to the presence of the DATASETS\SYSDBINFO.SQL.AGR file\data.