question

hiddenhandx-8816 avatar image
0 Votes"
hiddenhandx-8816 asked ErlandSommarskog commented

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

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hiddenhandx-8816 avatar image
0 Votes"
hiddenhandx-8816 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered hiddenhandx-8816 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cannot connect via the studio, cannot connect via jdbc.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sqlserver does not work. I have verified the port 1433 is open and only the old server (SQLEXPRESS) is turned on.

when using the command SQLCMD -A -S .\SQLEXPRESS -U sa -P mypassword


the response is

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

0 Votes 0 ·

My excuses! I forgot that by default the Dedicated Admin Connection is disabled by default. To enable it, you need to start SQL Server with a trace flag, but then you could use as well have tried Tom's suggestion with single-user mode.

Normally, you would set this trace flag as a startup option through SQL Server Configuration Manager, but it seems that you unfortunate configuration change made it unsuable.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered hiddenhandx-8816 edited

Hi @hiddenhandx-8816,


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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It'd be really helpful if MS give suggestions on what to do when uninstallation fails instead of just prompting unhelpful things like "blah blah blah must not be null".

A list services to stop, a list of directories, and a list of registry values to delete would be helpful. I have learnt this the hard way, but I am not the first that has encountered problems and I will not be the last.

0 Votes 0 ·