SQL Server Authentication Setup for IT Administrators

What is this?

This guide is for the IT professional or SQL Server administrator who looks after a store's computers. It explains how to configure the SQL Server that holds a Margin Master database so that it accepts a SQL Server login (username and password) in addition to Windows credentials, and how to let other computers on the store network reach it.

Share this document with your IT team or network administrator. Nothing in it requires Margin Master support to be involved, and every step uses standard Microsoft tools — SQL Server Management Studio, SQL Server Configuration Manager, and Windows Firewall.

You need this guide when:

• The store wants to run Margin Master on a second computer using the same database
• Your IT policy prefers a SQL Server login over Windows credentials for application access
• A workstation reports "Login failed for user 'MarginMaster'" or "Cannot connect"

A single-PC store that connects with Windows Authentication needs none of this.

Summary for the busy administrator

Everything below is done on the PC that holds the database, as a local administrator, and takes about ten minutes.

How Margin Master authenticates

By default Margin Master connects to its database with Windows Authentication — the Windows account running the application. On a single PC that is all that is ever needed, and it is why a fresh install works without anyone creating a login.

When a second computer shares the database, Windows Authentication is usually impractical: the workstation would need a Windows account the SQL Server recognises, which on a workgroup network it typically does not. For that case Margin Master uses a SQL Server login:

• On startup, Margin Master creates a SQL login named MarginMaster on its instance (if it does not already exist) and makes it a member of db_owner on each Margin Master database. There is no per-machine account and no enrollment step.
• Every workstation signs in with that same login. It is a shared credential for a trusted store LAN, not a per-user account.

What Margin Master cannot do on its own is the server side. A default SQL Server installation accepts only Windows authentication, has no inbound firewall rule, and (for a named instance) may not have SQL Server Browser running. Until those are changed, the login exists but cannot be used from another machine. That is what the rest of this guide covers.

If the login is missing: Margin Master needs the ALTER ANY LOGIN permission to create it. When the account it runs under lacks that permission — common on a SQL Server that IT manages — the application instead writes a ready-to-run script to C:\ProgramData\RetailerSoft\MarginMaster\Scripts\Create-MarginMaster-Login.sql for a DBA to execute as sysadmin. Review it, run it, and the login is in place.

What Mixed Mode changes — and what it doesn't

The one setting that matters is SQL Server's authentication mode, which has to move from "Windows Authentication mode" to "SQL Server and Windows Authentication mode" — commonly called Mixed Mode. Administrators rightly ask what that entails:

Which instance? Margin Master normally installs its own SQL Server Express instance named MARGINMASTER. If the store's point-of-sale system has its own SQL Server on the same PC, that instance is separate and should be left alone. Configure only the instance that holds the Margin Master database.

Step-by-step configuration

All steps are performed on the PC holding the database, as a local administrator.

1. Enable Mixed Mode authentication

In SQL Server Management Studio, connect to the instance, right-click the server in Object Explorer > Properties > Security > select SQL Server and Windows Authentication mode > OK.

Equivalent registry value, if SSMS is not installed:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceId>\MSSQLServer
    LoginMode  (DWORD)  =  2

<InstanceId> is listed under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL (for example MSSQL17.MARGINMASTER).

This does not enable the sa account and does not disable Windows authentication.

2. Enable TCP/IP

SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <INSTANCE> > right-click TCP/IP > Enable.

A dynamic port is fine and is the normal configuration for a named instance — leave it alone if one is already assigned. Only assign a static port (IP Addresses tab > IPAll > TCP Port) if the instance has no port at all, or if your policy does not allow SQL Server Browser to run.

3. Restart SQL Server

Steps 1 and 2 take effect only after a restart. In Configuration Manager, SQL Server Services > right-click SQL Server (<INSTANCE>) > Restart. Open connections are dropped.

4. Start SQL Server Browser (named instances only)

Configuration Manager > SQL Server Services > SQL Server Browser > Properties > Service tab > Start Mode: Automatic > OK, then right-click > Start.

SQL Server Browser is what lets a workstation resolve HOSTPC\MARGINMASTER to the instance's actual port. Skip this step for a default instance, or if you assigned a static port and will have workstations connect as HOSTPC,port.

5. Create the firewall rules

Margin Master itself never listens on a port and needs no inbound rule. The only inbound rules involved are for SQL Server on the host PC, and only when more than one computer uses the database.

A program rule on sqlservr.exe is preferable to a port rule: it is correct whether the instance uses a static or a dynamic port, and never needs revisiting if the port changes. In an elevated PowerShell:

