Share via

How to edit a DNS file with an option that includes a user and a password (MsAccess)

Anonymous
2021-05-19T09:34:47+00:00

hi

I want to connect a table  in Ms-SQL TO  MsAccess  :  Used -Link to the data source by creating a linked table

The SQL server is in a remote location

I have an IP address for the server

How to edit a DSN file, with the option to enter a name and password

Right now every time I log in

There is a request to enter a username and password

DSN File Content

[ODBC]

USER=user1234

password=1234

DRIVER=SQL Server

Trusted_Connection=Yes

DATABASE=Export

WSID=Export

TABLE=dbo.Invoice

APP=Microsoft Open Database Connectivity

SERVER=145.183.42.237,1453

In an attempt to connect tables using a DNS file

I get the following error

Then there is a request to enter a username and password

![](https://learn-attachment.microsoft.com/api/attachments/15a73ed8-f132-46cd-bd4f-d1f30287b39c?platform=QnA

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. George Hepworth 22,300 Reputation points Volunteer Moderator
    2021-05-20T17:34:53+00:00

    Again, you do not use the Linked Table Manager to set up the initial links. That doesn't allow you to change the "Save Password" option.

    In your case, use the other path as shown in your screenshot. It is slightly different from the A 2019 ribbon. When the wizard opens, follow it to create NEW links to the tables and be sure to select the "Save Password" option.

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. George Hepworth 22,300 Reputation points Volunteer Moderator
    2021-05-19T12:13:06+00:00

    There are a few relevant things here. The DSN includes the UID and PWD, but then it specifies Trusted_Connection=Yes, which would mean that you want to use Windows Authentication. Change that to Trusted_Connection=No

    The underlying question is how the SQL Server has been configured. If it accepts both Windows and SQL Server Authentication, you should be able to connect with a valid SS UID and PWD. I assume that to be the case given the description provided.

    Also, you are using the default driver, SQL Server. While that one is still viable, there are newer Drivers. Depending on your infrastructure and support, you might want to install and use one, such as ODBC Driver 17 for SQL Server. Before you do that, check with the DBA to see if that's going to be workable in your organization. Newer drivers do have better handling for some of the newer datatypes now available in SQL Server, so if it is feasible, it's probably worthwhile to upgrade the driver you use.

    1 person found this answer helpful.
    0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-19T22:55:41+00:00

    There are a few relevant things here. The DSN includes the UID and PWD, but then it specifies Trusted_Connection=Yes, which would mean that you want to use Windows Authentication. Change that to Trusted_Connection=No

    The underlying question is how the SQL Server has been configured. If it accepts both Windows and SQL Server Authentication, you should be able to connect with a valid SS UID and PWD. I assume that to be the case given the description provided.

    Also, you are using the default driver, SQL Server. While that one is still viable, there are newer Drivers. Depending on your infrastructure and support, you might want to install and use one, such as ODBC Driver 17 for SQL Server. Before you do that, check with the DBA to see if that's going to be workable in your organization. Newer drivers do have better handling for some of the newer datatypes now available in SQL Server, so if it is feasible, it's probably worthwhile to upgrade the driver you use.

    hi

    I was able to edit the DNS file successfully

    Connection from Access to SQL table without user request and password

    Trusted_Connection = No

    Thanks

    But the problem is that when exiting Access and re-entering there is a password requirement

    Is there an Anna script that solves the problem?

    0 comments
  2. George Hepworth 22,300 Reputation points Volunteer Moderator
    2021-05-20T00:36:00+00:00

    You have the option to save the Password when you first link the tables in the SQL Server, so that's the way I'd do this. Delete the existing links and then relink them, making sure you check this option:

    0 comments
  3. Anonymous
    2021-05-20T04:40:57+00:00

    You have the option to save the Password when you first link the tables in the SQL Server, so that's the way I'd do this. Delete the existing links and then relink them, making sure you check this option:

    Image

    HI

    I use Access 2016 and Access 2019

    I did not see a window like it showed "LINK TABLES"

    Access 2016 and Access 2019 have a different window?

    Attaches the Access 2016 screen

    Where do I find a screen like the one you presented?

    Image

    0 comments