Connecting a Second Computer

What is this?

A store has Margin Master working on one computer. They want to use it on a second computer too, with both looking at the same data. This walks you through it start to finish.

No SQL Server knowledge is assumed. Every step says what to click, what to type, and how to tell it worked. Where a step involves running something that looks like code, you are told exactly where to paste it and what a good result looks like.

You will work on two computers, and it matters which one you are on at each step:

Every step below is marked HOST or WORKSTATION. If you are remoting into both, keep two sessions open and check the marker before each step.

Budget about twenty minutes the first time.

Before you start

Have these ready:

• Remote access to both computers. You will be clicking on each in turn.
• Local administrator rights on the HOST. Several steps change Windows and SQL Server settings and will not work from a standard account.
• The MarginMaster login and password. Both are in The Margin Master login just below.
• Both computers on the same store network. This does not work over the internet and should not be made to.

One reassurance to have ready if the store's IT person asks what you are changing: nothing here opens the store to the internet, no router change is involved, and the sa account is not enabled or touched. If they want the detail, send them SQL Server Authentication Setup for IT Administrators, which is written for them.

The quick way: Check Connection Settings

Margin Master can do most of this for you. Try it first. The numbered steps further down are the fallback for when it cannot finish.

New in the next Margin Master update. If Tools in SQL Server Tools does not list Check Connection Settings... yet, skip to Step 1.

HOST

1. Open File > Utilities > SQL Server Tools, then Tools > Check Connection Settings...
2. On This computer is the host, click Check. Windows asks for permission; choose Yes. Each row shows a green ✓ when that setting is already right.
3. If any row shows ✗, click Fix Problems and choose Yes again. SQL Server restarts, which drops anyone using Margin Master for a few seconds.
4. Click Create Login. Margin Master creates a login named MarginMaster_LAN with its own random password, gives it access to every Margin Master database on this computer, and shows a connection block.
5. Click Copy to Clipboard, or Save to File..., and get the block to the workstation: paste it into the remote session or a chat, or copy the file across.

WORKSTATION

6. Open SQL Server Tools > Tools > Check Connection Settings... On a computer that has no database yet, the SQL Server Tools... button in the Connect to Database window gets you there.
7. On This computer connects to a host, click Paste from Clipboard (or Open File...).
8. Choose the Database, then click Check Connection. Every row says what it checked, and a row marked ✗ says what to do about it, usually on the host.
9. When it says Ready, click Use This Connection. Connect to Database opens with everything filled in. Click Connect.

Treat the connection block like a password. The password inside is not readable, but any copy of Margin Master can open the block. If one goes astray, click Create New Password... on the host. Every computer that used the old block then needs the new one.

If any of that does not finish, work through the steps below instead. They use the shared login.

The Margin Master login

Margin Master creates this login itself every time it starts, so it already exists on any computer that has run the application. The values are the same at every store, which is what lets one set of instructions work everywhere.

You will use these twice on the second computer: in Step 3 to test with SSMS, and in Step 4 to connect Margin Master itself.

This login is shared, so the store network is what protects it. Keep the firewall rules in Step 2 scoped to Domain and Private, and never forward the SQL Server port through a router. A store that would rather not depend on a shared login can use one of its own instead, covered in If a store's IT does not want the shared login.

Step 1 — Write down the connection name

HOST. Everything later depends on getting this one piece of text right, so do it first and write it down.

1a. Get the computer name

Right-click the Start button, choose System, and read the Device name. It might be something like STORE-PC or FRONTOFFICE.

1b. Find out which SQL Server is Margin Master's

Open SQL Server Configuration Manager from the Start menu, and click SQL Server Services on the left. You will see one or more entries that look like SQL Server (SOMETHING).

The word in brackets is the instance name. What you see tells you what to write down:

More than one entry? That is normal and important. Stores often run a point of sale that brings its own SQL Server, so you may see SQL Server (ROCKSOLID) or similar alongside Margin Master's. Only configure Margin Master's instance and leave the others completely alone. If you are not sure which is which, open Margin Master on the HOST and go to File > Database > Manage Database Connections. The SQL Server box shows the instance it is connected to right now. Close the window without changing anything.

