I am want to implement a more secure MS Access 2010 to MS SQL Server 2012 Express application than what I have been providing for my customers and therefore have been experimenting with and reading about DSN-less connections and DSN-less linked tables. I have code that I found online for each of these functions and have them working in isolated test. I have not actually implemented this yet in a real application because I don’t fully understand it.
Below are my questions/confusions.
1. I’m assuming that for the DSN-less connection to offer any real protection that I need to use SQL Server authentication and not Windows authentication. Is that correct? Because if I use Windows auth, then what is to stop someone from copying the front-end file to a thumb drive, placing it on any Windows PC where they are logged in, and then opening the database? But if I use SQL Server auth, then they will have to enter a username and password which exists on the SQL Server, correct? Is this the way you would do it, i.e. using SQL Server authentication? So that whether the front-end is on the PC it was meant to be on, or some other PC, in each case, the user is prompted for credentials before the code will successfully make the connection to the SQL Server database.
2. Concerning the DSN-less linked tables. Code example is here on this MS web site, http://support2.microsoft.com/kb/892490
. I am confused about how to use this in a way that helps make things more secure. I called this function and passed to it the applicable strings and sure enough it created a linked table for me. And when I hover over the linked table, up pops the hint text and it shows all of the connection information, even the user name, the only thing is does not show is the password and I just have to trust that it did not save the password, as does a DSN if you check that box. BUT – beyond this, the troubling thing to me is that I can open this database now and click on the DSN-less linked table and open it. It is using a Trusted Connection. So, how do I use the DSL-less linked table along with the DSN-less connection in the proper way to have a best practice semi-secure application?
I realize these questions might make some experts squirm because perhaps they are stupid questions, but I can’t seem to sift my way through understanding it myself. Therefore, I greatly appreciate your help.