Timberline ODBC Error with SQL Server Linked Server: Cannot initialize the data source object

Question

Just to lay the ground work:

• On a 64bit Windows server we have a 64bit install of SQL Server 2008 R2
• We installed a 32bit version of SQL Server Express on the same machine

• Using the 32bit Express version, we successfully created a linked server to a Timberline data store as follows:

  EXEC sp_addlinkedserver
       @server = 'TimberlineTest',
       @provider = 'MSDASQL',
       @srvproduct='Timberline Data',
       @datasrc = 'TimberlineDSN'
  

This then returns all of the tables:

exec sp_tables_ex 'TimberlineTest'

We also created a user as follows:

EXEC sp_addlinkedsrvlogin
 @rmtsrvname = 'TimberlineTest',
 @useself = 'False',
 @rmtuser = 'sa',
 @rmtpassword = 'xxxxxx'
GO

Also, within a SSMS query window, this works great when run against the Master db:

select * from timberlinetest.[c:\Training\Extended]..Master_PRM_Employee

Everything works just fine so long as we execute the queries from within SSMS. However, when we fire up any sort of third party tool that might try to access that linked server, we start running into trouble. We have a little query testing tool that allows you to enter a connection string and run queries. We're using this connection string to first get to the Master db:

Provider=SQLOLEDB; Data Source=localhost\SQLEXPRESS32BIT; Libraries=Master; User ID=sa; Password=xxxxxx

That connection works, and we can ping the server and query the Master db. But when we try the same Timberline query (select * from timberlinetest....) we run into this error:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "timberline test". OLE DB provider "MSDASQL" for linked server "timberline test" returned message "[Sage Timberline Office][Sage Timberline Office ODBCDriver][DRM File Library] Invalid account name."

A couple of things to note... for testing purposes:

• On the directory c:\Training\Extended we have opened security wide up, setting Everyone to full read-write permissions
• Within Timberline itself, we disabled security (Tools > Security Admin > Security Settings > Unchecked "Turn On Application Security")

I'm not sure what else to do here. Any advice would be great.

Answer 1

(Answered in a question edit. Converted to a community wiki answer. See Question with no answers, but issue solved in the comments (or extended in chat) )

The OP wrote:

Problem solved.

I changed the connection string to:

Provider=SQLOLEDB; Data Source=.\sqlexpress32bit; Libraries=master; Trusted_Connection=Yes

And that worked!!