Write the connection name down. From here on, wherever this document says STORE-PC\MARGINMASTER, type yours instead.

Step 2 — Let the HOST accept connections from other computers

HOST. Out of the box, SQL Server only accepts connections from the computer it is installed on, and only from Windows accounts. Six things have to change. Margin Master ships a script that checks all six and fixes them, so try that first.

The quick way

In Margin Master on the HOST, go to File > Utilities > Support Scripts. It asks for a password, which support has. (It is also reachable with no database at all, from File > Utilities > SQL Server Tools > Tools > Support Scripts.) Choose Enable Remote (LAN) Access and run it. Windows will ask you to allow it to make changes; say yes.

It changes nothing on the first run. It only looks, and reports PASS or FAIL for each of the six items in a PowerShell window. Read the list.

• Everything PASS — the HOST is already set up. Close the window and go to Step 3.
• Anything FAIL — the fixes are not applied automatically. Leave that same PowerShell window open, paste the line below into it and press Enter. It fixes what failed, then re-checks.

& "$env:TEMP\MarginMaster\InstallationScripts\Logs\Enable-SQLRemoteAccess.ps1" -Apply

Read the second report to confirm everything now passes.

It may refuse to run, and that is a safety feature. The script will not modify a SQL Server it cannot prove belongs to Margin Master. If you see it decline for that reason, you have pointed it at the point of sale's SQL Server. Go back to Step 1b, confirm which instance is Margin Master's, and run it against that one.

If the script did its job, skip to Step 3. If Support Scripts is not available on this version, or you would rather do it by hand, use the manual list below.

The manual way

Six items, in this order. The order matters: item 3 is what makes items 1 and 2 take effect.

1. Allow username-and-password logins.

Open SQL Server Management Studio (SSMS) on the HOST and connect to Margin Master's instance. In the Object Explorer panel on the left, right-click the very top entry (the server itself), choose Properties, then Security on the left of the dialog. Select SQL Server and Windows Authentication mode and click OK.

You will get a message saying the change takes effect after a restart. That is item 3. Click OK.

How you know it worked: re-open Properties > Security and the new option is still selected.

2. Turn on network connections.

In SQL Server Configuration Manager, expand SQL Server Network Configuration on the left, then click Protocols for MARGINMASTER (or whatever your instance is called). In the right-hand panel, right-click TCP/IP and choose Enable.

How you know it worked: the Status column for TCP/IP reads Enabled.

Do not change the port. Whatever is there already is correct.

3. Restart SQL Server.

Still in Configuration Manager, click SQL Server Services on the left. Right-click SQL Server (MARGINMASTER) and choose Restart.

Items 1 and 2 do nothing until you do this. It takes a few seconds and briefly disconnects anyone using the database, so if the store's point of sale shares this computer, do it outside business hours.

How you know it worked: the State column returns to Running.

4. Start the service that helps other computers find this one.

Only needed if your connection name has a backslash in it. If it is just STORE-PC with no backslash, skip this item.

In SQL Server Services, right-click SQL Server Browser and choose Properties. On the Service tab, set Start Mode to Automatic and click OK. Then right-click SQL Server Browser again and choose Start.

How you know it worked: State reads Running and Start Mode reads Automatic.

5. Let the connection through the Windows firewall.

Open Windows PowerShell as administrator (right-click Start > Terminal (Admin) or Windows PowerShell (Admin)), then paste both commands below and press Enter.

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

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

How you know it worked: each command prints a block of text describing the new rule. An error mentioning the file path means SQL Server is installed somewhere else on this computer. Search for sqlservr.exe under C:\Program Files\Microsoft SQL Server, find the folder matching your instance name, and correct the path.

Leave -Profile Domain,Private exactly as written. It is what keeps this limited to the store's own network.

6. Check the login exists.

Margin Master creates a login named MarginMaster by itself, so this is usually just a confirmation.

In SSMS on the HOST, click New Query on the toolbar. Paste this into the blank window and press F5:

SELECT name, is_disabled FROM sys.server_principals WHERE name = 'MarginMaster';

How you know it worked: a Results grid appears at the bottom with one row, MarginMaster, and is_disabled showing 0.

