Solved Encrypted Column Formula In A View Would've Been Ideal

[dalski]

Thanks Minty, was not aware that a function could be encrypted. But the problem lies that I am probably going to be deploying SSMS on the client's computer, where the encryption is next to useless.

@AHeyne I think I understand what you meant - a compiled .accde (native) formula/ procedure... is avoiding the Interpreted Language Assembly. BUT I believe the problem would be I'm doing maths with higher decimal points than Access allows; so problems will arise with 'Let Coercion' & others probs as stated...

 

[CJ_London]

OP really needs to create a security strategy document - protecting what, from whom, from doing what and how they could do it, backed up by a risk assessment. Once that is done, put relevant procedures in place. Some risks will be so low, it does not justify the cost of implementing the protection. Others might be high and require protection, regardless of the cost.

The 'what', in this case is a calculation in a query, not the app as a whole
The 'who' was originally the end user, but seems to have now extended to include competitors and hacking gangs
The 'doing what' is copying? changing? the calculation - relevance of data source?
The 'how' has yet to be defined - potential impact on competitors/hacking gangs - how do they get their hands on it in the first place?
The 'risks' have yet to be defined

 

[CJ_London]

going to be deploying SSMS on the client's computer

why? surely you would be deploying sql server?

Not to say the client couldn't install SSMS themselves

 

[GPGeorge]

I have to agree with CJ. To what end would you want to deploy SSMS on a client's computer? To make it easier for the client to connect to and explore/dink around with/modify the SQL Server database for your application?

I'm sure that, if the client has an IT department, their security team will want to be in the loop as well. SSMS would make it easier for your clients to explore/dink around with/modify other SQL Server databases on their network, in addition to the one you deploy. Again, a client could potentially install SSMS on their own, but why make it easier for tinkerers?

 

[dalski]

Thanks CJ/ GpGeorge, sorry I've used bad terminology here it seems. When I say deploy I mean the client will be using their own SSMS.

CJ you've assessed accurately in #22 & I agree a plan is needed, but I've only been in SSMS tinkering circa 1 month, so this is a bit of a stretch for me atm. It's roughly clear what I need to protect but too ambiguous to be of use to the forum. I tried to research as much as I can & thanks to you guys you've helped me get a quick basic grasp (thank you all).

Today I made the decision to stop researching & get back to work in SSMS, make some mistakes, learn & get going. Then hopefully I'll have more of an idea. Thank you all again. I'll keep you all updated.

 

[Minty]

SSMS wouldn't be deployed anywhere near a normal end user.
I have multiple clients some with multiple databases and none of them have SSMS installed, and wouldn't know what to do with it if they did.
It's a developer / DBA tool.

It's not uncommon for a whole IT team to only have one person that uses SSMS, to access multiple SQL servers and databases.
The client might have there own SQL SERVER but it doesn't by default mean they administer it.
I have a couple of clients who have there own Azure Tenants/Subscriptions. We do all the admin for them, they do have some limited logins via their IT support people but leave it to us to manage it all.

 

[AHeyne]

@AHeyne I think I understand what you meant - a compiled .accde (native) formula/ procedure... is avoiding the Interpreted Language Assembly. BUT I believe the problem would be I'm doing maths with higher decimal points than Access allows; so problems will arise with 'Let Coercion' & others probs as stated...

Unfortunately, I don't quite follow.
The only thing I understand is that you are saying that Access (VBA), is not capable of performing your calculations.
If that is the case, then there is no need to talk about VBA as the host for your calculation function (anymore), right?
And that also rules out the topic of ACCDE.

 

[Isaac]

Thanks CJ/ GpGeorge, sorry I've used bad terminology here it seems. When I say deploy I mean the client will be using their own SSMS.

CJ you've assessed accurately in #22 & I agree a plan is needed, but I've only been in SSMS tinkering circa 1 month, so this is a bit of a stretch for me atm. It's roughly clear what I need to protect but too ambiguous to be of use to the forum. I tried to research as much as I can & thanks to you guys you've helped me get a quick basic grasp (thank you all).

Today I made the decision to stop researching & get back to work in SSMS, make some mistakes, learn & get going. Then hopefully I'll have more of an idea. Thank you all again. I'll keep you all updated.

