Can You Restrict Important Info In Azure If Customer Owns Azure Subscription? (2 Viewers)

[dalski]

Been working on the premise that if selling an .accde with an Azure db managed through SSMS 22, you can restrict important info/ stored procedures/ tables/ queries... from the customer. I imagine through creating different users/ schema's... creating an admin user account & encrypting data/ passwords... to hide the clever stuff & creating a users account to read/ write only? I'll worry about this at the end I think as I have enough to learn but just dawned on me I better check; if not I'd be better off just using SSMS 22 locally, but Azure would be greatly preferred to allow users to work from home. I don't want to host Azure myself because the main selling point of my application is to avoid heavy subscription costs & they own the application. BUT if it is not possible given the poor performance I'm getting with Azure through SSMS 22 I'll drop Azure & use a local instance of SSMS, but I'm pretty sure it's possible?

 

[RonPaii]

Been working on the premise that if selling an .accde with an Azure db managed through SSMS 22, you can restrict important info/ stored procedures/ tables/ queries... from the customer. I imagine through creating different users/ schema's... creating an admin user account & encrypting data/ passwords... to hide the clever stuff & creating a users account to read/ write only? I'll worry about this at the end I think as I have enough to learn but just dawned on me I better check; if not I'd be better off just using SSMS 22 locally, but Azure would be greatly preferred to allow users to work from home. I don't want to host Azure myself because the main selling point of my application is to avoid heavy subscription costs & they own the application. BUT if it is not possible given the poor performance I'm getting with Azure through SSMS 22 I'll drop Azure & use a local instance of SSMS, but I'm pretty sure it's possible?

I'm just guessing here but as I understand it Azure db is basically web hosted SQL Server. If that's the case you should be able to manage user access at the database level instead of server allowing you to secure your code. Unfortunately Access don't support application security.

 

[sonic8]

I imagine through creating different users/ schema's... creating an admin user account & encrypting data/ passwords... to hide the clever stuff & creating a users account to read/ write only? I'll worry about this at the end I think as I have enough to learn but just dawned on me I better check;

It is not a good idea to put off these things to "the end".
If you plan to work with different user accounts, encryption, and anything else you might want to add, this is something that should be incorporated early. Some of your code will have to be adjusted for that and you should test it intensively.

if not I'd be better off just using SSMS 22 locally, but Azure would be greatly preferred to allow users to work from home.

You are using the term SSMS, which stands for SQL Server Management Studio, very (too) loosely in your post here. You should distinguish between SQL Server, which is the essential database server, and SSMS, which is just a replaceable administration tool, when describing your process.
In don't think its critical in this post, but also the term Azure is very imprecise. It could mean Azure SQL Database, Azure SQL Managed Instance, or an Azure VM with a regular SQL Server installed.

Even if we ignore the above imprecisions, you are mixing entirely different scenarios in that sentence.
SSMS is a tool you primarily use during development. If you also use it to manage the DB in production, the usage profile will be very different and your current performance issues with the DB Diagrams will be of little concern in that scenario.
User working from home are only a concern during production use and are completely irrelevant for the development infrastructure.

I don't want to host Azure myself

If the customer is the "owner" of the hosting platform, it will become difficult to protect things in the database from them (their top level administrators).

 

[dalski]

manage user access at the database level instead of server allowing you to secure your code. Unfortunately Access don't support application security.

Thanks Ron, ah db level in Azure seems the way to go.

It is not a good idea to put off these things to "the end".
If you plan to work with different user accounts, encryption, and anything else you might want to add, this is something that should be incorporated early.

Thanks Sonic, I was hoping that would not be the case as I'm just a learner & would've been able to just set permissions covariantly to objects/ schemas... Concentrating on just getting it working atm is what I really hope is possible. I understand it will affect my code... & I'd need to alter these later but I would hope printing an ERD & adding these after the app is built. I appreciate experts like yourself is obvious better practice to do this first at design-stage but given my limited capabilities I really want to avoid this atm as I have enough on my plate learning basics atm.

You are using the term SSMS, which stands for SQL Server Management Studio, very (too) loosely in your post here. You should distinguish between SQL Server, which is the essential database server, and SSMS, which is just a replaceable administration tool, when describing your process.

Thanks, was not aware.

could mean Azure SQL Database, Azure SQL Managed Instance, or an Azure VM with a regular SQL Server installed.

