Self-hosted customers can take advantage of AtlasBI by allowing firewall/access to AtlasBI IP addresses and providing a read-only SQL account that can query your database.
Microsoft Access Reports are created by each institution as needed. This requires setting up a safe ODBC link to your Ares database and creating the reports in MS Access. These reports can be extremely versatile. As long as the required data exists, you can create any report, using any custom parameters. A working knowledge of Microsoft Access is required to set up Access Reports.
While Microsoft Access reporting is still available to create custom reports in Ares, you may want to use SSRS reports instead. SQL Server Reporting Services (SSRS) is a server-based report generating software system that is used to produce and deliver a variety of interactive and printed reports. SSRS allows you to create your reports and run them locally, or access and run them from the web.
There are three steps involved in configuring Ares to work with Microsoft Access:
Setting up the SQL Server User Account
This step is for self-hosted sites only. Sites with servers hosted by OCLC or Atlas Systems will need to contact their server provider to have an account created for them.
These instructions were written using Windows Server 2008, SQL Server 2012 and SQL Server Management Studio. If you have different versions, the screens and process may vary slightly from the steps outlined here.
Adding the AresLink Login
- On your Ares Server or on any machine currently connected to the Ares server, open SQL Server Management Studio.
- Double click on the Security folder to expand it.
- Right click on the Logins folder and select New Login.
- Verify that you are on the General page of the Login screen.
- Type the Login name in the space provided. Atlas Systems suggests using the name AresLink, but you can name this anything you would like.
- Select SQL Server authentication.
- Type in a password for your login name. Be sure to use the same password consistently throughout this setup.
- Check or uncheck the 3 password enforcement options according to your institution's specific password policy.
- Under Default database select AresData (or the name of your Ares database).
- Click OK. The window will close and you will be back on the main page.
- Double click on the Logins folder to expand it.
- Double click on the new AresLink icon.
- In the properties screen, click the User Mapping page to open it.
- Under Users mapped to this login check the box next to the AresData(or the name of your Ares database).
- AresLink will now appear as a user next to the database name.
- Click OK to close the window.
Setting Permissions for the AresLink Login
You will want to give user AresLink read access to all tables in the AresData database. You do this by making the login a member of the Role Member db_datareader.
- Navigate to the AresLink user under Databases | AresData | Security | Users | AresLink.
- Double click to open the AresLink user.
- Verify that you are on the Membership page of the screen.
- Under Database role membership check the box next to db_datareader.
- Click OK to close the window.
Testing the AresLink Permissions
You can test the AresLink permissions by logging into the SQL Server Management Studio using the AresLink login and password and running a simple query.
If you receive any errors during this process, review the Adding the AresLink Login and Setting Permissions for the AresLink Login sections above to make sure the link was created correctly.
- If you have SQL Server Management Studio open, close it.
- Open SQL Server Management Studio again.
- To log in to your AresLink user account, select SQL Server Authentication.
- Enter your AresLink login and password.
- Click Connect.
- On the main screen, click New Query.
- In the open space type a simple query such as:
select * from customization - Click Execute to run the query.
- Query results will appear in a grid at the bottom of the screen along with the message Query executed successfully.
Setting up the ODBC Data Source Link
In Windows, the ODBC Data Source Administrator is located in the Start menu under the Administrative Tools folder. It is also found under the Control Panel\Administrative Tools.
- Open the ODBC Data Source Administrator.
- Click on the System DSN tab.
- Click Add. You will see a list of available drivers.
- Select SQL Server and click Finish. The Create a New Data Source to SQL Server window will open.
- Enter the name you want to use to refer to the data source. It may be easiest to use the name AresLink.
- Enter a description for the data source.
- Select your Ares server name to connect to.
- Click Next.
- Select SQL Server authentication as the method for the SQL server to verify the authenticity of the login ID.
- Type in your AresLink login ID and password.
- Click Next.
- Check the box next to Change the default database to and select AresData.
- Click Next.
- Click Finish.
- An ODBC Microsoft SQL Server Setup window will appear. This displays the ODBC data source configuration that will be created.
- To test the connection, click Test Data Source. It should say Tests Completed Successfully.
- Click OK and close the OBCD Application.
Setting up Microsoft Access
These instructions were written using Microsoft Access 2013 as a guide. If you have a different version, the screens and process may vary slightly from the steps outlined here.
- Open Microsoft Access.
- Choose the option to create a new Blank Database.
- Enter a title for your database and click Create.
- To import your data source, click the External Data tab and then ODBC Database.
- Select Link to the data source by creating a linked table.
- Click the Machine Data Source tab.
- Select AresLink from the list and click OK.
- Log in with the AresLink login ID and password and click OK.
- A Link Tables box is displayed. To link all of the tables, click the Select All button and click OK.
- If any tables ask you to Select Unique Record Identifier, click OK. When this is finished, the link is complete.
