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.
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:
- 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.
- Assign users to your security groups in the Active Directory
- Add the AD security groups as users to your SQL Database. Don’t add individual users.
- Create database security roles in your SQL Database that mirror your AD security groups: dbAdmins, dbUsers and dbReadOnly for example.
- Add the AD Security group to each database security role.
- Assign permissions to your SQL objects using the SQL Security Roles, not the AD security roles.
- 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.
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! 🙂
Fixed! Thanks for pointing it out.
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?
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.
As far as AD Security is concerned, many pc’s don’t have Active Directory enabled
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.