How to get SQL Server 2005 to work with a Niagara G3 Enterprise Network Server.

[Product_Support]

Issue

How to get SQL Server 2005 to work with a Niagara G3 Enterprise Network Server.

Environment

• Niagara G3 Enterprise Network Server
• SQL Server 2005

Cause

The SQL Server need to enable TCP/IP and open TCP port number 1433.

Resolution

"...To get SQL Server 2005 Express (the free edition) configured properly simply follow these steps...

1. Download SQL Server 2005 Express Edition here.
2. Download the SQL Server Management Studio Express from the above URL (note: advanced services are not needed). This is optional but it does provide a useful interface for running SQL queries if desired.
3. Before installing SQL Server 2005 Express Edition, ensure the user is logged on with administrative access to the PC and all older versions of SQL Server are uninstalled.
4. When installing SQL Server 2005, ensure mixed mode is enabled and the give 'sa' user a strong password.
5. Once installed, open the SQL Server Configuration Manager ('Start', 'Programs', 'Microsoft SQL Server 2005', 'Configuration Tools', 'SQL Server Configuration Manager').
6. Once the Configuration Manager has been opened, enable TCP/IP so G3 can connect to SQL Server (by default this is switched off).
7. Using the tree, navigate to 'SQL Server 2005 Network Configuration' and then 'Protocols for SQLEXPRESS'.
8. Right click TCP/IP and select 'Properties'. The TCP/IP Properties window should now appear.
9. On the first tab labeled 'Protocol', ensure the 'Enabled' property is set to 'Yes'.
10. Go to the second tab labeled 'IP Addresses'.
11. In the section labeled 'IPAll' delete any entry in 'IP Dynamic Ports' and enter the value 1433 in 'TCP Port'. Once completed click ok.  Note that the default port is a 5-digit number that will NOT work.  It must be removed.
12. Restart the SQL Server Express service. If it can't be located easily in Windows, then reboot the computer.
13. Niagara G3 should now be able to connect to SQL Server 2005. Before attempting to connect, ensure all firewalls are properly configured so the connection can be made.
14. If running the Enterprise Network Server on a different computer, then open up a Windows command prompt on the ENS computer and type 'telnet yourIPAddressGoesHere 1433'. If the command prompt window goes blank then connections can be made to this IP address and port number.
15. Open Workbench and run up the desired station.
16. Ensure the station is licensed to use the rdbSqlServer feature.
17. Add the 'RdbmsNetwork' component to the server station.
18. Add the 'SqlServerDatabase' component.
19. Configure the properties for the 'SqlServerDatabase' component.
20. Type in the IP address, port number (1433 in this example), user name and password for the connection.
21. Ping the 'SqlServerDatabase' and ensure its status is 'ok'.
22. Export historical data to the Sql Server database by adding a new 'SqlServerHistoryExport' component and setting up its 'History id' property to point to an existing history already in the station".

Ensure Niagara G3 version 3.0.100 and above is used in order for the connection to work.