SQL Server mess up and now nothing works! Unable to configure, REMOVE APP, reinstall app, etc. EVERYTHING!

hiddenhandx 96 Reputation points
2021-10-22T11:20:08.663+00:00

To perform some testing, I ran the following:

USE mydb;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'user connections', 1;  
GO  
RECONFIGURE;  
GO  

Then I restarted the computer.

Then I am unable to connect to the SQL Express Server.

Then I installed SQL Developer Server. Changing port numbers is a small price to pay for being able to continue working.

While I can now use the server, I am unable to do any development because when my application tries to connect, it gets "Error reading prelogin response: Connection reset".

So, I try to open SQL Server 2019 Configuration Manager, and it gives the error prompt "Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]"

And when I try to uninstall, repair, or whatever, I get "Registry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_ Slp: Value cannot be null."

How to resolve? If it's too screwed, then how to manually remove everything so that I can install a fresh copy?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,482 questions
0 comments No comments
{count} votes

Accepted answer
  1. hiddenhandx 96 Reputation points
    2021-10-25T04:05:13.383+00:00

    backup mdf and ldf. then just delete EVERYTHING - all SQL Server dirs, and remove \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

    MS errors are so ridiculously non-helpful.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-10-22T11:45:37.437+00:00

    You can start SQL Server in "single user mode" and reset the value.

    See:
    https://www.mssqltips.com/sqlservertip/6531/sql-server-single-user-mode-connection-with-sql-server-management-studio/

    After you connect run
    GO
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE ;
    GO
    EXEC sp_configure 'user connections', 0;
    GO
    RECONFIGURE;
    GO


  2. Erland Sommarskog 100.1K Reputation points MVP
    2021-10-23T09:53:30.93+00:00

    I think there is a less brutal way to fix this, to wit, the admin connection. The admin is a dedicated connection, and I don't think it counts against "user connections", so it should still work.

    On the command line run:

    SQLCMD -A -S .\SQLEXPRESS
    

    and then run the commands in Tom's post. The -A option, specifies that you want to use the Admin connection.

    You can also connect from SSMS by saying: ADMIN:.\SQLEXPRESS. You may get an error in this case, because SSMS tries to create an extra connection for Intellisense, but you will get a query window nevertheless.

    Note: I'm here assuming that your instance is called SQLEXPRESS, since you said it was Express Edition. You must run the above from the same machine that SQL Server is running on.


  3. Seeya Xi-MSFT 16,426 Reputation points
    2021-10-25T05:47:53.41+00:00

    Hi @hiddenhandx ,

    Error will only report direct errors, such as:

    Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

    SQL Server Configuration Manager use Window Management Instrumentation (WMI) to view and change some server settings. When connecting to servers, SQL Server Configuration Manager uses WMI to obtain the status of the SQL Server (MSSQLSERVER) and SQL Server Agent services. This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. (Quote from here.)
    It may not help you identify the root cause, but you can't say it's not useful. At least for single error, it is useful.

    If there are too many errors I also recommend that you uninstall sql server cleanly and reinstall it.
    Further, please use an administrator account to reinstall SQL Server on your computer(Right click->run as administrator).
    Lastly, good luck!

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.