Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  Can SQL Server Authetication credentials be cached

Author  Topic  MikePal Starting Member 1 Post Posted - 2013-02-11 : 11:50:35 Sorry for the long description here, but I want to describe the problem completely. We use Lawson ERP software that’s hosted by a third party, and we are changing hosting services, which means migrating from IBM DB2 on iSeries AS400 to SQL Server 2008 on Windows Server 2008 R2. Because of contractual restrictions, the SQL Server cannot be joined to our local AD. Also, it is not in a domain, so no cross domain trust can be configured. (Even if it could, contracts would prevent it.) So it’s a given that we must use SQL Server Authentication for ODBC connections. Users are set up with SQL server logins, but do not have named Windows accounts on the SQL Server. Users make ODBC connections in MS Access using linked tables and pass through queries, as well as in Excel with ODBC data connections. IBM provides Client Access software that’s installed on all user PCs which includes among other things, ODBC drivers to connect to DB2. It also caches user credentials, so that when the make the first ODBC connection after logging on to Windows (7 Pro in our case), they are not prompted to authenticate for ODBC access again until they restart or log out and back in to Windows. With SQL Server Authentication, we find that users must authenticate in SQL Server every time they make the first ODBC connection to the SQL Server after opening an Access database or Excel workbook. E.g., if I open an Access database, the first time I open a linked table, I must authenticate. After that, I can open linked tables and run queries without authenticating until I close that Access database. The next time it’s opened, I must authenticate on the first connection again. It’s worse with Excel queries, which users embed in workbooks. If I open a workbook with embedded queries and “Refresh all”, I have to authenticate once per connection in the workbook. After that, they can be refreshed without authenticating until the workbook is closed. There are multiple authentications needed add, update, and save queries. This behavior is the same with both file and system DSNs. My users do a lot of playing with data pulled from SQL Serve with Access and Excel, and some of the financial analysis workbooks have ten or more connections. They are getting annoyed at all the authentication requests. I’ve been searching for a way to cache their SQL Server credentials locally after the first authentication, as was done by the iSeries, so they’d only need to authenticate once per Window session, but I haven’t found anything helpful. Can this be done? If not, is there some “out of the box” thinking that I’m missing? Thanks, Mike .