Trouble opening ports in SQL Server 2019 Express - WMI Error [0x80071779]

0

Update:

Going back one version to SQL Server 2017 Express allowed me to save TCP/IP ports and IP addresses entered in SQL Server Configuration Manager.

None of these variables were saved in the registry:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server**INST**\MSSQLServer\SuperSocketNetLib\Tcp}\

In SQL Server Configuration Manager this error message only appeared when an IP section had it's "Active" state set to "no":
The specificed file is read only. [0x80071779].

For me this applies to both 2017 and 2019.


Old description:

I Have installed a local SQL Server 2019 Express (SSE 2019), that I can connect to locally with:
<computer_name>/<sql_server_name>.

I have issues trying to setup static ports to enable a remote connection.

When configuring ports in SQL Server Configuration Manager (SSCM) -> TCP/IP, upon saving I get a WMI error:
The specificed file is read only. [0x80071779].

Searching for the issue lead me to this page that shows the same error message that I get.

This page describes a workaround by changing the ports and IP settings in the registry instead. However all the sub-keys to TCP: IP1, IP2, IP3 etc. are missing for me. Could someone running a SQL server 2019 express confirm that this structure is still in use for this version of SSE?

It's strange that the WMI error points to a read only issue when I am able to change the TcpPort value in the key called Tcp with SSCM (where it's called IPALL) and by restarting the server the value in registry is updated correctly.
`

So far my attempts to connect remote have failed

I have:

  • Opened the standard SQL server port both in my router and my firewall
  • Tried to turn off the firewall completely
  • Added the keys missing and their contents in the registry based on this, but did not seem to work (SSCM didn't even read from them)
  • Restarted the SQL server and it's services, SQL Server Configuration Manager application and the computer several times while trying the above
  • Made sure that remote connection is on in SQL server properties (was on by default)

Some remarks about Connecting locally

Update:

When using only one instance of Sql server (and with working port management) you only need to enter the servers IP-address to initiate a connection.


Old:

While this went just fine:
<computer_name>/<sql_server_name>

I was unable to connect to the SQL server locally using 127.0.0.1 or 127.0.0.1, <port>.
What's the correct syntax for connecting to the localhost?

Some suggestions were to use a comma to separate IP and Port:
<x.x.x.x>/<SQL_server_name>, <PortNumber>

Would it work just as well using a colon?
<x.x.x.x>:<portNumber>/<SQL_server_name>

networking
sql-server
asked on Super User Jun 2, 2020 by user 88 91 • edited Jun 12, 2020 by Community

2 Answers

0

Uninstalled Sql server completely and installed a one version older sql server. Setting it up the same way and the issue was solved. Updated the question as well.

answered on Super User Jun 3, 2020 by user 88 91
0

In my case (SQL Server 2019) none of the above was effective. The problem was related with Hyper-V. I suspect that the network bridge it creates conflicts with the SQL Server network settings. Uninstalling Hyper-V restores normal functioning. It could be possible that just deactivating the Hyper-V network bridge also work.

answered on Super User Dec 4, 2020 by Fran

User contributions licensed under CC BY-SA 3.0