SQL Server Performance

Connecting to AS 2005 through ADOMD 9.0

Discussion in 'SQL Server 2005 Analysis Services' started by NileshRoy, Feb 19, 2008.

  1. NileshRoy New Member

    Hi All,
    I have a ASP.NET/C# application, that was running on AS 2000 cube. Now, i have migrated our cubes to AS 2005. I did all my changes in the application (mainly MDX) on my local machine, using a local IIS server. The connection string i used to connect to AS 2005 cubes was "Data Source=<servername>;Database=<dbname>;" This used to work fine on my local machine.
    However now I want to deploy my dll's in a public website (anonymous access). I am unable to connect from the webserver. It throws an error, "Either the user, FMLONMS00973$, does not have access to the Evamart1 database, or the database does not exist."
    I have tried adding User Id & Password to connection string, but to no avail. Can I do so? Can't I use a connectionstring similar to SQL Server trusted connection one?
    Any ideas? Suggestions!
    Cheers!
  2. dineshasanka Moderator

  3. NileshRoy New Member

    Thnaks Dinesh, I had gone through that link, however things are not very clear in that article.
    If anyone could clearly explain the following scenario:
    My login ID is roy and I am part of a domain XY. My login i.e. roy is part of a OLAP users group on AS 2005.
    Environment:
    I have an IIS server in XY domain itself. There is a website on it configured with windows authentication.
    I am able to connect to AS 2005 through my DLL using the connection string "Data Source=<servername>;Database=<dbname>;"
    Problem:
    However, when I install my DLL on the website, I am not able to connect.
    Analysis:
    I am sure this is an authentication issue. As i am able to connect to the AS server through website, if I change the authentication method to Anonymous and connect through my login i.e. roy.
    Questions:
    1) Do I need to add the domain XY as part of OLAP users group?
    2) Is this the best way for TCP/IP connectivity?
    3) Will HTTP connectivity be better in this case?
  4. satya Moderator

    This definetly sounds access issue, can you try using with a login that has SA privileges on the server, I believe it is better to check to see ADOMD.NET is installed properly or not refer to this link, and if you are using older versions of Visual Studio download ADOMD.NET 8.0 along with the hotfix here.
    You can also choose to use the OLE DB (COM) Provider for Analysis Services 2005. If you do, be sure to install the pre-requisite Microsoft Core XML Services.
  5. NileshRoy New Member

    Thanks Satya,
    Yes, this is an access issue. As described above, I am able to connect to AS 2005 from the website and run reports, when I am using my Login ID (anonymous access). This proves that ADOMD.NET 9.0 is installed properly on the IIS machine.
    It's just that when I run the website in Windows authentication mode, the AS 2005 is unable to authenticate the user. My question is, how do I allow all the users in XY domain (i.e. domain of IIS machine) access to AS 2005?
    Will I have to change connection string?
    Any other better way to provide access i.e. HTTP?
  6. satya Moderator

    If the Analysis Services database is on a separate machine from the web server or the desktop application, it is best if a Windows domain account is used when running OLAP Services. This account can then be granted access in Analysis Services. In ASP.NET, you can use impersonation to change the identity of the ASP.NET process, or in IIS6 or above change the identity of the application pool used for the web site.

Share This Page