Amazon

SQL Server Agent on AWS/RDS

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), AWS - RDS - SQL Server ( AWS - RDS ), Microsoft, Relational Data Services ( RDS ), sp_add_category (Transact-SQL), SQL Server, Technical, Uncategorized

Background

There is quite a bit of SQL Server management surface area that is not available when running on AWS/RDS.

 

Surface Area

For instance with SQL Server Agent :-

  1. SQL Server Agent
    • Category
      • Add new category
        • API
          • sp_add_category
        • Error
          • Msg 229, Level 14, State 5, Procedure msdb.dbo.sp_add_category
          • The EXECUTE permission was denied on the object ‘sp_add_category’, database ‘msdb’, schema ‘dbo’.
    • Job Step
      • Fetch Job Steps
        • API
          • SELECT * FROM msdb.dbo.sysjobsteps
        • Error
          • Msg 229, Level 14, State 5, Line 71
          • The SELECT permission was denied on the object ‘sysjobsteps’, database ‘msdb’, schema ‘dbo’.

 

Scripting

In terms of scripting, SQL Server Agent is not accessible in an AWS/RDS Environment.

AWS/S3 :- Error – “not authorized to perform: cloudfront:ListDistributions”

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), CLI ( AWS ), S3, S3 Bucket, s3browser.com, Technical ,

 

Background

Attempting to use S3 Browser to retrieve AWS/S3 resources, but getting the error listed in the Error Section.

Usage

S3 Browser

Upon registering AWS/S3 site and attempting to connect to registered site greeted with this prompt.

You are not allowed to get bucket list. Would you like to add an External Bucket?

Image

YouAreNotAllowedTogetBucketsList.2019019.1257PM.PNG

Error

S3 Browser

Access Denied/Is Not Authorized to perform Cloudfront:ListDistributions

Image

S3Browser.02.20190419.0103PM.PNG

Text

[W] [4/19/2019 21:12:09] Task failed (AccessDenied: User: arn:aws:iam::12817171829011:user/dadeniji is not authorized to perform: cloudfront:ListDistributions) TaskID: 236

 

TroubleShooting

Web Browser

Launched Web Browser accessed S3 Service.

Was able to review S3 buckets and folders via AWS Browser Console.

AWS/CLI

Download AWS/CLI

Downloaded AWS/CLI.

Installed AWS/CLI

Configure AWS/CLI

aws configure

Issue AWS/S3 Command

Issues AWS/S3, ls command :-


aws s3 ls

Error

Received error message.

Image

CLI.AccessDenied.01.20190419.0201PM.PNG


An error occurred (AccessDenied) when calling the ListBuckets operation : Access Denied.

 

Remediation

Please work with your AWS Administrators and have them grant your account S3 API Access.

AWS/EC2 – Adding Elastic Block Storage ( EBS )

Daniel Adeniji Amazon, Amazon Elastic Block Store ( Amazon - AWS), Amazon Elastic Compute Cloud ( EC2 ), Amazon Web Services ( AWS ), Technical

Background

Wanted to quickly go over the process of adding Elastic Block Storage ( EBS ) to an EC2 Instance.

What is Elastic Block Storage

A quick definition is available here.

Amazon Elastic Block Store (Amazon EBS) provides persistent block storage volumes for use with Amazon EC2 instances in the AWS Cloud. Each Amazon EBS volume is automatically replicated within its Availability Zone to protect you from component failure, offering high availability and durability. Amazon EBS volumes offer the consistent and low-latency performance needed to run your workloads. With Amazon EBS, you can scale your usage up or down within minutes – all while paying a low price for only what you provision.

Amazon EBS is designed for application workloads that benefit from fine tuning for performance, cost and capacity. Typical use cases include Big Data analytics engines (like the Hadoop/HDFS ecosystem and Amazon EMR clusters), relational and NoSQL databases (like Microsoft SQL Server and MySQL or Cassandra and MongoDB), stream and log processing applications (like Kafka and Splunk), and data warehousing applications (like Vertica and Teradata).

