none
how to operate multiple access databases against a single SQL Azure database RRS feed

  • Question

  • I want to operate multiple Access database against a single cloud-based SQL Azure database.  Each Access database uses an ODBC connection to link the appropriate tables in the SQL database.  Currently I have to login from each Access database.  I’d like for users to login one time and have the SQL database be open for each access database that may be opened.  Any suggestions would be appreciated.

    Wednesday, November 7, 2018 2:03 PM

All replies

  • To answer that we would need to know more about your configuration and security objectives.

    For example, you can extend Active Directory to the cloud, then users can login with Windows Auth without a single prompt.


    -Tom. Microsoft Access MVP

    Wednesday, November 7, 2018 3:23 PM
  • Hi Tom,

    I'm not sure if I'm giving enough detail, but here goes:

    We’re using SQL Server 2016 and Management Studio to maintain the SQL Azure database.  There is an admin ID and password for login to the database.  In addtion, the IT person managing the server has created an “alias” ID and password that users can login to access the tables on this server.  This ID and Password is used when logging in to the server through an ODBC data source and connection defined in each Access program.  For now, all users and myself need to be able to operate one or more of 5 Access databases at any time, from their repective PCs or laptops.

    thanks for working with me.

    Wednesday, November 7, 2018 7:39 PM
  • What I understand from this is that all users are using the same Alias ID and PW. That effectively means you have no security within the company, but of course outsiders don't know this data and the Azure firewall would keep them out anyway (assuming correct settings).

    In that case, you can refresh the linked tables at startup time using a connection string that includes the u/pw. See RefreshLink in the help file. You may also want to check out JStreet's relinker here: https://www.jstreettech.com/downloads.aspx


    -Tom. Microsoft Access MVP

    Thursday, November 8, 2018 2:35 AM
  • Hi Tom,

    thanks for the info - will check this out.

    Pete

    Thursday, November 8, 2018 2:12 PM
  • Pete - I really think you are doing this the hard way. Create an Active Directory group. Put your users in that AD group and Make the ODBC connection use Windows Authentication as Tom suggested. 

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, November 9, 2018 9:16 PM