No, you would NOT install SSMS on a user's computer!

 

[DaveMD]

To the OP, the significant confusion appears to be because you are referring to things incorrectly. To help clarify:

• SSMS is not SQL Server. SSMS is one of several client tools that can be used to administer SQL Server (a relational database management system) and develop SQL Server databases. Referring to SSMS as if it is SQL Server is a little like if you referred to a .NET application as Visual Studio (a tool used to develop the .NET application).
  
  SSMS is not a tool used by end-users of an application. The end-user would be using your front-end application (not SSMS), which would interact with the SQL Server backend database. When referring to SQL Server, you can just refer to it as SQL Server (or MSSQL as an abbreviation).
  
  
• To refer to Microsoft's flavor of the SQL language used by SQL Server, you can call it T-SQL (which stands for Transact-SQL). This can be useful when seeking or searching the internet for help to develop a SQL Server specific query or stored procedure.

 

[dalski]

Thanks all, yes I keep getting told off about referencing SSMS here; I appreciate the persistence in correcting me. I do want to learn correctly so thank you.

SSMS wouldn't be deployed anywhere near a normal end user.

No, you would NOT install SSMS on a user's computer!

Ok, having used various versions of the app I am building from several different companies; as an end-user at large companies, an end-user at medium sized companies & as an end-user small companies - I think a scenario has been overlooked; which I have experienced several times over. SSMS will be installed at the user's machine when the end-user is a single-user with no LAN. No IT dept & no LAN. They do install SSMS locally to their machine. I can say this with confidence because I've done it several times over with several different companies. They will have a typical USER role & DBA role. This will be a large part of my client-base & before you say stick to Access I don't want 2GB limit & the decimal points are not high enough & want the benefit of hierarchical data structure offered in SQL. That is why I am so concerned with security. Hopefully my concerns with security are becoming more justified now.

Unfortunately, I don't quite follow.
The only thing I understand is that you are saying that Access (VBA), is not capable of performing your calculations.
If that is the case, then there is no need to talk about VBA as the host for your calculation function (anymore), right?
And that also rules out the topic of ACCDE.

What I meant is I think I finally understood that you meant a compiled procedure... is safer to be stored natively in Access hidden in the compiled .accde; opposed to being stored in the SQL BE either as an Encrypted Stored Procedure or an Obfuscated Assembly. I now agree with you here (for what little my inexperienced opinion is worth) & I think I got bad advisement (not from this forum or anyone on here) stating it would be more secure in SQL Server.
Sorry I can't see how .accde is not relevant anymore as it will still have sensitive info in there whose security is still very much relevant & I'm keen to learn future weakness'.

To the OP, the significant confusion appears to be because you are referring to things incorrectly. To help clarify...

Thanks, I've struggled here. I think I'm getting it now; 'SQL Server' is quite misleading as it is the name of the language SQL. SQL is the query & 'SQL Server' is a 'db management' system.

 

[AHeyne]

...that you meant a compiled procedure... is safer to be stored natively in Access hidden in the compiled .accde; opposed to being stored in the SQL BE either as an Encrypted Stored Procedure or an Obfuscated Assembly.

No, sorry, I just mentioned that an assembly containing IL code is easier to decompile than an ACCDE file.

That's just the technical point of view.


You've mixed this up with the context in which it is used.

'Normally', the end user does not have administrative access to a SQL Server or its file system. Therefore, the assembly cannot be decompiled by the user.

If the SQL Server can be managed by end users, or if you do not trust the administrators, an assembly may be more vulnerable than an ACCDE file.

 

[dalski]

No, sorry, I just mentioned that an assembly containing IL code is easier to decompile than an ACCDE file...

Thanks, I may have muddied the waters with 'a compiled procedure', but this is what I meant to #30; I finally grasped the point you were making. At least what I was trying to write anyways .

 

[Isaac]

& before you say stick to Access I don't want 2GB limit & the decimal points are not high enough & want the benefit of hierarchical data structure offered in SQL

I for one would certainly never say "stick to Access". In my humble opinion, Access is not a good tool for back end database storage, period, and ought to be used as a front end as much as possible, period. Good job for using sql server for the back end. I would never use Access for the back end if I had sql server as an option - wouldn't even be a question mark