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.
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.
`
I have:
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.
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>
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.
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.
User contributions licensed under CC BY-SA 3.0