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

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

Continued Success with your project.