Cruel Joke: Prevent SQL Server From Starting, and With (Almost) No Indication Why

(last updated: 2019-03-07 @ 15:40 EST / 2019-03-07 @ 20:40 UTC )

I ran across something the other day that I thought would be more interesting / useful, but it ended being merely disturbing.

For some reason (most likely an obsolete one), SQL Server looks for the following file:

%SQLBinRoot%\sqlservr.ini

 
%SQLBinRoot% is where the sqlservr.exe file is located.

 
So, I created such a file (an empty one). And since it is an INI file, those are typically read when a program / application starts, so that’s what I tried…

Restarting the Instance in Object Explorer in SSMS

When I tried to “restart” the instance in SSMS, I received the following error message pop-up:

Unable to start service MSSQLSERVER on server ALBRIGHT. (mscorlib)
 
ADDITIONAL INFORMATION:
 
The MSSQLSERVER service on ALBRIGHT started and then stopped. (ObjectExplorer)

 
There is sooo much detailed information there that I don’t even know where to start. I mean, it will take days to sift through all of that debug-level info. 😼

Well, surely the ERRORLOG file will have something in it, right?

No Log Files!!

I went to the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log folder and found….. NOTHING NEW! That’s right: no new ERRORLOG file, no new SQLDump* files, nothing. No files in the Log folder had been created or updated as a result of me trying to start the instance.

Start Service from Command-Line

Next, I opened a Command Prompt “as Administrator” and ran the following (I have a default instance so the service name is “MSSQLSERVER”):

C:\WINDOWS\system32>net start mssqlserver
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service could not be started.
 
More help is available by typing NET HELPMSG 3523.

 
Again, not helpful.

Start sqlservr.exe as a Foreground Process

I wanted to see if there was an error early in the starting-up process that was maybe being dumped to the console or stderr, prior to the ERRRORLOG file being created and used. I went to the Binn folder:

C:\WINDOWS\system32>CD "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn"

 
and ran the following (the “-c” command-line switch is to run SQL Server “not as a service”):

sqlservr -c -s MSSQLSERVER

 
And absolutely nothing happened, outside of the command prompt coming back. But no error messages or anything. And again, nothing new or updated in the Log folder.

So I tried starting with “minimal configuration”:

sqlservr -c -f -s MSSQLSERVER

 
And absolutely nothing happened, outside of the command prompt coming back. But no error messages or anything. And again, nothing new or updated in the Log folder.

So I tried starting in “single user admin mode”:

sqlservr -c -m -s MSSQLSERVER

 
And absolutely nothing happened, outside of the command prompt coming back. But no error messages or anything. And again, nothing new or updated in the Log folder.

Permissions???

Could this perhaps be a permissions issue with the INI file that I created and the service account not being able to read it?

Nope: I gave “Full Control” to “Everyone” on that file. And no login or group in the security list had any “Deny” permissions, so the file was definitely accessible.

Event Viewer

I finally found something in the Windows Event Viewer, though none of it is terribly useful.

Windows Logs\System:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Service Control Manager"
              Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}"
              EventSourceName="Service Control Manager" /> 
    <EventID Qualifiers="49152">7034</EventID> 
    <Version>0</Version> 
    <Level>2</Level> 
    <Task>0</Task> 
    <Opcode>0</Opcode> 
    <Keywords>0x8080000000000000</Keywords> 
    <TimeCreated SystemTime="2019-03-01T18:05:56.404284000Z" /> 
    <EventRecordID>6199</EventRecordID> 
    <Correlation /> 
    <Execution ProcessID="880" ThreadID="11360" /> 
    <Channel>System</Channel> 
    <Computer>ALBRIGHT</Computer> 
    <Security /> 
  </System>
  <EventData>
    <Data Name="param1">SQL Server (MSSQLSERVER)</Data> 
    <Data Name="param2">10</Data> 
    <Binary>4D005300530051004C005300450052005600450052000000</Binary> 
  </EventData>
</Event>

 

