SQL Server Express 2019 Server - SQL Server does not exist or access is denied

Question

Hi.

Wondering if someone can help me before I bang my head through the wall.

We use some accountancy software which uses SQL Express. A recent update of the software gave me the push to install on a new Server 2019 Standard virtual server and use a newer version of SQL Express (2019). The installation went without any issues. There were a few advisories on the install such as enabled the SQL Protocols TCP/IP and Named Piped and put the SQL Server into Mixed Mode for authenitication, all pretty straight forward. At this point the accountancy software is running perfectly on the new server. The issue I have hit is when remote users try and access the SQL Server and the error SQL Server does not exist or access denied pops up. From my troubleshooting I pretty much think I have covered every possibility, these include:

SQL Server 2019 Uninstall / Reinstall
Firewall Off / Firewall On
Enable Protocol TCP/IP IPALL for Port 1433
Remote Telnet to SQL Server on 1433- Nothing happens
Ensure SQL Remote Connections is enabled
Remove all Anti-Virus (we use Sophos Intercept X which I know can be a little security concious)

I was testing access by enabling the SA account, creating a UDL file and attempting access when changing the above. I also confirmed that the original server with SQL Express 2008 was accessible without any issues.

Just to be certain I also created a brand new Server 2019 Standard VM and SQL Express 2019 from scratch without any data on the server and I still could not connect even when running through the same changes as listed above. I also uninstalled SQL Express 2019 and swapped for 2017 just to confirm it was nothing with the install / version.

I cannot for the life of me figure it out. I cannot see any logon attempts in the SQL Logs. All the points I can find to diagnose do not seem to get me anywhere. At the moment the accounts team are connecting via remote desktop when they need access. Its beginning to make us look a little out of our depths.

Is there anyone who could point me in the right direction. Any help would be greatly apprecaited.

Many thanks and have a great Christmas.

Daniel Hargrove

Answer 1

Hi @Daniel Hargrove ,

SQL Server does not exist or access denied

About this issue, Some of the problems from the server end are as below:

Installation of the SQL server is not done in the computer that is specified in the connection string.
SQL Server is not started.
The SQL Server name is different from the name of the computer.
The database is offline.
Renaming of database files.
The database is moved to a new server.

And some of the problems from the client end are as below:

In-existence of the name of the computer on the SQL Server
Ensure to specify the correct occurrence name if you are trying to access a named occurrence of SQL Server.
Using incorrect protocol.
The presence of any cluster in SQL Server may lead the network adapters to get configured incorrectly or might have an incorrect name.
It might just be the side effect of an MDAC mismatch.
Details: https://bobcares.com/blog/sql-server-does-not-exist-or-access-denied/

If the firewall on the server is blocking any incoming connections then check the firewall settings for the server operating system. However, in case if it is enabled then verify the port number the SQL Server is using is open in the firewall. Also, verify the UDP port 1434(if you are using dynamic ports).

please follow this link to see if it helps: https://www.sqlserverlogexplorer.com/database-does-not-exist-access-denied/#:~:text=Sometimes%20%E2%80%9CSQL%20Server%20does%20not%20exist%20or%20access,port%20exception%20is%20also%20added%20to%20the%20firewall.

Verify that the SQL Server service is started and ensure that the database is attached and online. Also, verify that the SQL Server Browser service is started on the application server if using a dynamic port for SQL Server.

-------------

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.

Answer 2

Just an update - By setting port 1433 on the TCP/IP Protcol I am not able to establish a ODBC connection with the SQL Server but if I change this back to dynamic it fails. Our accountancy app continues not to work eitherway. Time to pack it in for Christmas.

Many thanks, Dan

Answer 3

Hi Erland.

Many thanks for getting back to me.

I mentioned 1433 as I ready this could be entered instead of using the Dynamic Ports in order to connect. The strange thing I do not understand is that I have created a brand new test virtual server with nothing on it apart form SQL Express 2019 and I cannot connect even when trying to create an ODBC connection from my local PC. On the test there is no 3rd party applicaitons which use SQL its just Windows Server 2019 and SQL Express. The Firewall is off, no AV is installed. I created this as a test to determine where the application server I built is going wrong but the test gave me exactly the same results. The only way I have got around this is to install SQL Express 2019 on a physical WIndows 10 PC which after installation I was able to create an ODBC connection too. I have been clicking around for hours now but I just cannot get it to work. It might be one for me to walk away from and pickup again after Christmas.

Many thanks, Dan

Answer 4

The error message means that you were not able to reach the remote SQL Server instance. There are a number of reasons why this could happen. You seem to have covered some of them.

The one thing that stands out is that you talk about port 1433 and that is the port you can expect a default instance of SQL Server to use. However, SQL Express installs by default as a named instance, and then you get a dynamic port by default, and that port is not 1433.

I could go into more details, but I think it is better that you share more details on more exactly how you have configured networking for the instance, and how the connection strings from the remote machines look like.