SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

SQL Quantum Leap

Solomon Rutzky has been working with computers since the mid 1980s. He has experience with a variety of hardware platforms, OSs, programming languages, and RDBMSs. He has focused on SQL Server since 2002, and has written articles for SQL Server Central, including the Stairway to SQLCLR series, and Simple Talk. Solomon is the founder / owner of Sql Quantum Lift, and is the creator of the popular SQL# SQLCLR library. He answers questions on a few StackExchange sites and Ask.SqlServerCentral.com.

Comments

Leave a comment on the original post [sqlquantumleap.com, opens in a new window]

Loading comments...