Skip to main content

SQL Server connection

Subiekt GT, Subiekt nexo, Navireo, Comarch ERP Optima and WAPRO Mag keep their data in Microsoft SQL Server. The price checker connects to this server directly over the network and sends one read query per scan. This page covers what these programs have in common; program-specific fields are on each program's page.

Common fields​

FieldWhat to enter
AddressThe SQL Server address, with a port or an instance name — formats below.
UserA SQL Server login (not a Windows account). Use a dedicated read-only login — how to create it.
PasswordThe password of that login.
DatabaseThe name of the company database in SQL Server, e.g. Nexo_MyCompany or CDN_MyCompany.
Encrypt connection (SSL)Checked by default — details.
Search by product code (SKU)Search by the product symbol / code / index. On by default.
Search by EAN codeSearch by barcode. On by default.

With both search switches on, the app finds the product both by barcode and by symbol (when the customer scans a shelf label with the symbol). When both are off, the app searches by symbol only — the same as with SKU alone.

Address format​

Entry in the Address fieldMeaning
192.168.1.10Default instance, port 1433.
192.168.1.10:1433 or 192.168.1.10,1433The given port.
192.168.1.10\INSERTGTNamed instance — the port is provided by the SQL Server Browser service (UDP 1434).
192.168.1.10\INSERTGT,50123Named instance with a fixed port — the Browser is not needed.

You can copy the address the way the program shows it (e.g. SERVER\OPTIMA). It is safer, however, to enter the IP address instead of the computer name: an Android price checker does not always resolve computer names from a Windows network. The server should have a fixed IP address (or a reservation in the router), because after an address change the price checker stops connecting.

When the address contains a port, the app connects straight to that port and does not ask the SQL Server Browser service.

What to check on the server​

  1. The TCP/IP protocol is enabled for the instance (SQL Server Configuration Manager → SQL Server Network Configuration → protocols). In Express editions it is often disabled by default. Restart the SQL Server service after the change.
  2. The instance port. The default instance listens on 1433. A named instance usually has a dynamic port — the simplest way is to set a fixed port for it (TCP/IP properties → IPAll) and enter it in the address.
  3. Windows Firewall on the server allows inbound TCP traffic to the SQL Server port (and UDP 1434 if you rely on SQL Server Browser).
  4. SQL Server authentication is enabled (mixed mode: SQL Server and Windows). The app logs in with a SQL login and password only.

Connection encryption (SSL)​

  • Checked (default) — the app encrypts the connection if the server supports it; if not, it connects unencrypted. The server certificate is not validated, so the self-signed certificate that SQL Server creates by itself also works.
  • Unchecked — the connection is not encrypted.

If the server forces encryption and the box is unchecked, the connection test shows The server closed the connection. If the server requires encryption, check “Encrypt connection (SSL)”.

Advanced

In the Database field you can add jTDS driver parameters after a semicolon, e.g. Nexo_MyCompany;ssl=require or CDN_MyCompany;loginTimeout=30. A parameter added this way overrides the default (ssl from the checkbox, loginTimeout=15, socketTimeout=15).

How the app keeps the connection​

  • The connection opens when the app starts and after the settings are saved. During that time the screen shows Loading and scans are ignored.
  • If the connection fails, the screen shows No connection to the data source and Ask the store staff for help. Every following scan tries to connect again — no app restart is needed, e.g. when the WiFi came up later than the price checker.
  • If a query fails (dropped connection, server restart), the app opens a new connection and repeats the query. Only a second failure shows Data download error on the screen.
  • After more than 2 minutes without scans, the connection is reopened before the query, because firewalls and routers often silently drop idle connections.
  • The app waits 15 seconds for the connection to open and 15 seconds for the server's response.

Read-only SQL login​

The price checker hangs in the shop aisle, and the login and password are stored in its memory. Never enter sa or any other administrator account on it — anyone who gets hold of the device would have full access to the company database. Create a dedicated login that can only read the tables the app needs.

Run the script in SQL Server Management Studio as a server administrator. Replace the database name, the password and the list of tables with the one from your program's page:

USE [master];
CREATE LOGIN [pricechecker] WITH PASSWORD = N'Put-a-long-password-here', CHECK_POLICY = ON, DEFAULT_DATABASE = [CompanyDatabase];

USE [CompanyDatabase];
CREATE USER [pricechecker] FOR LOGIN [pricechecker];
-- only the tables the price checker reads:
GRANT SELECT ON dbo.tw__Towar TO [pricechecker];
GRANT SELECT ON dbo.tw_Cena TO [pricechecker];
GRANT SELECT ON dbo.tw_Parametr TO [pricechecker];

Tables needed by each program:

ProgramSELECT permission on tables
Subiekt GTdbo.tw__Towar, dbo.tw_Cena, dbo.tw_Parametr
Navireodbo.tw__Towar, dbo.tw_Cena, dbo.tw_Parametr, dbo.tw_KodKreskowy
Subiekt nexoModelDanychContainer.Asortymenty, ModelDanychContainer.PozycjeCennika, ModelDanychContainer.Cenniki, ModelDanychContainer.JednostkiMiarAsortymentow, ModelDanychContainer.KodyKreskowe
Comarch ERP OptimaCDN.Towary, CDN.TwrCeny
WAPRO Magdbo.ARTYKUL, dbo.CENA_ARTYKULU, dbo.CENA

Do not grant db_owner or db_datareader — the latter gives read access to the whole database, including customer data. After a major program update, run the connection test; if a permission error appears, run the GRANT statements again.

SQL Server — FAQ

Can I enter the sa login on the price checker?
It would technically work, but do not do it. The login and password are stored on a device that hangs in the store. Create a dedicated SQL login with SELECT permission only on the product and price tables listed on this page for your program.
Which port must be open for the price checker?
The SQL Server TCP port — 1433 for the default instance, or the fixed port set for a named instance. If you enter only the instance name without a port, UDP 1434 for the SQL Server Browser service is needed as well.
Can the price checker log in with a Windows account?
No. The app logs in with a SQL Server login and password, so SQL Server authentication (mixed mode) must be enabled on the server.
Should I check Encrypt connection (SSL)?
Yes, leave it checked as by default. The app encrypts the connection when the server supports it and connects unencrypted when it does not. Uncheck it only when the service team tells you to.
Why is it better to enter an IP address instead of the computer name?
Android does not always resolve computer names from a Windows network, so a name can give the error Computer cannot be found on the network. The server's IP address always works — just make sure it is fixed.