How To Embed The Credentials Of SQL Server In An Access Form

[R Tanner]

Hi,

I'm building a form that connects to a SQL Server database and the
database requires a password, which I have incorporated in the data
source. Despite that, SQL Server asks for the user to login every
time the first form is brought up and the user attempts to select a
value from a drop down. How can I get around this? I need the
password to be explicit (not windows authentication) on the server
because I cannot setup subscriptions in SSRS otherwise. I imagine I
would somehow need to pass the credentials programmatically to SQL
Server? This is probably much easier done in C#, but I don't want to
build this form in C# right away.

Does anyone have any experience with this? Thanks in advance.

 

[Albert D. Kallal]

When you setup the linked table, it has a check box to "save" the password
if you using passwords in place of windows authentication....

It's not clear if you try to eliminate all password prompt for the user, or
just want the user to enter the password prompt once?

Since each user has their own front end, I would like a considering
re-linking the tables (and any pass-though query) using a password that a
user enters at startup. some examples of an dsn less connection can be found
here:

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


So the answer here is to simply link the tables to the sql back end and make
sure you check the box to remember the password. It is just not clear if you
going to be prompting uses for a password at one given point in time or want
to elimonate this process altogether.

 

[R Tanner]

When you setup the linked table, it has a check box to "save" the password
if you using passwords in place of windows authentication....

It's not clear if you try to eliminate all password prompt for the user, or
just want the user to enter the password prompt once?

Since each user has their own front end, I would like a considering
re-linking the tables (and any pass-though query) using a password that a
user enters at startup. some examples of an dsn less connection can be found
here:

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

So the answer here is to simply link the tables to the sql back end and make
sure you check the box to remember the password. It is just not clear if you
going to be prompting uses for a password at one given point in time or want
to elimonate this process altogether.

No. There will only be one user accessing this application. I have
another windows form I will be programming that will have multiple
users...I plan on pulling the unique identity out of the system thread
and not requiring the user to login. I know who my user will be for
the app we are talking about. Ideally, I won't want him to have to
sign in at all. He has a unique Login which I have created on the
server. I used that Login in the data source when I created the
linked table. I swear I checked to remember the password, but maybe
not. I will post back after I have re-created the data source and re
imported the database tables.

 

[R Tanner]

No.  There will only be one user accessing this application.  I have
another windows form I will be programming that will have multiple
users...I plan on pulling the unique identity out of the system thread
and not requiring the user to login.  I know who my user will be for
the app we are talking about.  Ideally, I won't want him to have to
sign in at all.  He has a unique Login which I have created on the
server.  I used that Login in the data source when I created the
linked table.  I swear I checked to remember the password, but maybe
not.  I will post back after I have re-created the data source and re
imported the database tables.

Okay I recreated the data source and associated specific credentials
with the Login I am using in SQL and either/or fixed the problem.
Thankx