SQL Server 2017 Express - Service Start Encounters File Path Issue (on Windows Server 2012 R2 machine which is also serving as a Domain Controller)

PLEASE NOTE this is my 3rd install attempt, with an uninstall and reboot in between each attempt (so ye olde "uninstall and reinstall" suggestion, while often quite valid, is not so welcome at this point).

TL/DR: SQL Server 2017 Express installed to a Windows Server 2012 R2 machine which also serves as a domain controller is failing to start as a service and is reporting file path issues, despite having the correct file paths registered.

If you want to see all the things I've tried, grab your coffee, and comfy seat, and, if you're old like me, your readers. Otherwise, scroll on down to the bottom.

---

I'm trying to get up and running SQL Server 2017 Express to a Windows Server 20112 R2 machine. All seemed to go well with the install until it attempted to start the service.

After the most recent install, I dug through the weeds to see if I could get more info. Here's how that went ...

• Pared from the windows logs (same also appeared int he SQL Server log file, of course, I just like seeing the info vs error tags that are present in the event viewer version).

  INFO: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

  INFO: Authentication mode is MIXED.

  INFO: Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL14.PCI_SQLEXP\MSSQL\Log\ERRORLOG'.

  INFO: The service account is 'NT Service\MSSQL$PCI_SQLEXP'. This is an informational message; no user action is required.

  INFO: Registry startup parameters:
  -d C:\Program Files\Microsoft SQL Server\MSSQL14.PCI_SQLEXP\MSSQL\DATA\master.mdf -e C:\Program Files\Microsoft SQL Server\MSSQL14.PCI_SQLEXP\MSSQL\Log\ERRORLOG -l C:\Program Files\Microsoft SQL Server\MSSQL14.PCI_SQLEXP\MSSQL\DATA\mastlog.ldf

    INFO: Command Line Startup Parameters:
         -s "PCI_SQLEXP"
         -f
         -T 3608
  
    INFO: Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.  After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
  
    INFO: This instance of SQL Server last reported using a process ID of 7564 at 7/16/2020 8:25:54 PM (local) 7/17/2020 12:25:54 AM (UTC). This is an informational message only; no user action is required.
  
    INFO: Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details.
  
    ERROR: Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
  
    INFO: Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
  
    ERROR: TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 
  
    ERROR: TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. 
  
    ERROR: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
  
    ERROR: SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the operating system error log for information about possible related problems.
  

Initial research into this indicated that maybe SSL was being forced unsuccessfully, and to turn that off.

In computer management console, under

Services and Applications \ SQL Server Configuration Manager \ SQL Server Network Configuration \ right-click on Protocols for PCI_SQLEXP, Properties,

• the General tab's Force Encryption option is set to NO, and

• on the Certificate tab there is no SSL certificate associated. [which makes perfect sense as we haven't associated one to it, nor are we forcing SSL]

OK, so others said that when SSL is not being forced, maybe the registry key has a corrupt data value, and to remove that.

In Registry editor console, under

HKLM \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL14.PCI_SQLEXP \ MSSQLServer \ SuperSocketNetLib: Certificate, type = REG_SZ, data is clear

Then others still said that when the registry key was also fine, maybe it's a permissions issue and to check that the user has Log On as a Services rights.

In LOCAL SECURITY POLICY ...

• User Rights Assignment \ Log On as a Services, 'NT Service\MSSQL$PCI_SQLEXP' user is present.

And then, when that's present, to maybe change the user being, well, used, to NT SERVICE\NETWORKSERVICE, so

In computer management console

Services and Applications \ SQL Server Configuration Manager \ SQL Server Network Configuration \ SQL Server SErvices, right-click on SQL Server (PCI_SQLEXP)

• Change the Log On account to Built-In Account, Network Service

Resultant error

WMI Provider error [call to WMI provider returned error code: 0x800703b1]

Internet says to check the Active Directory users, and make sure account is in the SQLServerMSSQLServerADHelperUser$ComputerName group

In Active Directory Users & Computers \ PCI.local \ Users,

• The SQLServer2005SQLBrowserUsers$SERVER2 security group is present as domain local, but has NO members,

• The SQLRUserGroupPCI_SQLEXP security group is present as domain local, and also has NO members.

These are the only two SQL-related security groups I can find, which was an initial "ah ha, this is the core of the issue" moment, until I poked the internet further and saw that in recent versions Microsoft has moved away from using these security groups.

So this is the point I throw in the towel and start going back through to document my troubleshooting / try this / try that steps prior to seeking help.

I got to (5) and this time, instead of giving the WMI error, it let me change the account to NETWORKSERVICE.

Then it let me start the SQL Server (PCI_SQLEXP) service.

I yell at the computer and tell it that THIS, THIS is why people have a love/hate relationship with geekdom. I didn't CHANGE anything I jsut LOOKED at things, verifying they were the settings the internet suggested they should be, yet this time it let me complete the step vs. erroring. WHYYYYYYY????? yet: GLEE abounds because it let me do the step where it errored before. Such mixed emotions.

My person, who's all-knowing and wise, says: "make sure it stays running." Dang her.

I refresh the service list and lo and behold, it has stopped.

Pared from the windows logs.

yada, yada, yada same old information messages

The service account is 'PCI\SERVER2$'. This is an informational message; no user action is required. [OK, that threw me for a second, but then I remembered that's how NT AUTHORITY\NETWORKSERVICE works]

yada, yada, yada more same old information messages, and then .... A self-generated certificate was successfully loaded for encryption. [weeee!]

Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\PCI_SQLEXP ].[weeee!]

Server local connection provider is ready to accept connection on [ \.\pipe\MSSQL$PCI_SQLEXP\sql\query ]. [weeee!]

SQL Server is now ready for client connections. This is an informational message; no user action is required. [weeee!]

Starting up database 'msdb'.

Starting up database 'mssqlsystemresource'.

Starting up database 'model'.

FCB::Open failed: Could not open file d:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1. OS error: 21(The device is not ready.).

FCB::Open failed: Could not open file d:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\model.mdf for file number 1. OS error: 21(The device is not ready.).

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'd:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'd:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

Wait, what? Those are NOT the file paths. The file paths are correct in the Registry startup parameters (see step 1).

The internet says this is likely due to the machine being a domain controller. It is, indeed, being installed on a server that's also functioning as a domain controller and there's no getting around that, nor installing SQL Server to another machine.

I find a suggestion to try a NET START then SQL commands to change the file location values for these databases. https://serverfault.com/questions/447808/sql-server-express-service-is-not-starting

So I try NET START MSSQL$PCI_SQLEXP /f /T3608 and get an error message:

The service name is invalid. More help is available by tryping NET HELPMSG 2185.

Then for giggles, I type NET HELPMSG 2185 and submit. Response:

The service name is invalid.

Well, gee, that was helpful. Not.

Microsoft says: "To determine the service names, start Registry Editor (type Regedit.exe or Regedt32.exe) and go to the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services" and that "Under Services, the service name that works with the NET STOP and NET START commands is listed."

In REGISTRY EDITOR console, in the indicated drilldown, there's the service name, just as I expected and used: MSSQL$PCI_SQLEXP

And this is where I truly throw that towel in, or wave it in surrender. The service, MSSQL$PCI_SQLEXP, exists, and can be started via the SQL Server Configuration Manager. It just won't stay running because of the file location issue in (9). Thus I'm skeptical that it would stay running if I could start it via command line NET START command instead, so I am not going to pursue that "service name is invalid" error message further (unless one of you wise ones gives me reason to do so, along with some direction.)

---

Anyone have any suggestions on things I can try that I haven't already?

:0)