Datasource to AnalysisServices

I have a question regarding creating a datasource that connects to an
Analysis Services Database. (AS and RS are both 2005 editions.)
If I create a (shared) datasource to an Analysis server, and Specify the IP
address (10.200.23.24) of that server, I have no problems.
However, if I want to connect via HTTP, I am having a few problems.
-In the connection properties, I specify the Server Name to the HTTP url
(http://10.200.23.24/OLAP/msmdpump.dll)
-I specify a username and password with permissions to see the cubes and
data.
-I am able to select the database name from the dropdown list. (So,
obviously it is hitting the AS server, and the AS server is sending back the
databases in which I have permission to see.)
-If I select test connection, it initially fails, but if I go to the
advanced options, and remove the SSPI, test connection succeeds.
-Press OK to save the datasource, and run (preview) the report.
When I go to run (preview) the report, I get the following error:
"Non-Windows user credentials were supplied for a non-http connection to
Analysis Services. The Microsoft SQL Server Analysis Services data extension
requires Windows Integrated Security unless connecting via http or https"
I don't understand why I am getting that error when I am connecting via
http. Is there another property somewhere I have to turn on, so that it
knows I am using http?
As a side note, I know the HTTP connection works, as I can connect via HTTP
with both proclarity, and management studio. The problem only seems to be in
reporting services.
Any ideas?
-RyanHello Ryan,
What credetial mode you chose in the shared data source?
And have you follow this article to configure the HTTP access to AS 2005?
Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft
Windows Server 2003
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||The credentials are set at "Use a specific username and password".
I have the username of a local machine account on the server, and it's
password.
I've also tried the username in the format of MACHINENAME\UserName. That
didn't work either.
I know the http is set up properly, as I can access the cubes on that server
via HTTP with proclarity, and management studio, so I do not believe that is
the issue.
Does it have anything to do with the fact the report was designed with the
datasource pointing to a direct tcp connection, and then wanting to change
it to HTTP after the fact?
-Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:rvJmMbUNHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> What credetial mode you chose in the shared data source?
> And have you follow this article to configure the HTTP access to AS 2005?
> Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft
> Windows Server 2003
> http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Here's a little bit more information:
The "Type" of the datasource is set as "Microsoft SQL Server Analysis
Services" . This is where I am having the issue where it works fine with tcp
connection, but HTTP connection gives the error.
If I change the type to OLEDB, and build a connection string to connect to
my cubes, when I preview the report, I get no errors.
However, nothing shows up for any of the queries. Warnings that say:
"The data set 'DatasetName' contains a definition for the Field 'FieldName'.
The field is missing from the returned resultset from the datasource."
Followed by:
"The data set 'DatasetName' contains a definition for the Field 'FieldName'.
The data extension returned an error during the reading of the field. THere
is no data for the field at position 5."
Further, I watched the profiler in analysis services, and it looks like the
right queries are being executed.
My questions are:
1. Do I have to use an OLEDB connection to use HTTP?
-If so, how do I get rid of the above errors? (Are they due to the fact
the datasets were designed with the analysis services connection type, then
switched to oledb?)
2. If I can use HTTP on the direct "Microsoft SQL Server Analysis Services"
connection type, is there anything else (advanced properties) that needs to
be set to make it work?
3. Has anyone successfully created a report using TCP on the direct
provider, then tried switching it to HTTP after the report was complete?
Thanks,
-Ryan|||Hello Ryan,
The reason why you could not connect to the Analysis Service Via http in
Reporting Services is because of the credential.
If you have enabled the Integrated Authentication in IIS, you need to use
the domain account. Or you need to specify a same account in the server as
the one you use in the local machine.
Please try this and let me know if this could not resolve your issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Datareader Source connects to no longer existing ODBC-link

I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).

Hi Johndahl,

A quick question -- when you refer to "changed mappings", are you referring to the connection string used to connect to your Access database, or to column metadata (column names, data types, etc.)? If the latter, you'll want to fix (or even delete and recreate) your source adapters in your data flow components, as that is where column and table metadata are stored.

Also, slightly unrelated to this question, but I wonder if you've considered using the OLE DB Connection Manager/Source to connect to your Access database instead of ADO.Net through the ODBC bridge. The OLE DB provider for Jet should give you better performance and datatype fidelity overall.

-David

|||

Hi David

Thanks for the reply.

The changed mapping refers to the use of another Access-database and a changed network drive mapping. When I setup the connection and test it, the connection tests ok. However when retrieving data (whether in Server Explorer or DataReader Source) it returns an error message: refering to the database-connection no longer in use.

Furthermore, as you suggested, I tried the use of OLEDB but here also I get an error message when trying to retrieve data: "Object reference not set to an instance of an object". Again the connection tests ok.

John

|||

It would help if you could describe step-by-step how you setup your connections and source adapters.

Thanks,

Bob

|||

