Solved

Need to link an SQL table to Access using SQL authentication without requiring users enter a password.

Posted on 2015-02-16
10
105 Views
Last Modified: 2016-02-11
Hello,

What is the preferred way to link to an SQL table with Access using SQL Server authentication that does not require the user to enter a password?

I can connect using a system dsn, but don't know how to store the password in Access.

Thanks, my office opens in two hours and I'm trying to get this thing working before the staff gets in!  :-)
0
Comment
Question by:pcalabria
10 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 125 total points
ID: 40611945
Can't you store the password in the connection string?

http://support.microsoft.com/kb/303968/en
0
 

Author Comment

by:pcalabria
ID: 40611962
I don't remember ever creating a connection string.  That's the problem.

To add the table I used:
Get External Data
Import
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40611964
Which user is used to connect to the SQL Server database?
The recommendation for SQL Server logins is to use domain users so the credential of the current user would be used and then no password is required,
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40611970
Many prefer using a DSNless connection for this:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Basically, you include code in the Open or Load event of your application that rebuilds or verifies the linked tables prior to full startup. This means you don't have to ship or create a DSN with your application, and can instead just launch the application and recreate the links when needed.

You can also use a Trusted connection, as Vitor suggests earlier.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 40611986
We use a single account for user access, then calls this code with the given parameters:
Public Function AttachSqlServer( _
    Optional ByVal Hostname As String, _
    Optional ByVal Database As String, _
    Optional ByVal Username As String, _
    Optional ByVal Password As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server.
' 2014-08-09. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"
    Const cstrConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server;" & _
        "Description=Your Application Name;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim strConnect      As String
    Dim strName         As String
    
    On Error GoTo Err_AttachSqlServer
    
    Set dbs = CurrentDb
    strConnect = cstrConnect
    strConnect = Replace(strConnect, "{0}", Hostname)
    strConnect = Replace(strConnect, "{1}", Database)
    strConnect = Replace(strConnect, "{2}", Username)
    strConnect = Replace(strConnect, "{3}", Password)
    
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print tdf.Name, tdf.SourceTableName, tdf.Connect
            End If
        End If
    Next
    
    AttachSqlServer = True
    
Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
    
Err_AttachSqlServer:
    ' Custom error box:
'    Call ErrorMox
    Resume Exit_AttachSqlServer
    
End Function

Open in new window

This assumes that you use the tablenames in Access without the prefix dbo_ from SQL Server.

/gustav
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:pcalabria
ID: 40612109
@Lee
Thanks.  The link you provided was for creating a pass-through query.. is that what you meant?

@Victor
I can't get NT authentication to work because of the problems previously discussed.
I have created an SQL account and would like all my users to authenticate with this single account.
The question becomes how do I add the connection string to access, or edit the string created by the LTM?

@Scott
Thanks. I was able to get the connection string used by the the corrupt table with the help of another expert.
Now I know exactly what the string needs to be, but I don't know how to add it.
The LTM doesn't give me that option and I searched the code for the string with no luck.

@Gustav
Thanks for the code.  I don't completely understand it yet, but it seems more complicated than necessary in my situation.  Everyone who used the application can authenticate as the same user, I just need to know how to add the connection string.  At this point, I know what string to add, just where and how to put it into the code.
Thanks
0
 
LVL 84
ID: 40612124
Now I know exactly what the string needs to be, but I don't know how to add it. The LTM doesn't give me that option and I searched the code for the string with no luck.
The article I pointed you to shows how to create a linked table, and when doing that you can define the connection string. This is known as a "DSNless" connection, and essentially it removes the need for you to create or use a DSN. You only have to have a valid Provider/Driver installed on the machine, and use that valid PRovider/Driver in that connection string.
0
 

Author Comment

by:pcalabria
ID: 40612371
Problem solved.
I solved this one myself, and like many problems, the solution is so simply once known.
I will equally split the points to everyone who tried to help. Thanks!

The solution to embed the password for MS SQL Authentication was almost too easy.

Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay.  When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box.  CHECK IT!

Done.   You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password.  No code in autoexec or onOpen events to cause development issues.

NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do.  That was the distraction that kept me up all night!
0
 

Author Closing Comment

by:pcalabria
ID: 40612374
None of the solutions offered worked, but they all kept me going until I found the solution.

Step 1, Create a system DSN using SQL Authentication.
Step 2, Open access, create an odbc connection, enter the password, and click okay.  When the dialog box showing all the tables you have access to appear, select the tables that you want, and then notice that on the right side of the screen there is a save password box.  CHECK IT!

Done.   You can exit and then reload, or deploy to other users on the LAN, and you are not asked for a password.  No code in autoexec or onOpen events to cause development issues.

NOTE: You can NOT save the password using the Linked Table Manager, regardless of what you do.  That was the distraction that kept me up all night!
0
 
LVL 84
ID: 40612387
I believe we touched on this in one of your other questions along these same lines.

The only way to store a password (outside of using VBA to create your links) is to manually delete and recreate the links. You don't necessarily have to recreate the DSN, but you must log in and select the tables, as you mention.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now