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
– Crono, Commented Apr 2, 2014 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
– Crono, Commented Apr 2, 2014 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
– mellamokb, Commented Apr 2, 2014 at 18:43
@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
– Crono, Commented Apr 2, 2014 at 18:52

gregmac · Accepted Answer · 2014-04-08 15:25:48Z

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.

Jonathan Magnan · Accepted Answer · 2017-04-05 19:16:15Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related