Solved

Please help me to better understand (high level) how to use DSN-less connections and DSN-less linked tables

Posted on 2014-09-26
10
268 Views
Last Modified: 2014-10-01
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
0
Comment
Question by:RiverWalk
  • 6
  • 4
10 Comments
 
LVL 84
ID: 40347445
First, it sounds to me like you're using DSNless connections correctly. The only difference between a DSNless connection, and a non-DSNless connection is ... well ... the DSN :). They're not really intended to be more or less secure than a DSN connection. They're just more convenient, and they make changing data sources easier. In my opinion, they're the way to go with distributed applications.

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.
0
 

Author Comment

by:RiverWalk
ID: 40352627
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
0
 
LVL 84
ID: 40352839
but from what I have read and been told, a person can get around all of that if they want to bad enough
That'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.
0
 

Author Comment

by:RiverWalk
ID: 40353027
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
0
 
LVL 84
ID: 40353370
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).
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
ID: 40353395
To answer this:
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.
0
 
LVL 84
ID: 40353401
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.
0
 

Author Comment

by:RiverWalk
ID: 40353520
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
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40354279
In that case, SQL Server Authentication is probably the ONLY way to go. You really couldn't use Windows Authentication to connect to that database, unless you somehow made that web server part of your network, or were able to add your machine to their domain (which is very, very unlikely), or use several features of Windows (like Credential Manager) which would allow you to "share" your local credentials with the remote machine. As before, all of these are very, very unlikely with commercially hosted SQL Server databases on a web appliance.

I think there's some confusion regarding your understanding of Windows Authentication. WA does NOT mean that ANY Windows user on ANY Windows machine can open ANY SQL Server on ANY network. It means that SQL Server requires you to be an authenticated user on that domain or network in order to work with resources of that network. If you setup the SQL Server to use WA, then only those users who are authorized to connect to that domain could open the database. So as I mentioned earlier, if you were to pickup the Access FE from your work machine, and move it to your home machine, you still could not open the backend database tables from that machine unless you somehow managed to make your home machine a part of that network, or you ran a VPN that connected to your work network, and you logged into that network - in which case you'd need credentials to do so.

And it still doesn't account for the fact that in order to USE your application, the user would need to log into the SQL Server, so they'll need the password, or you'll need to store that in the application. So if I know the password, I could grab a copy of the FE, take it home, and then open it from that location, using the SQL password that I already know. The only thing this really stops would be someone who obtained the FE who does NOT know the SQL password - assuming, of course, that you're requiring the user to enter the password each time, and not storing it in the database somewhere.

And not to be pedantic about the whole thing, but there's nothing to stop a moderately capable user from creating a new Access database, linking to the linked tables in your existing Access database, and grabbing all the data. If you're overly concerned with this data getting into the wrong hands, then you should re-evaluate your choice of programming platforms, and move to something that is inherently more secure, like Java or .NET.
0
 

Author Comment

by:RiverWalk
ID: 40355056
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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 35
Convert int to military time 8 20
subform does update on ms/access 2 16
Code editor Problem 8 13
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now