Overview

This article explains how to configure a Red Lion/HMS device running Crimson 3.2 to communicate with a Microsoft SQL Server database. This can be used for:

SQL Data Logging (SQL Sync)
SQL Queries
Dynamic SQL commands using RunDynamicSQL()
Recipe management and data storage

Crimson communicates directly with SQL Server using Microsoft's Tabular Data Stream (TDS) protocol over TCP/IP. SQL connections require a SQL Server default instance and SQL authentication.

Prerequisites

Hardware

One of the following devices:

DA30D
DA50
DA70
CR3000 Series
Graphite Series
FlexEdge (with appropriate license)

SQL functionality varies slightly by platform and license.

Software

Crimson 3.2
Microsoft SQL Server (Express, Standard, or Enterprise)

SQL Express is sufficient for testing and small applications.

Step 1: Install SQL Server

Install SQL Server Express here: Microsoft SQL Server Express

Use the Default Instance

Crimson SQL synchronization does not support named SQL instances and requires the SQL Server to be installed as the default instance.

Enable Mixed Authentication

During installation select:

Mixed Mode Authentication

This allows both:

Windows Authentication
SQL Server Authentication

Crimson requires SQL Server Authentication.

Example:

Username: sa

Password: MyPassword123

Step 2: Configure SQL Server Network Access

Open:

SQL Server Configuration Manager

Navigate to:

SQL Server Network Configuration

Protocols for MSSQLSERVER

Enable:

TCP/IP

Open TCP/IP Properties and verify (right click TCP/IP, click properties, IP addresses, TCP Port):

TCP Port = 1433

Port 1433 is the standard SQL Server port used by Crimson.

Restart the SQL Server service after making changes:

Step 3: Configure Windows Firewall

Create an inbound firewall rule allowing:

TCP Port 1433

Alternatively, temporarily disable the firewall for testing.

Without firewall access, Crimson will not be able to connect to SQL Server.

Step 4: Create a Database

Launch SQL Server Management Studio (SSMS).

Create a new database:

CREATE DATABASE CrimsonDB;

Example table:

USE CrimsonDB;

CREATE TABLE ProductionData (

Timestamp DATETIME,

PartCount INT,

Temperature FLOAT,

OperatorName VARCHAR(50)

);

Step 5: Configure SQL Server Connection in Crimson

In Crimson 3.2:

Navigate to:

SQL Queries

SQL Sync

Configure the SQL Server connection:

Server Address

192.168.1.10

SERVERNAME

TCP Port

1433

Logon Username

Logon Password

MyPassword123

Database Name

CrimsonDB

Reference the SQL Server using its IP address whenever possible to simplify troubleshooting.

Step 6: Test Connectivity

Before configuring Crimson:

From a PC, verify:

ping 192.168.1.10

Then test SQL access using:

SQL Server Management Studio

Confirm:

Database exists
Login works
TCP/IP connections work

Troubleshooting

Cannot Connect to SQL Server

Verify:

SQL Server service is running
TCP/IP enabled
Port 1433 enabled
Firewall allows 1433
Correct username/password
SQL Authentication enabled

Login Failed

Typical causes:

Windows Authentication only
Incorrect SQL username
Incorrect password

Verify Mixed Mode Authentication is enabled.

Timeout Errors

Check:

Network connectivity
SQL Server load
Firewall
VPN routing

SQL Sync Not Updating

It is recommended not setting SQL Sync intervals too fast. Data logging occurs every two minutes, and shorter synchronization periods can cause sync errors and redundant processing.

Best Practices

Use SQL Authentication instead of Windows Authentication.
Use a dedicated SQL account instead of sa.
Use static IP addresses for SQL servers.
Test with SQL Server Management Studio before involving Crimson.
Escape apostrophes in string data before building dynamic SQL statements.
Use a default SQL instance, not a named instance.

References

Crimson 3.x: SQL Synchronization (HMS Networks Support)
Crimson 3.x: SQL Queries (HMS Networks Support)
Please visit the following link for additional assistance Crimson Tech Support Portal