Dsn-less secure my tables?

[brickelldb]

I made the entire db and interface all in one acess 2003 db. I then upsized the db to sql server 2005. Now i have a front end in access 2003 and the tables are linked to sql server 2005 tables (i believe this is DSN connection via odbc driver, correct? and please correct my terminology if wrong)

When i converted to .mde, i was succesful in stopping importing of my forms, macros, reports......but my tables and queries are still open to be "stolen" by importing/linking the linked tables (linked to my sql server).

I have been reading on dsn-less connections for 2 days now; however, I have not put into practice. After reading, I'm ASSuming this method would achieve my major objective which is to stop someone with basic access knowledge from stealing the information in the tables by simply opening a blank database and importing the tables.

**Would connecting my FE to sql tables via dsn-less connection help me achieve my goal?

Note: I have also succesfully stopped importing/linking of my tables by setting up mdw security to the front end. Any thoughts?

 

[brickelldb]

Did I satisfy my objective with my last NOTE ?

 

[Banana]

Well, I don't know what authenication method you are using to connect to SS-

With trusted connections (e.g. Windows domain), it seems a bit futile to me because anyone can just create a new blank database and connect to it whenever. But they can't take it home with it and connect it from there.

With SS authenication, you can just omit the password from DSN or use DSN-less connection. In either cases, you'd be supplying password 'just in time'.

Strictly speaking, there is no difference in security between a DSN or DSN-less, but it would be more accurate to say that DSN-less connection simplify the security because we're not depending external objects (e.g. registry where DSN values, including password, are stored plaintext).

 

[brickelldb]

When initially upsizing, i used sql auth only. And I simply logged in as SA when it prompted....However, I did NOT save the password.

I honestly planned on just logging in manually as sa when I distributed this front end onto each user machine........I know it is VERY impractical......but that was my thought process......that way it would connect......the user would not know the password.....and everything would be ok.....i would just need to know how to stop them from linking/importing the data from the sql linked tables.......

Through this method....Is the password stored somewhere on the machine that a knowledgable access user could find it, get into the sql database and steal information?

 

[Banana]

The only safe way to provide password is to provide it just in time.

Save it anywhere, and it will be found. You could encrypt it, but that only mean you need to hide the encryption key.

In my last project, I set up a custom DIY login for Access database asking my users for their username and password, taking them in and hashing the password into something else and used that as the password for the DSN-less connection string. Thus I got it just in time and without my users knowing the actual password to the backend.

 

[gemma-the-husky]

if a user can SEE your database window, they can just maketables to copy all the data

do you mean keep the data from all users, or just general users - its isnt so much the data thats the problem is it, or i don't think it should be - its the users data anyway - in virtually any system you can get at the data, and copy it if you know how - i mean you can connect and copy data from any system that provides a ODBC connection.

code is different - the way you manipulate the data clearly should be properly secured.

If I were buying a system I wouldnt be happy about paying for anything where I couldnt get at my own data directly.


