Solved Can You Restrict Important Info In Azure If Customer Owns Azure Subscription?

[dalski]

Thanks Doc. I've barely scratched the surface of absolute novice of SSMS/ Access & learnt what a SA was about 3 minutes ago so we can ascertain everyone on the forum is more capable than myself.

The only experience I had working with SSMS prior to this was using the app mentioned elsewhere where the publisher deployed SSMS locally to client's. I never had much problems with their account. I mistakenly deleted some foreign keys (when I didn't know what a foreign key was in them days). They provided scripts for these typical prob's. That was the only problem I encountered when using. I would imagine 'memory flush's' would be a script & possibly a password protected account with SA type privileges, so that is effectively the SA account protected with a password. Security Patches - what there would affect the main functioning of an Access db implementing SSMS? Are you saying the db needs to be rewritten on every update patch? I was worried about this kind of thing but surely essential methods... are not going to be changing on patches affecting the running of the app? I know .NET is a pig for this.

Leave the sys admin account like it is. Don't EVER interfere with it's operation. EVER. That is a black hole of information loss waiting to collapse. HOWEVER, you certainly CAN protect the admin account.

Thanks noted, I won't do that then for sure. My plan was any probs they call me & only I know the password of an elevated role & login remotely was my thoughts.

Again massively inexperienced, don't pretend to be a know it all, I'm just looking for pointers by others, which I'm most grateful of. Barely scratching the course it seems you can apply certain security/ privileges through users/ schemas/ many different class types/ whatever the term is but the point is they can be applied covariantly. As my user privileges/ structure is so simplistic I this will not be awkward to implement later & will probably apply covariantly via schema's (famous last words ) in restricting certain permissions/ roles (exc'd the SA account here; that's the problem; I would've thought renaming the SA account & adding a password gives me the option to access in the future but only by myself; it's effectively a SA account but mitigates the risk of users reverse engineering).

No doubt I'll cause some laughs with my ignorance - which I look forward to learn.

 

[RonPaii]

Surely that system will eventually require attention by a SysAdmin, such as for example when MS publishes the next round of patches. Happens every other Tuesday. Also, MS has done a lot to reduce memory leaks. But once memory gets all full of crap from those few remaining leaks, someone with admin rights has to go in to initiate a flush.

There is this minor issue that your SA is also a ROLE, not just a singular account. In fact, if you go by U.S. government standards, the actual "Administrator" account is never used except in very specific circumstances. Instead, the assigned admin has TWO accounts - one more or less a strong user ("power user"?) and the other is a true admin account. The admins normally do their "thing" via the power account and will only log in as a full admin when necessary.

Principles of stability and preferred practices as suggested by various security organizations suggest that the sys admin account MUST NEVER be compromised. The idea of blocking it pretty much guarantees that at some point you will be painted into a corner from which you can ONLY escape via the sys admin account.

My qualifications for speaking authoritatively on this are 28 1/2 years as a sys admin for U.S. Navy servers and about 8 years as a sys admin for a local company in the oil & gas industry plus another 3 years as the assistant admin for another oil & gas company. I hold an HP Certified System Administrator certificate for OpenVMS and a CompTIA Security+ certificate, the latter renewed four times for updated standards. I used to teach system security to other Navy admins. My advice is simple: Leave the sys admin account like it is. Don't EVER interfere with it's operation. EVER. That is a black hole of information loss waiting to collapse. HOWEVER, you certainly CAN protect the admin account.

Suggested government standards for a Windows sys admin account password are (a) not less than 15-character passwords (b) using at least 1 each of upper case, lower case, digit, and punctuation characters - but our site used at least 2 of each class (c) and changed once per month.

I have seen in a number of places that the SA account should be disabled on SQLServer.

 

[dalski]

Thanks Ron, yes I've seen quite a few articles stating this & linked previously. Granted my input has little value. If you created a 'role'/ whatever that effectively has SA permissions but password protected the different username with effective SA privileges you would remove a general SA hack I would've thought.

 

[The_Doc_Man]

Security Patches - what there would affect the main functioning of an Access db implementing SSMS? Are you saying the db needs to be rewritten on every update patch?

