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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
als315Commented:
What kind of autentification are you using? Windows or integrated?
Here are examples of connection strings:
http://www.connectionstrings.com/sql-server-2005
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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

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.
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
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.
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

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
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.