Safer passwords with SqlCredential

  • Article

IntroductionMany users of SqlClient with SQL Server Authentication have expressed interest in setting credentials outside of the connection string to mitigate the memory dump vulnerability of keeping the User Name and Password in the connection string. Starting with .Net Framework 4.5, we have introduced the ability to set the credentials outside of the connection string via the new SqlCredential Credential property of SqlConnection. Now the developer can create a SqlCredential object with a UserId and a SecureString Password to hold the credential values of a connection when connecting to a server. This helps mitigate the threat of credentials being leaked out to the page file in a page swap or being evident in a crash dump.

Use Case Example

System.Windows.Controls.TextBox txtUserId = new System.Windows.Controls.TextBox();

System.Windows.Controls.PasswordBox txtPwd = new System.Windows.Controls.PasswordBox();

using (SqlConnection conn = new SqlConnection("Server=myServer;Initial Catalog=myDB;"))

{

SecureString pwd = txtPwd.SecurePassword;

pwd.MakeReadOnly();

SqlCredential cred = new SqlCredential(txtUserId.Text, pwd);

conn.Credential = cred;

conn.Open();

}

Alternatively we can use the new SqlConnection constructor overload which takes both a connection string and credential object:

SecureString pwd = txtPwd.SecurePassword;

pwd.MakeReadOnly();

SqlCredential cred = new SqlCredential(txtUserId.Text, pwd);

using (SqlConnection conn = new SqlConnection("Server=myServer;Initial Catalog=myDB;", cred))

{

conn.Open();

}

SqlCredential Class

More information about the new SqlCredential class can be found at:
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.SqlCredential(v=vs.110).aspx

For information on how to get or set the SqlConnection.Credential property, please refer to:
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.credential(v=vs.110).aspx

It’s important to note that the SqlCredential constructor only allows SecureString marked as read only to be passed in as the Password parameter or it will raise an ArgumentException. The new credential property is incompatible with existing UserId, Password, Context Connection=True, and Integrated Security=True connection string keywords, and setting the credential property on an open connection is not allowed. It is strongly recommended that you set PersistSecurityInfo=False (default) so the credential property is not returned as part of the connection once it is opened.

Connection Pooling with Credential Property

With this new improvement now the connection pooling algorithm also takes the Credential property into consideration in addition to the connection string property when creating connection pools and getting connections from the pool. Connections with the same connection string and same instance of Credential property will use the same connection pool, but connections with different instances of the Credential property will use different connection pools, even if they use the same UserId and Password. For example, the developer tries to open several connections with different configurations as below:

string str1 = “Server=myServer;Initial Catalog=myDB;User Id=user1;Password=pwd1;”;
string str2 = “Server=myServer;Initial Catalog=myDB;”;
SqlCredential cred1 = new SqlCredential(user1, pwd1);
SqlCredential cred2 = new SqlCredential(user1, pwd1);
SqlCredential cred3 = new SqlCredential(user2, pwd2);

  1. 1.       SqlConnection conn1 = SqlConnection(str1, null); //different connection string
  2. 2.       SqlConnection conn2 = SqlConnection(str2, cred1); //different credential object
  3. 3.       SqlConnection conn3 = SqlConnection(str2, cred2); //different credential object
  4. 4.       SqlConnection conn4 = SqlConnection(str2, cred3); //different credential object and user/pwd

All 4 connections will use different connection pools, the most important thing to note here is that conn2 and conn3 will not share the same connection pool, as they use different credential object instances, even though those two instances use the same UserId and Password.

Using SqlCredential with other classes

To use the new secure password feature with SqlDataAdapter or SqlBulkCopy, a SqlConnection object with SqlCredential property needs to be constructed first and passed into the appropriate constructor of SqlDataAdapter and SqlBulkCopy that takes in a SqlConnection object rather than a connection string. The SqlDependency class currently does not support starting a listener for receiving dependency change notifications from SQL Server for both connection string and credential.

Of course the usage of SQL Server Integrated Authentication Mode is still the recommended way to authenticate for users with an Active Directory® infrastructure as there is no credential propagation and all security sensitive information is stored in the Active Directory’s database. And the usage of SQL Server Mixed Mode Authentication with UserId and Password specified in the connection string remains unchanged.

Stay safe and secure,

Wenchang Liu

ADO.NET Software Development Engineer in Test