OPENROWSET & SQL Server 2008 Security (SSAS)

Question

Hi!

I try to fill a SSAS Mining Model with Data from a (local) SQL Server Table

Both SQL Server and SSAS (all 2008) are on the same (devlopment machine) WinXP64. SQL Server Security is 'Windows Authentication'

MDX Code:

INSERT INTO MINING STRUCTURE [ttseries]
(
[col1],
[col2],
[col3],
[col4]
)
 OPENROWSET(
'SQLOLEDB',
'Server=(local);Trusted_Connection=YES',
'SELECT col1, col2, col3, col4 FROM  [MYDB].[dbo].[MYTable]')

If I execute the statement I recieve the error message:

Error (Data mining): Ad hoc connections as specified in OPENROWSET clauses cannot be used on this server.

I already adjusted the SQL Server Setting for Key

"Ad Hoc Distributed Queries" to 1

-> this was one of the common problems I found during my research, but it didn't help.

What else do I have to change or setup?

Thanks for your help! alex

Answer 1

I figured it out

In SSAS you have to set "DataMining\AllowedProvidersInOpenRowset" to "SQLOLEDB"

and it works...

cheers