Go to Top

DSN-less Connections and AD Security

Over the weekend I had the great pleasure to participate in SQL Saturday #73 in Orange County California, my two sessions were well attended and I had a lot of interest from SQL Server professionals on how to maximize performance between Access and SQL.

Why I won’t post my presentation
I received numerous requests to post my presentation and quite frankly I just don’t like doing so. Half of my presentation is my animated commentary, which you would not get if you only saw my PowerPoint slides. I put a lot of work into them and I feel you would be missing too much if you just saw the slides. You can however, get a list of all the links I refer in my presentation by clicking here.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

DSN-Less Connections Code
There was a lot of interest in using DSN-less connections during my presentation, they offer improved security over DSN and are more convenient to the user. I found Doug Steele’s article on the topic top notch. One improvement I would suggest is to use a SQL Server table instead of looping through existing Access tables since a user, (or you), may delete a link and leave your program broken. Expect a future post on the subject soon.

To learn more about the security risks of DSNs please look at Ben Clothier’s post at the Microsoft team’s blog.

Using AD security with your SQL Server Database
There is only one security model I recommend to my clients: Active Directory Security. In a nutshell:

  1. Create user groups in Active Directory corresponding to the roles in your database, i.e. dbAdmins, dbUsers, dbReadOnly. If you have multiple databases and you have the need for separate security schema you may want to include a qualifier such as dbShipAdmins for the shipping database.
  2. Assign users to your security groups in the Active Directory
  3. Add the AD security groups as users to your SQL Database. Don’t add individual users.
  4. Create database security roles in your SQL Database that mirror your AD security groups: dbAdmins, dbUsers and dbReadOnly for example.
  5. Add the AD Security group to each database security role.
  6. Assign permissions to your SQL objects using the SQL Security Roles, not the AD security roles.
  7. When a new employee needs access to the database, add them to the corresponding AD security group, not the SQL Server database.

That’s pretty much it, you now have a great security model that allows your clients to easily manage database users by using Windows Active Directory. No need to call you when there is a need to manage users!

Using AD Security in your Access Database
It doesn’t stop there, you also should use AD security to limit what users can do to in your Access database. Here is a great article that describes exactly that.

UPDATE: The link to Doug Steele’s article is now correct.

UPDATE 2: You may want to look at Paul Litwin’s DSN stripper here. I’ve not used it but it does look interesting.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

6 Responses to "DSN-less Connections and AD Security"

  • grovelli
    April 12, 2011 - 6:41 pm Reply

    As far as AD Security is concerned, many pc’s don’t have Active Directory enabled

    • Juan Soto
      April 12, 2011 - 6:57 pm Reply

      Active Directory is used mostly in corporate environments. I’ve seen it used in as little as five PCs on a network. When there is no AD you’ll need to rely on SQL security instead.

  • grovelli
    April 12, 2011 - 3:49 pm Reply

    Also, if you use Paul Litwin’s DNSStripper Utility, are we sure any table relinking code run at the accdb/mdb frontend startup doesn’t bomb when pointing to DSN-less links?

    • Juan Soto
      April 12, 2011 - 6:55 pm Reply

      You run the stripper once, then use relinking when the user changes or you need to refresh the links, so I don’t think it will bomb. I have not use the stripper since I have another means to create dsn-less tables, which I hope to have on my blog soon.

  • grovelli
    April 12, 2011 - 3:43 pm Reply

    Hi Juan, the link to the Doug Steele’s article, actually points to a Paul Litwin’s article, is that one what you meant? In any case it’s equally useful! 🙂

    • Juan Soto
      April 12, 2011 - 6:54 pm Reply

      Fixed! Thanks for pointing it out.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.