A note on Entity Framework connection strings when trying to access a provider connection string when SQL Server authentication is used

Just a quick note related to Entity Framework I found about while writing from C# code today: if you try to get the provider connection string from your EF connection string, and you use SQL Server authentication, the password might be missing from the resulting string.

Whether the password is returned depends on the way you are accessing the connection string. This is the most straightforward way:

string providerConnectionString = entities.Database.Connection.ConnectionString;

However, the problem with this simple method is that the "password=" part from the connection string is missing even though it does exist in the EF connection string. Thus, if you need to for example use an SqlConnection or SqlCommand object directly, the above connection string fails to work correctly, as it cannot authenticate without a password.

Here's a slightly more complex way, which works as expected:

ConnectionStringSettings setting = 
    ConfigurationManager.ConnectionStrings["MyEntities"];
string entityConnectionString = setting.ConnectionString;
EntityConnectionStringBuilder builder = new  
    EntityConnectionStringBuilder(entityConnectionString);
string providerConnectionString = builder.ProviderConnectionString;

When using the above method, the connection string does contain the password, and a connection with (say) the SqlConnection class works as expected.