How to handle expired SQL logins’ passwords in client code
Microsoft is always advise customers to use the new security features for SQL Logins in SQL Server. As you know SQL Logins are the logins where the login name and password are saved inside SQL Server and SQL Server is responsible for authenticated this login. We don't depend on Windows to validate them. Of course Windows authentication is more recommended as it's more secure but many times the customer has to use SQL login.
Starting SQL Server 2005, SQL Logins can have a policy inherited from the domain policy or the local machine policy so we can control the complexity of the password and how often this password will be expired. For more information about the password policy, refer to https://msdn.microsoft.com/en-us/library/ms161959(SQL.90).aspx
But usually when I recommend customers to use password expiration, they are concerned about their live application and how the application would respond the situation when the password is expired. I wrote a sample application by C# to handle this. For unmanaged code, you can follow up the instructions in this article.
To use managed code to respond to the password expiration, first we need to detect when the password expires. This would be by checking the SQLException error number, it's number 18487.
To change the password, I'm using SQL Server Management Objects (SMO) I'm assuming the structure of the connecting module would be like this
- Try to connect normally to SQL Server, using SqlConnectionStringBuilder class to build the connection string. I'm assuming that the client saves the password in a protected storage so we need a function like getPassword()to get the password from this storage
- If the password is expired (error 18487), the application is responsible to generate a new password generateNewPassword()and use the ServerConnection class to change the password.
I wrote pseudo implementation for getPassword() and generateNewPassword()but in real code, you should add your own algorithm.
Here's the code. It's implemented as a console application
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace TestSQLConnection
{
class Program
{
static void Main(string[] args)
{
SqlConnectionStringBuilder builder=new SqlConnectionStringBuilder();
builder.DataSource="myserver\\sql2k5";
builder.InitialCatalog="AdventureWorks";
builder.UserID="test";
builder.Password=getPassword();
SqlConnection conn = new SqlConnection(builder.ConnectionString);
try
{
Console.WriteLine("Trying to connect to SQL Server...");
conn.Open();
Console.WriteLine("Connection succeeded Connected to server version {0}, \n wiating your command...",conn.ServerVersion);
Console.ReadLine();
}
catch (SqlException ex)
{
Console.ForegroundColor = ConsoleColor.Green;
if (ex.Number == 18487) //the password expired
{
Console.WriteLine("the login password has expired, do you want to generate a new passwoed and use it(type 'Y' or 'N')");
string answer=Console.ReadLine();
if (answer == "Y" || answer=="y")
{
string newPassword = generateNewPassword();
ServerConnection srvConn = new ServerConnection();
srvConn.ServerInstance = "myserver\\sql2k5";
srvConn.LoginSecure = false;
srvConn.Login = "test";
srvConn.Password = getPassword();
srvConn.ChangePassword(newPassword);
Console.WriteLine("Password has changed");
savePassword();//save the new password for next connection
System.Threading.Thread.Sleep(3000);//give SQL server a chance to change the password and be ready
Console.WriteLine("Trying to connect to SQL Server again...");
builder.DataSource = "myserver\\sql2k5";
builder.InitialCatalog = "AdventureWorks";
builder.UserID = "test";
builder.Password = getPassword();
conn = new SqlConnection(builder.ConnectionString);
conn.Open();
Console.WriteLine("Connection succeeded Connected to server version {0}, \n wiating your command...", conn.ServerVersion);
Console.ReadLine();
}
else //close
{
System.Diagnostics.Process.GetCurrentProcess().CloseMainWindow();
}
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("error while opening connection");
Console.WriteLine("Error No: " + ex.Number);
Console.WriteLine("Message : " + ex.Message);
Console.ForegroundColor = ConsoleColor.White;
Console.ReadLine();
}
}
finally
{
if (conn != null && conn.State == System.Data.ConnectionState.Open)
conn.Close();
}
}
internal static string getPassword()
{
//you should implement the logic to get the password from encrypted storage
return "test";
}
internal static void savePassword()
{
//you should implement the logic to save the password in encrypted storage
}
internal static string generateNewPassword()
{
//you should implement the logic to randomly generate new complex password
return "test";
}
}
}