Windows Logs\Application:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Application Error" /> 
    <EventID Qualifiers="0">1000</EventID> 
    <Level>2</Level> 
    <Task>100</Task> 
    <Keywords>0x80000000000000</Keywords> 
    <TimeCreated SystemTime="2019-03-01T18:17:48.616546200Z" /> 
    <EventRecordID>16627</EventRecordID> 
    <Channel>Application</Channel> 
    <Computer>ALBRIGHT</Computer> 
    <Security /> 
  </System>
  <EventData>
    <Data>sqlservr.exe</Data> 
    <Data>2017.140.3045.24</Data> 
    <Data>5bc9b8be</Data> 
    <Data>KERNELBASE.dll</Data> 
    <Data>10.0.17763.292</Data> 
    <Data>b51bba8e</Data> 
    <Data>c06d007e</Data> 
    <Data>0000000000055549</Data> 
    <Data>17c8</Data> 
    <Data>01d4d05b13294a28</Data> 
    <Data>C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlservr.exe</Data> 
    <Data>C:\WINDOWS\System32\KERNELBASE.dll</Data> 
    <Data>e5626b3c-ff4a-4136-98e7-1b031b332e92</Data> 
    <Data /> 
    <Data /> 
  </EventData>
</Event>

 
and:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Windows Error Reporting" /> 
    <EventID Qualifiers="0">1001</EventID> 
    <Level>4</Level> 
    <Task>0</Task> 
    <Keywords>0x80000000000000</Keywords> 
    <TimeCreated SystemTime="2019-03-01T18:17:50.147526000Z" /> 
    <EventRecordID>16628</EventRecordID> 
    <Channel>Application</Channel> 
    <Computer>ALBRIGHT</Computer> 
    <Security /> 
  </System>
  <EventData>
    <Data>1452581614067743273</Data> 
    <Data>4</Data> 
    <Data>APPCRASH</Data> 
    <Data>Not available</Data> 
    <Data>0</Data> 
    <Data>sqlservr.exe</Data> 
    <Data>2017.140.3045.24</Data> 
    <Data>5bc9b8be</Data> 
    <Data>KERNELBASE.dll</Data> 
    <Data>10.0.17763.292</Data> 
    <Data>b51bba8e</Data> 
    <Data>c06d007e</Data> 
    <Data>0000000000055549</Data> 
    <Data /> 
    <Data /> 
    <Data>\\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER7E26.tmp.dmp \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER7EE2.tmp.WERInternalMetadata.xml \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER7F12.tmp.xml \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER7F10.tmp.csv \\?\C:\ProgramData\Microsoft\Windows\WER\Temp\WER7F6F.tmp.txt</Data> 
    <Data>\\?\C:\ProgramData\Microsoft\Windows\WER\ReportArchive\AppCrash_sqlservr.exe_b5afcb5ada43af3b6e4627f2ebdaa1dfaaceb35a_9116d591_7d0a82c9</Data> 
    <Data /> 
    <Data>0</Data> 
    <Data>e5626b3c-ff4a-4136-98e7-1b031b332e92</Data> 
    <Data>268435456</Data> 
    <Data>f9d10f7ad5b66b8594289b4829c57a29</Data> 
    <Data>0</Data> 
  </EventData>
</Event>

In the event shown directly above, towards the bottom, in the final “<Data>” element that starts with “\\?\C:\ProgramData...“, that entry does point to a folder containing a Report.wer file. It is a plain text containing a bunch of error dump info, but nothing that would seem to indicate where to even start looking to fix this. And, nothing useful for searching on, at least not as far as my searching around revealed.

Conclusion

There you have it: a nearly untraceable way to prevent SQL Server from starting. 🙀

Happy early April Fools’ Day!

UPDATE 2019-03-07

Affected Versions

I initially only tested on SQL Server 2017. I have now tested on additional versions and this behavior was not always present.

sqlservr.ini does not prevent startup:

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2

sqlservr.ini does prevent startup:

  • SQL Server 2012 (SP4 GDR)
  • SQL Server 2014 (SP3)
  • SQL Server 2016 (SP2 CU3)
  • SQL Server 2017 (RTM CU12)
  • SQL Server 2019 (CTP 2.3)

Additional Proof About Permissions

Originally I had speculated that permissions to read the sqlservr.ini file were not the problem since I had given “Everyone” the “Full Control” permission. Well, as I was testing the additional versions, I thought of another, more direct way to see if the SQL Server process really could read the file: try reading the file from within SQL Server!

I used the following command to successfully read the file:

EXEC xp_cmdshell
  N'type "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlservr2.ini"';

