ERROR MESSAGES ON THE WORKSTATION:
OLE DB provider "MSDASQL" for linked server "QUICKBOOKS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "QUICKBOOKS" returned message "[QODBC] QB Open Connection Failed. Create DCOM Instance Error = 8000401a, Unknown error.. Created qbXMLRP2 OK.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "QUICKBOOKS".
OTHER INFORMATION
Using QODBC server edition in SQL Server 2005 with a linked database to QuckBooks Pro data located on a different server. QuickBooks Pro is installed and open/running on the server under the Administrator's login. SQL queries against QuickBooks data on the server work as expected.
However, on the workstation, when using either SQL Server Management Studio or Excel via SQL Native ODBC drivers, receive the error "Cannot initialize the data source...." as shown above UNLESS a remote desktop connection is open to the SQL server while the query is being run.
I used the automatic DCOM configuration tools, and I have also manually verified the DCOM permissions: INTERACTIVE, IUSR, REMOTE INTERACTIVE and even the two QuickBooks users are added with ability to launch and access locally and remotely, even though that shouldn't be necessary - I tried it anyway.
I have also verified all the same users have permission to read/write to the QODBC program location and the fixed location for the Optimizer file.
When the remote desktop session to the server is open - nothing is running on that desktop session except the QuickBooks Pro program itself. Also, the desktop can be locked while the remote desktop session is open, and the queries will still work. It's only when the session is disconnected that the error message returns. When logged into remote desktop to the server, I am logged in as the Administrator. But, when I run the queries on the workstation I use SQL Security in an effort to make sure I am not connecting to the database as the server Administrator unintentionally.
I would appreciate any suggestions at all. I find this very odd.
