locked
MS Access ODBC Driver SQL 2005 User Impersonation RRS feed

  • Question

  •  

    hello everyone,

    Here's my problem:

     

    I created a SQL user with Datareader permission on a database (we have kinda CRM and some users connect to it using Access to export custom data) and I created a ODBC COnnection to that DB using SQL Authentication in order to make users to extract data without the risk of destroy my DB.
    Everything goes fine, but when I try to make an update to a table, i can do it. Using SQL Profiler I noticed that all the updates are made using my own Credentials (integrated security) and not the SQL login and PWd that I specified in the ODBC Connection...

    What can it be?


    Thanks in advance for your help.

    Renato
    Friday, June 27, 2008 10:48 AM

Answers

  • Unfortunately, we do store the username but not the password due to security issues.  ODBC DSNs are stored either in the registry or in a flat file (called a file DSN) and are not encrypted.  As a result, storing passwords would constitute a security vulnerability.  In today's world, storing a password actually implies encryption in all cases.  Since we don't encrypt this data, we can't store it for you (due specifically to security constraints).  I am not sure if Access has a separate store where passwords might be kept.

     

    Thanks,

     

    John

    Monday, June 30, 2008 11:49 PM

All replies

  • I read your problem statement a couple of times and this is what I understand - you have both integrated authentication property and username/password properties in your connection string. Integrated authentication supersedes SQL authentication properties. As long as there's an integrated authentication property present in the connection string the connection would use integrated authentication.

     

    Hope this helps.

    Friday, June 27, 2008 9:26 PM
  • Hi OLeg, thanks for your response.
    I try to be more exhaustive:

    The problem was that using SQL authentication to setup an ODBC COnnection for a MS Access DB pointing to a SQL DB, the system was always using my own credentials (I'm a Domain Admin) instead of using SQL credentials.

    I solved this using another account, and everything worked fine.

    Now I've another problem: once that SQL authentication works, MS Access always ask me the password for the SQL username; there is a way to store this password in order to let Access to no more asking it?


    Thanks in advance!

    Renato

     

    Monday, June 30, 2008 7:35 AM
  • Unfortunately, we do store the username but not the password due to security issues.  ODBC DSNs are stored either in the registry or in a flat file (called a file DSN) and are not encrypted.  As a result, storing passwords would constitute a security vulnerability.  In today's world, storing a password actually implies encryption in all cases.  Since we don't encrypt this data, we can't store it for you (due specifically to security constraints).  I am not sure if Access has a separate store where passwords might be kept.

     

    Thanks,

     

    John

    Monday, June 30, 2008 11:49 PM
  •  

    No one says that if I add manually in the .dsn file

     

    PWD= <password>

    Persistent security = true

     

    It would work...right?Not the better solution for security but maybe works for my needs.

    Tuesday, July 1, 2008 9:11 AM