Thanks, Azure SQL db, or an Azure SQL Managed Instance I think; I think it's a db but not positive. Not a VM.

SSMS is a tool you primarily use during development. If you also use it to manage the DB in production, the usage profile will be very different and your current performance issues with the DB Diagrams will be of little concern in that scenario.

Thanks, oh I see so Access will just be accessing Azure, that's good. The performance with SSMS 22 is really slowing me down atm. I'm slow enough with all the learning googling every topic as I go; compound that with slow performance I'm like a tortoise.

User working from home are only a concern during production use and are completely irrelevant for the development infrastructure.

Thanks, production use is my concern (if I ever get there), but correct me if I'm wrong but if deploying just via SSMS (no Azure) then the users will not be able to access remotely the same 'managed instance' (if correct term). So users will need to be on the same LAN; not allowing remote use (if using Azure [which I plan to]). That way users can work from home remotely.

If the customer is the "owner" of the hosting platform, it will become difficult to protect things in the database from them (their top level administrators).

This is massively concerning, I don't think Obfuscators are supported in SSMS/ Azure?). I never thought it would be uncrackable, I'm just trying to really make it hard for users/ competitors to ascertain the logic & mathematic formulae & business logic is my main concern. Or maybe if I setup Azure for them, they never get the login details but the difficulty is how they pay for Azure through me; maybe a separate paypal invoice or something like that would be possible? That would eradicate the problem completely I think. Mind you I'm diluting my main selling point.
I was under the impression that much could be hidden in the BE. I understand it is not normal to let the customer manage Azure & the reason why firms do not do this. I was hoping for more protection than just handing over an .accde. I was hoping to create a generic 'admin' user that has more privileges that a typical user (this would be the manager of the company). Which gives elevated privileges to certain items but most of the stuff being hidden to them. But another user in there (me) with big permissions (top level admin); but protect that with a password/ encryption methods (which I have little knowledge on atm & trying to avoid pitfalls like the linked making oneself a sysadmin exploit.

 

[sonic8]

if deploying just via SSMS (no Azure) then the users will not be able to access remotely the same 'managed instance' (if correct term).

'Managed Instance' is a term for a SQL Server instance hosted on Azure. On a local server it would just be an Instance. - There are more difference between a local SQL Server Instance and a Managed Instance on Azure, but for now let's not dive that deep.

You are basically correct. A SQL database on Azure will be available to everyone with credentials and an internet connection.
A local SQL Server Instance will not be by default. It can be made available by either the server being exposed to the internet or being reachable via a VPN.

I don't think Obfuscators are supported in SSMS/ Azure?

I think the best you can get is what the WITH ENCRYPTION option for stored procedures offers.

 

[dalski]

Thanks sonic8, that's very helpful. Found some courses on Azure which I'm looking into now to try to get a grasp:

• AZ900 MSN - Beginner Azure
• AZ104 - Azure Administrator

 

[dalski]

Ok, in order to be able to sell the application outright I think I may be better to deploy with SSMS as the back-end installed locally/ LAN. I should be able to avoid reduce privileges by installing/ deploying via a script & create a restricted principal/ type user with restricted privileges in them not being able to see a lot of the important things through choosing different type classes/ principals. Sadly I loose the ability of remote login but retain the main selling point of no subscriptions. Any great objections?

 

[sonic8]

with SSMS as the back-end

You're still mixing up the terms SSMS and SQL Server. SQL Server is the database server and SSMS is its administration tool. - I know what you mean, but other people might be confused if you don't get the terminology straight.

You want to install in the customer's LAN, don't you?
For a computer in their own LAN the customer will have an unrestricted administrator account, won't they?
How do you plan to restrict their unrestricted admin account? - I wouldn't know any sensible solution.

A permissions system for your application is absolutely sensible. But by trying to restrict privileged admin you are wasting time and effort.

 

[dalski]

Thanks sonic8, noted & apologies for my denseness. I thought it may be possible for me to create the user/ number of users & assign their names bespoke to whom buys the product but hide the 'unrestricted administrator account, deploy the application with default settings to hide the privileged accounts/ master engine (wrong terms I know sorry). I know I have not scratched the surface in my learning yet with users/ privileges... nor programming in general. I suppose my priorities are to not allow the customers to add users (as I wish to charge per user) & restrict the main business logic & mathematic formulae.