0

I'm working with ancient software using MS Access components on a Windows Server 2016 (configured as domain controller) Remote Desktop Terminal server (which I cannot circumvent or replace with local installations due to licensing hardware) that requires users to connect to a local SQL Server 2017 Express database.

Normal workflow is this:

  1. User logs in to remote desktop with their own user/domain account
  2. User starts software and uses it until end of workday, then closes software and remote desktop.

Situation now:

Being logged in as Administrator, I have access to the database and I can start the software and 'log in' on the software (using their software's dialog boxes) with the accounts specified in the database (table 'users'). But since we can't have everyone log in to the machine as Administrator (obviously) this is not a workable solution.

Back in the old days...

Back in 2009 when I did the installation of the old Windows 2008 R2 server in 2009 I remember installing SQL Server Express 2008 (?) and being able to run it as a sa user. This caused all connections to be run under this user as well, giving access to the database without problems by each and every (domain) user.

My problem now:

I can't seem to change the access to the database from user-based to a specific fixed user OR the administrator account for that matter. The access to the database is ALWAYS using the credentials of that of the USER.

I only see 2 ways out of this:

  1. I set up some kind of sa-user replacement and run the database as that specific user account.
  2. I give the domain group Personel access to the database in one way or the other.

My question for now:

Which option is best and/or where should I start looking for a solution?

Henry van Megen
  • 2,159
  • 2
  • 23
  • 35
  • Sounds like you should be using AD Groups as the `LOGIN` and `USER`. – Thom A Dec 27 '19 at 10:15
  • Also, why use 2008 Express? 2008 is completely unsupported. – Thom A Dec 27 '19 at 10:16
  • Also, having everyone RDP to a server sounds like an XY Problem. Shouldn't they be connecting from their local host? Why force them to RDP? – Thom A Dec 27 '19 at 10:21
  • I'm using MSSQL Express 2017.. and it has to be over RDP because they have no servers at their office and the software is meant to be run on a server, not a desktop. – Henry van Megen Dec 27 '19 at 11:06
  • Servers aren't meant to be workstations. You might connect to a server to do some processes to ensure services are working, but it's not where you want to spend your time working; which is clearly the case *"User starts software and uses it until end of workday, then closes software and remote desktop."* The user should be working at their PC, not on a remote server all day every day. – Thom A Dec 27 '19 at 11:36
  • @Larnu I did not come with these requirements; it's the software manufacturers requirements. It only works on the server as there is just 1 license USB dongle for the 5 desktop users that are allowed to use the software. Also, people working on a remote terminal server is a perfectly valid way of working for thin client situations. Why would there be millions of users worldwide that use these systems in this way? Microsoft doesn't make Remote Desktop User licenses for nothing. So yes, servers are meant to be workstations in some situations. – Henry van Megen Dec 27 '19 at 20:07
  • Remote Terminals and RDP are not the same thing. Remote Terminals and VPNs are very common. – Thom A Dec 27 '19 at 20:23
  • This changes nothing to the situation I cannot change. – Henry van Megen Dec 27 '19 at 23:13
  • But my original comment should. AD groups still seems to be what you want/need. – Thom A Dec 27 '19 at 23:49
  • @Larnu would you be willing to write this up as an answer? – Henry van Megen Dec 28 '19 at 20:32

1 Answers1

0

In order to give every Remote Desktop User access to SQL Server via a database admin account, right click on the Server name and select properties. In the Select a page section of the Server properties dialog click Connections. Check the box next to "Allow remote connections to this server". Click OK to close the server properties dialog.

Zasper
  • 1