Connect MS Access ADP to MS SQL Server 2005 with Citrix

I have a MS Access ADP, and have connected it to a MS SQL Server 2005 database.
Every time the user restarts MS Access he needs to reconnect to MS SQL Server.
Can this connection process not be automated? So that every time the connection info is stored?

Frans_TruyensAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Frans_TruyensAuthor Commented:
When I connect to MS SQL Server, I always get a message. It tells that the password of the sa is not encrypted before saving into the file. Attached you will find a printscreen in Dutch
ErrorMessage.docx
0
als315Commented:
What kind of autentification are you using? Windows or integrated?
Here are examples of connection strings:
http://www.connectionstrings.com/sql-server-2005
0
Frans_TruyensAuthor Commented:
We are using SQL Server logins. We are using the sa account with a password. We aren't using connectionstrings at all.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

als315Commented:
Connection string is used, but it is hidden in connection master. You can see all parameters in All Tab.
Description of parameters is here:
http://www.connectionstrings.com/Articles/Show/all-sql-server-connection-string-keywords

0
Frans_TruyensAuthor Commented:
The connection string is OK. But I always have to re-save it. Then it works. But every time I have to click on that option again.
0
BitsqueezerCommented:
Hi,

in an ADP you have server properties where you enter the connection information to the SQL server. In this dialog you will find a checkbox for "allow saving password", if you check this and click "OK" the connection information will be saved permanently (you will again get a warning that the password is not encrypted and so on but only once).

But you should NEVER EVER use the "sa" account to be saved in a frontend! This is the absolute highest security level which can perform ANY operation on the SQL Server so this one should ONLY be used for administration purposes on SQL Server directly. If you want to get all needed rights to the frontend, create a new SQL Server user and give him the right "dbo" for database owner, that's more than enough for developing the frontend. If you deploy your database later to the users, change it again to a user which only has read or read and write rights on the database (depending on what the frontend should be able to do on the database).

Cheers,

Christian
0
Frans_TruyensAuthor Commented:
That is the problem. It does not save the password. Everytime you restart the ADP via Citrix, you have to save the password again. It is more a CITRIX problem than a MS Access problem.
0
BitsqueezerCommented:
Hi,

I'm not really sure how it works in Citrix but the things should get saved into the Access ADP file itself. So the question is if working inside the Citrix environment makes it possible to save into the ADP file or maybe the file is being restored between two sessions. If that is the case maybe the saving of the password must be done with an account which has the right to save anything or the origin of the file which is restored back must be changed before restore.

Cheers,

Christian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frans_TruyensAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.