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 http://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
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);
Console.WriteLine("Connection succeeded Connected to server version {0}, \n wiating your command...", conn.ServerVersion);
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;
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