No, it is that an SA might have specific actions to perform in order to apply patches, particularly if the system option has been engaged to hold patches to a convenient time. The DB might or might not be affected because MS patches whatever they feel NEEDs to be patched based on recent hacker attack patterns. Patches are like a giant kaleidoscope (or, per Forrest Gump, a box of chocolates). You never know what you will see. Would the DB need to be written? Unlikely. Would parameters on the DB-related utilities need to be adjusted after a patch? It has been known to happen. See also your own thread on SSMS22 vs SSMS21, where somehow a speed difference between versions is involved and the odds are that the difference was related to an MS patch to SSMS with an unknown side-effect.

 

[DaveMD]

Based on this thread, the OP seems to have two objectives:

1. Help ensure customer has sufficient licenses to use the OP's application.
2. Protect certain parts of the application logic considered to be Intellectual Property.

There seems to be confusion in many areas in differentiating what might relate to these objectives versus other things when it comes to using SQL Server for the backend. Below are some areas that may help clarify.

Area	Notes to Help Achieve / Explain
Addressing Objective 1	Develop a written licensing agreement between you and the customer. This should specify the Terms of Service, such as how many concurrent users the license covers, how many machines the front-end application can be used on, etc. If the client does not abide by the terms, the agreement would provide a basis to seek legal action.
	Develop some licensing enforcement into the front-end application. For example, you could have logic in your compiled accde VBA that would limit how many active users are supported. This could be checked at application start-up. Avoid going overboard with this as you don't want it to be so restrictive and finicky that it causes issues for your customer to use the application as intended. For example, Intuit several years ago tried to add all sorts of licensing protections into TurboTax that ended up being so restrictive that they lost some of their good paying customer base -- too many headaches to use your software as a legitimate customer can translate to your application no longer being worth it to the customer.
Addressing Objective 2	Compile your front-end into an accde file and distribute that to the client. This will protect your code to a reasonable extent. It won't protect someone malicious from trying to reverse engineer but this concern is often overstated provided you have entered into an agreement with a paying customer. Typically those who have paid for your application are concerned more about having sound software and support for their business purpose rather than trying to hack your product.
	For Stored Procedures in SQL Server that you consider to have Intellectual Property, encrypt them as already suggested by others in other posts above.
SQL Server notes that are not reasonable and/or will not help with Objective 1 and 2.	Do not attempt to restrict your own customer from being able to administer the backend SQL Server database / environment. This is not only not feasible but it is unreasonable. There seems to be confusion in this thread in this area. The client will have their licensing with Microsoft for SQL Server (assuming they are not using just the free Express version). The database for your application is quite likely one of many SQL Server databases your customer utilizes/manages. Your customer would and should have the ability to administer their SQL Server environment -- not only to apply patches, but also to implement sound database backup and recovery processes, etc. All of these things require system administrator level access.
	There is a difference between SQL Server database roles/permissions and instance level logins and permissions (like sa). For example, for the database for your application, you typically will define certain functional roles that will be used by the application (PurchasingInitiator, PurchasingSupervisor, AccountsPayableInitiator, AccountsPayableSupervisor, etc.). But at the instance level, your client is going to have at least one database administrator (DBA) with sysadmin permissions. Technically, sysadmin allows that individual to manage all databases in that instance and have access to manage all data, manage permissions, and technically they could also change table definitions, delete objects, make and restore backups, etc (basically do anything). However, the DBA is responsible for managing things well in an organization and it would be "on them" so to speak if they maliciously or carelessly modified things in the backend database for your application. Remember, your customer needs their systems and databases to work properly for their business purposes and be safeguarded in case something goes wrong (if a flood destroys the server, it would be on the customer/DBA to ensure they have adequate off-site backups to be able to recover).
General terminology confusion	Throughout this thread, the OP seems to refer to SQL Server as SSMS. As others have noted, these are two very different things. SSMS is simply one of several tools that can be used to manage a SQL Server instance/database. SSMS can be installed on a machine without SQL Server being installed on that same machine. Referring to SSMS as if it is SQL Server would be a little like referring to your website as Google Chrome (a tool to access your website). To help avoid confusion in communication, you can just refer to it as SQL Server or MSSQL. If you are trying to refer to SQL Server's flavor of the SQL language, you can refer to that as TSQL (which stands for Transact-SQL). To differentiate Azure based SQL Server from on-premise SQL Server, you can just indicate Azure SQL Server versus On-prem SQL Server.