SQL Login fails from web application for one stored procedure call

Question

Fortunately, this is in UAT rather than production.

The UAT servers are Azure VMs running SQL Server 2012 and Windows Server 2012. On one page in the application I get an error

Login failed for user 'User'. at System.Data.SqlClient.SqlInternalConnection

Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.GetSpParameterSetInternal(SqlConnection connection, String spName, Boolean includeReturnValueParameter)
at Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.GetSpParameterSet(String connectionString, String spName, Boolean includeReturnValueParameter)
at Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache.GetSpParameterSet(String connectionString, String spName)

The login is a SQL Server login, not a domain login

On the SAME web page, there are 3 other stored procedure calls that are made to the same database with the same connection string prior to this stored procedure begin called

I have dropped and recreated the stored procedure

I have dropped and recreated the database login

The account is a member of a database role that grants it EXECUTE rights on the schema this stored procedure belongs to

If I log into SSMS as this user, I can:

Expand the stored procedure list for the database
Expand the parameter list for the affected stored procedure
Run the affected stored procedure and get the expected results

I have an alternative web server set up on the SQL Server which uses domain logins in the connection string, that runs with no problem. We are trying to deprecate the SQL Server version of the web site.

Can anyone suggest what might be causing this, and how to address it?

Strange! did you try changing the procedure name? as you said "On the SAME web page, there are 3 other stored procedure calls that are made to the same database with the same connection string prior to this stored procedure begin called" — Sin, Oct 06 '15 at 01:06
@Hybridzz - That would require me doing an updated deployment to UAT, so it will have to wait a day or two. Be interesting if it works — Roger Willcocks, Oct 06 '15 at 23:49
@Dan - Yes, a WTF error: Login failed for user 'User'. Reason: Password did not match that for the login provided. [CLIENT: 10.10.0.15] Hmm, but there is a difference between how the failing SP is being called and the successful ones. — Roger Willcocks, Oct 06 '15 at 23:59

score 2 · Accepted Answer · edited May 23 '17 at 11:52

Thanks you .NET SqlClient security behaviour. Working call via Data Application block

return SqlHelper.ExecuteReader(ConnectionString, sql_OutboundQueue_TypeAndStatusCount, DBUtility.GetNull(since));

This chains down to calling SqlHelperParameterCache.GetSpParameterSet

Failing call via Data Application block

SqlConnection con = new SqlConnection(QueueDataProvider.ConnectionString);
SqlCommand cmd = new SqlCommand(QueueDataProvider.OutboundQueue.sql_OutboundQueue_Search, con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(con.ConnectionString, cmd.CommandText));

Reason: con.ConnectionString has the password cleared if you are using SQL Logins, but only AFTER you call con.Open

Thanks to: ConnectionString loses password after connection.Open

SQL Login fails from web application for one stored procedure call

1 Answers1