73

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();
Improve this question
18
  • 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.. Commented Sep 17, 2012 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?). Commented Sep 17, 2012 at 21:31
  • @insta: Tha's what OP said: " i noticed that conn.ConnectionString is losing the password" Commented Sep 17, 2012 at 21:33
  • 1
    Is it unreasonable that SqlConnection would strip the password out for security reasons? Commented Sep 17, 2012 at 21:36
  • 1
    @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. Commented Sep 17, 2012 at 21:54

3 Answers 3

Reset to default
104

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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
@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.
i see, so do you have any idea where i could have missed up? and again really thank you :)
@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...
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!
67

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.

Improve this answer

Comments

8

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.

Improve this answer

1 Comment

Thank you. This helps me to fix the connection string. But how can I get the SqlTransaction property? @ProgrammingLlama Please help

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.