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?

Answer 1

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