How can I hide userid/password in a SQL Server connection string?

Question

Before I re-invent the wheel, is there a standard way to hide at least a password in a SQL Server connection string?

(Written in C#)

I have a highly multi-threaded app that is aware of multiple databases, and so to aid in troubleshooting and debugging there are various places where the connection string can be logged. I can't control what customers using this product use for connection strings and so it's not at all uncommon to find passwords in them. I know this because it's also not uncommon that people turn on DEBUG-level logging and send us log files along with a problem report, and those logs contain their database passwords (and as a result our support ticket system contains passwords).

I realize best practice is to NOT put passwords in connection strings, but that part is out of my control (unless of course we change our app so it refuses to run if you give it a connection string with an unencrypted password... that seems a bit draconian to me).

Really want I want it to log is:

Server=myServerAddress;Database=myDataBase;User Id=****;Password=*****;

(I'm up in the air over if User id/name is a sensitive thing or not, and/or useful to log or not -- comments on that welcome).

I could build a simple regex (eg /Password=[^;]+/), but before I do I just want to see if there are other cases I'm not considering, especially if this is done already.

The best would be to use SSPI context but I'm guessing that's not an option in your scenario. — Crono, Apr 02 '14 at 18:27
@mellamokb I feel safe assuming it's a .NET language given the OP's history in SO but still that's something I'd like to be sure of too. :) — Crono, Apr 02 '14 at 18:41
I ask because in .NET for example, you have the built-in class `DbConnectionStringBuilder` for parsing and manipulating connection strings easliy. — mellamokb, Apr 02 '14 at 18:43
@mellamokb true but there's no guarantee that the produced connection string will be, password aside, an exact match of the original one. Of course maybe it's not so important, but for logging purposes it might be. — Crono, Apr 02 '14 at 18:47
@mellamokb / chrono: Not a bad idea, because in this case it would probably still be enough for troubleshooting purposes (eg, even if not exact match, you'd still be able to tell which one of a finite set of possible connection strings it was using). — gregmac, Apr 02 '14 at 18:50
@mellamokb add an answer so that the OP can accept it. Using a connection string builder will be easier and likely more efficient than my proposed answer. — Crono, Apr 02 '14 at 18:52
Possible duplicate of [How can I hide my password in my C# Connection string?](http://stackoverflow.com/questions/7581801/how-can-i-hide-my-password-in-my-c-sharp-connection-string) — Michael Freidgeim, Mar 06 '17 at 05:37

score 8 · Accepted Answer · answered Apr 08 '14 at 15:25

Okay, this answer really belongs to @mellamokb but here's what I used:

    /// <summary>
    /// Accepts a SQL Connection string, and censors it by *ing out the password.
    /// </summary>
    /// <param name="connectionString"></param>
    /// <returns></returns>
    public static string CensorConnectionString(string connectionString)
    {
        var builder = new DbConnectionStringBuilder() {ConnectionString = connectionString};
        if (builder.ContainsKey("password"))
        {
            builder["password"] = "*****";
        }
        return builder.ToString();
    }

Couple notes:

It does seem to preserve the ordering of properties.
I don't think I actually found any case where the output wasn't identical to the input (other than the password, of course), but I still wouldn't rely on this. For my purposes, I don't care because it's debug information, and a human looking at this can figure out what database is being used, which is the goal.
I censor the password always by 5 *'s on purpose: I don't want to leak information about the length of the password.

score -1 · Answer 2 · edited Apr 05 '17 at 19:16

In c# one thing you could do is splitting up the connection string in key / values strings:

var keyValues = connectionString.Split(';');

Then browse through each string and split it again:

var listToKeep = new List<string>();

foreach (var keyValue in keyValues)
{
    var key = keyValue.Split('=')[0].Trim();

    if (key != "Password" && key != "Pwd") listToKeep.Add(keyValue);
}

And now you can recreate your connection string without the password by rejoining the other fields again:

var safeConnectionStringForLog = string.Join(";", listToKeep.ToArray());

As mellamokb suggests in the comments you may want to use SqlConnectionStringBuilder to parse your connection string, remove the password and get a new string. Of course your connection string may not be using the exact same parameters in the exact same order though.

How can I hide userid/password in a SQL Server connection string?

2 Answers2