none
Error Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user when running SQL Server SelectTable Adapter Sample RRS feed

  • Question

  • Hi,

    I'm receiving the following error when I try to run the SQL Server SelectTable Adapter Sample on BizTalk Server 2009 against a remote SQL Server 2005 database. The SFB\Innesda user exists in the ADAPTER_SAMPLES database on the remote machine & I can login directly to the remote machine & select from the employee table without problem. Any help would be greatly appreciated.

    Regards, David.

    Event Type:        Warning

    Event Source:    BizTalk Server 2009

    Event Category:                BizTalk Server 2009

    Event ID:              5743

    Date:                     12/07/2010

    Time:                     15:08:03

    User:                     N/A

    Computer:          COE-BIZ-001

    Description:

    The adapter failed to transmit message going to send port "SelectTable_LOBPort" with URL "mssql://COE-DEV-V14//ADAPTER_SAMPLES/". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user 'SFB\Innesda'. ---> System.Data.SqlClient.SqlException: Login failed for user 'SFB\Innesda'.

       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 System.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 owningConnection)

       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 Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()

       --- End of inner exception stack trace ---

     

    Server stack trace:

       at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()

       at Microsoft.Adapters.Sql.ASDKConnection.Open(TimeSpan timeout)

       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnection(Guid clientId, TimeSpan timeout)

       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)

       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.OnOpen(TimeSpan timeout)

       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

       at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)

       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

       at System.ServiceModel.Channels.CommunicationObject.Open()

     

    Exception rethrown at [0]:

       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

       at System.ServiceModel.ICommunicationObject.Open()

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.GetChannel[TChannel](IBaseMessage bizTalkMessage, ChannelFactory`1& cachedFactory)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage)".

     

    Monday, July 12, 2010 4:02 PM

Answers

  • If you intended to use a Windows account then you actually need to set this for the host account or use the BizTalk Enterprise SSO and setup the host account to use the SSO account mapping. The credentials box is not where you would specify Windows credentials for using the WCF-SQL binding.

    Glad you got it working.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by David_I Friday, July 16, 2010 11:35 AM
    Thursday, July 15, 2010 4:43 PM
    Moderator

All replies

  • Usually you import a binding file generated when you create a schema for the WCF-SQL adapter. Did you enter the password in the BizTalk admin console under the port properties for the account after importing the binding file?

    Thanks, 


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, July 12, 2010 4:11 PM
    Moderator
  • Hi Ben,

    Thanks for the reply. Yes I entered the login & p/word under the Credentials screen in BizTalk admin. Incidentally, yesterday I was being warned that my p/word was about to expire & wondered if that was causing a problem to the login process. As such I changed the p/word yesterday then updated the credentials in BizTalk to reflect this & waited a bit for the change to filter through but this still didn't work. Eventually I rebooted both the BizTalk server & the database server but still no joy. Fyi, due to our security policy the p/word is a mixture of upper & lower case letters, a number & ends with a #. I was wondering if any of that causes SQL Server a problem so I tried entering the username & p/word all in uppercase but this didn't make any difference. When I run the profiler on SQL Server I get the following error when running the orchestration "Login failed for user 'SFB\Innesda'. [CLIENT: xx.x.xx.xxx]". Any thoughts?

    Regards, David.

    Tuesday, July 13, 2010 8:18 AM
  • Hi again,

    Just in case I changed my p/word to remove the # in case it was causing any problems but as expected this made no difference. I also tried using the credentials for sysadmin user but this threw up the same error.

    Regards, David.

    Tuesday, July 13, 2010 1:15 PM
  • That sounds really weird. Do you have SQL authentication enabled on the SQL Server?

    On my BizTalk server I do not have any problems logging into my SQL Server. When you installed BizTalk, were you an sa on the SQL Server?

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, July 13, 2010 2:07 PM
    Moderator
  • Yes, the remote SQL Server instance has SQL Server & Windows Authentication mode enabled & the BizTalk SQL Server has Windows Authentication set. They both have Allow remote connections to this server enabled too. The SQL Server install on the BizTalk was performed as an sa as was the install of SQL Server on the remote system. Both SQL Server have "Require distributed transactions for server-to-server communication" unticked, I presume that is correct?

    Thanks, David.

    Tuesday, July 13, 2010 4:14 PM
  • All of that configuration sounds correct. Since you are not getting a transaction error, don't worry about the distributed transactions settings.

    Are your BizTalk and SQL servers part of the SFB domain? If you can login as these users in SQL Management Studio it should work in BizTalk too. Are you sure you are connecting to the default instance of SQL?

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, July 13, 2010 10:19 PM
    Moderator
  • Yes, both servers are in the SFB domain. I've managed to figure out what the problem is, the remote server's SQL Server log shows the error as "Error: 18456, Severity: 14, State:6". On checking in http://sqlserver-qa.net/blogs/tools/archive/2007/05/18/msg-18456-level-14-state-1-server-servername-line-1-login-failed-for-user-name.aspx it states "Attempt to use a Windows login name with SQL Authentication". So although the remote SQL Server is configured for "SQL Server & Windows Authentication mode" it appears to be using SQL Authentication for some reason. To confirm this I created a SQL Server login & that fixed the problem. Any idea why this would be?

    Thanks, David.

    Thursday, July 15, 2010 3:20 PM
  • If you intended to use a Windows account then you actually need to set this for the host account or use the BizTalk Enterprise SSO and setup the host account to use the SSO account mapping. The credentials box is not where you would specify Windows credentials for using the WCF-SQL binding.

    Glad you got it working.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by David_I Friday, July 16, 2010 11:35 AM
    Thursday, July 15, 2010 4:43 PM
    Moderator
  • That explains it for me Ben. Thanks for your help. David.
    Friday, July 16, 2010 11:36 AM
  • Hi Ben,

    I have the same issue and the DBAs have specified that they do not want us using specific SQL Accounts and authentication, their policy is to use domain accounts which are trusted but unfortunately when you populate the credentials part of WCF Custom adapter the login details are passed along with the password which it doesn't like and doesn't work hence the reason I am getting the Login error.  Is there any way of passing the credentials part of this adapter as trusted i.e. a setting that I am not seeing.  I do have this working by creating another Host with a specific domain account and credentials which sends the Login details as required but as usual I have other factors resisting going in this direction which are out of my control.  My simplistic view on the world is that they should provide this feature in WCF Adaptor.

     

    Cheers

    AIMac

    Wednesday, November 17, 2010 1:11 PM
  • Hi Been/All

    I am facing some different issue .I have a insert operation on the table with WCF sql adapter .On stagging server table structure is [User1].Table name and on prod the table structure is [User2].Table name. Wat I found that schema structure itself is differing from stagging  to Prod and thats reason SQL server is unable to validate the schema structure .

    After that i tried to create Prod User with different credential on my local machine and deployed the new MSI with binding I have changed the SQL Credential according to prod SQL server .Than i found login failed . My SQL admin shown me that the  user have all rights on the table.

    Can you please let me know is there any issue which is causing this problem.

    cheers

    Abhishek


    Saturday, August 18, 2012 4:09 PM
  • Hi Ben ,

    It Worked for me by changing the SOAP Action Header with User details in WCF Custom Configuration.

     

    Tuesday, October 22, 2013 12:35 PM
  • Hi Ben,

    I have the same issue. Can you please elaborate more. I have a host instance running with my windows credentials which is again I am using to access my SQL Server. But getting the above error in Receive Port

    Tuesday, July 8, 2014 5:18 PM
  • These threads are very old and closed.  If the solutions provided don't address your problem, you should open a new thread with your specific situation.  Include the entire error message.

    Tuesday, July 8, 2014 5:42 PM