I have seen a few post on this before. If you use SQL Server user and
password in Data Source connection string, the password is never returned to
the AMO client. If you're doing any updates to the cube (and call AMO Update
method) the subsequent Process method will fail because the cube was updated
with an incomplete connection string.

Is there any way out of this mess? Are we forced to use Windows
authentication to perform this kind of operations? Did Microsoft overdo
something with security here?

We have an application that updates cube metadata programmatically and
cannot prompt the user for the password.

Stan

cube metadata vs access to source data are 2 differents things
when you say "We have an application that updates cube metadata
programmatically"
do you talk about an application that add dimension, cubes, measures etc...
in an OLAP Database?
This type of access only use Windows authentication, there is no SQL Server
login because its SSAS not SQL Server.

Can you describe what is your issue, where and when you suffer a limitation?


"Stan Kondrat" <StanKondrat (AT) discussions (DOT) microsoft.com> wrote

As an example, an attribute can be added to a dimension:
Dimension dim;
DimensionAttribute attr;
dim = db.Dimensions.GetByName("Product");
attr = dim.Attributes.Add("Weight");
attr.KeyColumns.Add(CreateDataItem(db.DataSourceVi ews[0], "DimProduct",
"Weight"));
attr.AttributeHierarchyEnabled = false;
db.Update(UpdateOption.ExpandFull);
db.Process(ProcessType.ProcessFull);

if you have Connection to the datasource as the following using SQL
Server Audentication:
db.DataSources(0).ConnectionString "Provider=SQLNCLI.1;Data
Source=MyServerNamedInstance;Persist Security Info=True;User
ID=sa;Initial Catalog=AdventureWorksDW" String

The following exception will be returned trying to process the
database:
+ ex {"OLE DB error: OLE DB or ODBC error: Login failed for user
'sa'.; 28000.
' Errors in the high-level relational engine. A connection
could not be made to the data source with the DataSourceID of
'AdventureWorksDW', Name of 'AdventureWorksDW'.
' Errors in the OLAP storage engine: An error occurred while
the dimension, with the ID of 'Product', Name of 'Product' was being
processed.
' Errors in the OLAP storage engine: An error occurred while
the 'Category' attribute of the 'Product' dimension from the
'AmoAdventureWorks' database was being processed.
' OLE DB error: OLE DB or ODBC error: Login failed for user
'sa'.; 28000.
' Errors in the high-level relational engine. A connection
could not be made to the data source with the DataSourceID of
'AdventureWorksDW', Name of 'AdventureWorksDW'.
' Errors in the OLAP storage engine: An error occurred while
the dimension, with the ID of 'Product', Name of 'Product' was being
processed.
' Errors in the OLAP storage engine: An error occurred while
the 'Product Line' attribute of the 'Product' dimension from the
'AmoAdventureWorks' database was being processed.
' OLE DB error: OLE DB or ODBC error: Login failed for user
'sa'.; 28000.
' Errors in the high-level relational engine. A connection
could not be made to the data source with the DataSourceID of
'AdventureWorksDW', Name of 'AdventureWorksDW'.
' Errors in the OLAP storage engine: An error occurred while
the dimension, with the ID of 'Product', Name of 'Product' was being
processed.
' Errors in the OLAP storage engine: An error occurred while
the 'List Price' attribute of the 'Product' dimension from the
'AmoAdventureWorks' database was being processed.
' "} System.Exception




Jeje wrote:

so, why you don't use the NT authentication method but based on the service
account (not the user account or a SQL login)?


<aabdel1 (AT) gmail (DOT) com> wrote

Because we do not want to force the client to use Windows
Authentication.

Abdel



Jéjé wrote:

well...
create an SQL login without a password on the server and use it to access
the database instead of a secured access.
this is not safe but could easely solve the issue.

I don't understand why you don't want to force the client to use Windows
Authentication while its required to access the AS server! (so you currently
use this windows authenticaion)
your users must have administrator access to the AS database to do these
changes, so you are able to give them access to the source database too
using the windows authentication.

but I hope this workaround will help you.

<aabdel1 (AT) gmail (DOT) com> wrote

Because we do not want to force the client to use Windows
Authentication.

Abdel



Jéjé wrote:

We have cases where the AS server and the Data Source server reside on 2
different machines. The data source is properly secured using SQL Server
login and no serious client will accept the login with no password.

In some cases drillthrough is required from the web application. Using the
windows authetication for the data source is not the preferred solution in
that case as it gives the anonymous users access to the data source outside
the context of the drillthrough data.

This is a serious limitation. Are there any plans to fix this problem?

Stan

"Jéjé" wrote: