multiple odbc login screens

[jernigam]

We are using Access 2003.

We have a couple of users that share an Access database that utilizes
linked tables to a SQL server database using ODBC. For one user,
every time he opens a linked table or query accessing a linked table,
it will prompt him for the SQL Server password multiple times. The
other users only get prompted once. Then they can open any other
table without a password prompt.

Any ideas on why one user gets multiple password prompts?

I prefer to not setup the table with the stored password. The user
should have to enter it once.

 

[Armen Stein]

We are using Access 2003.

We have a couple of users that share an Access database that utilizes
linked tables to a SQL server database using ODBC. For one user,
every time he opens a linked table or query accessing a linked table,
it will prompt him for the SQL Server password multiple times. The
other users only get prompted once. Then they can open any other
table without a password prompt.

Any ideas on why one user gets multiple password prompts?

I prefer to not setup the table with the stored password. The user
should have to enter it once.

Is is possible that the first user is leaving the first table open
while opening subsequent tables? Access will utilize an already-open
connection and not need to prompt for credentials again.

In our Access-SQL applications, we open a DAO recordset in code when
the app opens, supplying the user name and password from a form or
local table. The recordset object is set to Global and we don't close
it until the application is closed. This avoids all subsequent
prompting.

Also, in the table ODBC linking dialog there's an option to save user
name and password. Perhaps one user did and the other didn't? I've
seen that even with the "save" setting, the user may still be prompted
once.

One more thing - have you looked into using Windows Authentication
instead of SQL Authentication? This would avoid all prompting and
storing of the the credentials.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[jernigam]

It doesn't matter if the other user has it open or not. Only one user
gets multiple password prompts. He will have to enter the password
10 times or more to open a small table. Everyone else only gets it
once, and then they can open any table without a prompt.

We prefer to not save the password, because it makes it visible in the
database.

The DAO option might work. I will have to look into it more.

We can't use Windows authentication. The database is on a remote
server. Someone else manages it and is not setup for Windows
authentication.

 

[Armen Stein]

It doesn't matter if the other user has it open or not. Only one user
gets multiple password prompts. He will have to enter the password
10 times or more to open a small table. Everyone else only gets it
once, and then they can open any table without a prompt.

Oh, multiple prompts on just ONE table? That is weird, not sure what
would cause that. Maybe a network connection instability issue? Maybe
someone else can comment.

We prefer to not save the password, because it makes it visible in the
database.

Access hides & encrypts the password when you choose the option to
save it. It might not be perfect security for a determined hacker, as
it must be in there somewhere, but it isn't visible in the table's
connection string.

What I would really like, and I've asked the Access team for this, is
that Access would also hide & encrypt the password for passthrough
query connection strings.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[jernigam]

Yes it is multiple prompts for one table.

I finally gave up and relinked all the tables and stored the
password. However, I am able to view the password in the sysobjects
table.