none
BDC Model / External Data Type Security - Getting a SqlException (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON')

    Question

  • Hello All :)

    I have some BDC models setup and they're working great. A problem arises when anyone else tries to see the models on my virtual machine.
    We're all on the same domain. I have created a number of web parts that have lists bound to the bdc models. When another user on the network attempts to view these webparts, I can see the following in the SP logs;

     

    10/08/2010 14:50:42.57 	w3wp.exe (0x12E4)  	0x070C	SharePoint Foundation 	Web Parts  	89a1	High 	Error while executing web part: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at Sys...	fe8d9c74-fdf0-4d54-a47a-cc0855196b9c
    
    
    
    

    I've seen blogs and pages discussing different authentication methods, RevertToSelf, etc. etc., but I haven't seen anywhere describing how to actually set this up properly. I've tried creating a Secure Store, with my own credentials, and then used DP Designer to change the Secondary Secure Store Application Id for the external system to the name of the secure store I created. That doesn't seem to make any difference...

    Does anyone have a walkthrough or additional info for this?

    Cheers,
    Jamie


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 11, 2010 9:08 AM

Answers

  • Since you are using integrated security = true in connect string because of the doublehop issue u have to use "reverttoself" for BCS authentication which would mean your application pool's identity will be used when connecting to database. You'll just need to make sure app pool account has permission to connect to the database

    as far as specifying the authenticationmode

    If you used VS2010 to create the BDCModel project:

    Select the LobSystemInstance in BDC Explorer view and in the property explorer there is an option called Custom Properties collection

    clicking the "..." button will bring up Property editor window where in you enter "AuthenticationMode" for name and "RevertToSelf" for string value

    If you used SPD 2010 to create your external content type:

    Authentication mode can be set in connection properties


    Microsoft Certified Master | SharePoint 2007 blogs.msdn.com/ramg
    Sunday, October 17, 2010 3:23 PM
  • I managed to get my BDC models working for other users on the network by disabling impersonation in SP's web.config file (C:\inetpub\wwwroot\wss\VirtualDirectories\80);

     <system.web>
     <identity impersonate="false" /></system.web>

    SP can still see who the request is coming from (the other users on the network for example), but requests to the database are done as the app pool identity. This is what I believed RevertToSelf was for.

    From what I can see SP is ignoring the AuthenticationMode property set on the LobSystemInstance. No matter what I put in there SP seems to only run based on what was in the web.config. If impersonate=true, requests to the database are impersonated as the user viewing the page (which brings you into the kerberos double hop issue when the servers are separate etc.), and if impersonate=false requests to the database are done as the underlying app pool identity.

    So... partial resolution :)


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Tuesday, October 19, 2010 10:15 AM

