ODBC Connection must be re-built each time

[domtrump]

I have several Power Pivot workbooks that use tables connected by ODBC. Every single time I open one, I get a connection failure message (see below). I have to go into design mode and REBUILD the connection string every time. Then it works for the rest of the day, but the following day it fails again. The problem appears to be that it stops prompting me for my password. In my ODBC set-up (I am connecting to an IBM iSeries), I have it set to PROMPT for SQL connect. However, I get the error message in Power Pivot every time. I do not have a problem with this same connection in MS Access or with tables linked directly to Excel - it is just with Power Pivot tables.

Error Message:

"The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source."

More Details:

OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed; IM006; [IBM][System i Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user DMARIANO on system RACSHOWCASE is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 10.13.3.22; 28000.

A connection could not be made to the data source with the DataSourceID of 'b5c97f88-42de-4279-ad69-bfce6e78bbc2', Name of 'SHOWCASE'.

An error occurred while processing table 'TOPTABLE15'.

The current operation was cancelled because another operation in the transaction failed.

I'm also including my ODBC connection string. Is there something I can add to the connection string to force it to prompt me for a password? If anyone can point me in the right direction to resolve this, I'd appreciate it. Thanks.

Connection String:

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=RACSHOWCASECA;SYSTEM=RACSHOWCASE;UID=DMARIANO;DBQ=,CASDTA31 SMSDTA31 SHOWCASE DW_USERS CMMODOBJ;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;TRANSLATE=1;SIGNON=1;"

 

[domtrump]

Could someone point me in the right direction to get this resolved? Is there another forum that is more appropriate for this issue? I chose this forum because, as I stated, it is only Power Pivot that demonstrates this problem with the same connection. I'd like to roll these reports out to some other users, but they would not be savvy enough to rebuild ODBC connections every time the data needs refreshing (nor should they be). Any help or suggestions would be appreciated. Thank you.

-Dom