By: Sadequl Hussain | Updated: 2015-02-17 | Comments (3) | Related: > Security
Problem
MSSQLTips.com recently hosted a webinar on monitoring, securing and protecting SQL Servers in Amazon Web Service's (AWS) RDS environment. The webinar was followed by a Q & A session where attendees had the opportunity to post their questions. This tip tries to answer some of those questions.
Solution
Back in December 2014, I presented in a webinar hosted by MSSQLTips.com. The topic of the webcast was "Monitoring, Securing and Protecting SQL Servers in Amazon RDS". The webinar also featured a short presentation by David Maman from GreenSQL. By the time the hour-long session ended, attendees had posted a number of questions in the online chat panel. There wasn't enough time to answer all of them, but fortunately the host Jeremy Kadlec had recorded the session. In this tip, I will answer some of those questions.
Before we begin...
In case you didn't attend the webinar or haven't followed our series on SQL Server in RDS, I recommend you look at those first. Our articles about SQL Server in RDS are part of an ongoing series. I hope to post a few more tips on the subject, so stay tuned.
Question: Can you still have standard SQL Server logins with Amazon RDS?
Yes. SQL Server in RDS supports Mixed Mode authentication and allows you to connect via standard SQL Server logins only. That means even when the server runs in Mixed Mode authentication, you can't log in with Windows credentials, nor can you create logins mapped to Windows AD accounts.
Why? Well that's because RDS runs its SQL Servers in a completely black-box manner. It's not part of your Windows network when you connect to it. And you don't have any Windows account in the Active Directory Domain the machine is part of. So the only way SQL Server can give you access is through a standard login.
Now if you are wondering about the type of user access I had mentioned in my presentation, that was about AWS user accounts, not SQL Server logins.
There are two ways you can access and interact with SQL Server RDS instances. They are completely different security mechanisms used in two different scenarios. One doesn't relate to other and access via one mechanism doesn't mean you have automatic access via the other.
First, we talked about AWS user accounts. Every AWS client has a root account that allows it to securely log in to Amazon's Web Service. This root account is created when a client fist signs up with Amazon. This client can be a corporate company, an individual user, another hosting service provider or say an online gaming company. Whatever the identity, a client will always have a root account.
Now obviously the root account has a lot of power associated with it and there's no need to use it for day-to-day administrative tasks. In fact AWS recommends you don't use the root account to log into AWS console, rather create an administrative user for that.
So the AWS account owner can create individual users and groups under the root account. These users can be given different sets of privileges to do different sorts of tasks in your AWS hosted infrastructure. For example, your web developers can have access to S3 buckets only and nothing more. Your server-ops can have access to all EC2 instances while your DBAs may need access to databases servers only. So depending on work function, you can create various AWS groups and assign permissions to those groups. You can then assign users to the groups.
Users can log into AWS services through a number of ways. You can choose to create individual username and passwords for them. This will allow them to log in via the AWS console. Your users could be connecting via custom code in an application. These code can be written in any modern language and framework like .NET, Java or Python. To make it all work, you will need to have AWS create access credentials for the users. The credentials (an Access Key ID and a Secret Access Key) are seamlessly passed on to AWS during log in.
An AWS administrator can perform all these user-related functions from the Identity and Access Management (IAM) module.
Now think about a group of junior DBAs you have hired. Their job is to create RDS instances and back them up. They can also stop, snapshot and terminate RDS instances. They can change Parameter Groups or modify an instance's properties. Traditional best practice is to create a group, assign all these permissions to that group, then create individual users and add to that group.
Mind you, these activities are outside SQL Server: it's to do with what people can do to RDS as an AWS service, not what they can do with SQL Server. It's got nothing to do with SQL Server access. So the functions of creating, deleting, modifying, snapshotting or terminating instances are common across all database platforms supported by RDS. A DBA could be creating a SQL Server instance in one hand and deleting a PostgreSQL instance in another: all logged in as the same user.
DBAs interacting with your RDS instance don't get an automatic entry to SQL Server. To administer SQL Server from its database engine, DBAs still need SQL Server logins to connect. An RDS SQL Server instance allows you to create standard SQL Server logins only. So after you grant AWS users permission to work with RDS, you have to create a new set of logins for these users in SQL Server itself.
Question: Are all of the permissions at the RDS instance level or can you still assign rights for tables, stored procedures, etc.?
Yes, you can assign and fine-tune rights and privileges on tables, stored procedures, views and functions in RDS SQL Server instances. This is just like you would do in an on-premises SQL Server instance.
The DBA controls access on database and objects using native SQL Server commands like CREATE LOGIN/CREATE USER/CREATE SCHEMA/GRANT./REVOKE/DENY, etc.
Question: Is native SQL Server encryption available in RDS?
SQL Server Transparent Data Encryption (TDE) is available in AWS RDS. You can use TDE with SQL Server 2008 R2 and 2012 Enterprise Editions. With TDE, data at rest in your database can be encrypted and seamlessly decrypted when it needs to be read. The whole process becomes transparent with proper configuration.
TDE uses a two step process for encrypting data. First, a certificate is created from the database master key of the database you want to encrypt. The certificate is then used to encrypt the data encryption keys. AWS RDS manages and backs up the database master key and the certificate it generates, so you don't have to worry about their safe keeping.
Now the way you enable TDE for SQL Server in RDS is to assign it an Option Group that has TDE enabled. Remember that when you roll out an RDS instance, you have to assign it an Option Group. Option Groups are like DB Parameter Groups in the sense that they define certain behaviors of the instance. If you don't have a custom Option Group to assign to RDS, AWS assigns the default Option Group.
You can either choose to add the TDE option to the default Option Group or create a new Option Group and add TDE there. Next, you assign the Option Group to your RDS instance and SQL will have TDE enabled.
The following images show how TDE enabled Option Groups are created and assigned to RDS instance.
Question: Can you move database backups outside the Amazon RDS infrastructure before deleting an instance?
Short answer, no. RDS allows you to make backups in two different ways (and again I have covered this topic before). One is via scheduled backups, the other is via snapshots. The difference between scheduled backups and snapshots is that snapshots don't allow a point-in-time-recovery (PITR) - it's just a snapshot of the instance at any particular time. Scheduled backups allow you to go back any time within the last thirty-five days. Another difference is that when you delete an RDS instance, all its backups also get deleted, snapshots don't. In fact when you try to delete an instance, RDS gives you a chance to create a final snapshot. The idea is that you can always come back and create a new instance from the snapshot, if needed.
RDS doesn't support individual database backups. It's either all or nothing. Whether it's scheduled backups or snapshots, RDS will create a complete image of the full instance that would include every database and users in the system. The snapshot is saved internally by AWS, you can't get a flat file to restore in your on-premises server.
Now suppose your company wants to move away from RDS and wants to run databases from on-premises servers or EC2-hosted SQL instances (if you are wondering what's an EC2 instance - that's your virtual machine in the Amazon cloud.). Snapshots would be pretty useless in such cases. The only solution would be to create empty databases in an on-premises or EC2-hosted SQL Server instance and then using the Import/Export Wizard to migrate the data. Note that you will still need to script-out other components like users and jobs and recreate them in the destination server.
Question: How do you recommend running SQL Server maintenance with RDS? Is it necessary or does Amazon take care of it for you?
Amazon doesn't take care of any SQL Server maintenance job for you. This is still the responsibility of the DBA. Unfortunately, SQL Server in RDS doesn't support maintenance plans. That means you have to write your own custom code to check database integrity, update database statistics, rebuild or reorganize indexes and shrink databases files. Your code would have to be called from scheduled jobs. Those jobs and their schedules become your maintenance plan.
If you are used to maintenance plans doing the dirty job for you, this means extra work on your part. However, there are freely available scripts out there that can save you both time and effort. One such free tool is Ola Hallengren's SQL Server Maintenance Solution. All you need to do is to adapt and customize that codebase for RDS use.
You can also check out these MSSQLTips.com resources:
- Maintenance
- Database Consistency Checks (DBCCs)
- UPDATE STATISTICS
- Fragmentation and Index Maintenance
- Database Shrinking
Question: We send a lot of e-mails from SQL Server, how can we send e-mails directly from Amazon RDS?
Unfortunately Database Mail feature isn't available for SQL Server in RDS.
Having said this, we can try to work around the limitation. Think about it: if your application sends e-mails to business stakeholders after a stored procedure runs successfully, you can change the way it's done. Instead of sending an e-mail, the procedure can add a record to a message table in the database. This record will have a date time stamp, a status flag (success or failure), a short message and a flag indicating if the message has already been sent.
You can then create a "wrapper application" running in an EC2 instance which would connect to your RDS instance and poll the message table. This polling could be periodic, for example every fifteen minutes. If there are new messages in the table, the wrapper would send the contents of the record via e-mail.
The wrapper application doesn't have to be a complex one. You can write a simple client application in any language that can connect to a SQL Server. You don't have to create any e-mail functionality from scratch either: the application can use Simple Notification Service (SNS) or Simple E-mail Service (SES) to send its e-mails.
If you don't want to use traditional languages like Python or C#, there's another way. Your polling commands could be coming from another SQL Server running in the EC2 instance. This time, you will have control over Database Mail. The stored procedure that fetches new records from the RDS message table would use Database Mail to send its e-mails.
Question: Do the alerts you mentioned include SQL Server severity such as severity level above 20 or is there no concept of the native alerts?
The alerts that I talked about in presentation were from AWS CloudWatch. CloudWatch is a web service that monitors almost all AWS services and resources. You can configure it to send notifications when some threshold value is crossed. That's just a very simple definition of what CloudWatch can do. It's a extremely powerful tool, however, CloudWatch can monitor AWS resources only from the host level, not within the application itself.
What this means is you can configure CloudWatch to monitor your RDS instances on their host and network level only and it can't get any deeper. If you think about an on-premises server, that's like using the Resource Monitor and PerfMon counters for disk, CPU, RAM and network only. When CloudWatch alarms notify you the average disk write latency is high, you know there's an I/O issue with your database. There's no way to look at the underlying host so you have to analyze your queries or look at different wait types and troubleshoot from there.
Now if SQL Server fails itself, it will raise a critical or fatal error before it crashes and CloudWatch can't trap it for you. That makes sense because RDS is a managed environment - AWS retains control over everything - you have limited access to the database server only. If you are to define an alert, that notification needs to go to an operator. And how do you define how an Operator will be notified? If it's an e-mail, SQL Server will have to talk to an e-mail server, which is not present in the RDS ecosystem.
Question: What's the best alternative for native SQL Server Alerts?
That's the next logical question. The best alternative to native SQL Server Alerts is to actually monitor SQL Server error log from outside RDS and generate our own Alerts. RDS does expose the SQL Server error log and it's possible to read off records from it. Chances are, SQL will create an error log entry when a fatal or critical event happens. All you need to do is to poll SQL error log for such events.
Just like in our previous example, we can write an application that would run from an EC2 instance and poll the RDS SQL Server error log every few minutes. If there's a critical or fatal error, the application would send a message to Simple Notification Service, which in turn can notify you.
How would you write such an application? Well, there are many choices. AWS exposes its APIs for different languages. These APIs are available in various SDKs for popular programming languages. AWS also offers something called the Command Line Interface or CLI. CLI is basically a simple application that can be downloaded and installed on any machine and used for accessing AWS resources. So instead of clicking through the AWS Console in a browser, you could be creating, deleting or snapshotting RDS instances from command line. And since it has command line utilities, you can automate those utilities using scripting languages like Bash, Python or PowerShell.
To give an example, there are three commands that come with the RDS CLI:
rds-describe-db-log-files, rds-watch-db-logfile and
rds-download-db-logfile. Using a PowerShell script, you can call these
commands to get a list of error log files, download or parse them. If your script
finds any fatal or critical error message in the log file(s), it can send you a
message. This PowerShell script could be running from an EC2 instance that acts
as your "monitoring server". All you need to do is to download and install the RDS
CLI in that machine and configure it to access your RDS instance.
Conclusion
As I like to say, RDS is not an one-size-fit-all solution. It has its limitations. But despite those limitations, there are applications that can make use of its features. In certain cases it can save significant costs associated with running Windows and SQL Server. You need to decide if your applications are suitable for RDS. At the end of the day, you can work around most of the limitations as we have seen; whether it's worth spending time and effort to do so is another factor to consider.
Next Steps
- Create a SQL Server RDS instance, connect to it using SQL Server Management Studio and check what features are not available.
- Check out these case studies from AWS RDS web site.
- Think about ways to circumvent some of the limitations if they are required for your application. For example, what's the next best thing you can do to implement replication? Should there be a job that copies data across to another SQL Server? Can you refactor some of your application's architecture?
- Take some time and watch the webinar.
About the author
Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-02-17
