Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Login to Sql Server (Access 2000)

    My Access database uses several linked tables from a Sql Server 7 database. The first time I try to open a form or report, I get a Sql Server password dialog, and have to enter the password. Thereafter (during that Access session), the password "holds", and I don't have to enter it again.

    Any ideas on how to force this password in, so users don't have to enter it? An auto-exec macro, some vba code, etc.?

    Thanks for any help on this issue.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Login to Sql Server (Access 2000)

    If you hard code it into Access anywhere, you've just laid your SQL Server database wide open. However, there's no reason you can't use a UDL to link to the database, and you could store the login and password in that.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Login to Sql Server (Access 2000)

    Are you using Windows security in SQL, or are you using mixed security and logging into SQL with a SQL only password? (Hopefully not sa!) We run a number of systems using ODBC links to SQL, and in general there is no login prompt required as long as you are using Windows security. The downside is that you do need to implement permissions via Users and Roles to make that work well. On the other hand SQL Security is more robust than Access. Speaking of which, there's a great new book out that will get your attention if you are concerned about the possibility of hacking SQL Server. The title is "Hacking Windows 2000 Exposed" and it has an entire chapter on the ways people might try to exploit weaknesses in SQL and Win2000.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Login to Sql Server (Access 2000)

    Thanks for the quick reply.

    I am using SQL Server security. We have a user set up just for this application, with the proper roles/permissions. When I open the database, no password is asked for. However, when I open a form or report which is using one of the linked tables, the login dialog appears. Since we're using a dsn, the proper username appears (the one attached to that dsn), and I need to enter the password. This only happens once per Access session.

    What I'm looking for is a way to do this first login programatically so the user doesn't have to do it. I am also using ADO for certain pieces of the application, where I need to access the tables directly, without using a form. I wonder...if I open the public connection object at the beginning (autoexec macro running code) maybe that will work? Any ideas? Also, Charlotte mentioned using a udl file instead of hard-coding my connection string properties (thanks, Charlotte). Don't know how to do that yet, but will research msdn.

Posting Permissions

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