Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    MSDE/SQL server connection (Access2002/XP)

    I have just installed the MS Desktop Engine version of SQLServer that ships with OfficeXP. I am running Windows XP Pro. I have no trouble connecting with the server (that is, connecting an Access project with the server) if I log in as Administrator or if I give my regular account administrator priviledges, but I can't connect if I am logged in normally. The error message says I am "not associated with a trusted SQL Server connection." I have tried every setting I can think of but nothing works. Can anyone help?

    Fafner

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

    Re: MSDE/SQL server connection (Access2002/XP)

    I believe the issue is with the users that have been defined in the MSDE install. In SQL Server, by default people with Administrator priviledges login as SA - which is probably the only defined user in SQL which has any significant priviledges. It's been a while since I played with the tools so I don't remember the facility for adding SQL users - but if you are running a domain, you can use Integrated security so that anyone with an NT userid can be given permissions to SQL databases. Hope this gets you on the right track. If you get stuck, repost - I'll be at my office late tonight and can give you more details.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: MSDE/SQL server connection (Access2002/XP)

    Thanks for your quick reply. First, I am not on a domain. I have a small workgroup (4 machines) but am concerned only with this one particular desktop. As for users defined in the MSDE install, there was no opportunity to do such a thing. I simply clicked on the exe file, agreed to the terms, and there were no more dialog boxes. It simply installed itself. The only way I can find to get at it is to right click on MSSQLServer in the Services window and go to properties. On the logon tab, I tried every possible combination of settings, to no avail. I realize that this isn't a full-fledged version of SQL server, but there must be a way to give myself permission as a normal or power user, not just as an administrator. If not, I suppose I will always have to run Access as an administrator, so it's not like I have no options, but it seems that this shouldn't be the only way it is supposed to work.

    Fafner

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

    Re: MSDE/SQL server connection (Access2002/XP)

    There is some help available for the MSDE that describes how to do simple user admin, but I can't give you much detail until I get to my office tonight. Will post then, but no, you shouldn't have to be an admin user.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: MSDE/SQL server connection (Access2002/XP)

    I have continued to look for an answer to this problem, but still no luck. Any further thoughts?

    Fafner <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSDE/SQL server connection (Access2002/XP)

    I don't have Access 2002 and never work with MSDE but maybe the following KB article and the links on it can help you :
    HOW TO: Connect to Microsoft Desktop Engine (Q319930)
    Francois

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

    Re: MSDE/SQL server connection (Access2002/XP)

    Sorry - life got alot more complicated than I had anticipated in my previous reply. I think most of your questions can be answered in this white paper. BTW, I have been calling it the MSDE, but Microsoft changed the name for XP to SQL Server 2000 Desktop Engine. Anyhow, there is a section in the document that describes some of the issues associated with setting up users, and basically suggests that you install the SQL Server 2000 Client tools, which gives you the Enterprise Manager.
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: MSDE/SQL server connection (Access2002/XP)

    Thanks for your reply. I have read the white paper and, unless I misread it, it only confirms what I have found to be the case, that is, that I can connect only as an administrator. Setting a password doesn't work in a workgroup (I am not on a domain) because, as Q319930 explains, "there is no domain controller that can validate your Windows account." So I am back where I started. The SQL Server 2000 Client tools don't seem to be available with the Desktop Engine, only with the full version, so the Enterprise Manager is not available to me. I tried to find it as a download, but no luck.

    It seems odd that this is the way the Desktop Engine is supposed to work, that is, with no options for regular users in a workgroup.

    Thanks for your help.

    Fafner

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

    Re: MSDE/SQL server connection (Access2002/XP)

    I'll do a bit more research and see what options you have in terms of adding users that are not SA and setting their priviledges in the database. One option is to get the Developer version of SQL Server 2000 - we subscribe to a service that costs about $400 a year that gets us this sort of stuff. If you are interested, I'll get more details about it. In the meantime, I'll be studying some books.
    Wendell

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

    Re: MSDE/SQL server connection (Access2002/XP)

    The developer version of SQL Server 2000 comes with Office XP Developer.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    336
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Re: MSDE/SQL server connection (Access2002/XP)

    I just wanted to report that I figured out how to connect to the SQL Desktop Engine from Access without having to always run Access as an administrator. It is done from within Access. Run Access as an admin and connect a project with the MSDE. From View/Server properties, check Enable sa username. Then set password with Tools/Security/Set login password. Close Access, open Access as regular user, and inter the login password. Seems pretty simple now that I know how to do it. I was looking to change settings from within the Desktop Engine, not from within Access.

    Fafner

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

    Re: MSDE/SQL server connection (Access2002/XP)

    This just goes to show you that assumptions can lead you astray - I was for some reason presuming you were trying to establish an ODBC connection rather than using an ADP. It's been a while, but I think you may also be able to create new SQL Server logins from an ADP. Sorry I didn't think to explore that line a long time ago! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Wendell

Posting Permissions

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