Recover access to SQL Server

Owned by Stephen Jennings (Unlicensed)
Last updated: Apr 20, 2022 by Anthony Turriciano
4 min read

Problem

Earlier versions of NorthStar Order Entry did not add the Administrators group to the SQL Server sysadmins role. This meant that users could not access the NorthStar databases in SQL Server.

This document demonstrates how to add the Administrators group to the sysadmins server role.

Procedure for current versions of Order Entry

In current versions of NorthStar Order Entry, Web Admin has a tool specifically for this task. Choose "Fix SQL Authentication" on the Utilities page. 

localhost:8082/webadmin/utilities.html

Procedure for previous versions of Order Entry

This requires restarting SQL Server and possibly Order Entry, so we recommend doing this while the site is closed.

Step 1: Opening SQL Server Configuration Manager

In the Start menu, search for "SQL Server Configuration Manager", and run it.

If you are using Windows 10, SQL Server Configuration Manager may not be listed in the Start menu. In this case go to Start → Run and run one of the following commands:

  • "SQLServerManager12.msc" (SQL Server 2014)
  • "SQLServerManager11.msc" (SQL Server 2012)
  • "SQLServerManager10.msc" (SQL Server 2008)

it is also found in C:\Windows\SysWOW64\  labelled SQLServerManager11.msc

Step 2: Putting SQL Server in single-user mode

Make sure all NorthStar Order Services are stopped



In the "SQL Server Services" node, stop all SQL Server services. Make a note of which ones were started, you will need to restart them in step 4.

Find the SQL Server service named "SQL Server (SQLEXPRESS)". The instance name might be something other than "SQLEXPRESS", but the name should just be "SQL Server". Right-click and click Properties.


Under the Startup Parameters tab, add a new parameter "-m" (this is a hyphen and a lowercase m, with no whitespace).

Save these settings and restart ONLY the "SQL Server (SQLEXPRESS)" service.

Step 3: Adding Administrators to the sysadmins server role

Open the Start menu and find SQL Server 2012 Management Studio, or whichever version of Management Studio is installed. Right-click and click "Run as Administrator".

Connect to the SQL Server.

Open the node Security → Logins. If there is already a node named "BUILTIN\Administrators", skip to the next step.

Right-click "Logins" and click "New Login". In the "Login name", type "BUILTIN\Administrators", then click OK. Right-click the Logins node and click "Refresh".

Right-click Security → Logins → BUILTIN\Administrators and click "Properties". In the Server Roles page, flag "sysadmin", then click OK.

Close SQL Server Management Studio.


Step 4: Put SQL Server back into multi-user mode

In SQL Server Configuration Manager, go back into SQL Server properties and remove the "-m" startup parameter.

Restart the "SQL Server (SQLEXPRESS)" service, and restart any other services which were originally started in step 2.

Step 5: Verify

Verify that NorthStar Order Entry is able to connect to SQL Server.

Restart SQL Server Management Studio (Run as Administrator) and verify you can still connect to SQL Server.

Troubleshooting

If you cannot connect to SQL Server in step 3, another service may be connecting to SQL Server before management studio. Single-user mode only allows a single user to connect to SQL Server, so if Order Entry or another service is connecting first, SQL Server Management Studio will not be able to connect.

Stop SQL Server, then ensure all the following services are stopped:

  • SQL Server Agent
  • SQL Server Browser
  • NorthStar Order Entry
  • NorthStar Remote
  • NorthStar Scheduler

Restart SQL Server and continue with step 4.

Login Failed. The login is from an untrusted domain and cannot be used with windows authentication

Follow these steps to disable loopback:

  1. Click Start, click Run, type regedit, and then click OK.

  2. Locate and then click the following registry subkey:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

  3. Right-click Lsa, point to New, and then click DWORD Value.

  4. Type DisableLoopbackCheck, and then press ENTER.

  5. Right-click DisableLoopbackCheck, and then click Modify.

  6. In the Value data box, type 1, and then click OK.

  7. Exit Registry Editor.

  8. Restart the computer.

Should look like this:

Method 2 on the microsoft page here: https://docs.microsoft.com/en-us/troubleshoot/windows-server/networking/accessing-server-locally-with-fqdn-cname-alias-denied