First the ODBC was setup on the server (System DSN). Then in SSIS a connection was established using the Connection Manager. Provider is the ADO.NET for ODBC. The DSN which was setup in the ODBC Administration is chosen en tested if it can connect. Tests are always succesfull. Also setup is a connection in the Server Explorer.

For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.

However then the network drive mapping and the name of the Access-database changed (the old network drive mapping was deleted !!!). This change was executed in de ODBC Administration using the same DSN. Expected was that this change was enough for SSIS to connect to the correct, new database. However then the troubles began...

Testing the connections resulted in ok results. However upon collecting data through the datareader source we received HY024 errors stating that a connection using the OLD network drive mapping and database name could no longer be established. The question here is of course how it still knows what this old network drive mapping and database name is because the only reference to it (ODBC) is no longer available.

Afterwards we setup a new DSN in ODBC, we worked in a new package and even started a new project. In all cases the problem endured. It looks like SSIS/Visual Studio has somehow, somewhere saved the old network drive mapping and database name and is using it as default ODBC connection not using the definition of the DSN setup in ODBC Administration.

Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?

|||

Could you check the connection string assigned to your ADO.NET connection manager?

I guess the server explorer does some caching. Here is something that might help clean it out:

when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.

HTH,

Bob

|||

I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:

- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

Both the dbq as the systemdb point to the correct paths and databases. Testing the connection results in an OK-status. However when accessing the tables in Server Explorer an error message is generated:

"SQL Execution Error

Executed SQL Statement: SELECT * from table

Error Source: odbcjt32.dll

Error Message: ERROR [HY024][Microsoft][ODBC Microsoft Access Driver] 'H:\path\previousdb.mdb' is not a valid path. Make sure that the correct pathname is spelled correctly and that you are connected to the server on which the file resides"

In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?

|||

Probably cached in the server explorer. Try deleting them and creating new ones.

When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.

HTH,

Bob

Datareader Source connects to no longer existing ODBC-link

I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).

Hi Johndahl,

A quick question -- when you refer to "changed mappings", are you referring to the connection string used to connect to your Access database, or to column metadata (column names, data types, etc.)? If the latter, you'll want to fix (or even delete and recreate) your source adapters in your data flow components, as that is where column and table metadata are stored.

Also, slightly unrelated to this question, but I wonder if you've considered using the OLE DB Connection Manager/Source to connect to your Access database instead of ADO.Net through the ODBC bridge. The OLE DB provider for Jet should give you better performance and datatype fidelity overall.

-David

|||

Hi David

Thanks for the reply.

The changed mapping refers to the use of another Access-database and a changed network drive mapping. When I setup the connection and test it, the connection tests ok. However when retrieving data (whether in Server Explorer or DataReader Source) it returns an error message: refering to the database-connection no longer in use.

Furthermore, as you suggested, I tried the use of OLEDB but here also I get an error message when trying to retrieve data: "Object reference not set to an instance of an object". Again the connection tests ok.

John

|||

It would help if you could describe step-by-step how you setup your connections and source adapters.

Thanks,

Bob

|||

First the ODBC was setup on the server (System DSN). Then in SSIS a connection was established using the Connection Manager. Provider is the ADO.NET for ODBC. The DSN which was setup in the ODBC Administration is chosen en tested if it can connect. Tests are always succesfull. Also setup is a connection in the Server Explorer.

For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.

However then the network drive mapping and the name of the Access-database changed (the old network drive mapping was deleted !!!). This change was executed in de ODBC Administration using the same DSN. Expected was that this change was enough for SSIS to connect to the correct, new database. However then the troubles began...

Testing the connections resulted in ok results. However upon collecting data through the datareader source we received HY024 errors stating that a connection using the OLD network drive mapping and database name could no longer be established. The question here is of course how it still knows what this old network drive mapping and database name is because the only reference to it (ODBC) is no longer available.

Afterwards we setup a new DSN in ODBC, we worked in a new package and even started a new project. In all cases the problem endured. It looks like SSIS/Visual Studio has somehow, somewhere saved the old network drive mapping and database name and is using it as default ODBC connection not using the definition of the DSN setup in ODBC Administration.

Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?

|||

Could you check the connection string assigned to your ADO.NET connection manager?

I guess the server explorer does some caching. Here is something that might help clean it out:

when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.

HTH,

Bob

|||

I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:

- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

Both the dbq as the systemdb point to the correct paths and databases. Testing the connection results in an OK-status. However when accessing the tables in Server Explorer an error message is generated:

"SQL Execution Error

Executed SQL Statement: SELECT * from table

Error Source: odbcjt32.dll

Error Message: ERROR [HY024][Microsoft][ODBC Microsoft Access Driver] 'H:\path\previousdb.mdb' is not a valid path. Make sure that the correct pathname is spelled correctly and that you are connected to the server on which the file resides"

In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?

|||

Probably cached in the server explorer. Try deleting them and creating new ones.

When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.

HTH,

Bob