Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    11

    Unanswered: Driver using trusted connections no matter what

    Hello,

    I have an Access 2010 program (that a consultant virtualized for us) that we run successfully on our Windows 7 standard image computes. However, on one person's computer we are having problems. His computer does not have the standard image. I don't know how Windows 7 was installed.

    Anyway, the problem we're having is that when the program tries to connect to the database it is using trusted connections, and that won't work here. We use database logins. The odbc string in the code does not specify the authentication method. It has always just worked, so I guess the driver (or MDAC) default was to not use trusted connections.

    I might have to change the odbc string to explicitly say not to use trusted, but that's not as easy as it sounds. My user's computer does not have Access on it. I would have to either have the program virtualized again or install Access Runtime on his computer. I was hoping somebody here has seen this issue before and knows a better way to fix this problem.

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are 2 ways to connect an application to a server using an ODBC connection:

    1. You can create and define an OBDC link using the ODBC Data Source Administrator. With this tool you register a permanent ODBC link either in the HKLM hive (System DSN) or in the current user hive (User DSN) of the Registry, or you can use a File DSN (where the properties of the connection are stored in a file in text format).

    Once defined, the application uses the properties stored during the creation process to connect to the data provider. This is typally the case when an application such as Access uses attached (linked) tables.

    2. You can also use a DSN-less connection. With this, the properties of the connection are determined when an object from a data library (DAO, ADO, JDO, ...) opens the connection to the server. This allows a dynamic configuration of the connection. Ex:
    Code:
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef("")
    '
    ' Open a trusted connection on the server SOLIMAN for the database Sales.
    '
    qdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
    qdf.SQL = "SELECT * FROM INVOICES WHERE CreationDate >= '2014-01-01';"
    In both cases, security parameters to validate the connection at the server level must be provided. Try to determine how your application connect to its server. For this, open the ODBC Data Source Administrator and see if a connection was defined there, both on a functional machine and on the machine that causes problems. If such a DSN exist, note all the parameters (if any) in both cases and compare them. If there is no DSN defined, then there is perhaps an .ini file of some kind that contains the connection parameters. Again, compare both systems.
    Have a nice day!

  3. #3
    Join Date
    Feb 2008
    Posts
    11
    Thank you, Sinndho. It's a DSN-less connection (DAO code), and reads the login, password, etc. from an INI file which the user is able to read. This code has worked for years and still does except on this one computer (although we have only tried it on a handful of Windows 7 computers). The ODBC string does not specify whether or not to use trusted connections. I have a feeling the default in the driver or MDAC has changed from non-trusted to trusted.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If your application tries to connect to a MS SQL Server, as far as I know and according to MSDN (Using ODBC with Microsoft SQL Server), the only case when no security parameters are required in the connection string is when the SQL Server is configured to run in the Integrated Security Mode (i.e. when the server accepts connections only from users (or groups) defined in the Active Directory of the domain (see the paragraph intitulated "Integrated and Standard Security" in the aforementioned document).

    So, if your server is configured to run in Integrated Security Mode and a machine is not able to connect to it, the first thing I would do would be to check whether this machine is a member of the domain in which the SQL Server is runing or not.

    If the SQL Server is configured to run in the Standard Security Mode (i.e. all connections are defined at the SQL Server level) or in Mixed Security Mode (i.e. connections can be defined either at the SQL Server level or be provided by the A.D.) AND no security parameters are provided in the connection string, this would mean that the SQL Server accepts anonymous connections which, as far as I know, is not allowed by default.

    You should perhaps check with the DBA managing the SQL Server (if any) or check by yourself how the SQL Server is configured (provided you are granted sufficient privileges on this server).

    As far as MDAC is concerned (and again, as far as I know), this component is a collection of Data libraries (ODBC, ADO, OLEDB) and it cannot natively be configured to use a preset security model. About MDAC, see: Microsoft Data Access Technical Articles Overview
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •