MS ACCESS connect to SQL server BE: Authentication Prompt every time I attempt to

[adewale4favour]

Hi Programmers,

I will really appreciate some ideas that can help here; I have created an ACCESS project, with BE to SQL server on a live host. I was able to establish an ODBC connection to the Server, and had my linked tables migrated successfully, however, I do have issues with the Authentication. I have an sa account set up on the sql server, and connected through the ODBC, but each time I tried to Load the application, it will prompt me for that sa credentials before establishing connection.

I am trying to figure this out through the VBA, DAO object, but still not working. Any idea pls

 

[pbaldy]

I've been using DSN-less linked tables for awhile, but if memory serves when you link a table via the ODBC data sources, there's a checkbox somewhere along the line that lets you save the password.

 

[GPGeorge]

Hi Programmers,

I will really appreciate some ideas that can help here; I have created an ACCESS project, with BE to SQL server on a live host. I was able to establish an ODBC connection to the Server, and had my linked tables migrated successfully, however, I do have issues with the Authentication. I have an sa account set up on the sql server, and connected through the ODBC, but each time I tried to Load the application, it will prompt me for that sa credentials before establishing connection.

I am trying to figure this out through the VBA, DAO object, but still not working. Any idea pls

Here's the checkbox Paul mentioned. You'd have to delete existing links and recreate them using this dialog, checking the Save password option during that relinking.

Keep in mind that when you save the password, it's stored in the accdb in plain text.

 

[Josef P.]

[Slightly off topic]
Note: The sa password is saved directly in the application or unlocked with it ... the cracker's heart laughs.
Windows authentication is not wanted?

 

[GPGeorge]

[Slightly off topic]
Note: The sa password is saved directly in the application or unlocked with it ... the cracker's heart laughs.
Windows authentication is not wanted?

Agreed. Windows Auth is generally better. However, there are times, such as working with a remotely hosted SQL Server of Azure SQL, when that's not an option.