SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Can SQL Server Authetication credentials be cached
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikePal
Starting Member

1 Posts

Posted - 02/11/2013 :  11:50:35  Show Profile  Reply with Quote
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
.
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000