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.

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