Cannot connect to SQL Server Express

Question

I have a Server running Windows Server 2019 Essentials and SQL Server Express 2019. This can be accessed on the machine through SSMS. I have followed the configure the SQL Server remote access ( Enable TCP/IP Port 1433, restarted services, Added rules in Firewall) but the Server is not showing Under network servers via SSMS on a network pc. I have uninstalled and reinstalled SQL Server a number of times and followed the setup but no effect.

What am I likely to be missing ? How can I solve ?

Answer 1

For integrated security to work in a workgroup, you must have the same username and password on both machines. But it may still not work, as some setting in Windows has to be in the appropriate position.

You are better off using SQL authentication. That is, you login with a user and password defined in SQL Server.

You need to configure the instance to permit SQL authentication. Right-client the server node itself and select Properties, and then go the Security page. You need to restart SQL Server for the change to take effect.

You create a login like this:

CREATE LOGIN Ian WITH PASSWORD = 'IWillNotTellYou!'

Presumably, you want to be sysadmin:

ALTER SERVER ROLE sysadmin ADD MEMBER Ian

Answer 2

What do you mean with "not showing"? What happens if you just type in servername\instance and try to connect?

Note that by default SQL Express is installed as a named instance \SQLEXPRESS and it will not listen on port 1433, unless you explicitly configure this port. If it is not listening port 1433, you need to make sure that the browser service is running.

There is no point in uninstalling and reinstalling. This only means that you have to redo network configuration.

Answer 3

Judging from the error messages. XXX is the correct choice, because this error tells us that you can reach the server, but you are not able to authenticate. The other error means that you are not reaching SQL Server at all.

The error message "Cannot generate SSPI context. " indicates that you are trying to access with Windows authentication a.k.a Integrated Security, which is the recommended way. However, this is difficult to get working if you only have a workgroup and do not have a domain. Do you have a domain?

Answer 4

Hi @Ian Taylor ,

Please follow below steps to troubleshot the issue,

1). The default instance (an unnamed instance) is listed as MSSQLSERVER, for the default instance of SQL Server, the server name is the computer name. For a named instance of SQL Server, the server name is the <computer_name>\<instance_name>, such as node3\SQLEXPRESS.
If a named instance, make sure SQL Server browser service is running. Make sure the instance name is spelled correct and there is actually such an instance on your target machine.
2). Make sure SQL Server is configured to allow remote connections.

3). Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports
4). You just have a workgroup not a domain, as Erland mentioned, please local connect to SQL Express to create a SQL server login then remote connect SQL server Express using SQL server Authentication.

5).Enable SQL server authentication and restart SQL server service to make this change effect.

6). Remote connect to SQL Express as below screenshot.

If it is still not work, please try below steps.

1. Test server connectivity with PING from the client machine.
2. Test port connectivity using TELNET to the server and port from the client machine.For example
   TELNET <server-name> 1433
3. Check firewall settings if step 1 or 2 connectivity test fails

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.