Solved Entra ID Or Azure To Manage Logged In Users/ Licensing

[dalski]

I'm new to App Development & the titled is quite terrifying as a noob. Reading the helpful thread with valuable contributions from @The_Doc_Man, @isladogs & @Ken Sheridan. I am trying to make it as difficult as possible to use my Access FE at a reasonable price. We all know Access is not the most secure...

I've read all MSN articles on Principals, Roles, Users, Securing SQL Server... several times It seems the best solution is to independently validate the user with a compiled FE (.accde). Not to use SSMS locally as it can easily be bypassed.

Most of my customers (probably won't even get any) will be single-users; running SSMS locally using SQL Server 25. Some will be multiple-users & there will be cases where SQL Server will be installed on the network for multiple-users... but I would like to isolate SSMS from the login using an .accde FE to verify the user intermittently in the FE of the application.

Ideally I'd like to use Windows Authentication through Access to validate the user outside of SSMS (installed locally 90% of the time). Some customers will be in a domain environment, others will be local... Entra ID seems more expensive than Azure.

Azure with an independent table like in the linked helpful thread may be a solution as it seems you can restrict customer IP addresses... & block brute-force attacks... but it seems with Azure you would just be using the username as a string from Environ(); opposed to a check tied to the actual Windows User Account. One concern is most people in UK are on a dynamic IP address. So I wonder if problems could happen if the IP address changes or if the IP address is within a range that the ISP provides to it's customers? I am on a dynamic IP address & experimented with Azure for a month & never encountered this problem but it seems a certain possibility?

It seems Entra ID is made for this thread using the actual Windows User Account itself (not a string) to validate the user amongst other things. I'm put off by it's cost & unknown future costs. Also possible future customers will be from companies who will already be managing their Entra ID account so I wonder if an independent small developer like me could add an already known account ID to mine? I would think so. But if it is far more secure then I will do it; seems £15/ month for 300 users (wishful thinking).

Sorry if it's a silly question, bit overwhelmed with the topic. Ultimately should I use Entra ID or Azure as an online solution to manage logins/ users from all different companies... for my Access FE?

 

[GPGeorge]

Two things leap out at me.

"I am trying to make it as difficult as possible to use my Access FE at a reasonable price."

I think that is a typo or misstatment, is it not? Do you REALLY want to make using your application difficult?

"...running SSMS locally using SQL Server 25."

No, your users don't need SSMS.

SSMS is a MANAGEMENT tool, not intended for end users. You, the developer or DBA, use it to set up databases, create tables, views and stored procedures, define logins users and security.

No customer should ever need SSMS, nor should you want them to be able to get to your SQL Server database via SSMS. Whether they have it installed independently of your application is a separate question. If so, your security issues are increased by virtue of their being able to connect to the SQL Azure database outside your Access application.

You will have to install SQL Server, or have it installed for you, in the customer environment, of course, but that's different from also providing users with a management tool to mess around with your SQL Server and the databases on it.

As for the rest, the security issues around using Access with SQL Azure are just too complicated for me to comment.

 

[CJ_London]

Have you put together a security document to identify what you want to protect? from who? By doing what? That may be dictated by existing customer requirements or your own requirements. If so, that should identify where you need to focus your efforts. It may well be access is not the way to go.

 

[dalski]

Thanks George.

"I am trying to make it as difficult as possible to use my Access FE at a reasonable price."
I think that is a typo or misstatment, is it not? Do you REALLY want to make using your application difficult?

Difficult to bypass license manager.

"...running SSMS locally using SQL Server 25."
No, your users don't need SSMS...

I'm pretty sure SSMS is required locally to access tbl's, SP's, Vw's... My competitor uses this method & I had to set it up with SQL Server Express (at three different companies). Occasionally having to go in there after creating an orphaned record... a backup/ restore... Though my competitor likely uses C# for the main application, they absolutely used to install locally for single-user clients. I can say this because I was a single-user at three different companies & a multi-user at a fourth company (not installed locally). Bigger firms it's installed on the network obvs. As I said most of mine will be single-users. They've probably moved to Azure now to better protect Intellectual Property. We scratched the surface here some months ago now.

As for the rest, the security issues around using Access with SQL Azure are just too complicated for me to comment.

My question was - the linked helpful thread has a typical users table... setup. I could use Azure as a separate db for the users merely to license the product. With the additional benefits mentioned from accessing an Azure db. @Albert D. Kallal got the gist of it in the scratched surface thread. I'm pretty confident that will work using SQL Server Authentication providing a username & password, but wondered if it was possible to authenticate users with Windows Authentication. To clairfy this authentication will be outside of the SSMS & from MS Access merely accessing the table with encrypted info. Very much like Colin's & Ken's fine e.g's.

It may well be access is not the way to go.

We all know Access is not the most secure...

It's the best option for me atm with limited knowledge & benefits of RAD. Protection of Intellectual Property is a different topic really so I won't digress. A different competitor uses MS Access FE with a flat-file BE. Having used several different applications of this type at various different companies these are the only two worth talking about. But we're getting off-topic here. For now it is only about licensing users & mitigating users using the application without paying.

 

[tvanstiphout]

Would this work: the FE queries the name of the network, and calls your website, where you have a list of approved network names.
You could also query for other fairly unique data points, e.g. the SQL Server connection string.

 

[GPGeorge]

SSMS is required ONLY for YOU, the developer, who creates and manages the databases, and for any DBAs maintaining the SQL Server database on behalf of the user.

SSMS is NOT required by users who will license your Access application for their use.

In fact, you should hope that your end users do not install and use SSMS to bypass your Access Front End and get directly to your tables outside the Access application. That is, unless you plan to have your users create their own tables in the SQL Azure database, or create their own views and stored procedures directly in the SQL Azure database, outside your Access application.

Even that is not 100% true. There are alternative tools which anyone can install to manage a SQL Server database.

I suppose you could take the position that your users will be authorized to create their own views and stored procedures in the SQL Server database. And that would require a management tool like SSMS or dBeaver. But somehow, I do not believe that is your intent here.

As I said before, I'm not experienced enough with the intricacies' of SQL Server security and authentication to participate in that discussion. I'll leave that to the experts.

 

[dalski]

Thanks Tom, using network names instead could be a good idea. I see benefit in applying 'helpful thread's logic of an external users table hosted on Azure & accessed by the FE but concerned with customers having a dynamic IP address & Azure's firewall, blocking existing customers because their Dynamic IP address has changed.

you should hope that your end users do not install and use SSMS to bypass your Access Front End and get directly to your tables outside the Access application.

I do not disagree, but I believe the reason is as in your own words below. If I can avoid installing SSMS on deploying the App I certainly will.

and for any DBAs maintaining the SQL Server database on behalf of the user.

Single-users will be acting as their own DBA's won't they. They will need an integrated environment to carry out this maintenance as supported by Doc #20 in thread 'scratched the surface' & @DaveMD & in your own words above. The first company I speak of has high market-capilization. They certainly would have minimised their exposure as much as possible, yet still had to install SSMS at client end. Again - not arguing with your point & agree. I got a bit mocked in another thread about securing SSMS Security; but my concerns were for good reason which you have identified.

I suppose you could take the position that your users will be authorized to create their own views and stored procedures in the SQL Server database. And that would require a management tool like SSMS or dBeaver. But somehow, I do not believe that is your intent here.

Correct that is not a requirement.

 

[Minty]

You definitely do not need to install SSMS for an end user to gain access to the SQL Server back end.
If the application is self contained, once it's installed there should never be any need for anyone to do any modifications to the backend.
Backups/ etc. Yes, but that is a separate topic.

That's where you provide support as the seller.
If it's single user / single machine application you may have been better leaving it with a Access backend, and avoid the complications of a SQL serve back end?

 

[CJ_London]

Single-users will be acting as their own DBA's won't they.

Why? I have had clients with a single user who as an organisation had an on prem sql server with dba’s who managed the server - maintenance, backup, security, etc. it was the organisations requirement that all databases resided in the server and they managed who had access to what data and when

 

[GPGeorge]

Spoiler:

Thanks Tom, using network names instead could be a good idea. I see benefit in applying 'helpful thread's logic of an external users table hosted on Azure & accessed by the FE but concerned with customers having a dynamic IP address & Azure's firewall, blocking existing customers because their Dynamic IP address has changed.


I do not disagree, but I believe the reason is as in your own words below. If I can avoid installing SSMS on deploying the App I certainly will.

Single-users will be acting as their own DBA's won't they.

Spoiler:

They will need an integrated environment to carry out this maintenance as supported by Doc #20 in thread 'scratched the surface' & @DaveMD & in your own words above. The first company I speak of has high market-capilization. They certainly would have minimised their exposure as much as possible, yet still had to install SSMS at client end. Again - not arguing with your point & agree. I got a bit mocked in another thread about securing SSMS Security; but my concerns were for good reason which you have identified.

Correct that is not a requirement.

No, I do not envision someone who purchases a license to use an Access/SQL Azure database application needing to be a DBA to use it.

That is precisely the point of purchasing a licensed product from you. They do not need, or want, to be a DBA and trouble-shooter. They are paying you for that as part of the license fee.

Alternatively, as CJ points out, in an organization with an IT department, there are DBAs to do that work.

 

[GPGeorge]

You definitely do not need to install SSMS for an end user to gain access to the SQL Server back end.
If the application is self contained, once it's installed there should never be any need for anyone to do any modifications to the backend.
Backups/ etc. Yes, but that is a separate topic.

That's where you provide support as the seller.
If it's single user / single machine application you may have been better leaving it with a Access backend, and avoid the complications of a SQL serve back end?

Much clearer and more precise than my long-winded offerings. Thank you.

 

[dalski]

Thanks for persevering with me, appreciated. So what is the solution in managing users online (restricting unauthorized people) using my Access FE? A Uses table in Azure like helpful thread would have been good but Azure only allows Private IP addresses through so I would run into a problem with most users having a dynamic IP address. Is it Entra ID?

 

[tvanstiphout]

I'm guessing you want to restrict the number of users connected to the database to the number of licenses.
If you follow my post #5, that web service call will return the number of licenses.
Then you query the database at startup time as well as from time to time during the session for the number of users connected to your database, and complain if the number is too high.
Let the users table be handled by the local admin. It is none of your concern. The number of concurrent users is.

 

[dalski]

Thanks Tom, I was a little worried about brute-force attacks & sql injection... I have not built a website yet & was hoping Azure would have these tools built in for 2026 & suprising they don't. You can set a range of IP addresses for dynamic IP's but only our lord knows what them ranges would be. Seems Entra ID has tools built in when using Transparent Data Encryption (TDE) to auto-block persistent attacks, but not a typical username, password login .
Thank you all for your help.

 

[tvanstiphout]

Thanks Tom, I was a little worried about brute-force attacks & sql injection... I have not built a website yet & was hoping Azure would have these tools built in for 2026 & suprising they don't. Seems Entra ID has tools built in to auto-block persistent attacks, but not a typical username, password login .
Thank you all for your help.

It's pretty easy. Search phrase: "how to create a web service endpoint using REST api". I would stay away from SOAP and WCF - too complicated for your needs.