I think I understand the general concept of how these files work, but I want to make sure, and ask a couple questions about them.
Am I correct in thinking MDF files are "attached" to the SQL instance only as long as the application is running and are "detached" when the application closes?
My question, if that is true, is: is it possible for an application to "attach" an MDF file when it starts, and as long as the application is running and the database is attached, allow remote connections to the same SQL server instance access to that attached database? This is assuming of course that the SQL server instance already has the property set to allow remote connections and that the user(s) connecting to that SQL server remotely have valid SQL server credentials.
The reason why I'm asking is I am trying to create an application that stores data on a server in some form and allows network users to access that data. A basic 3 tier data application. My concern comes from worries that if I allow end users "direct" access to the SQL server, the data is less secure (it holds medical information and thus needs to be secure). I tried creating my own socket server that would return datasets to clients so that the clients couldn't access the SQL server directly, but the complexity of extracting, updating, deleting and maintaining such a venture is beyond me...If I never deal with Async objects again I will die happily. I could probably do it, but after pondering it last night, I figured I am just re-inventing the wheel and am probably worried about nothing.
Do you think there is any major concern with attaching a database file and allowing remote applications to connect to and extract data directly from a SQL server instance? Each remote application would have a middle layer between the window's forms and the SQL server in a class file that would check permissions and get the necessary data. But that class file (dll) would reside on each remote machine and is open to tampering.
Am I overly paranoid about this? Obviously security is no laughing matter, but similar products to what I am working on store the data in dbf files (dBase) and don't even bother encrypting the data! User's access the dbf files through a network share!
Any suggestions/thoughts?