more techie travails

So, after working a whole bunch of hours yesterday, did I take the evening off and relax? Of course not.

After dinner, I went down to the computer dungeon and tried 'one last thing', and disaster ensued.

I have an Access app that links to another Access database (trivial) but it also uploads data to a SQL Server database. I am using SQL Server Express on my development PC, and it works great.

However, I am using Windows Authentication, so no Userid/Password is required for the data access - it just works. In the morning's conference call, one of the attendees envisioned a scenario where the app would need to connect to a SQL Server database over the internet, requiring Userid/Password authentication.

Fair enough, I thought it would be 'interesting' to create a 2nd System ODBC DSN for the same local database that I'm already testing with, but this time specifying password authentication, just to see what would happen. Not only did it not work, but, suddenly, my entire SQL Server instance was throwing error messages and the original stuff was no longer working. In fact, SQL Server Management Studio could no longer connect to SQL Server.

YIKES!!!!!!! I broke it.

Believe me, ladies and germs, when I tell you that I spent the next 4 hours screaming (silently) as I tried everything I could think of, uninstalling, downloading fresh install packages, installing them, failing, uninstalling them, etc. In utter defeat and total failure, I shut the computer down and went to bed.

This morning, as I awoke from uneasy dreams, I did not find myself transformed into a giant cockroach, thank goodness, but I did come downstairs, try again, hit the Google, and found one guy on one site that recommended checking whether TCPIP was enabled within the SQL Server configuration. Huh?

Needless to say, that worked, but my databases were not showing up. Another 15 minutes of messing around and, lo and behold, everything got restored, and I am now back where I was at 6 pm last night, when I prepared to 'try one little thing to see if it works'.

Is there a lesson in all this? Probably not.

In the meantime, does anyone have any experience writing an Access app that connects to a SQL Server database that was set up with 'Mixed Mode' authentication? Anyone? Hello?

The fun never ends.

UPDATE:  In case you are unsure exactly what 'travails' means, here is the precise definition:

Definition of TRAVAIL. 1. a : work especially of a painful or laborious nature : toil b : a physical or mental exertion or piece of work : task, effort c : agony, torment ...