Overview

  1. Logon to your AWS Console
  2. EC2 Instance
    • Access EC2 Instance page ( https://console.aws.amazon.com/ec2/v2/ –  Link )
    • On the left panel,  Access Instances
    • Search for host among list of EC2 Instances
    • Note Instance Information
      • Instance Name
      • Availability Zone
        • Ours is us-east-1c
  3. Storage
    • Access EC2 Page
    • On the left panel,  Access Elastic Block Storage ( EBS ) / Volumes
    • Volumes
      • The EBS Volumes are listed
      • Click on the “Create Volume” link
      • Fill out information on new storage
        • Volume Type
          • General Purpose SSD ( gp2)
        • Storage Size
          • For us we specified 1200 GB
        • Availability Zone
          • The zones is your current region are listed
          • We chose us-east-1c to match our targeted host’s zone
        • Tags
          • Please provide useful identifying tags
  4. Attach Instance to Host
    • Using Remote Desktop connect to host
    • Launch Computer Management
    • Access Storage / Disk Management
    • Scan Disks
    • Initialize Disk
    • Format Disk
    • Assign Drive Letter

 

ScreenShot

Instances

Instance – 01

Find Instance ( using Private IP Address )

management-integrationservices.20190416.0258PM.PNG

Elastic Block Storage ( EBS )

Elastic Block Storage – Volumes

List Volumes

ElasticBlockStorage.List.01.20190416.0228AM.PNG

 

Create Volume

Enter Volume Information

ElasticBlockStorage.CreateVolume.01.20190416.0301AM.PNG

Volume Create Successfully

ElasticBlockStorage.CreateVolume.02.successful.20190416.0302AM.PNG

Attach Volume

Attaching Volume

ElasticBlockStorage.AttachVolumes.01.20190416.0958AM.png

Host

Computer Management

Computer Management \ Storage \ Disk Management

Screen – 01

ComputerManagement.20190416.0235AM.PNG

 

Screen – 03

Disk Scanned

ComputerManagement.03.20190416.0308AM.PNG

Screen – 04

Prepare to Initialize Disk ..

ComputerManagement.04.InitializeDisk.20190416.0309AM.PNG

Screen – 05

Initialize Disk ..

ComputerManagement.05.InitializeDisk.20190416.0310AM.PNG

Screen – 06

Disk Initialized

ComputerManagement.Browse.01.20190416.0311AM.PNG

Screen – 08

New Simple Volume Wizard

ComputerManagement.SimpleVolumeWizard.01.0311AM.PNG

Screen – 08

New Simple Volume Wizard – Specify Volume Size

ComputerManagement.SimpleVolumeWizard.SpecifyVolumeSize.02.0312AM.PNG

Screen – 09

New Simple Volume Wizard – Assign Drive Letter or Path

ComputerManagement.SimpleVolumeWizard.AssignDriveLetter.01.0313AM.PNG

 

Screen – 09

New Simple Volume Wizard – Format Partition

ComputerManagement.SimpleVolumeWizard.FormatPartition.01.0315AM.PNG

Screen – 10

New Simple Volume Wizard – Completing the New Simple Volume Wizard

ComputerManagement.SimpleVolumeWizard.Completing.01.0315AM.PNG

AWS/RDS – SQL Server – Self Identify

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Microsoft, Relational Data Services ( RDS ), SQL Server, Technical

Background

Playing around more and more with AWS/RDS.

There is quite a bit of restrictions compared to self hosted platform.

Self Identify

To use some code on premise or cloud hosted, need to sprinkle in conditional statements.

Code

Outline

Here are some fingerprints to look for :-

  1. Server Name
    • AWS/RDS
      • EC2AMAZ-N4O
  2. Databases
    • RDSADMIN

SQL


declare @servername sysname
declare @bAWS       bit
declare @bAWSRDS    bit

set @servername
	= cast(serverproperty('servername') as sysname)

set @bAWS =
	isNull(charindex( 'EC2AMAZ-', @servername), 0)

set @bAWSRDS =
	isNull( (db_id('rdsadmin') ), 0)

print 'server :- ' + @servername
print 'AWS :- ' + cast(@bAWS as varchar)
print 'AWSRDS :- ' + cast(@bAWSRDS as varchar)

Output

Platform – RDS

Platform – RDS – Image – 01

selfIdentify.AWSRDS.20190205.1202PM

 

AWS – Account’s – Canonical User ID

Daniel Adeniji Amazon Web Services ( AWS ), CLI ( AWS ), Identity and Access Management ( IAM ) - AWS, Management ( S3 ), S3, S3 Bucket, Security Credentials ( AWS - IAM )

Background

Reviewing access to an S3 Bucket and noted a lone account has access.

GUI

Amazon S3 / Bucket

S3Bucket.Permissions.AccessControlList.20190131.0539PM.PNG

Translate Canonical ID

Let us translate the Canonical ID

Outline

  1. CLI
    1. Install CLI
    2. Configure CLI
    3. Issue S3 list bucket command

Tasks

Install CLI

Install CLI from here

Configure CLI

Syntax


aws configure

Syntax

C:\>aws configure
AWS Access Key ID sonwlson
AWS Secret Access Key 19101
Default region name [US East]: us-east-1
Default output format [None]:

Commands

aws s3api list-buckets

Syntax

aws s3api list-buckets

Output

{
    "Buckets": [
        {
            "Name": "cf-templates-us-east-1",
            "CreationDate": "2019-01-16T20:28:17.000Z"
        },
        {
            "Name": "dbsqlserver",
            "CreationDate": "2019-01-24T00:13:01.000Z"
        },
        {
            "Name": "dbsqlserver.virginia",
            "CreationDate": "2019-01-26T11:55:58.000Z"
        },
        {
            "Name": "dbsqlserveruseast",
            "CreationDate": "2019-01-30T18:30:56.000Z"
        }
    ],
    "Owner": {
        "DisplayName": "aws",
        "ID": "25"
    }
}

Explanation

The canonical ID is listed in the section owner\ID.

S3 Browser – Pro – Purchase

Daniel Adeniji Management ( S3 ), S3, S3 Bucket, s3browser.com

Background

Having evaluated S3 Browser for about a week now.

Enjoy using it, but will like more.

 

Pro Version

It is time to upgrade from the free to Pro.

Purchase

To purchase please go here.

Purchase Pro

Prepare

purchase.ProVersion.20190131.0917AM.PNG

Shopping Cart

purchase.yourshoppingcart.20190131.0927am

Payment Method

purchase.YourShoppingCart.PaymentMethod.20190131.0929AM.PNG

 

Activate

Outline

  1. Please connect to the Host that you will be sourcing from
    • Activate
      • Ensure host as Internet access
      • Enter activation key
      • Click “Activate button”
      • Wait to receive activation message

 

Activation

activate.cropped.20190131.0939am

Successful Activation

activate.successful.20190131.0942AM.PNG

Determine Currently Installed Version

Outline

  1. Launch App
  2. Version Info available
    • Top Menu Bar
    • Menu
      • Help / About

Images

Top Menu Bar

Image – Free

version.free.dadeniji.20190201.0705AM

Textual
  1. Version Number :- 8-1-5
  2. Free Version ( for non-commercial use only )
Image – Pro

version.pro.dadeniji.20190201.0712AM.PNG

Textual
  1. Version Number :- 8-1-5
  2. Pro Version

 

Help / About

Image – Free

version.help.about.free.dadeniji.20190201.0748AM

Textual
  1. Version Number :- 8-1-5
  2. Free Version ( for non-commercial use only )
Image – Pro

version.help.about.pro.dadeniji.20190201.0747AM.PNG

Textual
  1. Version Number :- 8-1-5
  2. Pro Version

 

AWS – Discussion Forums – Registration

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Discussion Forums ( Amazon - AWS - Services ), Technical

Background

Running into petty roadblocks with AWS and so it is time to look for help.

Forum

URL

The forum’s URL is https://forums.aws.amazon.com/index.jspa ( Link ).

Registration

Though one might already be registered as an Amazon buying customer, Amazon Web Services usage and registration is another thing, and so is the privilege to post questions in the AWS forum.

Outline

  1. Launch web browser
  2. Go here
  3. Member ?
    • If existing user, please click on the My Account / Console button
    • If new user, please click on the Sign Up button
  4. Register for Discussion Forums
    • IAM
      • Choose an IAM Discussion Forum Nickname
      • Enter your Email Address
  5. Wait till your Account is created and validated

 

Images

Home Page

userregistration.signup.20190126.0852am

Discussion Forums – AWS Identity & Access Management ( IAM ) – Forums Nickname & Email

userregistration.forumnicknameandemail.20190126.0902am

Your Account is not ready for posting messages

userRegistration.accountNotReadyForPostingMessages.20190126.0835AM.PNG

 

 

Discussion Thread

Outline

  1. Start Discussion Thread
  2. Thread
    • Thread Header
    • Thread Body
    • Thread Preview
      • Because of markup it is important to use the Preview pane
        • See if some of the body’s text are being interpreted as markup
        • See if actual markup are effectual
  3. Wait till your Account is created and validated
  4. Post Messages

Images

Start Discussion Thread

Plain Text

forumQA.20190126.0832AM.PNG

Preview

forumQA.20190126.0836AM.PNG