Cached ODBC Connections with SSO with Ron McCarry
Secure Your SQL Server Data Without Compromising the User Experience (an Access User Group talk with Ron McCarry)

Database security shouldn't require sacrificing user convenience—yet many Access developers struggle to find this balance when connecting to SQL Server backends.
In this Access Pacific presentation, Ron McCarry demonstrates an elegant approach to implementing secure database access using cached ODBC connections combined with single sign-on authentication. His solution allows applications to seamlessly authenticate users without prompting for credentials while still maintaining strict security controls over who can access and modify SQL Server data through various entry points.
For Access developers looking to protect their SQL Server data while maintaining a smooth user experience, this presentation offers practical techniques that can be implemented in both new and existing applications.
Background and Motivation
Security Objectives
- Restrict database access to only the intended application
- Prevent unauthorized access even with knowledge of passwords
- Eliminate need for users to manually log in
- Allow application-level user account management
Technical Approach
- Uses cached ODBC connections for persistence
- Implements database-level user accounts
- Encrypts credentials in both front-end and back-end
- Leverages SQL Server's partial containment feature
Implementation Details
SQL Server Configuration
- Set database to partial containment mode
- Create dedicated schema for user management
- Implement database roles for access control
- Use stored procedures for all user operations
Access Application Components
- Class modules handle ODBC and ADO DB connections
- Encryption routines protect stored credentials
- Automatic user detection on startup
- Table linking uses cached connection
Security Features
- Administrator account has limited stored procedure access
- User credentials encrypted in SQL Server
- Application manages all user maintenance
- Read-only access available for reporting needs
Key Technical Considerations
Database Setup
- Partial containment enables database-level accounts
- User management objects isolated in dedicated schema
- Role-based security model
- Limited administrator permissions
Connection Management
- Initial connection caches credentials
- Instance-level connection persistence
- Application close required for security
- Automated credential management
Limitations and Gotchas
Technical Constraints
- Case sensitivity issues with encrypted usernames
- SQL Server connection string character limitations
- Instance-level cached connections
- Application roles not supported
Implementation Notes
- Requires compiled front-end for security
- Password characters must be validated
- Test logins after user creation
- Consider encryption key management
Conclusion
Ron's solution offers a practical approach to implementing secure SQL Server access through Microsoft Access applications. While not completely unbreakable, it provides a robust security model that prevents casual data access outside the application while maintaining a seamless user experience. The implementation is particularly valuable for organizations needing to protect data integrity without imposing additional login requirements on users.
Recording
The full recording is available on YouTube:
Join Live!
Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.
Attending live gives you the opportunity to:
- Interact directly with presenters during Q&A sessions
- Network with other Access developers
- Share your own experiences and challenges
- Get immediate answers to your specific questions
- Participate in group discussions
With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.
Acknowledgements
- Base cover image generated by FLUX-schnell
- Initial draft generated by Claude-3.5-Sonnet