Please help me to better understand (high level) how to use DSN-less connections and DSN-less linked tables
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.
Thank you!
Riverwalk
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.
Thank you!
Riverwalk
ASKER
I am so sorry for the delayed response. I normally respond much faster. Thank you for your help with this.
I always do secure the front-end, by locking down everything I can: compile the code, hide things, lock down the shift key, etc., but from what I have read and been told, a person can get around all of that if they want to bad enough, by connecting and importing the objects from within another database, or using a hex data reader, that kind of thing.
But here is the question from above that I am wanting answered/addressed... I understand that if I use SQL Server auth, and I use the code that I found online, that in order to connect to the server I have to enter the SQL Server password, but if I use Windows auth, then as long as I can login to Windows, I'm good. Therefore, how could Windows auth be just as secure as the SQL Server auth? What is to stop someone from taking my database off of one computer, copying it onto another computer where as long as they can login to that computer, at their home or wherever, then they will have access to the server. But again, if it uses SQL Server auth, they will need to know that password. It will not be good enough that they are logged into their PC, the will need to know the SQL Server password. What am I missing here in my understanding? It seems like the SQL Server auth is FAR more secure.
Thanks again for your help!
Riverwalk
I always do secure the front-end, by locking down everything I can: compile the code, hide things, lock down the shift key, etc., but from what I have read and been told, a person can get around all of that if they want to bad enough, by connecting and importing the objects from within another database, or using a hex data reader, that kind of thing.
But here is the question from above that I am wanting answered/addressed... I understand that if I use SQL Server auth, and I use the code that I found online, that in order to connect to the server I have to enter the SQL Server password, but if I use Windows auth, then as long as I can login to Windows, I'm good. Therefore, how could Windows auth be just as secure as the SQL Server auth? What is to stop someone from taking my database off of one computer, copying it onto another computer where as long as they can login to that computer, at their home or wherever, then they will have access to the server. But again, if it uses SQL Server auth, they will need to know that password. It will not be good enough that they are logged into their PC, the will need to know the SQL Server password. What am I missing here in my understanding? It seems like the SQL Server auth is FAR more secure.
Thanks again for your help!
Riverwalk
but from what I have read and been told, a person can get around all of that if they want to bad enoughThat's true of any environment, however. Given enough time, a determined person will circumvent all of your security processes. That said, Access is probably less secure than, say, a .NET or Java compiled application. Simply put, if you're overly concerned with that, then you probably should not be using Access for your development platform.
What is to stop someone from taking my database off of one computer, copying it onto another computer where as long as they can login to that computer, at their home or wherever, then they will have access to the server.Nothing would stop them, including the method you use for SQL authentication. You mention this is a client application, so I'm assuming you're deploying this to your user - so you would have no control over any of this, at least to any great degree. If the user wants to take your Access FE and SQL Server mdf files, they'd be able to do so, and there's not much you could do about it.
ASKER
Thanks again Scott. Can you address my actual question and point that I have made, in which I am looking for either refutation and explanation or a confirmation that I am correct? I am looking for a direct answer to my question about Windows auth verses MS SQL auth, as it pertains to someone being able to steal an Access front-end file off of a PC, take it home for example, put it on their computer and open it, (because it merely uses Windows auth). But... If the database uses SQL Server auth, then they would have to guess the password, correct? And if so, wouldn't this be more secure than merely Windows auth?
Thank you,
Riverwalk
Thank you,
Riverwalk
In most cases - no, that could not happen, either with Windows authentication or SQL Server Authentication.
That said:
IF the SQL Server is setup to accept remote connections, and IF the network where the SQL Server lives allows the connection through to that server, and IF you've got the connection string setup to use the IP address of the server, then it's certainly possible your application that uses SQL Server Authentication could connect from a remote location. It would be highly improbable, however, assuming the person in charge of your network has a modicum of sense :)
However, it could only happen with Windows Authentication if the user is running a VPN that connects their home machine to the corporate network, and if they log into that domain - in other words, it's not just any old Windows login, it has to be a login to the domain that Windows recognizes and associates with the defined SQL Server. So if I take the file home, and log into my home machine, and then try to connect to my work network, I'd have to log into THAT network in order to be able to connect to the database. Merely logging into my home machine would NOT allow me to open the SQL Server on the company network. That is also a highly unlikely scenario (and one that is inherently more secure, at least in my opinion).
That said:
IF the SQL Server is setup to accept remote connections, and IF the network where the SQL Server lives allows the connection through to that server, and IF you've got the connection string setup to use the IP address of the server, then it's certainly possible your application that uses SQL Server Authentication could connect from a remote location. It would be highly improbable, however, assuming the person in charge of your network has a modicum of sense :)
However, it could only happen with Windows Authentication if the user is running a VPN that connects their home machine to the corporate network, and if they log into that domain - in other words, it's not just any old Windows login, it has to be a login to the domain that Windows recognizes and associates with the defined SQL Server. So if I take the file home, and log into my home machine, and then try to connect to my work network, I'd have to log into THAT network in order to be able to connect to the database. Merely logging into my home machine would NOT allow me to open the SQL Server on the company network. That is also a highly unlikely scenario (and one that is inherently more secure, at least in my opinion).
To answer this:
With Windows Authentication, they'd have to do the same - guess the username and password - but in general those requirements are much, much more strict.
But... If the database uses SQL Server auth, then they would have to guess the password, correct? And if so, wouldn't this be more secure than merely Windows auth?In order for your users to connect to the database, they'll have to know the username and password, or you'll have to store those credentials and enter it for them. If they know the password, then all this is for naught, since they can use SSMS to open the database and take what they wish. If they don't, then if they're even mildly determined they could spend an hour or two on the internet and figure out very quickly how to create linked tables from your Access FE.
With Windows Authentication, they'd have to do the same - guess the username and password - but in general those requirements are much, much more strict.
Finally:
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.This would be true regardless of the connection method you use. Once you've created those linked tables and opened the database, if the user can get to those tables, they can view all the data.
ASKER
I think I am getting closer to the fully disclosed question and answer that this thread is about. I think I need to add a critical peice of information that did not occurr to me to add previously. Let me know if this extra tidbit changes your answer verses Windows auth and SQL Server...
My SQL Server database is being hosted online by an Internet Hosting company. No VPN of any kind is needed. All you need to know to connect are the following: Server name (will have ".com" after it), Database Name, Username and Password. The only other thing you need is an Internet connection. That's why I am wondering if SQL Server auth is the way to go, because in the scenario I've described here, as long as you have the SQL Driver installed on your PC, you could copy the front-end to your desktop and login to the database, IF Windows auth was used. Correct? BUT, if SQL Server auth is used, you would be stopped in your tracks, because you would need to know the username and password. < -- Does this change your answer?
I realize that regular users would need to know the username and password. But in light of what I stated above, wouldn't that be better than having a situation where anybody (a complete stranger) could take the database off the PC, put it on their own PC and be able to get in?
Thank you!
Riverwalk
My SQL Server database is being hosted online by an Internet Hosting company. No VPN of any kind is needed. All you need to know to connect are the following: Server name (will have ".com" after it), Database Name, Username and Password. The only other thing you need is an Internet connection. That's why I am wondering if SQL Server auth is the way to go, because in the scenario I've described here, as long as you have the SQL Driver installed on your PC, you could copy the front-end to your desktop and login to the database, IF Windows auth was used. Correct? BUT, if SQL Server auth is used, you would be stopped in your tracks, because you would need to know the username and password. < -- Does this change your answer?
I realize that regular users would need to know the username and password. But in light of what I stated above, wouldn't that be better than having a situation where anybody (a complete stranger) could take the database off the PC, put it on their own PC and be able to get in?
Thank you!
Riverwalk
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I appreciate all your help with this. I'm gong to chew on it a while and perhaps I'll either see my way through it or more likely come up with better questions based on what I hope becomes a better understanding, once I digest what you have tried to teach me here. Thank you so much!
Riverwalk
Riverwalk
That said - moving your data over to SQL Server will give you better DATA security, but it won't give you a more secure application, at least in terms of usage and licensing. Sounds to me like you need to take some steps to secure the FE a bit more, like hiding the Nave Pane, disabling Shift Key Bypass, etc etc. Those would make your FE a bit more secure in terms of usage and such.
These might help:
http://technet.microsoft.com/en-us/library/cc751805(v=office.12).aspx
http://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm
As to your questions:
1. Unless you're installing the SQL Server, you must be sure that you can use SQL Authentication. This feature is often turned off by DBAs. I'm not sure I'd use SQL Authentication in an application, if I were given the choice. Windows Authentication is simpler to use, and is no less secure than SQL Authentication, at least in my opinion.
2. No matter how you link the tables, once they're linked the user could do just what you describe - double click on the linked table to show the data - so I'm not sure exactly what you're referring to here. If you don't want the user to double click the tables, then don't show them the Nav Pane.
Finally - it sounds as if you're concerned as much about licensing and usage as other things. Be aware that Access can be tough to manage in those scenarios. It was never really designed to be create commercially distributed applications, so doing so can be tough. In my opinion, if you're goal is to create an application you want to sell/distribute "in the wild" then your best bet is to get busy learning a more compatible language, like .NET.