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. Apart from the password of the shared MarginMaster login, which Margin Master support provides on request, 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 Defender 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"
• Several people sign in to the same computer with their own Windows accounts and should all reach the database

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

Margin Master can do this for you. On the PC that holds the database, File > Utilities > SQL Server Tools, then Tools > Check Connection Settings..., checks and fixes the settings below, creates a MarginMaster_LAN login with its own random password, and produces a connection block to paste on the workstation. See Connecting a Second Computer. The tool arrives in the next Margin Master update. This guide remains the manual equivalent, and describes exactly what the tool changes.

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 gives it full access to 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.
• On a shared computer where several people sign in with their own Windows accounts, the saved connection belongs to the computer, not to one Windows account. Set it to the MarginMaster login once and every Windows user on that PC connects with it. Only Mixed Mode (step 1 and the restart) is needed for that; the network steps are for other computers.

What Margin Master cannot do on its own is the server side. A SQL Server Express instance installed by Margin Master's SQL Server Tools from September 2026 on is already in Mixed Mode, with sa left disabled, so the login can sign in as soon as it exists. Any other SQL Server, including one installed by an earlier version of Margin Master, accepts only Windows authentication until it is changed. Neither kind has an inbound firewall rule, and a named instance may not have SQL Server Browser running. Until those are in place, the login cannot be used from another machine. That is what the rest of this guide covers.

If the login is missing: Margin Master can only create the login when the Windows account it runs under is allowed to create logins on that SQL Server. On a SQL Server that IT manages, that right is often withheld. Contact Margin Master support, who will give your DBA what they need to create the login.

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.

If SQL Server and Windows Authentication mode is already selected when you open that page, Mixed Mode is already on; skip to step 2.

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

If SSMS is not installed on this PC and you would rather not install it, Margin Master support can make this change for you.

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 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.

Open Windows Defender Firewall with Advanced Security from the Start menu.

Rule 1 — SQL Server database engine

1. Click Inbound Rules on the left, then New Rule... on the right.
2. Rule Type: choose Program, then Next.
3. Program: choose This program path, click Browse..., and select sqlservr.exe in the instance's folder under C:\Program Files\Microsoft SQL Server — the folder whose name ends in the instance name (for example MSSQL17.MARGINMASTER), then MSSQL > Binn. Click Next.
4. Action: choose Allow the connection, then Next.
5. Profile: leave Domain and Private ticked and clear Public, then Next.
6. Name: Margin Master - SQL Server (TCP), then Finish.
7. Optionally, open the new rule's Properties > Protocols and Ports and set Protocol type to TCP.

If you prefer a port rule, choose Port at step 2 instead, then TCP and Specific local ports set to the instance's port.

Rule 2 — SQL Server Browser (named instances only)

1. Inbound Rules > New Rule...
2. Rule Type: choose Port, then Next.
3. Choose UDP, and Specific local ports: 1434. Click Next.
4. Action: choose Allow the connection, then Next.
5. Profile: leave Domain and Private ticked and clear Public, then Next.
6. Name: Margin Master - SQL Browser (UDP 1434), then Finish.

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, in SSMS Object Explorer expand Security > Logins and look for MarginMaster. Right-click it > Properties > Status: Login should be Enabled.

If the login is missing, start Margin Master once on the host PC and it will be created, or contact Margin Master support. The login is MarginMaster; its password is a fixed value shared by every workstation, and Margin Master support gives it to the store's IT administrator on request. Because it is shared, the Domain and Private firewall scoping in step 5 is what keeps it to the store's own network. Margin Master support can also confirm the login's access to the Margin Master database for you.

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 already trusts the instance's certificate on its own, 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

First confirm Trust server certificate is ticked — it is the most common cause. Then match the message to the Troubleshooting table below. Margin Master support can also test the connection for you and tell you whether the problem is on the host or in SSMS.

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:

• Owner-level access to 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.
• Enforce password expiration turned off (SSMS login Properties > General) 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 any server-wide permissions 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.

Check Connection Settings creates a login of exactly this kind for you: MarginMaster_LAN, with owner-level access to every Margin Master database, password expiry off, and a random password that only the host keeps. Workstations receive it through the connection block rather than by typing it.

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. 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

• Connecting a Second Computer — the same task as a click-by-click walkthrough, for a support analyst driving both machines
• Firewall and Network Requirements — the outbound endpoints every store needs
• Prerequisites — supported SQL Server versions
• Database Connection Setup — the workstation-side connection window