[edited[
And a lot of the time when you do link to back ends, its amazing how appalling the data design actually is. Its a major issue trying to work out what they actually do with some of the stuff

 

[brickelldb]

I don't wany anybody to be able to get at the data without having to supply sql credentials....

In other words unless it's myself or the owner specifically authorizing that an individual need access to the sql db to run crystal reports or use excel, etc. I dont want anyone with general access knowledge to be able to link/import any information.

From what I've come across, been explained......if i use dsn (linking to the sql tables) and i use MDW security......an access user can't create a blank database and import my tables without having design rights - he'll be able to see them but won't be able to actually go through with linking them........and if i supply the sa password myself manually when i initially distribute the access front end.....then they will have no way of knowing the sql credentials.....and if i disable shift and disable everything and produce an .mde......then they will have no access of viewing the tables period.....

Am i making sense or have it all wrong?

As I mentioned before...with all things additionally being ignored....I dont want a general access user to be able to import/link my tables whatsoever by having access installed on their computer and having access to the .mde front end as described above.....

 

[Banana]

There's no need to involve .MDW in this. Sure, they can help with protecting your Access objects, but when it's compiled into a .MDE, all code are gone and users would have to pay for it to decompile (AFAIK, there is only one company that offers this service and they will ask you to prove ownership).

But more importantly: It can be totally unsecured, but if the password is omitted and is not saved anywhere in the .mdb then it's useless until someone who knows password can supply it.

For an extra measure of security, you can add a MSysConf table to the SQL Server to "instruct" Access to not save the password but to me that's only the half of equation. By simply asking for password at the time of opening Access, it can be much more secured and no amount of importing or copying Access objects will get around that.

As for you being the only one to authorize access- how do you intend to accomplish this? It would require you to go to each user to authorize them in, which is impractical or save the password, which defeats the whole point (and even if it was hidden or encrypted, it just postpones the crack but doesn't prevent it).

That's why I decided to ask my users for password and use a hash of the password as the password to provide access. Combine this with a MDE then my algorthim is compiled and thus inaccessible to any attempt to figure out what was the salt I used for hashing.

Last thing, why sa??? Why not just create a new user? You do understand that by giving out sa credential, I can just use a passthrough and do froo-froo with the SQL Server? Just. Don't. Give. sa. credentials.

 

[brickelldb]

I used sa just for development...i would def create a new user at time of distribution.

You mentioned once you create .mde you cant do anything....however that only secures your forms, macros, reports, pages and modules...you are still able to import the tables and queries.....

 

[Banana]

If you read carefully, I said to NOT save password so even if they imported the table, they still have *NO* access because there's no password anywhere. The MDE aids by compiling my hashing and salting algothirm so they wouldn't know what I did to their password and what was actually passed to the backend.

So, the user opens Access MDE file, get a login screen, enter a username and password. Let's say password is "froo-froo".

The password is then hashed into this:

"cf7de580a6f7e6c7e1ca57fc5e96d089c8d68fa0377cfe5ea3a04f0e96ac12f4"

(It should be salted prior to hashing, but this will suffice for illustrate.

This is the actual password that I then pass to the backend for log in. The user doesn't even know that "froo-froo" he entered in the log in screen wouldn't work if he went to Data Sources (ODBC) manager and created his own connection DSN- he'd be rejected because it wasn't the "froo-froo" that was used but the hash of it.

Did it make sense?

 

[namliam]

Not to hijack the thread... but I am having trouble creating a DNS-less connection AND beeing able to add/change/remove data from/in/to the table(s).

Anyone have any clue as to that?
I have ensured that my user can edit data and the tables have properly setup primary keys.
Using a DNS connection I can add/remove/change data, but using a DNS-less connection fails everytime.

DNS connection: (works)
ODBC;DSN=DSNReportDB;Description=ReportsDB;UID=MyUser;APP=Microsoft Office XP;DATABASE=myDatabase;TABLE=dbo.TheTable

DNS-less connection: (fails)
ODBC;DRIVER=SQL Server;SERVER=Adam01;APP=Microsoft Office XP;DATABASE=myDatabase;TABLE=dbo.TheTable

I can view the data, but not change it

 

[Banana]

Out of curiosity, if you go to registry or the file where DSN is contained, what do you see stored in there?

 

[brickelldb]

I see. So what you are saying is that if the sql pw is not saved then if they are NOT logged in and they created a blank db to import from the .mde file they have on their client machines....they would not have the access to import the tables.....

 

[Banana]

Right. And even if they did log in, and imported the linked table, it still wouldn't work because the password still isn't there. They could import the actual data, but that would certainly pop up on the server's log...

 

[brickelldb]

Now i see what you are saying. On a side note, Let me ask you.....if you use dsn-less connection for access to the tables on the sql server. Do the tables still show up as linked in the linked table manager? Or are all tables connected via the coding?

 

[Banana]

It'd show up and act like any other linked table. DNS or lack thereof is just a method of connection, not method of interacting... (not best description, but hopefully get the idea...)

 

[brickelldb]

You were def. major help in piecing it all together. Do you have the code or a sample of your method for the logins posted on the forum?

 

[Banana]

Google for Doug J Steele's DNS-less connection. For hash, google "VBA SHA-256", which is what I used. There's a .bas file out there from a guy named David Ireland (?). The rest is just making sure you have a form for log in and a button or something to accept the password and run it through hash (don't forget to salt it else it's vulnerable to rainbow tables!) then pass it into Doug's code.

Good luck!

PS Make sure the option dbAttachSavePWD is not enabled. Also, you may want to make your switchboard form or whatever is the "main form" that's open all time to have code that forces the application to quit when it's closed to close a security hole where the connection is saved in cache and you can close the database (but not Access environment itself) and open another database and still have the same connection.

 

[brickelldb]

Good deal. Hey thanks again for everything!