Processing a dimension using AMO does not work if the connection to the “DataSource” used is

Processing a dimension using AMO does not work if the connection to the “DataSource” used is “SQL Server Authentication”

amoDimension.Process(ProcessType.ProcessFull)

The following exception is returned:

- ex {"OLE DB error: OLE DB or ODBC error: Login failed for user ' MyUserId '.; 42000.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'MyDataBase', Name of ' MyDataBase'.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Products', Name of 'ProductDim' was being processed.

Errors in the OLAP storage engine: An error occurred while the 'Products' attribute of the 'ProductDim' dimension from the ' MyDataBase SQL SERVER' database was being processed.

"} System.Exception

The connection string to the “DataSource” is:

ConnectionString "Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase " String

ConnectionStringSecurity PasswordRemoved {1} Microsoft.AnalysisServices.ConnectionStringSecurity

Everything work properly is if I switch the connection type to “Windows Authentication”

Thanks,

Yones

Do you have a password in the connection string ? If you do, have you called dataSource.Update() or database.Update(UpdateOptions.ExpandFull) before the dimension.Process() ? Because the server will not return the passwords from connection strings to the client (AMO included). Instead, the server removes the password and sets the ConnectionStringSecurity property (of DataSource object) to PasswordRemoved. And the Update method will save the connection string with no password. The work-around is to avoid calling Update or to re-specify the password.

Adrian Dumitrascu

|||

Thank you very much Adrian.

It seems like the problem is happening because of database.Update(UpdateOptions.ExpandFull) but I need to called this method because I am adding new attributes to some dimensions.

Could you please tell me how can I re-specify the password?

Thanks,

Yones|||

> Could you please tell me how can I re-specify the password?

Set the DataSource.ConnectionString before the database.Update, specifying the password (and this is a problem because you don't want to put the password in clear text in the source code; is it acceptable to invoke UI to ask for the password ? or read it from a secure place). As a reminder, you need to worry about the password only if DataSource.ConnectionStringSecurity == PasswordRemoved.

Adrian Dumitrascu

|||

I am not sure if the password is removed just after calling dataSource.Update() or database.Update(UpdateOptions.ExpandFull) because I just did the following two lines of code and when I checked the password, it was already removed.

m_amoServer.Connect(i_strDataSource)

m_amoDataBase = m_amoServer.Databases.Item(i_strDatabase)

The result:

m_amoDataBase.DataSources (0)

ConnectionString"Provider=SQLOLEDB.1;Data Source=MyServer\SQLSERVER2005;User ID=MyUserId;Initial Catalog= MyDataBase "String

ConnectionStringSecurityPasswordRemoved {1}

Thanks Again,

Yones

|||

The server doesn't provide the password to AMO (for security - a server admin can setup the connection strings, while database admins can do other tasks without needing to know the passwords). The server keeps the password internally (so you can re-process objects), but it doesn't give it to clients. Because of this, the Update method would send back to the server an incomplete connection string, overwriting the existing full connection string.

Adrian Dumitrascu