Specifies a stored procedure that is called when a user connects via a database login or web service.
|PUBLIC role||For current user||For other users|
|Allowed to set permanently?||Yes, with SET ANY SECURITY OPTION||Yes, with SET ANY SECURITY OPTION||Yes, with SET ANY SECURITY OPTION|
|Allowed to set temporarily?||Yes, with SET ANY SECURITY OPTION||Yes (current connection only), with SET ANY SECURITY OPTION||No|
By default, the sp_login_environment stored procedure is called. The login procedure is called after all the checks have been performed to verify that the connection is valid. The procedure specified by the login_procedure option is not executed for event connections, but it is executed for normal login and web service connections.
You can create a custom login procedure by creating a new stored procedure and setting login_procedure to the owner and name of this new procedure.
This custom procedure should call the dbo.sp_login_environment stored procedure at some point during its execution since it detects when a TDS connection occurs and sets appropriate database options using the dbo.sp_tsql_environment stored procedure. Failure to do so can break TDS-based connections. It is not a good practice to revise either of the dbo.sp_login_environment or dbo.sp_tsql_environment stored procedures.
A password expired error message with SQLSTATE '08WA0' can be signaled by a user-defined login procedure to indicate to a user that their password has expired. Signaling the error allows applications to check for the error and process expired passwords. However, it is better to use a login policy to implement password expiry and not a login procedure that returns the expired password error message.
If you use the NewPassword=* connection parameter, signaling this error is required for the client libraries to prompt for a new password. If the procedure signals SQLSTATE '28000' (invalid user ID or password) or SQLSTATE '08WA0' (expired password), or the procedure raises an error with RAISERROR, the login fails and an error is returned to the user. If you signal any other error or if another error occurs, then the user login is successful and a message is written to the database server message log.
The following example shows how you can limit the total number of database connections.
CREATE PROCEDURE DBA.login_check( ) BEGIN DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000'; // Allow a maximum of 3 concurrent connections IF( DB_PROPERTY( 'ConnCount' ) > 3 ) THEN SIGNAL INVALID_LOGON; ELSE CALL dbo.sp_login_environment; END IF; END go GRANT EXECUTE ON DBA.login_check TO PUBLIC go SET OPTION PUBLIC.login_procedure='DBA.login_check' go
The following example shows how you can block connection attempts if the number of failed connections for a user exceeds 3 within a 30 minute period. All blocked attempts during the block out period receive an invalid password error and are logged as failures. The log is kept long enough for a DBA to analyze it.
CREATE TABLE DBA.ConnectionFailure( pk INT PRIMARY KEY DEFAULT AUTOINCREMENT, user_name CHAR(128) NOT NULL, tm TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP ) go CREATE INDEX ConnFailTime ON DBA.ConnectionFailure( user_name, tm ) go CREATE EVENT ConnFail TYPE ConnectFailed HANDLER BEGIN DECLARE usr CHAR(128); SET usr = event_parameter( 'User' ); // Put a limit on the number of failures logged. IF (SELECT COUNT(*) FROM DBA.ConnectionFailure WHERE user_name = usr AND tm >= DATEADD( minute, -30, CURRENT TIMESTAMP ) ) < 20 THEN INSERT INTO DBA.ConnectionFailure( user_name ) VALUES( usr ); COMMIT; // Delete failures older than 7 days. DELETE DBA.ConnectionFailure WHERE user_name = usr AND tm < DATEADD( day, -7, CURRENT TIMESTAMP ); COMMIT; END IF; END go CREATE PROCEDURE DBA.login_check( ) BEGIN DECLARE usr CHAR(128); DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000'; SET usr = CONNECTION_PROPERTY( 'Userid' ); // Block connection attempts from this user // if 3 or more failed connection attempts have occurred // within the past 30 minutes. IF ( SELECT COUNT( * ) FROM DBA.ConnectionFailure WHERE user_name = usr AND tm >= DATEADD( minute, -30, CURRENT TIMESTAMP ) ) >= 3 THEN SIGNAL INVALID_LOGON; ELSE CALL dbo.sp_login_environment; END IF; END go GRANT EXECUTE ON DBA.login_check TO PUBLIC go SET OPTION PUBLIC.login_procedure='DBA.login_check' go
The following example shows how to signal an error indicating that the user's password has expired. However, it is better to use a login policy to implement password expiry notification.
CREATE PROCEDURE DBA.check_expired_login( ) BEGIN DECLARE PASSWORD_EXPIRED EXCEPTION FOR SQLSTATE '08WA0'; IF( condition-to-check-for-expired-password ) THEN SIGNAL PASSWORD_EXPIRED; ELSE CALL dbo.sp_login_environment; END IF; END;