• No rows at all — the login is missing. Open Margin Master on the HOST and close it again, which usually creates it, then re-run. If it is still missing, look for a file at C:\ProgramData\RetailerSoft\MarginMaster\Scripts\Create-MarginMaster-Login.sql. Open it in SSMS and press F5 to run it.
• is_disabled shows 1 — the login exists but is switched off. Escalate rather than guessing; someone disabled it deliberately.

Step 3 — Prove it works from the WORKSTATION

WORKSTATION. Do this before touching Margin Master. It is the same connection, so if this works Margin Master will work, and if it fails you learn exactly which item in Step 2 to go back to. Starting in Margin Master instead tells you far less.

You need SSMS on this computer. If it is not installed, see Installing SQL Server Management Studio.

Open SSMS. The Connect to Server box appears. Fill it in:

Trust server certificate catches almost everyone. Recent versions of SSMS refuse the connection with a long certificate error unless it is ticked. It reads like something is wrong with the HOST, and nothing is. If you do not see the checkbox, click Options at the bottom of the box and look on the Connection Properties tab.

Margin Master has no equivalent setting and needs none. This is an SSMS quirk only.

Click Connect.

How you know it worked: the Connect box closes and the server appears in Object Explorer on the left.

Now expand Databases and write down the Margin Master database name. It is usually the store name or number. You need it in Step 4.

If it would not connect

Match the message to the row, then go back and redo that item in Step 2.

Do not guess at the password. Three wrong attempts do not lock this login, but a wrong password produces the same "Login failed" message as a genuine setup problem, which will send you chasing the wrong thing. Confirm the password with support before re-running Step 2.

Step 4 — Connect Margin Master

WORKSTATION. Everything here is what you just typed into SSMS, so this part is quick.

Open Margin Master. If it has never connected to a database, the Connect to Database window appears on its own. If it is already connected to something, go to File > Database > Manage Database Connections.

1. In the SQL Server box, type the connection name from Step 1. Type it rather than scanning for it; you already know it is correct because SSMS just used it.
2. Untick Use Windows Authentication. A Username and Password box appear.
3. Enter MarginMaster and the password.
4. Click Test Connection. A green checkmark means success and a red X means failure. If you get a red X here but SSMS connected fine, re-check the username and password for typing mistakes.
5. Open the Available Databases list and choose the database name you wrote down in Step 3. The store details underneath fill in on their own, which confirms it is a real Margin Master database.
6. Click Connect.

If the Network scan button finds nothing, ignore it. Scanning relies on a broadcast that Windows Firewall routinely discards, so it often comes up empty on networks that are working perfectly. Typing the name is the reliable way, and Step 3 already proved the name is right.

Confirm it stuck

Close Margin Master completely and open it again. It should go straight to the main window without asking for the database or the password. The credentials are saved, encrypted, on this computer at C:\ProgramData\RetailerSoft\MarginMaster\DatabaseConnections.json.

If it prompts again, the connection did not save. Repeat Step 4 and make sure you click Connect rather than closing the window after Test Connection.

When you are finished

• SSMS on the WORKSTATION connects to the HOST
• Margin Master on the WORKSTATION opens to the main window with the store's real data
• Closing and reopening Margin Master does not prompt for anything
• Margin Master on the HOST still works exactly as before

That last one is worth actually checking before you end the call. Nothing in Step 2 should have affected the HOST, but item 3 restarts SQL Server, and it is better to find a problem while you are still connected.

If a store's IT does not want the shared login

Some sites prefer a login they create and control. That is fine and changes nothing about Steps 3 and 4, except that you enter their username and password instead of MarginMaster.

Pass them two requirements. The login has to be db_owner on the Margin Master database, because Margin Master builds and changes tables as it works and read-only access is not enough. And password expiry should be switched off, because an expired password stops Margin Master on every computer at once, with no obvious cause.

Related

• SQL Server Authentication Setup for IT Administrators — the same task written for a store's own IT person, safe to forward
• Database Connection Setup — the Connect to Database window, field by field
• Installing SQL Server Management Studio — if SSMS is missing from the workstation
• Firewall and Network Requirements — the outbound addresses every store needs