Safer passwords with SqlCredential

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

• Anonymous
  March 12, 2012
  Interesting.  One question.  It appears that the SqlCredential object should be a singleton to make sure that a new connection is grabbed from the existing connection pool instead of spinning up a new pool each time.  Am I correct?

• Anonymous
  March 16, 2012
  Will Microsoft release a DbCrendential base class? In my opinion this feature must be part of the ADO.NET specification (Db classes); it cannot be a custom feature of the Microsoft .NET Data Provider for SQL Server.

• Anonymous
  April 11, 2012
  Nice security feature for ADO.NET Connections.

• Anonymous
  April 16, 2012
  To Bryan: It is up to the user to manage the usage of their SqlCredential object, we wanted to be able to support scenarios where users need to create multiple SqlCredential instances with different login information as well. For your application, it does make sense to make the Sqlcredential object a singleton if you only have one connection login and want to avoid creating multiple connection pools for the same login.

• Anonymous
  April 16, 2012
  To Cal: Storing Passwords securely for SqlClient/Sql Server Authentication was a popular ask from our customers and we wanted to address this issue with SqlCredential. We didn’t get any request for this for the other drivers like oledb/odbc but we will consider this for future versions.

• Anonymous
  April 24, 2013
  Is there an example of using SQLCredential with an EntityFramework connection string coming from an applictaion web.config?

• Anonymous
  June 04, 2014
  This is great up to a point however there seems no way to use SqlCredential (SecureString password) with the EntityConnection class under Entity Framework.

• Anonymous
  June 10, 2014
  Entity Framework 6.x used DbConnection not SqlConnection so this is next to useless if using Entity Framework. Can we please add SqlCredential to DbConnection also for all of us using Entity Framework.

• Anonymous
  June 09, 2015
  Wenchang, as per your answer "For your application, it does make sense to make the Sqlcredential object a singleton " The securestring will remain in memory for long time in the process? how can we avoid that if we use singleton here...? I think best way to handle connectionpool group to use different connectionpoolKey other than Sqlcredential object as key.