Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    4

    Question Unanswered: SQL Server backend -- automate authentication

    My company wants to migrate a number of Access databases into SQL Server, and I'm assigned the task of setting up Access as a front end. I migrated two databases as a test and have created links to them in a new Access database, but my immediate problem is automating the SQL Server authentication when I open the linked tables.

    Currently, the first time I open one of the linked tables, the SQL Server Login window appears with the correct server listed, but with my Windows username as login ID. I have to back up, enter the SQL Server login and password. After that, all tables are available to me. The problem is that this is not an acceptable method of working when made available to users in the real world.

    Is there a code I can put in to login automatically to the linked database without involving the user?

    All solutions, suggestions, hints, and clues are appreciated.

    TIA,

    Patrick

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If both server and client machines are members of a domain, all you need to do is to grant connection rights to the users (or better: to an Active directory group of users) to the server, then grant access to this group to the concerned database. to do this:
    1. Open MSSMS (Microsoft SQL Server Management Studio) with administrative rights.
    2. Expand the "Security" folder of the treeview on the left (in the Object Explorer).
    3. Expand the "Logins" folder in the "Security" folder.
    4. Right-click on the "Logins" folder and select "New Login..." in the popup menu.
    5. Enter the required information in the "Login - New" dialog box (if you don't know how, contact the DBA or search in Books Online).
    6. Click OK.
    7. If you did not specify the concerned database in the "Default database" combo in the "Login - New" dialog box, expand the "Databases" folder, then expand the folder of the concerned database.
    8. Expand the "Security folder of the database, then right-click on the "Users" folder and select "New user..." in the popup menu.
    9. Enter the required information in the "Database User - New" dialog box (if you don't know how, contact the DBA or search in Books Online).
    10. Click OK.
    That's it.

    If If both server and client machines are not members of a domain, the process is quite the same but you'll have to define each user individually at the level of the SQL Server (as no information from a domain is available).

    You can also check, and change if necessary, the "Connect" property of each attached table in the Access database. Here is how:
    Code:
    Sub ListConnections()
    
        Const c_SQL As String = "SELECT name FROM MSysObjects WHERE type = 4;"
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim pty As DAO.Property
        Dim rst As DAO.Recordset
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                Debug.Print "Table: " & .Fields(0).Value
                Set tdf = dbs.TableDefs(.Fields(0).Value)
                Set pty = tdf.Properties("Connect")
                    Debug.Print , pty.Name, pty.Value
    '
    ' To change the connection string, use:
    '
    '            tdf.Properties("Connect").Value = "<some connection string>"
    '
                Set tdf = Nothing
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    If you don't know which values must be used in the connection string, see: http://msdn.microsoft.com/en-us/libr...2(SQL.80).aspx
    Have a nice day!

  3. #3
    Join Date
    Oct 2014
    Posts
    4
    Sinndho,

    I'm sorry, but while I do appreciate your help, this is still Greek to a great extent, or at least still requires more knowledge than I have to start; it's quite a bit over my head, and it's not making sense. I have a only small background with SQL Server (I've taken a course in T-SQL, and I can move around a bit in VS), and the DBA here is not much of a source (he's actually an Oracle man, and works in SQL rather grudgingly).

    I'm not even sure what you mean when you ask whether the server and clients are members of a domain, so I cannot authoritatively answer that question, except to guess that SQL Server is, and Access is not. I don't know where to look for a "Connect" property in an Access table. While I'm capable of learning, I think I'm a bit short of the background needed for what you are suggesting.

    I'm just looking for a way to automatically log in to the linked SQL Server table (perhaps by running a script when the Access database is first opened). Is there a script that can be run to do that in VBA or Powershell?

    Thanks,

    Patrick

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Patrick Rock View Post
    I'm just looking for a way to automatically log in to the linked SQL Server table (perhaps by running a script when the Access database is first opened). Is there a script that can be run to do that in VBA or Powershell?
    Patrick
    Patrick,

    If the SQL Server and the Access front-end are properly configured, there is nothing to do at all: the connection to the database is automatic and provided that the proper credentials where entered, either through the ODBC control pannel or via the Connect property of the attached tables, the user will never have to enter any kind of identification information. As I tried to explain (sorry if I was not clear enough), there are basically 2 methods for granting access to a SQR Server database: if the machines are member of a domain, you use the Active Directory of this domain to authenticate the connection (the credentials are provided by the Active Directory Server, if you prefer). If the machines are not members of a domain, you must define users and their password at the level of the SQL Server and use these credentials in the Connect property of the attached tables, either directly by editing the properties of the QueryDefs (see my former post), or by properly configuring the ODBC connection that will be used to attach the tables. A third method consist in using what I would call "dynamic" attached tables, i.e. you create the attached tables using VBA code. In any case, the users must be defined at the SQL Server level in a way or another.

    If you want me to, I can post a T-SQL script to create logins and users on a SQL Server, but I'll need to know whether the server is member of a domain or not. You can easily find this piece of information by examining the login screen of a computer or by checking the properties of the network interface of the machine. In windows 7:
    1. right click on Computer.
    2.In the popup menu, select Properties.
    3. In the General information windows that opens, you'll find the information around the middle of the page: ""Name, domain and Workgroup parameters".
    Have a nice day!

  5. #5
    Join Date
    Oct 2014
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    If the SQL Server and the Access front-end are properly configured, there is nothing to do at all: the connection to the database is automatic and provided that the proper credentials where entered, either through the ODBC control pannel or via the Connect property of the attached tables, the user will never have to enter any kind of identification information.
    Well, then, we did something wrong here. I set up an ODBC link, and gave it the username and password, and I can see the linked tables without trouble, but every single time I newly open Access and go to open the SQL tables, they ask for username and password again.

    Quote Originally Posted by Sinndho View Post
    As I tried to explain (sorry if I was not clear enough),
    I am perfectly willing to believe that the problem is with me and my poor understanding of the subject. It's been the case often enough.

    Quote Originally Posted by Sinndho View Post
    If you want me to, I can post a T-SQL script to create logins and users on a SQL Server, but I'll need to know whether the server is member of a domain or not.
    It is a member of a domain. I've just spent a week in a number of varied discussions, and had at least three definitions of the word "domain" to juggle in my head (not all of them to do with computers), so please forgive the momentary confusion.

    I would be grateful for a code example or two.


    Thanks for the help,

    Patrick

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here we go !

    1. Grant access to the SQL Server and to a database for a domain user (create login):

    - Open SSMS and connect to the desired SQL Server.
    - Click New Query
    - In the query window, paste this code and execute the query:
    Code:
    USE [master]
    GO
    -- Add a Windows login to SQL Server
    CREATE LOGIN [DomainName\UserName] FROM WINDOWS;
    GO
    USE [DatabaseName]
    GO
    -- Name the user the same name as login
    CREATE USER [DomainName\UserName] FOR LOGIN [DomainName\UserName]
        WITH DEFAULT_SCHEMA = dbo;
    GO
    Where:
    - DomainName is the name of the domain.
    - UserName is the name of the user in that domain.
    - DatabaseName is the name of the database to which you want to grant access (duh!).

    Note: For security reasons, you should not allow access to the dbo schema to any normal user (dbo = Database Owner). However, I suppose that you did not create specific schemas in the database so it will be all right to use the dbo schema, at least for now... "True" dba's would howl like a pack of wolves, or have a fit, when reading this: ignore them... for now.


    2. Set up an ODBC connection properly for domain authentication:

    - Open the Control Pannel.
    - Select ODBC.
    - Open the System Data Source tab.
    - Click on the Add button.
    - Select SQL Server Native Client and click on the Terminate button.
    - In the New Data Source wizard window type the name you want to give to the data source. It can be the name of the database or it can be any name you see fit. You can also type in a description (optional).
    In the Server combo, select the name of the SQL Server for which you want to create the connection. It can take a little time for the list part of to combo to drop down, especially if the network is slow and/or if there are many SQL Servers connected to the network.
    - Click Next.
    - Select With Integrated Windows authentication (option selected by default), then click Next.
    - Check Change default database and select the name of the database for which you want to create a connection in the combo.
    - Leave all other options by default and click Next.
    - Don't change any settings on the next page and click Terminate.
    - Click on the Test Data Source button. If everything is properly set up, a notification windows will state the the tests were successful.
    - Click the OK button to close all windows of the Data Source ODBC Administrator.

    Notes:
    a) You must have administrative rights on the computer.
    b) You can also open the ODBC Manager frm within Access:
    - Select the Externa Data tab.
    - Select ODBC Databases.
    - Select Link to the data source (attached table) and click OK.
    - Select the Machine Data Source tab.
    - Click New.
    - Select System data source and click Next.
    - Select SQL Server Native Client and click Next.
    - Click Terminate.
    - Follows the steps form "In the New Data Source wizard window type..." here above.
    c) You can use the same technique for changing the properties of an existing ODBC connection.
    d) It's also possible to create a .reg file to directly create the necessary keys in the Registry but I prefer not to mess with the Windows Registry without absolute necessity.

    3. Refresh the connections of the linked tables:

    - Open the Access project.
    - Select the Externa Data tab.
    - Select Linked Tables Manager.
    - Check all the tables the link of which you want to refresh.
    - Also check Always ask a new... and click OK.
    - In the Select Data Source window, open the Machine Data Source tab and select the data source you created at step 2, then click OK.

    Final note: My systems are not configured in English (nor Windows, nor SQL Server, nor Access), so the exact words might be slightly different from the one I used here. I tried to translate the best I could.
    Have a nice day!

  7. #7
    Join Date
    Oct 2014
    Posts
    4
    Thanks so much. Monday being our busy day (weekly reports to run, correct, and distribute), it will be a day or two before I can go through this all. But again, thanks.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome ! Come back if you have questions or encouter problems.
    Have a nice day!

Tags for this Thread

Posting Permissions

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