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

    Unanswered: How To Provide the SQL server Loing dialog data Automatically ?

    Dears,
    I am working on an access DB that has linked tables and connects to the SQL by DNS ODBC.
    The point is that I want to connect using SQL authentication rather than using windows authentication (to prevent the users to login and link to the tables, just see the provided data on the forms designed to!)

    I found a code that creates an odbc by code at the following Link:http://support.microsoft.com/kb/171146/en-us
    But i am getting SQL server Loing dialog asking for the sa user and password and I cant find any code to provide this automatically.
    The following is my code that i was using while login using windows authentication

    Code:
         Dim db As Database
         Dim rs As Recordset
         Dim rs1 As Recordset
         Dim rs2 As Recordset
         Set db = CurrentDb
         Set rs1 = db.OpenRecordset("ProgV", dbOpenSnapshot)
         Set rs2 = db.OpenRecordset("Ver", dbOpenSnapshot)
         
         
         Query1 = "SELECT Machines.SysUserName, Machines.MName From Machines WHERE Machines.SysUserName = fOSUserName()  AND Machines.MName = fOSMachineName()"
         Set rs = db.OpenRecordset(Query1, dbOpenSnapshot)
    the code is not working unless i type the sa user and password "which is not provided to a user"

    Do any one have a code to provide the sa username and password automatically

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You might consider using sp_setapprole instead. It is much more secure, conceptually much simpler, and it avoids the need to have the user log in to SQL Server at all.

    -PatP

  3. #3
    Join Date
    Feb 2008
    Posts
    4
    Thanks PatP for your rely,
    I have gone through the above Link but i guess i am still confused regarding how to implement this,
    I have liked tables that should login to the existing SQL server, and i still don’t find a code to let it through

    also i am using SQL 2000 and i am not sure if this is applicable for 2000 or 2005 SQL
    do u have any idea how to implement the "sp_setapprole (Transact-SQL)" in to my SQL database and the access application connecting to it ?
    Last edited by ahmed_zedan77; 02-08-08 at 15:51.

  4. #4
    Join Date
    Feb 2008
    Posts
    4
    do any one have any suggestion about how to connect to an obdc - dns by code ?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry about the delay in responding... I've been a wee bit busy at work lately.

    What I would recommend is allowing you users to connect using their existing Windows credentials. This is more secure, better supported, and a whole lot easier to manage than reverting to using SQL Server connectivity in a Microsoft Access application.

    The trick that you need is in your application startup macro/module you need to execute the call to sp_setapprole. You give the Windows users access to your database, but NO permissions at all. You grant all of the permissions needed by your application (and only those permissions, no extras) to the role. That way the users connection to the database itself is handled via Windows Authentication, and once the application connects it executes sp_setapprole as the very first thing so that suddenly the application has all of the permissions that it needs.

    If the users connect on their own (using MS-Access or other tools), they can connect to the database just fine, but since they have no permissions there is nothing that they can do.

    I'm going to move this thread to the MS-Access forum. There are lots of folks there that have the experience needed to ferret out whatever details you need, and explain them better than I can... It has been many years since I've built an application using Microsoft Access for anyone except myself or my family.

    -PatP

  6. #6
    Join Date
    Feb 2008
    Posts
    4
    thanks PatP
    now I can handle creating the stored procedure in the sql as following :
    Code:
    sp_setapprole [ @rolename = ] 'role',
            [ @password = ] { encrypt N'password' } 
        |
            'password' [ , [ @encrypt = ] { 'none' | 'odbc' } ]
            [ , [ @fCreateCookie = ] true | false ]
        [ , [ @cookie = ] @cookie OUTPUT ]
    but I don’t know how to call the stored procedure from access,
    can you help me on this !!
    Last edited by ahmed_zedan77; 02-10-08 at 13:28.

Posting Permissions

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