Using MS SQL Server 2019 Express in Win Server
Can’t find or access a server on another PC on the LAN.
Firewalls are OFF.
2008 R2 is easy to set up by turning on tcp/ip protocols in configuration manager.
Searched this forum and the Web. I remember seeing an article but can’t find it. A qualified friend in an IT company in Adelaide has the same problem.
Hi Eric
Does this help?
https://www.sqlshack.com/how-to-install-sql-server-express-edition/
One thing I remember from last time I installed sqlserver is that tcp/ip didn’t work until I restarted the service (after enabling tcp/ip).
That link confirmed that TCP/IP is turned off by default. Already addressed that. At the end of your link I found an update link. I am downloading CU11 Cumulative Update 11 for SQL Server 2019. Fingers crossed.
I have restarted the service multiple times. But I’ll give that another try after I install updates.
Perhaps search for cliconfig.exe?
Thanks I’ll give it a go.
Hi Eric,
You also have to edit the DB settings in SSMS to allow remote connections. Hope that helps.
Regards,
Jarrod Hollingworth
Thankyou, I’ll look but I can’t access it from ado in Delphi.
Hello Eric,
I don’t have MS SQL Server 2019 installed. But I do have MS SQL Server 2017. The tool to configure the transport layer in MS SQL Server 2019 for the client and the server can be found in (see also here)
C:\Windows\SysWOW64\SQLServerManager15.msc
My SQLServerManager14.msc looks like this on my virtual machine:
for the client side
and for the server side:
And in the MS documentation it states:
The default instance of the Database Engine listens on port 1433; therefore, you do not have to configure a fixed port. However, named instances including SQL Server Express listen on dynamic ports.
So I am not quite sure if it is possible to configure SQL Server Express to listen on a fixed port, which you would then have to open.
In general it is better to build an application server listening on a special port for REST requests or a DataSnap server to access a remote DB. If you are using Delphi Professional and don’t have access to the REST server classes or DataSnap classes you may find TMS XData helpful.
I hope this will help to get you connected to your remote DB or to do the next step in building a multi-tier application.
Salut,
Mathias
I have used REST and DataSnap. Delphi version 13. haven’t updated to 14.
MS donate Licenced SQLServer full version, but I haven’t bothered to install it. Express has always worked in the past.
I can’t connect from the WinServer to SQL ServerExpress on my desktop or connect from my desktop to SQL ServerExpress on my WinServer.
My Client side and Server side are different
Server side (MS Win Server) SQL setup is same as you attachment but client side (Desktop Win 10) setup is also the same but with has an extra option ODIN\ODINExpress14 client protocols as well as the general client protocols. In your link to google near the end of the page it mentions
Hello Eric,
that might contribute towards your challenge. If I read the MS documentation on MS SQL Server correctly MS SQL Server Express uses a dynamic port to make it harder to achieve what you have in mind. Now I am not a MS SQL Server expert. I know Firebird much better. But from a business point of view it would make sense if Microsoft were to introduce such a limitation. If you have a license try MS SQL Server Standard instead, try that one.
Please be aware that even in times of NBN and fast connections a remote database will not behave like a DB in your LAN. The application and components maybe way too “chatty” to get a decent performance out of your client-server application. This is when a multi-tier application will make sense.
A good starting point on how to move a client-server application to DataSnap can be found here.
Salut,
Mathias
Problem Solved
Following research by Mathias
From: Mathias Burbach
Sent: Sunday, 25 July 2021 6:35 PM
To: Eric March
Subject: Re: MS SQL Server 2019 - connection issues
Hello Eric,
I had another look at the MS SQL Server documentation and found the following sentence here:
To enhance security, the Database Engine of SQL Server Developer, Express, and Evaluation editions cannot be accessed from another computer when initially installed. This lesson shows you how to enable the protocols, configure the ports, and configure the Windows Firewall for connecting from other computers.
That link above should enable us to step through the instructions step by step and setup your Windows Server 2019 together with MS SQL Server Express 2017 so you can in fact connect from another machine in the network. One thing we have not done today is to assign a fixed port to your MS SQL Server Express instance and then to adjust the Firewall settings accordingly.
Please try the “Lesson 2: Connecting from Another Computer” or do it together with your nice.
Salut,
Mathias
2 Likes
@Eric_March I have removed the email addresses in your post - we are trying not share email, phone numbers, addresses etc for privacy reasons - these forums are open to anyone to read - which means spammers, scammers etc will be looking for that sort of info to scrape.
Thank you. Sorry about ineptitude. Mathias asked me to do so but fortunately you were quick off the mark.
1 Like