Purpose: To document the full ritual of integrating Hikvision iVMS-4200 with SQL Server, ensuring future resilience and communal clarity.
🔧 Phase 1: SQL Server Preparation
1. Enable TCP/IP and Port 1433
- Open SQL Server Configuration Manager
- Navigate to SQL Server Network Configuration → Protocols for MSSQLSERVER
- Right-click TCP/IP → Enable
- Double-click TCP/IP → go to IP Addresses tab
- Scroll to IPAll section → set
TCP Port = 1433 - Click OK and restart the SQL Server service
Verify SQL Server is listening on the correct port:
SQL
SELECT local_net_address, local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL;2. Enable Mixed Mode Authentication
- Open SQL Server Management Studio
- Right-click your server name → Properties
- Go to Security tab
- Select SQL Server and Windows Authentication mode
- Click OK and restart the SQL Server service
Confirm Mixed Mode is active:
SQL
EXEC xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode';If the result is 2, Mixed Mode is enabled ✅
3. Test Manual Connection
CMD
sqlcmd -S 192.168.1.10,1433 -U sa -P [PASSWORD]🔥 Phase 2: Windows Firewall Verification
Create a firewall rule to allow SQL Server traffic:
PowerShell
New-NetFirewallRule -DisplayName "SQL Server 1433 Inbound" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow🧠 What This Command Does
- Creates a new rule named
SQL Server 1433 Inbound - Direction: Inbound — allows incoming traffic
- Protocol: TCP — used by SQL Server
- LocalPort: 1433 — the default SQL Server port
- Action: Allow — permits traffic through the firewall
🧱 Phase 3: Table Schema Alignment
Step 1: Create the Database
SQL
CREATE DATABASE Attendance;Step 2: Switch to the Database
SQL
USE Attendance;Step 3: Create the Table
SQL
CREATE TABLE TimeAttendance (
ATTENDANCE_id INT,
ATTENDANCE_date_time DATETIME,
ATTENDANCE_date DATE,
ATTENDANCE_time TIME,
ATTENDANCE_direction NVARCHAR(10),
ATTENDANCE_device NVARCHAR(50),
ATTENDANCE_serial NVARCHAR(50),
ATTENDANCE_name NVARCHAR(100),
ATTENDANCE_card NVARCHAR(50)
);Step 4: Verify Field Names
SQL
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TimeAttendance';🧪 Optional Verification Query: Confirming sa Access
To verify that the built-in sa login is mapped to the database:
SQL
SELECT name, type_desc
FROM sys.database_principals
WHERE name = 'sa';If this query returns a row, sa is explicitly mapped. If not, it still has full access via sysadmin role unless denied.
🧪 Phase 4: Profiler-Based Validation
- Launch SQL Server Profiler
- Filter by
LoginName = saandDatabaseName = Attendance - Click Save in iVMS-4200
- If no activity: internal block
If activity appears: SQL handshake confirmed ✅
🔄 Phase 5: Console Reset
- Exit iVMS-5200 Service Management from system tray
- Open Hikvision Watchdog and restart all services
- Close and reopen iVMS-4200 Client
- Create a new Third-Party DB profile
- Use updated IP:
192.168.1.10 - Click Save — success message appears 🎉
🧠 Optional Enhancements
- Install SQL Server Native Client
- Monitor inserts with triggers or logs
- Begin device-level sync once console config is validated
The Save Event of October 21
Live Profiler Observation and Schema Update
Location: SQL Profiler Console
Timestamp: October 21, 2025, 11:33 AM IST
Trigger: Hikvision iVMS-4200 console attempting to save attendance data to SQL Server
Timestamp: October 21, 2025, 11:33 AM IST
Trigger: Hikvision iVMS-4200 console attempting to save attendance data to SQL Server
Starting Error
Command Type: SQL
INSERT INTO TimeAttendance ( ATTENDANCE_id, ATTENDANCE_date_time, ATTENDANCE_date, ATTENDANCE_time, ATTENDANCE_direction, ATTENDANCE_device, ATTENDANCE_serial, ATTENDANCE_name, ATTENDANCE_card ) VALUES ( '0x213770230456yz', '2020-03-03T21:55:30', '2020-03-03', '21:55:30', 'OUT', 'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT', 'TT-T260420190215V020005CH172860346', 'Jack Williams', 'HZ626262' );
This insert failed due to a schema mismatch — the ATTENDANCE_id column was not ready to accept string-based IDs.
Resolution Steps
1. Dropping the Primary Key Constraint
Command Type: SQL
ALTER TABLE TimeAttendance DROP CONSTRAINT PK_TimeAttendance;
2. Altering the Column Type
Command Type: SQL
ALTER TABLE TimeAttendance ALTER COLUMN ATTENDANCE_id NVARCHAR(50);
3. Save Attempt and Timeout
The console retried the save multiple times. Each click resumed the buffered sync.
4. Duplicate ID Discovery
Command Type: SQL
SELECT ATTENDANCE_id, COUNT(*) FROM TimeAttendance GROUP BY ATTENDANCE_id HAVING COUNT(*) > 1;
000001 appeared 103 times — indicating that ATTENDANCE_id is an employee ID, not a unique record ID.
5. Composite Key Strategy
Command Type: SQL
ALTER TABLE TimeAttendance ALTER COLUMN ATTENDANCE_id NVARCHAR(50) NOT NULL; ALTER TABLE TimeAttendance ALTER COLUMN ATTENDANCE_date_time DATETIME NOT NULL; ALTER TABLE TimeAttendance ADD CONSTRAINT PK_TimeAttendance PRIMARY KEY (ATTENDANCE_id, ATTENDANCE_date_time);
This binds each record to a unique combination of employee ID and timestamp.
Final Confirmation
Command Type: SQL
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'TimeAttendance';
The output confirmed that PK_TimeAttendance is now active and enforcing uniqueness.
Legacy Notes
- This marks the first successful schema handshake between Hikvision and SQL Server
- SQL Profiler served as a live mirror, reflecting each insert in real time
- The composite key now protects the archive, ensuring every moment is uniquely preserved
