'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use Access Runtime with SQL Server Login Credentials
Question details
The user wants to distribute an Access database so others can run reports against SQL Server without sharing personal login credentials or preventing simultaneous database access.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Distributing an Access database for reporting purposes to users who might not have a full Microsoft Access license.
- Observed behavior
- Looking for a secure way to manage SQL Server credentials and ensure concurrent database access while utilizing Access Runtime.
Ensure you have administrative access to your SQL Server to create new user credentials, and back up your primary Access database before testing Runtime modes.
Deploy Access Runtime with Separate SQL Server Logins
The most secure and robust method is to issue separate SQL credentials and use Access Runtime for users without a full license.
Access Runtime allows end-users to run an Access application without needing a paid license. Providing separate SQL Server credentials ensures your personal login is kept secure and prevents database locking.
Access your SQL Server Management Studio (SSMS) and create a dedicated SQL Server login for the end-user rather than sharing your personal credentials.
On the target user's machine, open the ODBC Data Source Administrator and configure a new DSN using the newly created SQL Server credentials.
Make a copy of your database file and change its extension to .accdr. Open this file to simulate Runtime mode and check for any unexpected errors or missing ribbon commands.
Provide the Runtime version of the database to the user. Test concurrent access by opening the database through two separate shortcuts simultaneously to ensure neither user is locked out.
Create a Blank Launcher Database for Running Reports
If configuring new DSNs and SQL logins on multiple machines is too complex, you can create a simplified launcher database.
Looking for a Lightweight Office Suite? Try WPS Office
While WPS Office does not include a database management tool like MS Access, it is an excellent, free alternative for Word, Excel, and PowerPoint. If you export your SQL Server database reports to spreadsheets or PDFs, WPS Office can handle them seamlessly with full Microsoft format compatibility.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install the application: Run the installer and follow the on-screen instructions to complete the setup process in minutes.
- 3. Open exported reports: Use WPS Spreadsheet or WPS PDF to open the data reports you generated from your SQL Server and Access database.
Frequently Asked Questions
What is Microsoft Access Runtime?
Access Runtime is a free distributable version of Microsoft Access that allows users to run database applications without needing a paid, full version of Access installed on their computers. It disables design-related features and only allows users to interact with the pre-built application.
How do I test my Access database in Runtime mode?
You can simulate Runtime mode by making a copy of your database file and changing its extension from .accdb (or .mdb) to .accdr. Opening this renamed file will run the database in Runtime mode, helping you identify if any custom ribbons or menus are missing.
Why shouldn't I share my SQL Server login credentials?
Sharing personal SQL Server credentials poses a security risk, makes it impossible to audit which specific user performed an action, and can sometimes cause concurrent access conflicts or database locking when multiple users log in simultaneously with the same ID.
Can multiple users access an Access database simultaneously?
Yes, multiple users can use an Access database concurrently if the database is properly split into a front-end (forms, queries, reports) stored on the user's local machine, and a back-end (tables) stored on a shared network drive or a SQL Server.