# Database engine -- program rule, correct for static and dynamic ports alike.
# Adjust the path to match the instance.
New-NetFirewallRule -DisplayName "Margin Master - SQL Server (TCP)" `
    -Direction Inbound -Action Allow -Protocol TCP `
    -Program "C:\Program Files\Microsoft SQL Server\MSSQL17.MARGINMASTER\MSSQL\Binn\sqlservr.exe" `
    -Profile Domain,Private

# SQL Server Browser -- named instances only
New-NetFirewallRule -DisplayName "Margin Master - SQL Browser (UDP 1434)" `
    -Direction Inbound -Action Allow -Protocol UDP -LocalPort 1434 `
    -Profile Domain,Private

If a port rule is preferred over a program rule, replace -Program ... with -LocalPort <the instance's port>.

Never scope these rules to the Public profile, and never forward the SQL Server port through a router. This is a shared credential intended for a trusted store LAN only.

6. Confirm the Margin Master login

Margin Master creates this login itself on startup, so it normally already exists. To verify, run against the instance:

-- Login exists, enabled, not locked out
SELECT name, is_disabled,
       LOGINPROPERTY(name, 'IsLocked')  AS IsLocked,
       LOGINPROPERTY(name, 'IsExpired') AS IsExpired
FROM sys.server_principals
WHERE name = 'MarginMaster';

-- Login is db_owner in the Margin Master database (run with that database selected)
SELECT m.name AS MemberName, r.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals r ON r.principal_id = drm.role_principal_id
JOIN sys.database_principals m ON m.principal_id = drm.member_principal_id
WHERE r.name = 'db_owner' AND m.name = 'MarginMaster';

If the login is missing, start Margin Master once on the host PC and it will be created (or run the generated script described above). Margin Master support will provide the password — it is a fixed value shared by every workstation and is not published in this documentation.

7. Verify from the workstation with SSMS

Connect SSMS on the second PC before Margin Master, every time. SSMS and Margin Master connect the same way: same server name, same login, same password. A successful SSMS connection therefore proves the whole chain at once — the instance is reachable, Mixed Mode is on, the login works — and it shows you the database name Margin Master will ask for next. If SSMS cannot connect, Margin Master will not either, and the Margin Master connection window gives you far less to diagnose with.

In Connect to Server on the workstation:

Trust server certificate is not optional in current SSMS. SSMS 20 and later default Encryption to Mandatory, and SQL Server Express presents a self-signed certificate, so the connection is refused with a certificate chain error until this box is ticked. It reads like a host misconfiguration and is not one. Margin Master sets TrustServerCertificate in its own connection string, so this is a step SSMS needs and the application does not.

Once connected, expand Databases and note the Margin Master database name. That is the name to select in Connecting the workstation below.

If SSMS will not connect

Drop to sqlcmd. It bypasses the SSMS encryption defaults, so it separates a client-side setting from a genuine host problem:

sqlcmd -S HOSTPC\MARGINMASTER -U MarginMaster -P <password> -Q "SELECT @@SERVERNAME, DB_NAME()"

Using your own SQL login instead

If your policy calls for a login you create and control rather than the shared MarginMaster login, that works too. The Connect to Database window accepts any SQL Server login. Requirements:

• Member of db_owner on each Margin Master database. Margin Master creates and alters tables, indexes and its own supporting objects at runtime, so read access alone is not enough.
• Password expiration turned off (CHECK_EXPIRATION = OFF) is strongly recommended — an expired password stops Margin Master silently on every workstation at once.
• Creating and restoring databases is done from the host PC, where Margin Master runs as a local administrator; a workstation login does not need server-level roles for day-to-day use.

Margin Master will still create the MarginMaster login on startup when it has permission to. It is harmless to leave in place, or you may disable it once your own login is in use.

Connecting the workstation

With SSMS connected in step 7, every value below is one you have already proven. Re-enter the same four, on the second PC:

1. Open Margin Master. If it is already connected to a local database, use File > Database > Manage Database Connections to reach the Connect to Database window.
2. In SQL Server, enter the same server name you typed into SSMS — HOSTPC\MARGINMASTER for a named instance, or just HOSTPC for a default instance.
3. Clear Use Windows Authentication.
4. Enter the same Username (MarginMaster, or your own login) and Password.
5. Click Test Connection, choose the database you saw under Databases in SSMS from the Available Databases list, then Connect.

There is no encryption setting to match here. Margin Master trusts the instance's certificate on its own, so the SSMS checkbox from step 7 has no equivalent in this window.

The credentials are stored encrypted on the workstation in C:\ProgramData\RetailerSoft\MarginMaster\DatabaseConnections.json. Close and reopen Margin Master once to confirm it reconnects without prompting.

The window itself is described field by field in Database Connection Setup.

Encryption

Margin Master connects with TLS and is configured to trust the SQL Server instance's own certificate, which for SQL Server Express is the self-signed certificate the instance generates at install. Traffic between the workstation and the host is encrypted; no certificate has to be issued or installed for Margin Master to connect.

SSMS does not make that assumption for you. From SSMS 20 onward it requires Trust server certificate to be ticked against the same instance, which is why step 7 calls for it explicitly. Issuing a certificate from a CA the workstation trusts removes the need for the checkbox, but no part of Margin Master requires it.

Troubleshooting

Related

• Firewall and Network Requirements — the outbound endpoints every store needs
• Prerequisites — supported SQL Server versions
• Database Connection Setup — the workstation-side connection window