Different behaviors in similar SQL Server connection string using C#

Question

I have encountered a situation which looks weird to me. Though I have figured out another simpler way for my code to work but I'm curious to know as why this is happening. Here is the code that works absolutely perfect :

public DataHelper()
{
    this.dataSource = "MyMachine";
    this.initCatalog = "myDB";
    this.uid = "sa";
    this.pass = "thep@ssw0rd";
    this.conStr = "Data Source=" + dataSource + ";Initial Catalog=" + initCatalog + ";User ID=" + uid + ";Password=" + pass;
    this.conn = new SqlConnection(conStr);
}

Needless to say that this is a default constructor and the conn is the instance of SqlConnection.

But when I'm using another constructor definition:

public DataHelper(string constr)
{
    this.conStr = constr;
    this.conn = new SqlConnection(conStr);
}

I find that the connection can't be opened, and the application crashes this error:

System.Data.SqlClient.SqlException was unhandled
Class=14
ErrorCode=-2146232060
HResult=-2146232060
LineNumber=65536
Message=Login failed for user 'sa'.
Number=18456
Procedure=""
Server=MyMachine
Source=.Net SqlClient Data Provider
State=1

StackTrace:

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at SampleWork.DataHelper.doSelect(String sql) in C:\Users\RGP\documents\visual studio 2015\Projects\SampleWork\SampleWork\DataHelper.cs:line 66 at SampleWork.DatabaseSetup.btnDataLogin_Click(Object sender, EventArgs e) in C:\Users\RGP\documents\visual studio 2015\Projects\SampleWork\SampleWork\DatabaseSetup.cs:line 55 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at SampleWork.Program.Main() in C:\Users\RGP\documents\visual studio 2015\Projects\SampleWork\SampleWork\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

As the exception states, below are few lines of code responsible to make the call for database connection:

DatabaseSetup.cs

private void btnDataLogin_Click(object sender, EventArgs e)
{
    string dbName = txtDbName.Text.Trim();
    string uid = txtUid.Text.Trim();
    string pass = txtPass.Text.Trim();
    string machine = System.Environment.MachineName;

    if (dbName.Length != 0 && uid.Length != 0 && pass.Length != 0)
    {
        string constr = "Data Source=" + machine + ";Initial Catalog=" + dbName + ";Persist Security Info=True; User ID=" + uid + ";Password=" + pass;
        MessageBox.Show(constr);
        //DataHelper helper = new DataHelper();//This works
        DataHelper helper = new DataHelper(constr);//Doesn't work

        helper.doSelect("select * from abc");
    }

DataHelper.cs:

the doSelect(string sql) which behaves differently and throws exception depending on constructor definition:

public SqlDataReader doSelect(string sql)
{
    conn.Open();

    SqlCommand cmd = new SqlCommand(sql, conn);

    SqlDataReader reader = cmd.ExecuteReader();
    return reader;
}

Though I have also checked through breakpoints and there's nothing wrong with connection string during the execution of doSelect().

Can anyone help me to understand this scenario?

The SQL Server error log will contain a message with the specific reason for the login failed error. I'm curious what that is if the connection string is identical. — Dan Guzman, Apr 21 '18 at 11:42
@DanGuzman SQL Server is just giving error code 18456. Surprisingly this gives state 1. And if it is state 1 for error 18456 then this should be the same case for the first constructor definition in DataHelper.cs — Ravi Gaurav Pandey, Apr 22 '18 at 05:38
Are you saying there is no additional text in the SQL Server error log about the cause of the error? More details should be logged, although not returned to the client. — Dan Guzman, Apr 22 '18 at 11:06
A couple of tips: `SqlConnection` is `IDisposable`, so your `DataHelper` class should also be `IDisposable`, and when you use it, you should put it in a `using` block. `SqlCommand` and `SqlDataReader` are also disposable; but that's going to be hard to handle with this inverted design you've come up with - I wouldn't dispose Cmd before DR. Consider taking a look at [Jon Skeet's suggestion for using Linq](https://stackoverflow.com/a/1464929/5198140) as guidance toward a way of encapsulating DataReader processing - this will avoid the inversion, and keep the disposal easy with a `using` block. — Richardissimo, Apr 22 '18 at 20:19
Thanks @Richardissimo ..for suggesting useful resource on this topic. — Ravi Gaurav Pandey, Apr 26 '18 at 08:22

Different behaviors in similar SQL Server connection string using C#

0 Answers0