Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: How do I eliminate SQL Server Login Prompt?

    It's an Access application with linked tables in SQL Server.

    When I try to execute this instruction in VBA:
    Set rsLog = db.OpenRecordset("Log", dbOpenDynaset, dbSeeChanges)
    Becaues Log is one of my linked tables, I get a login prompt from SQL Server. The title is SQL Server Login. Data Source Inquiry_test (that's the DSN of my SQL database). There's a checkbox for Use Trusted Connection. Then input fields for login id and password.

    I want to eliminate this prompt. Can anyone tell me how? Is there something else I can do in code, or do I have to configure this in ODBC Data Sources? Also interesting to note, if you check off Use Trusted Connection and say Ok, you are reprompted with the check erased, as if it rejected that as an option without telling you why.

    Read more for the gory details...Inquiry_test is a copy of a database called Inquiry. It was created using a backup and restore. From what we can see, these two databases are configured EXACTLY the same. The reason for Inquiry_test's existence is to test version 2 of the Access app. With version 1 and Inquiry, there was never a login prompt. I can't see why I'm getting one with version 2. (The version of Access and SQL Server are the same. It's called version 2 because of user enhancements, not any Microsoft software changes).

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You can put the login and password in your DSN.
    Make sure "With SQL Server authentication...." is selected,
    Check the "Connect to SQL Server...."
    Put in your login ID and password.

    That should take care of the problem.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Unless I've misunderstood your advice, I don't believe that helped.

    I'm configuring the System DSN, I give it a name (Inquiry_test) and tell it what server I'm connecting to, then on the next screen are the two radio buttons to either use Windows authentication or SQL Server, I choose the latter and then check the box on the lower half of the screen "Connect to SQL Server to obtain default settings for the additional configuration options." I enter the same Login ID and password here as I do when I run my app and get the unwanted prompt.

    What I am trying next is setting up a connect string before I try the OpenRecordset, but I'm not sure exactly of the syntax. Do you think this would even help?

    Any other ideas?

    Thanks.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    hmmmm.....

    Could have something to do with using DAO instead of ADO. I'm not sure on that one, since it's been a long time since I used DAO.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    set up your SQL server to use windows authentication/ integrated authentication.
    OR
    supply the password as part of the call to the connection (If I remember right those parameters you dont supplly as part of the DSN defintion can be added as part of the object set up.) you could do this on start up of the access application, require a password.. validate in you SQL server and then stroe the password TEMPORARILY int he applciations data as a globsal variable. Id suggest you grab the USERID using an API call..., especailly DONT use the environ variable.

    but I really wouldnt want to set up USERID's & Password as part of the DNS.. thats going to be a pig of a maintenance problem over time as each person moves/ leaves / starts, their rights change, their PC changes...
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    When you build your connection, use this property setting:
    db.Properties("Integrated Security").Value = "SSPI"

    This uses Windows Authentication, and the login prompt goes away.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jul 2006
    Posts
    111
    Thank you, but while you were responding to me I was also researching and I think I found the fix. There was a thread from over 4 years ago on this forum entitled "ODBC Login Dialog Box" and the resolution there has worked for me (at least so far, but I've learned to test things to death before I begin the celebration when it comes to ODBC problems). It was to check off the Save Password box when you link your tables. I was able to get to the main screen without receiving the SQL Login prompt, whereas before that's when I would get it.

    Thanks for the interest in my problem.

Posting Permissions

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