Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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.

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.

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help, SQL Server

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores