Sql-server – Find application password expiration date

SQL Server does not track this information by default, so you will not be able to look back and see when these happened in the past. The trace event is 107 and it is not in the default trace (and I don't see anything in the system_health extended event session either).

For the future, you can set up an audit specification (no idea if this is an option for you because you didn't specify version or edition). You may also be able to capture these events using a server-side trace or an event notification on ALTER LOGIN.

Server-Side Trace

Here is a script that will produce a server-side trace that captures just the Audit Login Change Password Event (and this works for both ALTER LOGIN and sp_password) - please be sure to update the path in the first EXEC line:

DECLARE @TraceID INT, @mfs BIGINT;
SET @mfs = 5;

EXEC sp_trace_create @TraceID OUTPUT, 0, N'C:\path\tracefile', @mfs, NULL; 

EXEC sp_trace_setevent @TraceID,107,1, 1;
EXEC sp_trace_setevent @TraceID,107,11,1;
EXEC sp_trace_setevent @TraceID,107,8, 1;
EXEC sp_trace_setevent @TraceID,107,12,1;
EXEC sp_trace_setevent @TraceID,107,14,1;
EXEC sp_trace_setevent @TraceID,107,40,1;
EXEC sp_trace_setevent @TraceID,107,42,1;

EXEC sp_trace_setstatus @TraceID, 1;

Later when you want to review the events the trace captured, you can see the last update time for each login with the following query:

DECLARE @path NVARCHAR(260);

SELECT @path = [path]
FROM    sys.traces
WHERE  id = <traceID from above>;

SELECT TargetLoginName, LastTimeStamp = MAX(StartTime)
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 107 -- in case you've added other events
  GROUP BY TargetLoginName;

You'll probably want to schedule this query periodically and snapshot the results somewhere, since the data won't hang around in the default trace forever.

Event Notifications

If you'd rather have an EVENT NOTIFICATION instead of a server-side trace running, you can do it this way. First, create a table in MSDB to track the login name and the time of the change (you may want to capture other information from the event as well, like I did with the trace above, but this is a bit more involved and I'm trying to maintain some brevity).

USE [msdb];
GO

CREATE TABLE dbo.PasswordChanges
(
    LoginName     SYSNAME,
    EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Now, we need a queue and a service for our notification:

CREATE QUEUE PasswordChangeQueue;
GO

CREATE SERVICE PasswordChangeService
    ON QUEUE PasswordChangeQueue 
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

And a procedure to actually do something when the event happens:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @message_body XML;

   WHILE (1 = 1)
   BEGIN
       WAITFOR ( RECEIVE TOP(1) @message_body = message_body
          FROM dbo.PasswordChangeQueue), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChanges(LoginName) 
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
   END
END
GO

And finally set the notification to use this procedure.

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Now change the password for a few logins, and you should see results from the following query:

SELECT LoginName, LastChange = MAX(EventDateTime)
  FROM dbo.PasswordChanges
  GROUP BY LoginName;

You'll need to decide whether you want to use OS authentication or use a password file. See http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba006.htm#i1006628 to help you decide.

Will users administer the database by first logging in to the database server using eg ssh? Then you can use OS authentication.

Best practice for OS authentication: No-one should be logging in as the oracle OS user. Each DBA should have their own OS account and login as that. Their OS account should be a member of the OS DBA group (remember that only members of the OS DBA group can login using sqlplus / as sysdba).

If you want to allow remote administration, then you'll either need a secure connection to the database (just like SSH provides that to the server when administering locally) or you'll need to use a password file.