locked
Adomd ConnectionString ignore User ID RRS feed

  • Question

  • Hi all.

    I must use Windows Authentication and I want that my application connects to MS SQL Server (Database Engine) with authenticated user. So I used ASP.NET impersonation in IIS.

    Also I want that my application connects to MS Analysis Services (OLAP Cube) with specific user.

    So I specify connection string with User ID and Password.

    I tried something like this:

    "provider=msolap;Data Source=<server name>;initial catalog=<initial catalog>;User Id=<UserName>;Password=<Password>"

    and some other options from http://msdn.microsoft.com/en-us/library/dn140245.aspx.

    But SQL Server Profiler shows me "Anonymous Logon" when I connect from client.

    What did i do wrong?

    Tuesday, August 13, 2013 6:34 PM

Answers

  • >But I think I can simplify my question. I  don't want to use double-hop scenario.

    You can't specify a user name and password in the connection string.  You can use those credentials to impersonate that user before you connect to SSAS.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by PetrovSerega Thursday, August 15, 2013 7:24 AM
    Wednesday, August 14, 2013 2:02 PM

All replies

  • Hi,

    Microsoft OLAP (SSAS) doesn't support SQL logins.

    Your application has to login with a Windows account.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, August 13, 2013 6:39 PM
  • But SQL Server Profiler shows me "Anonymous Logon" when I connect from client.

    Hello,

    In your post you mix up the relational database (RDBMS) and the multidimensional database (SSAS). So which engine are you monitoring with SQL Profiler, RDBMS or SSAS?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 13, 2013 6:42 PM
  • Microsoft OLAP (SSAS) doesn't support SQL logins.


    Yes & sure, but SSAS supports the impersonate of a Windows account, so you can pass Windows credentials within the connection string; as long as it is a valid Windows account

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 13, 2013 6:54 PM
  • Ok , i know that and i use Windows Account.
    Tuesday, August 13, 2013 7:33 PM
  • I'm monitoring SSAS. RDBMS works fine.

    Tuesday, August 13, 2013 7:35 PM
  • But SQL Server Profiler shows me "Anonymous Logon" when I connect from client.

    I developed web application with ASP.NET.

    So I have client machine that connected to IIS server and server code that connected to SSAS server.


    Tuesday, August 13, 2013 7:40 PM
  • Hi,

    Under what Windows account is IIS running on your Web Server?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, August 13, 2013 7:41 PM
  • My Application Pool works under NETWORK SERVICE.
    Tuesday, August 13, 2013 7:47 PM
  • Hi,

    That explains the "Anonymous" account you're seeing on the OLAP SQL Profiler trace.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, August 13, 2013 7:48 PM
  • >My Application Pool works under NETWORK SERVICE.

    >>That explains the "Anonymous" account you're seeing on the OLAP SQL Profiler trace.

    No it doesn't.  NETWORK SERVICE communicates off-box as the machine account, not as anonymous.

    An anonymous login attempt typically indicates a Kerberos constrained delegation failure, as this is a double-hop scenario. 

    So you have two options:

    1) Get Kerberos Constrained Delegation working so that the impersonated credentials can be used for Analysis Services.  If your SQL Server is on a separate server from IIS and you can connect to it using the impersonated credentials, then you already have this working for SQL.  You just need to add the ability for the App Pool identity to delegate credentials to Analysis Services.

    2) Revert to the App Pool identity or impersonate some other account which is an SSAS Administrator and pass the end user's identity to Analysis Services using the EffectiveUserName connection string property.

    EffectiveUserName:

    "Use when an end user identity must be impersonated on the server. Specify the account in a domain\user format. To use this property, the caller must have administrative permissions in Analysis Services. For more information about using this property in an Excel workbook from SharePoint, see Use Analysis Services EffectiveUserName in SharePoint Server 2013. For an illustration of how this property is used with Reporting Services, see Using EffectiveUserName To Impersonate in SSAS."

    David


    David http://blogs.msdn.com/b/dbrowne/



    Tuesday, August 13, 2013 8:27 PM
  • >My Application Pool works under NETWORK SERVICE.

    >>That explains the "Anonymous" account you're seeing on the OLAP SQL Profiler trace.

    No it doesn't.  NETWORK SERVICE communicates off-box as the machine account, not as anonymous.

    An anonymous login attempt typically indicates a Kerberos constrained delegation failure, as this is a double-hop scenario. 

    So you have two options:

    1) Get Kerberos Constrained Delegation working so that the impersonated credentials can be used for Analysis Services.  If your SQL Server is on a separate server from IIS and you can connect to it using the impersonated credentials, then you already have this working for SQL.  You just need to add the ability for the App Pool identity to delegate credentials to Analysis Services.

    2) Revert to the App Pool identity or impersonate some other account which is an SSAS Administrator and pass the end user's identity to Analysis Services using the EffectiveUserName connection string property.

    EffectiveUserName:

    "Use when an end user identity must be impersonated on the server. Specify the account in a domain\user format. To use this property, the caller must have administrative permissions in Analysis Services. For more information about using this property in an Excel workbook from SharePoint, see Use Analysis Services EffectiveUserName in SharePoint Server 2013. For an illustration of how this property is used with Reporting Services, see Using EffectiveUserName To Impersonate in SSAS."

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thanks. I will try it.

    But I think I can simplify my question. I  don't want to use double-hop scenario.

    So I have this code:

    AdomdConnection mdConn = new AdomdConnection();
    mdConn.ConnectionString = @"provider = msolap; Data Source = olap-db;initial catalog = OLAP_SSAS;User Id = domain\someAccount;Password=pass;";
    mdConn.Open();

    I start my project from local machine and i see in profiler Audit Login event with domain\myLogin but I want domain\someAccount




    Wednesday, August 14, 2013 7:47 AM
  • >But I think I can simplify my question. I  don't want to use double-hop scenario.

    You can't specify a user name and password in the connection string.  You can use those credentials to impersonate that user before you connect to SSAS.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by PetrovSerega Thursday, August 15, 2013 7:24 AM
    Wednesday, August 14, 2013 2:02 PM
  • >But I think I can simplify my question. I  don't want to use double-hop scenario.

    You can't specify a user name and password in the connection string.  You can use those credentials to impersonate that user before you connect to SSAS.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thanks! That's work!
    Thursday, August 15, 2013 7:24 AM
  • Hi,

    I totally agree with you.
    Can you please send us any reference to impersonate Window Authentication, currently we are using Class library project which will be in Cloud, hosted through micro-services.

    It would be really great if any one can answer my query.

    Tuesday, August 1, 2017 2:38 PM