ConnectionString loses password after connection.Open

Question

i'm using ADO.NET to get some information from the database on a server,
so this is what i do:

string conStr = "Data Source=myServer\SQLEXPRESS;Initial Catalog=DBName;User ID=myUser;Password=myPassword";  SqlConnection conn = new SqlConnection(conStr);  conn.Open(); // do stuff conn.Close(); 

but after calling Open method i noticed that conn.ConnectionString is losing the password so it becomes:

"Data Source=myServer\SQLEXPRESS;Initial Catalog=DBName;User ID=myUser;" 

which causes exception with any SqlCommand afterwords
how to fix this?
Note:The strange thing is that does not happen always
Edit: i don't think it has anything to do with the command it self but anyway

SqlCommand command = new SqlCommand("select GetDate()", conn); SqlDataReader reader = command.ExecuteReader(); 

Answer 1

This is by design, for security reasons. From MSDN:

The ConnectionString is similar to an OLE DB connection string, but is not identical. Unlike OLE DB or ADO, the connection string that is returned is the same as the user-set ConnectionString, minus security information if the Persist Security Info value is set to false (default). The .NET Framework Data Provider for SQL Server does not persist or return the password in a connection string unless you set Persist Security Info to true.

Answer 2

Look in the connection string, in order to keep the password in the ConnectionString property you must add "Persist Security Info=true;" to the connection string itself.

The following example will strip the password out:

string conStr = "Data Source=localhost;Initial Catalog=MyDatabase;User Id=MyUser;Password=MyPassword"; SqlConnection conn = new SqlConnection(conStr); conn.Open(); conn.Close(); Console.WriteLine(conn.ConnectionString); 

The following example will keep the password in the conn.ConnectionString:

string conStr = "Persist Security Info=True;Data Source=localhost;Initial Catalog=MyDatabase;User Id=MyUser;Password=MyPassword"; SqlConnection conn = new SqlConnection(conStr); conn.Open(); conn.Close(); Console.WriteLine(conn.ConnectionString); 

Its a property set inside the connection string itself not in the SqlConnection object, I put it in the beginning of the connection string just so you don't have to scroll to see it, it can go anywhere in the connection string, I usually see it at the end.

Like others have said, if you need to do this you quite possibly are not using the SqlConnection object exactly as it was intended.