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();

well for starters do you have actual code after you execute conn.Open() otherwise you are opening it and then closing it based on what you have in your example above.. — MethodMan, Sep 17 '12 at 21:31
conStr cannot be modified by SqlConnection in the code you have posted. Are you trying to look at conn.ConnectionString (or some similar property?). — Bryan Boettcher, Sep 17 '12 at 21:31
@insta: Tha's what OP said: _" i noticed that conn.ConnectionString is losing the password"_ — Tim Schmelter, Sep 17 '12 at 21:33
@insta yes, an exception occurred when i tried to execute a command and when i debugged the code i saw that the connection string is missing the password — Alaa Jabre, Sep 17 '12 at 21:33
@Star: Can you show the command that causes the exception or is it really arbitrary? — Tim Schmelter, Sep 17 '12 at 21:34
If there was an exception, you should post it in your questions... — Gromer, Sep 17 '12 at 21:35
Is it unreasonable that SqlConnection would strip the password out for security reasons? — Bryan Boettcher, Sep 17 '12 at 21:36
kind of hard to tell what's going on unless the OP is willing to show more reasonable code.. — MethodMan, Sep 17 '12 at 21:39
@Star: Have to used a `Try/Catch` around the `ExecuteReader` and set a breakpoint in the catch? — Tim Schmelter, Sep 17 '12 at 21:44
@DJKRAZE: [`GETDATE`](http://msdn.microsoft.com/en-us/library/ms188383.aspx) is a `SQL-Server` function that returns the current time. http://sqlfiddle.com/#!3/d41d8/163/0 — Tim Schmelter, Sep 17 '12 at 21:46
Tim I am aware of that but why does he not just use DateTime.now it's part of C# — MethodMan, Sep 17 '12 at 21:48
@DJKRAZE: I assume because he wants to convince us that it has nothing to do with the actual query but with the connection string. (_"which causes exception with any SqlCommand"_) — Tim Schmelter, Sep 17 '12 at 21:50
@TimSchmelter Nope , that is really what i'm executing (or let's say what the previous programmer did and now need to fix it) — Alaa Jabre, Sep 17 '12 at 21:52
@Star: Then i must agree with DJ_KRAZE that it's pointless. Why do use a query that does nothing but to return the current time in db? `DateTime.Now` would be more efficient. — Tim Schmelter, Sep 17 '12 at 21:54
@TimSchmelter to get the time one the server, i don't need the local time. is there something wrong with that? :) — Alaa Jabre, Sep 17 '12 at 21:57
and I am quite sure that there is more code than just that.. start from the beginning @Star first of all use Try{}Catch{} around your ExecuteNonQuery() command if there is an error please report that error..if you need to wrap the connection.Open code in a try{}Catch{} and see if it even connects otherwise report on that error.. you must understand that we are here to help you not hurt you and there are many of us here whom are experts and can see flaws right off the bat in your code.. take some constructive advise — MethodMan, Sep 17 '12 at 21:58

score 94 · Accepted Answer · edited Mar 01 '22 at 03:06

94

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.

edited Mar 01 '22 at 03:06

David Gardiner

16,892
20
80
117

answered Sep 17 '12 at 22:32

Thomas Levesque

286,951
70
623
758

great i read that somewhere and didn't know how to use it, can you tell me how to use it please? because i don't see **Persist Security Info** anywhere thanks – Alaa Jabre Sep 17 '12 at 22:34
7

@Star, there is an example in the link I gave you. Just include "Persist Security Info=True" in the connection string. But anyway, you shouldn't need to do that... if you need the ConnectionString property to retain the password, you're probably not using it properly. – Thomas Levesque Sep 17 '12 at 22:36
i see, so do you have any idea where i could have missed up? and again really thank you :) – Alaa Jabre Sep 17 '12 at 22:41
3

@Star, my best guess is that you're reusing a SqlConnection after it has been closed; you should create a new one. That's the best I can do without seeing more of your code... – Thomas Levesque Sep 17 '12 at 23:10
6

Silently deleting part of the connnection string. By default! And only after it has been used for the first time... This is a great way of making sure developers waste time debugging weird issues. Like code arbitrarily throwing `SqlException: Login failed for user ...` because you inspected a connection string at the wrong time. This is a weird way of doing security. They could at least have made it throw an exception instead of returning bogus data! – Zero3 Sep 17 '18 at 12:00

MurgleDreeBrah · Answer 2 · 2013-01-29T16:50:10.347

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.

ProgrammingLlama · Answer 3 · 2017-01-27T00:46:24.323

You may want to add your own validation but this will take a standard SqlConnection (without persist security) and access the private ConnectionOptions property to retrieve the connection string.

public static string SqlConnectionToConnectionString(SqlConnection conn)
{
    System.Reflection.PropertyInfo property = conn.GetType().GetProperty("ConnectionOptions", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
    object optionsObject = property.GetValue(conn, null);
    System.Reflection.MethodInfo method = optionsObject.GetType().GetMethod("UsersConnectionString");
    string connStr = method.Invoke(optionsObject, new object[] { false }) as string; // argument is "hidePassword" so we set it to false
    return connStr;
}

Note that this might break if MS change the underlying implementation, since we're using reflection. I'm not advising this as the best way to do it, but it's a way.

Thank you. This helps me to fix the connection string. But how can I get the SqlTransaction property? @ProgrammingLlama Please help — Roshmi Augustine, Jul 03 '23 at 06:34

ConnectionString loses password after connection.Open

3 Answers3

Linked