Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    I have an Access XP front-end that works with a SQL Server back-end. Approximately 30 users are using this system, each with a copy of the front-end on their PCs. They connect to the back-end over a LAN. There are also some users on our Terminal Server. They have a copy of the front-end on their Terminal Server desktops, and connect to the back-end over the LAN as well. The ODBC source for the linked tables uses Windows authentication to grant the users access to SQL Server.

    Problem #1: When I view the Process Info under Current Activity using EM, the Terminal Server users show up as DomainName/username. That's good. However, the regular users show up as ServerName/Guest. I can't get them to authenticate using their Windows authentication. They are slipping in as guests.

    Problem #2: I want to create a hierarchy of Database Roles eg. Salesperson, Team Manager, Branch Manager and individual Database Users to add to these roles. I want my Access users to log in to the SQL Server as registered Database Users. How can I do this?

    I hope I'm coming across clearly. Any input is much appreciated [img]/forums/images/smilies/smile.gif[/img])

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

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    It sounds as if you have the mixed mode of security running in SQL Server. It is generally recommended that the "guest" account not have any priviledges at all in the database. Check out http://www.sqlsecurity.com/DesktopDefault.aspx for lots of tips about setting up security and about protecting yourself against hacking. What you want is integrated security in SQL Server, which uses the Windows domain UserID as the ID for SQL Server - unfortunately it's not uncommon for people to have trouble with ODBC settings in setting up a DSN. There is now an entire book on the subject as well which is described on the site above. You can also find some additional sources for SQL Server info on our Links for Denver Area Access User Group at the bottom of the web page. There are tradeoffs in using both Access User Security and SQL Server security - double administration is required if you use both, but Access lets you set permissions on front-end objects.

    Post back if any of this appears to be gibberish, or you find yourself still stuck.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    Wendell - Thanks a lot for your response. I have to really think about what we're doing here, and probably educate myself further on how to set up SQL Server properly. I will definitely come back with questions once I've sorted myself out better [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    OK. I think I know what I want to do. Let's say I have 50 users on their own PCs using their own copies of an Access front-end which links to tables on a Sql Server back-end. The Sql Server machine is connected via the office network to the users' PCs. I want to use SQL Server security to grant permissions to my various users. Can I do this with this setup? The only way that I thought of doing it is by having each user provide a username and password upon opening the Access front-end, and then, in code, relinking the tables with the user's userid and pwd to the back-end. Is there any easier way? Also, if that is the best way, can anyone give me some pointers re: relinking tables in code?

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

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    Hi TJ
    I think what you want to do is set your SQL Server up with Integrated Security which uses their NT domain login as the login to SQL Server. We routinely do that, and set up groups so that people can be changed with minimal disruption. Otherwise, you have to do administration on both SQL Server, your network and Access. For 50 users that can be a substantial pain. With the Integrated approach, the ODBC driver connection can simply use their NT login, and there isn't a need to relink tables. Take a look at Mary Chipman and Andy Baron's book on SQL Server for Access Developers - it should give you a decent background in SQL Server security and the use of ODBC connections.

    The things that is lacking in SQL Server is the ability to prevent people from running certain forms - in effect a customized menu for different groups of users. You can solve that by using different front-ends for the various user groups, but we do typically set up Access User Security to handle those kind of issues. That same issue is why we usually don't deploy ADPs but use either MDB or MDE front-ends.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    Wendell -
    It definitely sounds like Integrated Security is the way to go for me. I just have one problem. I've tried using it (i.e. My Windows NT domain is called NE. I added some users as new logins to my SQL Server registration (e.g. NE/jsmith, NE/dmcdougal). However, they simply do not get authenticated. Do you have any idea as to what my problem might be? I feel like I'm treading in very unfamiliar waters here. Thanks a lot!

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

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    I don't understand what you mean when you say the users do not get authenticated. Are they still coming in as Guest? Or are they getting an error message? In general we don't provide a guest account at all. On your ODBC connections, it should be using the NT login for that user/workstation.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    I'm sorry for not being clear - I'm still trying to clarify things for myself. The problem begins when I try to create a FileDSN to use as an ODBC connection. If I choose to use Windows NT Authentication, the configuration wizard does not allow me to choose my database as the default database UNLESS I have the SQL Server/Guest account set up for that database on the SQL Server. I really don't want to create a guest account. Also, when I do create the Guest account and complete the DSN configuration successfully, the tables are linked via the guest account and not via NT authentication. How can I ditch this Guest account business?

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

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    I believe the problem is you are trying to create a File DSN rather than a User or System DSN. We normally run either File of System, and when we create the ODBC driver link the second page asks if you want to use SQL Server or Windows authentication. In the middle of that page is a Client Configuration button (not sure I got the name quite right) that lets you pick a SQL login. If you choose that, you can specify any SQL login, including an NT login. Then you can go back and change to Windows authentication and it should remember the login you chose. Hope this helps a bit.
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    I've tried creating FILE, USER, and SYstem DSNs. I haven't had success with any of them. On the second page of the DSN Configuration wizard, I chose to use Windows NT Authentication and I checked the box next to the words: "connect to sql server to obtain default settings for the additional configuration options". I've attached a jpeg image of the Client configuration screen. I don't see where I would specify the SQL login and then go back and change to Windows authentication. Sorry to be a pain but...can you clarify?
    Attached Images Attached Images

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

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    Sorry about the delay in responding - we have computer troubles and other issues going on. I'm not sure where you got the dialog box you attached, but DSNs using ODBC are usually done with the ODBC Data Source Administrator. For Windows 2000 it is found in the Control Panel under the Administrative Tools subfolder. In Windows XP it is in the same subfolder, but is called Data Sources (ODBC). I've attached a screen shot of the dialog box that shows what it looks like when you are adding a System DSN for a SQL Server database, and the button used to Configure Client connections. Hope this helps.
    Attached Images Attached Images
    Wendell

  12. #12
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Security and Access front-end (SQL Server 2000, Access XP)

    Wendell - thanks for your response. A lot has happened since I last posted. To sum it up: I realized that our users were not logging in to our network domain when they were logging on to their computers. Therefore, Windows authentication for SQL Server wasn't working because the users weren't being authenticated by the network at all. Now that I know what the problem is, we're working on solving it (and, of course, creating other problems along the way [img]/forums/images/smilies/smile.gif[/img] ).

Posting Permissions

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