Make SQL Server linked table more secure

Ben Clothier, Access MVP, shares a great tip on Cached ODBC connection in Access, which helps to avoid storing login information in linked tables and pass-through queries. This tip is quite useful for SQL Azure, where you can't use Windows security and have to supply login and password!

There is an interesting behavior in Access we want to take advantage of. When Access opens an ODBC connection, it caches that connection. Any subsequent ODBC objects that happen to match on three parameters—ODBC driver, server, and database—will reuse that cached connection. This means we don’t have to specify the full connection string for all ODBC objects each time. We only need to supply the complete connection string once at startup and store only the incomplete connection string. We can then leave it up to Access to match subsequent ODBC objects to that cached connection string. This helps immensely in simplifying the security setup.