Monitor SQL Login creation on Amazon RDS for SQL server

Maintaining database security is indispensable for the success of any organization. Implementing database user authentication and authorization is an important step in securing a database system. Conventional database authentication is based on a username-password mechanism. This process requires constant time and effort from both DBAs and end users to manage the credentials. In contrast, incorporating relational database authentication with a centralized authentication service such as Microsoft Active Directory (AD) is a more secure and widely-accepted industry best practice.

SQL Server supports two modes of authentication

Windows
Mixed Mode

Amazon Relational Database Service (Amazon RDS) for SQL Server supports only mixed mode authentication. In scenarios, where customers want to leverage windows authentication alone, they can implement a custom solution to monitor the creation of SQL logins.

In this post, we show you a solution where you get an email notification when a SQL Login is created within your Amazon RDS for SQL Server instance.

Solution overview

The solution in this post uses Amazon RDS for SQL Server configured with Database Mail. Database Mail enables you to send messages from the SQL Server to users by using a Simple Mail Transfer Protocol (SMTP) server. The Amazon RDS for SQL Server instance has a SQL agent job configured to periodically monitor the creation of SQL logins within the database instance. Upon detecting a SQL login creation, the SQL Server agent job sends an email message with the login details to the Database administrators so that they can take appropriate action.

The following diagram illustrates our solution architecture.

Amazon Simple Email Service (Amazon SES) is an email platform that provides an easy and cost-effective way for you to send and receive email using your own email addresses and domains.

Prerequisites

To get started, you must have the following prerequisites:

An Amazon RDS for SQL server instance.
SQL server Database Mail configured with Amazon SES.
Familiarity with SQL Server Management Studio (SSMS).
Familiarity with creating SQL Agent job.

Configure Database Mail to send emails via Amazon SES

In the post Using Database Mail on Amazon RDS for SQL Server we showed you detailed steps for configuring Database Mail with Amazon SES. You can use Database Mail to send email messages to users from your Amazon RDS on SQL Server database instance. Amazon RDS supports Database Mail for all SQL Server versions on the Web, Standard, and Enterprise Editions.

After configuring the Database Mail, you can send a test email to quickly validate whether or not it’s successful.

Creating a SQL Agent Job

Your next step is to create a SQL agent job and set a schedule to periodically monitor the SQL logins.

Log in to SQL Server using SQL Server Management Studio.
Open the context menu (right-click) for Jobs under the SQL Server Agent, then choose New Job.
Provide the name for the SQL Agent job.
Choose Steps under the Select a page section and choose New.

Provide a step name and copy-paste the following T-SQL script within the command section. This script monitors the sys.sql_logins DMV and sends an email notification when it finds an SQL Login other than the master user.

DECLARE @SQLLoginCount INT
DECLARE @myTableVariable TABLE (SQLLogin Nvarchar(50), LoginCreateDate  datetime)
DECLARE @mailBody Nvarchar(MAX);
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @SQLLoginCount = (SELECT count(*) FROM master.sys.sql_logins where name not like '##%' and name not in ('rdsa', 'admin')) --Edit master username
Insert into @myTableVariable (SQLLogin, LoginCreateDate) Select [name], [create_date] FROM master.sys.sql_logins where name not like '##%' and name not in ('rdsa', 'admin') --Edit master username

If @SQLLoginCount > 0
    BEGIN
         SET @xml = CAST(( SELECT [SQLLogin] AS 
         'td','',LoginCreateDate AS 'td',''
         FROM @myTableVariable 
         FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

         SET @body ='<html><body><H3>A new SQL login(s) was detected on the RDS SQL Server. Please verify why this login(s) was created</H3>
         <table border = 1> 
         <tr>
         <th> SQLLogin </th> <th> LoginCreateDate </th> </tr>'    
 
         SET @body = @body + @xml +'</table></body></html>'
         EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'Notifications',
         @recipients = '<xyz@example.com>', --Edit email address
         @body = @body,
         @body_format ='HTML',
         @subject = 'New SQL Login(s) Created On RDS SQL server ';
    END

The following changes have to be made in the T-SQL script as per your preferences:

admin – We have used the default ‘admin’ as master user in this solution. Edit the master username if you have changed it from the default ‘admin’. This change needs to be made twice in the script.
@recipients – Edit the email address of the recipient as required. This change needs to be made once in the script.

Once the changes to the script have been made, choose OK.
Choose Schedules under the Select a page section and choose New.
Provide a Name for the Job Schedule, select the frequency as per your preference, and choose OK.
Choose OK to create the SQL server agent job.

Testing the setup

Create a SQL Login. You can either do this using T-SQL or SSMS.

USE [master]
GO
CREATE LOGIN [sid] WITH PASSWORD=N'******', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

After a few minutes, you’ll receive an email notification alerting you about the new SQL login. The following screenshot shows an example email.

Clean up

If you are testing this solution, to avoid additional charges and to remove the components created, complete the following steps:

Using SSMS, connect to the RDS instance and Delete the SQL Server Agent job created.
On the Amazon RDS console, select the RDS SQL Server instance, and on the Actions menu, choose Delete.

Summary

In this post, we showed you how to create a SQL Server agent job that sends an email notification through Amazon SES when a SQL Login is created within your Amazon RDS for SQL server instance.

If you have any comments or questions, share them in the comments section.

About the Authors

Sid Vantair is a Solutions Architect with AWS covering Strategic accounts. With over a decade of experience in working with relational databases, he thrives on resolving complex technical issues to overcome customer hurdles. Outside of work, he cherishes spending time with his family and fostering inquisitiveness in his children.

Poulami Maity is a Database Specialist Solutions Architect at Amazon Web Services. She works with AWS customers to help them migrate and modernize their existing databases to AWS cloud.

AWS Database Blog