Post reply

persistent ODBC authentication

  • June 29, 2017 at 1:39 pm

    #322334

    I have a few users who use ODBC to connect to an SQL Server database.  The ODBC connection tests out in the Data Sources (ODBC) app and can be used in both Access and Power BI, but the authentication is not persistent.  In Access, the user is required to enter in their username and password when first connecting and creating a new Access database as well as when adding tables to an existing Access database.  

    The username and password are stored in the ODBC connection so I was hoping there was some way to allow users to not have to enter it when using their applications.

  • June 29, 2017 at 2:08 pm

    #1948824

    If you are on a Windows domain, you can use trusted connection. With integrated security, users can connect without getting prompted for a password if they are logged in to their workstation.

  • June 29, 2017 at 2:35 pm

    #1948832

    The password won't be stored other than in one situation which would be bad to use but it would be using a file DSN and just add the entry for the password by adding a line: PWD=WhateverPassword.
    And it is a horrible direction to go down as it is stored in plain text. Pretty much the reason many of the drivers won't save passwords anymore as they used to be plain text in the registry. It is not recommended. And if you have any auditing, it will fail. The better approach is to use Windows authentication as Joe already suggested.
    If they are getting prompted for credentials during their work that's a different issue. Access will cache the credentials when it opens the connection and will reuse the cached credentials if driver, server and database are the same. When one of those change, you get prompted for credentials. I think there might be some weird ways to get around that with a connection to a passthru query and I can't remember the details of it but if they are creating new access database, that's not of much use.
    Windows authentication is the way to work around all of this. 

    Sue

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply