Linked SQL Server tables ask for password every time 1

[KyleS]

Well, it's been a while but I've got a question I just can't answer (even with searching this site, Google and msdn)

I have an Access 97 dB (well, about 30 dBs actually) that I didn't write (pretty standard story really) which is connected via DSN to an AS400. That link works great. I need to remove those and hook them up to the corresponding tables in our SQL Server 2000 instead. This is simple to do intitally. My problem is, the next time the dB is opened, it's asking the user for their SQL Server name/pwd.

I've tried hardcoding the userID/pwd into the file DSN, no dice. I've even tried to reattach the AS400 tables (just for grins) and I still have the problem of it asking for my pwd every time (which it doesn't do on the established links. The person who set this up is gone (of course) and was apparently deathly allergic to documentation...

Is there some trick I'm missing to getting the database to read the file DSN, or some other way to do this?

To answer, no, we're not using Windows authentication. Yes I know what it is, why it's good, why it's bad, and why it likes pink elephants. No, I can't just switch to pass through queries or do it all via code. Yes it's better, and eventually I will switch them all over. But I need to switch the tables now, not spend 3 weeks writing all these queries/code for all these databases, inevitably missing one or two and wreaking all sorts of havoc. I hate using DSNs to link to the server, but such is life. Until I have time to rewrite all thes dBs, I've gotta live with their design.

Thanks in advance for any and all suggestions...

Kyle

 

[PHV]

Did you tick the remember password checkbox when you linked the tables ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KyleS]

Thanks for the reply PHV.

Maybe I'm missing something (I've always used pass-through or DAO/ADO to connect tables so sadly most of the DSN stuff is new to me). When I go to File-->Get External Data--> Link Tables, then select ODBC Data Sources I get the standard "Select Data Source" window with options for File DSNs or Machine DSNs. Regardless of which one I choose, or if I create a new one of either I don't see a remember password checkbox.

I get a "Use Trusted Connection", but that's just Win Auth. Am I just not seeing it somehow? I get that checkbox when I create a UDL I see the checkbox, but now when it's a dsn. I've edited the DSN myself, adding my "pwd=<password>" and "Trusted_Connection=No" but to no avail...

Kyle

 

[PHV]

When the DSN is choosed a Link Tables popup appears and the check box should be in the bottom right.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KyleS]

Wow, I'm just all kinds of stupid today...

I spent all that time looking for the checkbox anywhere bu there. Thx for the quick answer PHV.

Kyle