We are SQL Server Experts. Call Us TODAY! +17738095456 or email us at sales@itimpact.com
  • The Team
  • Our Services
    • Dashboards Developers
    • Microsoft SQL Server
    • Microsoft SQL Server hosting for Access
    • SQL Server Integration Services (SSIS)
    • SQL Server Reporting Services (SSRS)
    • SQL Server Data Quality Services (DQS)
    • Microsoft Access
    • Microsoft VB.Net
    • Cross Functional / Business Unit Integration
    • Financial Statement Consolidation
    • Website Design
  • Case Studies
    • Automatic Reporting for Security Services Firm
    • Amazon API Integration with Access
    • Custom Quoting and Proposal Solution
    • Data Quality Analysis for Government Program
    • New Claim System Reduces Processing Time
    • Pipe Tracking Software
    • Product Search Analysis
    • Safety Training, Incident and Accident Tracking Tool
    • Union Dues and Membership Management System
  • Blog
  • Contact
  • The Team
  • Our Services
    • Dashboards Developers
    • Microsoft SQL Server
    • Microsoft SQL Server hosting for Access
    • SQL Server Integration Services (SSIS)
    • SQL Server Reporting Services (SSRS)
    • SQL Server Data Quality Services (DQS)
    • Microsoft Access
    • Microsoft VB.Net
    • Cross Functional / Business Unit Integration
    • Financial Statement Consolidation
    • Website Design
  • Case Studies
    • Automatic Reporting for Security Services Firm
    • Amazon API Integration with Access
    • Custom Quoting and Proposal Solution
    • Data Quality Analysis for Government Program
    • New Claim System Reduces Processing Time
    • Pipe Tracking Software
    • Product Search Analysis
    • Safety Training, Incident and Accident Tracking Tool
    • Union Dues and Membership Management System
  • Blog
  • Contact
Toggle panel Social icons
SQL Server

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.

No Comments on “DSN-less Connections and AD Security”

  1. grovelli April 12, 2011 at 3:43 pm

    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! 🙂

    Reply
    1. Juan Soto Post authorApril 12, 2011 at 6:54 pm

      Fixed! Thanks for pointing it out.

      Reply
  2. grovelli April 12, 2011 at 3:49 pm

    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?

    Reply
    1. Juan Soto Post authorApril 12, 2011 at 6:55 pm

      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.

      Reply
  3. grovelli April 12, 2011 at 6:41 pm

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

    Reply
    1. Juan Soto Post authorApril 12, 2011 at 6:57 pm

      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.

      Reply

Click here to cancel reply.

Leave a Reply Cancel reply

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

XHTML: You can use these tags <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Recent Posts

  • 23 Jan. 2019 7 essential SQL skills that can help you command a higher paycheck

  • 18 Jan. 2018 Quick Guide on Importing Data from SQL Server (or SQL Azure) to Cosmos DB

  • 10 Jan. 2018 Top 7 Questions about Basic Availability Groups

  • 04 Jan. 2018 An introduction to mssql-cli, a command-line client for SQL Server

  • Share and work together on the intranet with SharePoint Team Sites

ITImpact

About IT Impact, Inc.

IT Impact, Inc.

IT Impact, Inc. is a minority-owned software company specializing in custom software solutions. We work with SQL Server, Microsoft Access, web applications, and much more.

Our staff is top of the line and includes two Microsoft MVPs!

Twitter

Tweets by @ITImpactInc

Facebook

© ITImpact. Webdesign by GreatCustomWebsites.com