This uses the same permissions as when SQL Server is starting up and tries to access the file because xp_cmdshell executes in the security context of the service account (well, unless the Login is not in the sysadmin server role and the proxy account is configured, but my Login is a sysadmin). The filename in the statement shown above is “sqlservr2.ini” because I renamed it so that I could start the instance (renaming it is faster for testing again as opposed to deleting and recreating later).

Also, for SQL Server 2012, 2014, and 2016 I am using SQL Server Express LocalDB. LocalDB executes as a background user process (i.e. no service account), which means it is running in the security context of the Windows Login that started the instance. The process also runs in the C:\Program Files\Microsoft SQL Server\vvv\LocalDB\Binn folder (where “vvv” is the integer version number ; “110” = SQL Server 2012, “120” = SQL Server 2014, and so on), so I didn’t need to specify the full path:

EXEC xp_cmdshell N'type sqlservr2.ini';

Obsolete or New(ish)?

I initially thought that this file was an obsolete construct given:

  1. It’s an .ini file.
  2. There’s no error handling for it (sometimes indicative of code written in the early stages of a software project).
  3. There is mention of it in the SQL Server 7.0 install files, at least according to this page at checkfilename.com (search for the text “sqlservr.ini” on that page).

But, there is also evidence of more recent usage:

  1. This file doesn’t cause problems for SQL Server 2005 – 2008 R2, but starts causing the problem in SQL Server 2012.
  2. It is mentioned in a new(ish) system error message, number 49934: New sys.messages Entries in SQL Server 2017 (search for the text “sqlservr.ini” on that page).

6 thoughts on “Cruel Joke: Prevent SQL Server From Starting, and With (Almost) No Indication Why”

    1. Hey there. Thanks! It wasn’t a random guess; it was an educated guess. I was looking for undocumented options, and knowing that string literals are stored as-is in programs (.EXE, .DLL, etc), I was looking through the list of strings in some SQL Server modules via Process Explorer (part of the Sys Internals collection of tools). At least I think it was there that I saw “sqlservr.ini” by itself and thought I would see what it did.

      As for reporting this, I had thought about it but didn’t because I have reported enough bugs and enhancement requests (almost 60 combined!) and most go unanswered. I get the impression that Microsoft folks don’t like using the incredibly crappy UserVoice system (which is, sadly,
      probably worse than the slloooowww Connect system it replaced) any more than we do. It feels like a mostly /dev/null wasteland of one-way communication from us to Microsoft, with an occasional update, just enough to keep us believing that there is someone on the other end (but most likely it’s just a bot running from Azure that picks a random item to do something with). Either way, I did just report it: sqlservr.ini in same folder as sqlservr.exe prevents startup but no error message or even log file.

      Like

    1. Hi Pam. Thanks for the response. Honestly, this particular “issue” is not terribly important. I was just mentioning it because I stumbled upon it. But, given that this is something that will likely never be encountered by anyone (ok, I admit that the chances went up slightly from 0% since I did post about it, not denying that), I will feel pretty insulted if even 60 minutes is devoted to fixing this non-issue impacting no one, and no time is devoted to the 5 minute fix (seriously, one non-functional line of code) for this SQLCLR / LocalDB issue that has been out there for almost 4 years (the line of code to add is even in the ticket, and I am 99% sure that I can even tell you where it should go (not source code file as I don’t have any insight into that, but the assembly, class, and method), and even help test it, AND no time devoted to the 15-30 minute fix that will reduce the complexity of deploying SQLCLR assemblies to almost nothing and be an opening for SSDT to even possibly handle it, by allowing Asymmetric Keys to be created from a VARBINARY literal (just like Certificates), and was requested almost 2 years ago, is no new functionality outside of getting the bytes from an input parameter instead of a loaded assembly or file (but they are the same bytes so no new parsing), and impacts a lot more people than Microsoft is either aware of or wants to accept.

      Sorry if that was a bit much to read. But that’s the other part of why I didn’t initially report this: I didn’t want it to be a distraction from working on things that have much greater impact to us even if it doesn’t show up on number of votes for these issues, or people complaining directly to you, or any other way that Microsoft is probably using to measure relative importance (including CEIP data).

      Anyway, thanks and take care, Solomon…

      Like

Thoughts? Questions? Comments? Suggestions? Words of praise?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s