Deploy MS SQL database in Amazon RDS

Create database Security Group

First, create a Security Group for your database:

  1. Navigate to AWS console -> Services.
  2. Go to the Compute section and select EC2.
  3. Go to the Network & Security section and select Security Groups.
  4. Click Create Security Group and provide your values for the inbound rules.
  5. After created, select the Security Group and go to Actions -> Add/Edit Tags.
  6. In the Key text box type Name, and in the Value text box enter MS SQL DB.
  7. Click Save.

Create MS SQL database

  1. Navigate to AWS Console -> Services.
  2. Go to the Database section and select RDS.
  3. Go to Instances and click Launch DB Instance.
  4. Select Microsoft SQL Server -> SQL Server Enterprise Edition.
  5. For Use Case, select Production.
  6. Specify the DB Details.
  7. Add your DB Instance Identifier and Master user credentials.


  8. Click Next.
  9. Configure Advanced Settings:
    • Launch the Database in your VPC.
    • Choose whether your database to be publicly accessible or no.
      Select Yes if you want to allow EC2 instances and devices outside the VPC that hosts the DB instance to connect to this DB instance. If you select No, Amazon RDS will not assign a public IP address to the DB instance, and no EC2 instance or devices outside of the VPC will be able to connect. If you select Yes, you must also select one or more VPC security groups that specify which EC2 instances and devices can connect to the DB instance. Click here to learn more.
    • Select the Availability Zone from the current region in which you want the DB instance created.
    • Note: For high availability and fault tolerance, we recommend you to create a DB replica in different zones in the previous step.

  10. Select the Database Security Group you created as described in the Create database Security Group subtopic.
  11. Add your Database Options:
    • Set a Database name.
    • Specify a Database port.
    • Select the previously created Parameter Group (only if you would like to force the SSL connections)
    • Leave the default Option Group.
    • Select the Encryption – enable or disable it.
    • Select Backup retention period (not mandatory).
    • Select Monitoring (not mandatory) – enable or disable it.
    • Select Maintenance (not mandatory) – enable or disable it (not mandatory).
  12. Click on Create Database.

  13. When you finish with your setup, click Launch DB instance.

Using SSL with Microsoft SQL Server database

You can use Secure Sockets Layer (SSL) to encrypt connections between your client applications and your Amazon RDS DB instances running Microsoft SQL Server. SSL support is available in all AWS regions for all supported SQL Server editions.

When you create a SQL Server DB instance, Amazon RDS creates an SSL certificate for it. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks.

Force all SSL connections

The first way is to secure your database is to Force SSL for all connection – this happens transparently to the client, and the client does not have to do any work to use SSL.

You could do this by creating a Parameter Group:

  1. Navigate to AWS console -> Services.
  2. Go to the Database section and select RDS.
  3. Go to Parameter groups and click Create parameter group.
  4. Select the values in the following fields:
    • Parameter group family
    • Group name
    • Description (optional)
  5. When you finish with your selections, click Create.
  6. After creation, select the parameter group and go to Parameter group actions and click Edit.
  7. Find the parameter that you would like to change and click Edit parameters.
    • Set the rds.force_ssl parameter to true to force connections to use SSL. The rds.force_ssl parameter is static, so after you change the value, you must reboot your DB instance for the change to take effect.
  8. Insert the desired value and then click Save changes.

Assign the parameter group to your database

  1. Navigate to RDS Instances.
  2. Select your database and then go to Instance actions -> Modify.
  3. Go to the Database options section and select your DB parameter group from the drop-down list.
  4. Save and apply changes immediately.
  5. Restart your database.

Encrypt Specific Connections

To use SSL from a specific client, you must obtain certificate, upload it to the client computer and then specify it during the SecureTransport installation.

You could execute the following command to obtain the certificate file and certificate CN:

  • openssl s_client -connect <host>:<ssl_port> where <host> is the database Endpoint.

Learn more about Using SSL with a Microsoft SQL Server DB Instance.

Connect to your MS SQL database

  1. Navigate to AWS console -> Services.
  2. Go to the Database section and select RDS.
  3. Navigate to Instances.
  4. Select your newly created Database and then go to Instance Actions ->See Details.
  5. Under the Security and network section, see Endpoint: you will need to copy & paste it in the next step.
  6. Use Microsoft SQL Management Studio on your Administration Host.
    Add new connection and provide values for the following options:
    • Server type – select Database Engine
    • Server name – paste the newly created endpoint from the RDS MS SQL Database
    • Authentication – select SQL Server Authentication
    • Login – database administrator login name
    • Password – database administrator password

Create tables and set ownership of the MS SQL database

Use the following script to create a table:

USE master;
GO
CREATE DATABASE STDB ON PRIMARY (NAME=STDB1, FILENAME ='D:\RDSDBDATA\DATA\STDB.mdf', MAXSIZE = 4GB, FILEGROWTH = 5MB);
ALTER DATABASE STDB ADD FILEGROUP ST_DATA;
ALTER DATABASE STDB ADD FILEGROUP ST_FILETRACKING;
ALTER DATABASE STDB ADD FILEGROUP ST_SERVERLOG;
ALTER DATABASE STDB ADD FILE (NAME='ST_DATA_STDB', FILENAME = 'D:\RDSDBDATA\DATA\ST_DATA_STDB.ndf', SIZE = 200MB, FILEGROWTH=50MB) TO FILEGROUP ST_DATA;
ALTER DATABASE STDB ADD FILE (NAME='ST_FILETRACKING_STDB', FILENAME='D:\RDSDBDATA\DATA\ST_FILETRACKING_STDB.ndf', SIZE = 50MB, FILEGROWTH=10MB) TO FILEGROUP ST_FILETRACKING;
ALTER DATABASE STDB ADD FILE (NAME='ST_SERVERLOG_STDB', FILENAME='D:\RDSDBDATA\DATA\ST_SERVERLOG_STDB.ndf', SIZE = 200MB, FILEGROWTH=10MB) TO FILEGROUP ST_SERVERLOG;
ALTER DATABASE STDB SET READ_COMMITTED_SNAPSHOT ON;
GO

Use the following script to create user login:

USE STDB;
CREATE LOGIN STDB WITH PASSWORD='STDB', DEFAULT_DATABASE=STDB, CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF;
GO
USE STDB;
EXEC sp_grantdbaccess 'STDB', 'STDB';
EXEC sp_addrolemember 'db_ddladmin', 'STDB';
EXEC sp_addrolemember 'db_datareader', 'STDB';
EXEC sp_addrolemember 'db_datawriter', 'STDB';
GO

Related Links