All replies

  • I'd also mention that the back-end SQL database is not running on the SP 2010 virtual machine.

    Locally from the virtual machine I can view the web parts etc.
    I am a local administrator on the virtual machine

    From my desktop (from which I remote into the virtual machine) I cannot view the web parts etc.
    I get the following error displayed in the web part: An error occurred while retrieving data from SupervisionBDC. Administrators, see the server log for more information. Correlation ID:069e7aac-e649-46fc-b03b-3ffffaeac03d

    That correlation ID gives the same exception as above in the first post.


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 11, 2010 9:15 AM
  • It might also be worth mentioning that only users who would have access to the underlying SQL database would be accessing these BDC models

    So if it's possible to authenticate against the DB as the user who's browsing the BDC model then that'd be super.

    I'm using Linq2Sql, and its connection string is configured to use Integrated Security=True;


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 11, 2010 2:46 PM
  • Another thing worth noting is that I'm not sure which database the SqlException is happening on - the SP databases on the VM, or the DCD models' underlying data store (on a remote machine).

    Also, I don't really have rights to change user permissions and the likes on the remote database server, so I can't test if giving ANONYMOUS LOGON dbo access (just to test) would resolve the problem


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Wednesday, October 13, 2010 9:37 AM
  • Another useful piece of information. When I create BDC models pointing at a database local to the machine that SP is running on, I can view them from my local computer. When I create BDC models pointing at a remote database (relative to the VM), I cannot view them from my local computer.

    So it looks like it's the double-hop security issue that the secure store service is supposed to resolve...


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Friday, October 15, 2010 3:23 PM
  • Since you are using integrated security = true in connect string because of the doublehop issue u have to use "reverttoself" for BCS authentication which would mean your application pool's identity will be used when connecting to database. You'll just need to make sure app pool account has permission to connect to the database

    as far as specifying the authenticationmode

    If you used VS2010 to create the BDCModel project:

    Select the LobSystemInstance in BDC Explorer view and in the property explorer there is an option called Custom Properties collection

    clicking the "..." button will bring up Property editor window where in you enter "AuthenticationMode" for name and "RevertToSelf" for string value

    If you used SPD 2010 to create your external content type:

    Authentication mode can be set in connection properties


    Microsoft Certified Master | SharePoint 2007 blogs.msdn.com/ramg
    Sunday, October 17, 2010 3:23 PM
  • Hi Ram,

    Many thanks for your reply. The application pool is running as my domain account (and I have access to the database in question). I added AuthenticationMode to the Custom Properties collection but it doesn't seem to have made a difference :( I am still receiving the SqlEception, relating to the ANONYMOUS account;

     

    10/18/2010 11:09:32.28 High Leaving Monitored Scope (EnsureListItemsData). Execution Time=575.444085770678 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28 High Error while executing web part: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at Sys... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...tem.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owni... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...ngConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(ICon... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...nectionUser user) at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe() at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode() at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery`1.System.Collections.IEnumerable.GetEnumerator() at Microsoft.SharePoint.BusinessData.Runtime.AbstractSystemUtility.CreateEntityInstanceDataEnumerator(Object rawAdapterEntityInstanceStream, ISharedEntityState sharedEntityState) at Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.WrapAsEntities(IMethodInstance methodInstance, IEntity thisEntity, IParameter parameter, ITypeDescriptor entityInstanceRootTypeDescriptor, Object correspondingAdapterObject, IEntity targetEntity, ISystemUtility dat... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...aSystemUtility, ILobSystemInstance lobSystemInstance) at Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs, IFilterCollection filters) at Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.ExecuteFiltered(IEntity this, IFilterCollection filterCollection, IMethodInstance methodInstanceToExecute, ILobSystemInstance lobSystemInstance) at Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.<FindFiltered>b__3(IEntity e, IMethodInstance mi, IFilterCollection fc, ILobSystemInstance lsi) at Microsoft.SharePoint.BusinessData.Runtime.Entity... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...InstanceEnumeratorFactory.CreateEntityInstanceEnumerator(IEntity entity, IMethodInstance methodInstance, IFilterCollection filters, ILobSystemInstance lobSystemInstance, ExecutionCallBack executionCallBack) at Microsoft.SharePoint.BusinessData.MetadataModel.Static.Entity.FindFiltered(IFilterCollection filterCollection, String finderName, ILobSystemInstance lobSystemInstance) at Microsoft.SharePoint.SPListDataSource.GetEntityInstanceEnumerator(XmlNode xnMethodAndFilters) at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrderi... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...ng, List`1& lstEntityData) at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData) at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow) at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData() at Microsoft.SharePoint.SPListItemCollection.get_Count() at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltList... 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28* High ...WebPart webpart, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList) at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal() at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator() at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform) 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.28 High Leaving Monitored Scope (DataBinding DataFormWebPart (Supervision Institutions BDC 01)). Execution Time=577.959768629812 5c737f48-d6a0-4019-bc0e-79e833f945b3

    10/18/2010 11:09:32.30 Medium Leaving Monitored Scope (Request (POST:http://vm-sp2010-t4:80/Lists/Supervision%20Institutions%20BDC%2001/ReadList.aspx)). Execution Time=637.693617484904 5c737f48-d6a0-4019-bc0e-79e833f945b3


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 18, 2010 9:52 AM
  • Is there any way SP can impersonate the user who's viewing the LIST that the BDC model is on?


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 18, 2010 11:16 AM
  • Another very strange thing. If I (or another user) remotes into the VM, we can see the models.

    If I access the VM over the network in IE, I can see myself as System Account in the top right hand corner (and the other user can see themselves appropriately too), but it tries to authenticate with SQL as ANONYMOUS. That strikes me as strange ...

     

    With Integrated Security=true in the connection string I can see that SP is impersonating the user that's accessing the model (but only if they're doing it locally from the VM). I don't want the user impersonated - I want the app pool identity to hit the database...


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 18, 2010 1:57 PM
  • I created a new simple BDC model with just one entity. It uses LinqToSql to retrieve the relevant entities;

    Partial Public Class InstDetailsService
    
      Public Shared Function ReadItem(ByVal institutionNumber As Integer) As SU_InstDetail
        Dim context = New SupervisionL2SDataContext("Data Source=EN-SQL-23QSS3J\SYST;Initial Catalog=Supervision;Integrated Security=True")
        Return (From tempInst In context.SU_InstDetails Where tempInst.InstitutionNumber = institutionNumber Select tempInst).[Single]
      End Function
    
      Public Shared Function ReadList() As IEnumerable(Of SU_InstDetail)
        Dim context = New SupervisionL2SDataContext("Data Source=EN-SQL-23QSS3J\SYST;Initial Catalog=Supervision;Integrated Security=True")
        Return (From tempInst In context.SU_InstDetails.Take(1000) Select tempInst Order By tempInst.InstitutionNumber)
      End Function
    End Class
    

    I gave the LobSystemInstance a property of AuthenticationMode, of type System.String with value RevertToSelf. SP is still impersonating as the user, which causes other users to hit the database as ANONYMOUS LOGON.

    This is the feature.xml file:

    <?xml version="1.0" encoding="utf-8"?>
    <Feature xmlns="http://schemas.microsoft.com/sharepoint/" Title="NewBDCModels Feature1" Description="My SharePoint BDC Feature" Id="dc6c34ce-6879-4209-bd75-f790cec9a103" ReceiverAssembly="Microsoft.Office.SharePoint.ClientExtensions, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" ReceiverClass="Microsoft.Office.SharePoint.ClientExtensions.Deployment.ImportModelReceiver" Scope="Farm">
     <Properties>
      <Property Key="GloballyAvailable" Value="true" />
      <Property Key="BdcModel1" Value="BdcAssemblies\NewBDCModels.dll" />
      <Property Key="IncrementalUpdate" Value="true" />
      <Property Key="ModelFileName" Value="BdcModel1\BdcModel1.bdcm" />
     </Properties>
     <ElementManifests>
      <ElementFile Location="BdcModel1\BdcModel1.bdcm" />
      <ElementFile Location="BdcAssemblies\NewBDCModels.dll" />
     </ElementManifests>
    </Feature>
    
    

    And this is the BdcModel1.bdcm file:

    <?xml version="1.0" encoding="utf-8"?>
    <Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog" Name="BdcModel1">
     <LobSystems>
      <LobSystem Name="BdcModel1" Type="DotNetAssembly">
       <LobSystemInstances>
        <LobSystemInstance Name="BdcModel1">
         <Properties>
          <Property Name="AuthenticationMode" Type="System.String">RevertToSelf</Property>
         </Properties></LobSystemInstance>
       </LobSystemInstances>
       <Entities>
        <Entity Name="InstDetails" Namespace="NewBDCModels" Version="1.0.0.13">
         <Properties>
          <Property Name="Class" Type="System.String">NewBDCModels.InstDetailsService, BdcModel1</Property>
         </Properties>
         <Identifiers>
          <Identifier Name="InstitutionNumber" TypeName="System.Int32" />
         </Identifiers>
         <Methods>
          <Method Name="ReadItem">
           <Parameters>
            <Parameter Name="instDetails" Direction="Return">
             <TypeDescriptor Name="InstDetails" TypeName="NewBDCModels.SU_InstDetail, BdcModel1" IsCollection="false">
              <TypeDescriptors>
               <TypeDescriptor Name="InstitutionNumber" TypeName="System.Int32" IsCollection="false" IdentifierName="InstitutionNumber" />
               <TypeDescriptor Name="InstitutionName" TypeName="System.String" /></TypeDescriptors></TypeDescriptor></Parameter>
            <Parameter Name="institutionNumber" Direction="In">
             <TypeDescriptor Name="InstitutionNumber" TypeName="System.Int32" IdentifierEntityName="InstDetails" IdentifierEntityNamespace="NewBDCModels" IdentifierName="InstitutionNumber" /></Parameter>
           </Parameters>
           <MethodInstances>
            <MethodInstance Name="ReadItem" Type="SpecificFinder" ReturnParameterName="instDetails" ReturnTypeDescriptorPath="InstDetails" />
           </MethodInstances></Method>
          <Method Name="ReadList">
           <Parameters>
            <Parameter Name="instDetailsList" Direction="Return">
             <TypeDescriptor Name="InstDetailsList" TypeName="System.Collections.Generic.IEnumerable`1[[NewBDCModels.SU_InstDetail, BdcModel1]]" IsCollection="true">
              <TypeDescriptors>
               <TypeDescriptor Name="InstDetails" IsCollection="false" TypeName="NewBDCModels.SU_InstDetail, BdcModel1">
                <TypeDescriptors>
                 <TypeDescriptor Name="InstitutionNumber" IdentifierName="InstitutionNumber" IsCollection="false" TypeName="System.Int32" />
                 <TypeDescriptor Name="InstitutionName" TypeName="System.String" /></TypeDescriptors></TypeDescriptor></TypeDescriptors></TypeDescriptor></Parameter>
           </Parameters>
           <MethodInstances>
            <MethodInstance Name="ReadList" Type="Finder" ReturnParameterName="instDetailsList" ReturnTypeDescriptorPath="InstDetailsList" />
           </MethodInstances></Method>
         </Methods></Entity>
       </Entities>
      </LobSystem>
     </LobSystems>
    </Model>
    
    

    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Monday, October 18, 2010 4:46 PM
  • Right, so I've discovered that I can't get Sharepoint to not impersonate. When it impersonates, kerbos hasn't been configured to allow the double hop, etc. etc. But we don't want it to impersonate, as the app pool will get a dedicated u/p on the network. Using AuthenticationMode = RevertToSelf doesn't seem to have any effect. In fact I can put any names and types in for the properties and it doesn't make any effect, e.g. <property name="?!?!?!?!?!?!" Type="??!?!?!?!!?">??!?!?!</property> etc. I even tried calling the RevertToSelf windows api to 'undo' the impersonation, but it's not working. If I log the current identity before and after the call, it's always the identity of the user who's viewing the model - not the identity of the app pool. Very frustrating :(
    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Tuesday, October 19, 2010 12:06 AM
  • I managed to get my BDC models working for other users on the network by disabling impersonation in SP's web.config file (C:\inetpub\wwwroot\wss\VirtualDirectories\80);

     <system.web>
     <identity impersonate="false" /></system.web>

    SP can still see who the request is coming from (the other users on the network for example), but requests to the database are done as the app pool identity. This is what I believed RevertToSelf was for.

    From what I can see SP is ignoring the AuthenticationMode property set on the LobSystemInstance. No matter what I put in there SP seems to only run based on what was in the web.config. If impersonate=true, requests to the database are impersonated as the user viewing the page (which brings you into the kerberos double hop issue when the servers are separate etc.), and if impersonate=false requests to the database are done as the underlying app pool identity.

    So... partial resolution :)


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081
    Tuesday, October 19, 2010 10:15 AM
  • Another thing worth noting is that I'm not sure which database the SqlException is happening on - the SP databases on the VM, or the DCD models' underlying data store (on a remote machine).

    Also, I don't really have rights to change user permissions and the likes on the remote database server, so I can't test if giving ANONYMOUS LOGON dbo access (just to test) would resolve the problem


    I wrote a book :) http://www.amazon.com/Microsoft-Visual-Studio-2008-Programming/dp/0071604081

    solves the problem but opens database to anyone (I tested it)

    Thursday, April 25, 2013 11:50 PM