AWS/RDS – Networking via Security Group Rules

Background

Last night went to sleep a bit disgruntled as I could not connect to an RDS Instance I had setup a week or so ago.

Good Night

Had a good night sleep and started back down that same lane today.

TroubleShooting

Security Group Rules

Image

Tabulate

Type Rule Explanation
CIDR/IP – Inbound 10.8.203.222 / 32 This is my IP Address while I was setting up the Account
CIDR/IP – Inbound 10.8.203.0/24 Class C subnet that I set up to allow other people to connect
CIDR/IP – Outbound 0.0.0.0/0 Allow RDS Instance to connect to all outbound IPs

 

 

Edit Inbound Rules

Here are the existing inbound rules

Image

 

Adding Inbound Rules

Adding new class C network …

Image

Added Inbound Rules

Added new class C network

Image

 

AWS/RDS – SQL Server – DB Restore – Error – “Aborted the task because of a task failure or a concurrent RESTORE_DB request”

Background

Issue “msdb.dbo.rds_restore_database“.  But, when database restore progress using “msdb.dbo.rds_task_status” received an  error message.

Error Message

Error Text

Aborted the task because of a task failure or a concurrent RESTORE_DB request

Error Image

 

Remediation

Review SQL Instance and ensure that Option Group is pointing at the right S3 bucket.

Screen Shots

Options Groups

Accessed “Option Groups“…

Selected Option by placing a check mark to the left of it, and clicking the Modify group

 

Options Group – …vs2017Express

Edit

  • Installed Options :- SQLServer_Backup_Option
  • S3 Bucket :- dba-bucket

 

 

Review Assignment

Once our OptionGroup has been assigned SQLSERVER_BACKUP_RESTORE and it has been granted access to specific S3 bucket.

AWS-RDS :- Backing up SQL Database

Background

Backing up and restoring databases is one of the common tasks performed by DBAs.

Because of the regularity of the tasks, the DBA often schedules them to periodically occur and walks away.

Outside of occasionally alerts due to storage issues, life is good and fancy free.

 

AWS/RDS

Snapshots

In the Amazon RDS World, snapshots can be configured to auto-occur and life remains drama free.

 

S3 Storage

To facilitate husbandry of databases in-house and general safe-keeping, AWS now supports actual database backups.

Backup

Outline

The steps are:

  1. S3
    • Provision S3
  2. AWS Identity and Access Management IAM Role
    • Setup IAM Role
      • Setup Trust Policy for Native Backup and Restore
      • Setup Permissions Policy for Native Backup and Restore
  3. RDS Instance
    • RDS Instance Option Group
      • Option Groups
        • SQLSERVER_BACKUP_RESTORE

 

Steps

Step – S3

For the sake of brevity we will not touch upon provisioning a new S3.

We will instead focus on switching roles to a predefined role that has access to the S3 bucket.

Need to know
  1. S3 bucket
    • It is customal to have various S3 buckets in an organization
    • Determine the one pre-assigned to the group
    • Understand the folder structure
Permissions

In our original setup, my personal account did not have permission to the S3 bucket.

Permissions was granted to specific roles, and not to individual user’s personal accounts.

And, so if at first you fail to see S3 folders or the ones that you are looking for, please do not be too dissuaded,  as permissions might have been granted to specific roles instead.

 

Step – Identity & Access Management ( IAM )

Just as for S3, Identity and Access Management ( IAM ), is another big area and so we will skip the details here.

There are two areas that are important and have to be satisfied to allow RDS to access S3 buckets.  Those two areas are:

  1. Trust Policy
  2. Permission Policy
Trust Policy
Syntax

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Explanation
  1. Designate that principal rds.amazonaws.com is allowed access to the S3 Service

 

Permission Policy
Syntax
{
    "Version": "2012-10-17",
    "Statement":
    [
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
        "Resource": "arn:aws:s3:::bucket_name"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
        "Resource": "arn:aws:s3:::bucket_name/*"
        }
    ]
}


Explanation
  1. Bucket
    • s3:ListBucket
      • List Bucket
    • s3:GetBucketLocation
      • Get S3 Bucket Location
  2. Bucket and sub-folders
    • s3:GetObjectMetaData
      • Get Object Metadata
    • s3:GetObject
      • Get Object
    • s3:PutObject
      • Put Object
    • s3:ListMultipartUploadParts
      • List Multipart upload parts
    • s3:AbortMultipartUpload
      • Abort Multipart Upload

Step – RDS Instance Option Group – Option Group – SQLSERVER_BACKUP_RESTORE

As said earlier, the option to perform database backup and restore is new to RDS.

The specific option’s name is SQLSERVER_BACKUP_RESTORE.

Need to know
  1. Review the SQL Server Instance
    • Review the Option group
      • Console
        • Sample
          • Region :- US-East-2
            • https://us-east-2.console.aws.amazon.com/rds/home?region=us-east-2#option-groups:

 

Step – Backup

After all that, we are finally at the point where we can backup the database

Metadata

The Stored Procedure that we will using is dbo.rds_backup_database.

Let us review how it should be invoked.

To do so we will use the sp_help command.

Syntax

exec msdb.dbo.sp_help 'dbo.rds_backup_database'

Output

Actual
Syntax

exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
        , @overwrite_S3_backup_file=@overwrite

Sample

declare @dbName sysname
declare @s3arn  sysname
declare @overwriteS3BackupFile int

set @dbName = 'datahub';
set @s3arn = 'arn:aws:s3:::sqlServer/backup/datahub/datahub_20171207_0448PM.bak'
set @overwriteS3BackupFile = 1

-- exec  msdb.dbo.sp_help 'rds_backup_database'
exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
	, @overwrite_S3_backup_file = @overwriteS3BackupFile



Output

Review
Syntax

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName

Sample

declare @dbName sysname

set @dbName = 'datahub';

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName


 

Output

 

References

  1. Amazon / AWS /RDS
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Importing and Exporting SQL Server Databases
      • Importing and Exporting SQL Server Databases
        Link
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Options for the Microsoft SQL Server Database Engine
      • Options for the Microsoft SQL Server Database Engine
        Link

Amazon AWS/SQL Server – Task Status

Background

Back in 2012, Amazon started offering support for MS SQL Server.

Recently they added support for whole database backup and restore.

 

Review

To review the status of current operation and past ones, one can issue a query against the msdb.dbo.rds_task_status Stored Procedure.

 

Metadata

msdb.dbo.rds_task_status

Syntax


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		, @task_id = @taskID

Sample

Sample  – Review all tasks

SQL


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 

Output

Grid

Sample  – Review tasks targeting specific database

Sample  – Review tasks targeting specific database ( datahub )


declare @dbname  sysname
declare @taskID  int

set @dbname = 'datahub'

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		--, @task_id = @taskID

Output

Sample  – Review tasks targeting specific task

Sample  – Review tasks targeting specific database ( task )

If you initiated a task and recorded the task Number, you can go back and inquire about that task, as well.

Sample  – Review tasks targeting specific database ( task – Not Indicated)

If you indicate that you will be passing a task ID, but pass in a null one, you will get an error.


declare @dbname  sysname
declare @taskID  int

--set @taskID = 

exec msdb.dbo.rds_task_status 
     @task_id = @taskID

Output

Grid

Textual

Msg 50000, Level 16, State 1, Procedure rds_task_status, Line 36 [Batch Start Line 2]
Could not find the specified task. Execute without any parameters to show all tasks.

Amazon – RDS – Parameters – Trace Flags

Background

As intimated in our last post, one is able to effect MS SQL Server DBCC Trace Flags through RDS Parameter groups.

Currently, there are about 74 options that one can access through parameter groups.

In this post, we will focus on the dozen DBCC trace flags that are exposed.

 

Trace Flags

 

Trace Flag Description Default Recommendation Reason
 1204 Returns the resources and types of locks participating in a deadlock and also the current command affected  Off  On The information is very useful for troubleshooting deadlocks
 1211 Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks  Off  Off  It is better to allow the SQL Engine to control lock escalation
 1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.  Off  On Same as Trace Flag 1204, helpful for deadlock troubleshooting
 1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.  Off  Off Same as Trace Flag 1211, best to allow SQL Engine control
 2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE  Off  Off Again, allow the SQL Engine to throttle whether to enable parallel checking
 3205 Disable hardware compression for tape drivers  Off  Off MSFT doc states – “If a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression“.
 3226 Suppress log entries for backup operations  On  On Suppress successful backup logging.  This reduces errorlog cluttering especially useful for the more frequent log backup
 3625 Limits the amount of information returned in error messages  On  On In our own environment, the default security put in place ( for errorlog) by SQL Server is sufficient
 4199 Controls multiple query optimizer changes previously made under multiple trace flags   Off  On You want to check your SQL Server Version and Patch level and see if it supports this Trace Flag.

If it does, I will say test the Trace Flag out, and keep it enabled, if it does cause harm.

Please read more here.

 4616 Makes server-level metadata visible to application roles  Off  Off No need to expose this class of information
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration  Off  Off Memory dump is useful for diagnostic and so no need to disable memory dumps
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express  On  On Dedicated Administrator connection is useful for accessing stalled SQL instances

 

 

Code

Enable Trace Flag – 1204

aws rds Modify-db-parameter-group

Sample code for enabling a trace flag through AWS/RDS CLI.

Syntax


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group  --db-parameter-group-name [parameter-group-name]  --parameters "ParameterName=1204,ParameterValue=1, ApplyMethod=immediate"

Sample


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group  --db-parameter-group-name corp  --parameters "ParameterName=1204,ParameterValue=1, ApplyMethod=immediate"

 

dbcc traceoff

Sample code for disabling a trace flag through Transact SQL.

Syntax

DBCC TRACEOFF ([trace-number], -1)
GO

Sample


DBCC TRACEOFF (1204, -1)
GO

Output – Image

DBCCTraceoff

Output – Text


Msg 2571, Level 14, State 3, Line 1
User 'guest' does not have permission to run DBCC TRACEOFF.

Explanation

  1. As we do not have sysadmin privileges, we are not able to issue DBCC TraceOn/DBCC TraceOff
    • This command “requires membership in the sysadmin fixed server role” ( Link )

 

 

Review Enabled Trace Flags

List all enabled trace flags

Code


DBCC TRACESTATUS(-1);
GO

Output

DBCCTraceStatus-Enabled

Explanation

  1. I am familiar with all the other Trace flags ( listed above ), outside of Trace Flags 4199 and 8017
    • 4199
      • Need to clear cached plan
        • SQL Server query optimizer hotfix trace flag 4199 servicing model
          https://support.microsoft.com/en-us/kb/974006

          If DBCC TRACEON\TRACEOFF is used this does not regenerate a new cached plan for stored procedures. Plans could be in cache that were created without the trace flag
    •  8017
      • Aaron Morelli
        Twitter – @sqlcrossjoin
        Topic – A Topical Collection of SQL Server Trace Flags
        Link – https://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf
        Ken Henderson 2005, page 387 (paraphrased): basically means “no offline schedulers”.
        Normally, when using affinity to restrict the CPUs that SQL can use, SQLOS starts up schedulers for every CPU on the box, but then keeps schedulers that it is not allowed to use in “offline” state. However, those schedulers are using resources, so you can prevent SQL from ever creating those schedulers by turning on this flag. You can combine this with 8002 to achieve the “move among CPUs” effect for your schedulers
        This flag appears to have been turned on by default in SQL 2005 Express Edition, as evidenced by all of the upgrade warnings people were experiencing when trying to upgrade to SQL 2008 Express.
      • Makes sense as SQL Server Express Edition does not support SQL Server Agent

 

References

Trace Flags – MSFT

  1. Trace Flags (Transact-SQL)
    https://technet.microsoft.com/en-us/library/ms188396.aspx


Trace Flags – Generic

  1. Warner Chaves – The Most Important Trace Flags for SQL Server
    http://sqlturbo.com/the-most-important-trace-flags-for-sql-server/
  2. Aaron Morelli – Trace Flag Respository
    https://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf
  3. Derik – sqlHammer – Derik’s Favorite Trace Flags
    http://www.sqlhammer.com/deriks-favorite-trace-flags/

 

Trace Flags – 4199

  1. SQL Server query optimizer hotfix trace flag 4199 servicing model
    https://support.microsoft.com/en-us/kb/974006
  2. Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
    https://www.mssqltips.com/sqlservertip/3320/enabling-sql-server-trace-flag-for-a-poor-performing-query-using-querytraceon/
  3. Benjamin Pierce – SQL 2008 – 2012 Query Optimizer Trace Flag 4199 – Increase performance
    http://www.symantec.com/connect/blogs/sql-2008-2012-query-optimizer-trace-flag-4199-increase-performance
  4. Joe P – Developer Gems – SQL Server Trace Flag 4199
    http://developergems.blogspot.com/2012/06/you-know-sometimes-as-software.html
  5. David K. Lee – Convergence of Data and Infrastructure – SQL Server Trace Flag 4199
    http://www.davidklee.net/2012/08/23/sql-server-trace-flag-4199/
  6. Paul White – Optimization Phases and Missed Opportunities
    http://sqlperformance.com/2013/06/sql-indexes/recognizing-missed-optimizations
  7. Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
    https://www.mssqltips.com/sqlservertip/3320/enabling-sql-server-trace-flag-for-a-poor-performing-query-using-querytraceon/

 

Trace Flags – 4616

  1. Gerard Conroy – SQL Server Trace Flag 4616 no longer required for Dynamics NAV 5.0 SP1 or Dynamics NAV 2009 SP1
    https://blogs.msdn.microsoft.com/nav/2010/02/11/sql-server-trace-flag-4616-no-longer-required-for-dynamics-nav-5-0-sp1-or-dynamics-nav-2009-sp1/

 

SQL Server Builds

  1. Microsoft SQL Server Version List
    http://sqlserverbuilds.blogspot.in/

 

Amazon – AWS – RDS – DB Parameter

Background

Amazon RDS Offers an ability to access some of the more system level configurations of the hosted database.

In MS SQL Server, some of those options are available via sp_configure.

Here is how to view exposed configuration via AWS\RDS CLI Commands.

Parameter Groups

List DB Parameter Groups

Code


aws rds describe-db-parameter-groups

Output

ListDBParameterGroups

 

List Specific DB Parameter Group

Code


aws rds describe-db-parameter-groups --db-parameter-group-name default.sqlserver-ex-12.0

Output

ListSpecificDBParameterGroup

 

Parameter Values

List all DB Parameter Values

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table --query "Parameters[*].[ParameterName, ParameterValue]" | more

Output

ListDBParameterValues

 

List DB Parameter – Scan for startup procs

Code


aws rds describe-db-parameters  --db-parameter-group-name default.sqlserver-ex-12.0 --output table  --query "Parameters[?ParameterName==`scan for startup procs`]"

Output

ListDBParameterValue-ScanForStartupProcs

 

List DB Parameter – Optimize for adhoc workloads

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`optimize for ad hoc workloads`].[ParameterName, ParameterValue, ApplyMethod]"

Output

ListDBParameterValue-OptimizeForAdhoc

 

List DB Parameter – Max Server Memory

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`max server memory (mb)`].[ParameterName, ParameterValue, ApplyMethod]"

Output

ListDBParameterValue-MaxServerMemory

Explanation

  1. We are using free AWS and so our max memory is 1GB // SQL Server Express

 

List DB Parameter – Trace Flags

Code


@rem Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema
aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?ParameterName==`1222`].[ParameterName, ParameterValue, ApplyMethod]"

Output

— Empty —

 

Code


aws rds describe-db-parameters --db-parameter-group-name default.sqlserver-ex-12.0 --output table ^
 --query "Parameters[?DataType==`boolean`].[ParameterName, ParameterValue, ApplyMethod]" 

Output

DescribeDBParameters-Boolean

 

Explanation

  1. One workaround for accessing Trace Flags figures is to look at records whose datatype is Boolean

 

Change DB Parameter – Trace Flags

Code


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group --db-parameter-group-name default.sqlserver-ex-12.0 --parameters "ParameterName=1222,ParameterValue=1, ApplyType=immediate"

Output – Textual

A client error (InvalidParameterValue) occurred when calling the ModifyDBParameterGroup operation: Cannot modify a default parameter group.

Output – Image

CannotModify-DefaultParameterGroup

 

Copy DB Parameter

Code


aws rds copy-db-parameter-group ^
 --source-db-parameter-group-identifier "default.sqlserver-ex-12.0" ^
 --target-db-parameter-group-identifier "corp.sqlserver-ex-12.0" ^
 --target-db-parameter-group-description "corp.sqlserver-ex-12.0" 


Output-Text


A client error (InvalidParameterValue) occurred when calling the CopyDBParameterGroup operation: The parameter DBParamet
erGroupName is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, an
d hyphens; and must not end with a hyphen or contain two consecutive hyphens.

 

Output-Image

NotAValididentifier

Bug Reports

  1. aws_db_parameter_group name argument error #6044
    https://github.com/hashicorp/terraform/issues/6044

 

Create DB Parameter

Let us create a new DB Parameter group – corp.vMSSQL12

Code


aws rds create-db-parameter-group ^
 --db-parameter-group-name "corp.vMSSQL12" ^
 --db-parameter-group-family "sqlserver-ex-12.0" ^
 --description "corp.sqlserver-ex-12.0" 


Output-Text


A client error (InvalidParameterValue) occurred when calling the CreateDBParameterGroup operation: The parameter DBParameterGroupName is not a valid identifier. 
Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Output-Image

CreateDBParameterGroup-WithPeriod

Explanation

  1. DB Parameter group name, corp.vMSSQL12, has a period in it
  2. And, unfortunately the code fails when the parameter has a period

 

Create DB Parameter

Let us create a new DB Parameter group – corp.vMSSQL12

Code


     aws rds create-db-parameter-group ^
        --db-parameter-group-name "corp" ^
        --db-parameter-group-family "sqlserver-ex-12.0" ^
        --description "corp.sqlserver-ex-12.0" 


Output-Text

{
 "DBParameterGroup": {
 "DBParameterGroupName": "corp",
 "DBParameterGroupFamily": "sqlserver-ex-12.0",
 "Description": "corp.sqlserver-ex-12.0"
 }
}

Output-Image

CreateDBParameterGroup-Successful

Explanation

  1. When we supplied  a simpler name, corp rather than corp.vMSSQL12, we are good.

 

Change DB Parameter

 

Code


aws rds modify-db-parameter-group  --db-parameter-group-name corp  --parameters "ParameterName=1222,ParameterValue=1, ApplyMethod=immediate"

Output-Text

{
 "DBParameterGroupName": "corp"
}

Output-Image

ChangeDBParameterGroup-Successful

 

Modify DB Instance

Code


aws rds modify-db-instance  --db-instance-identifier adriel --db-parameter-group-name=corp

Output

modifyDBInstanceGroup

 

Textual

Here are some worthy values…

  1. PendingModifiedValues – Empty
  2. DBParameterGroups
    • DBParameterGroupName :- corp
    • ParameterApplyStatus :- applying

Restart DB Instance

Code


aws rds reboot-db-instance  --db-instance-identifier adriel

Output

restartDBInstance

 

Textual

Here are some worthy values…

  1. PendingModifiedValues – Empty
  2. DBParameterGroups
    • DBParameterGroupName :- corp
    • ParameterApplyStatus :- pending-reboot

Conclusion

Couple of noteworthy points …

  1. It is best to create and craft DB Parameter groups before hand
    • If you do not assign a DB Instance to a user created Parameter group, you will be assigned a default parameter group
    • Unfortunately, default parameter groups, are not modifiable, and as such one has little flexibility for configuring instances assigned to them
    • Assigning DB instances to them during creation will reduce the number of modifications and reboot thereafter
  2. Since different versions of your database will support and expose different configurable parameters, it is likely best to create DBMS Version parameter groups

 

References

Amazon AW Documentation

  1. AWS Documentation » AWS Command Line Interface » AWS Command Line Interface » Using the AWS Command Line Interface » Controlling Command Output from the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/controlling-output.html
  2. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Working with DB Parameter Groups
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Viewing
  3. AWS Documentation » Amazon Relational Database Service (RDS) Documentation » API Reference » Actions » DescribeDBParameters
    http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBParameters.html

 

Implementation

  1. Why You Should Always Use a Custom DB Parameter Group When Creating an RDS Instance
    http://www.stratalux.com/blog/always-use-custom-db-parameter-group-creating-rds-instance/
  2. VMware Continuent Documentation Center – Changing Amazon RDS Instance Configurations
    https://pubs.vmware.com/continuent/tungsten-replicator-2.2/deployment-amazonrds-rdsconfig.html

 

Amazon – AWS – RDS – SQL Server – DB Server – IP Address

Background

Wanted to see what it will take to access our RDS Server via its IP Address as different from accessing through the FQDN.

 

Console

RDS Home Page

Access our region specific RDS Home page ( https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2#dbinstances: ).

DBInstance

 

Get Matching IP Address

Code:


ping FQDN

Output:

ping

 

Validated

Validated that we are able to access the SQL Server Instance using the FQDN’s IP Address

What is the Server’s actual IP Address?

Dynamic Management View

SYS.DM_EXEC_CONNECTIONS

Code


SELECT 
		  [Machine Name] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
		, [ServerName] = SERVERPROPERTY('ServerName')
		, [IP Address Of SQL Server] = LOCAL_NET_ADDRESS
		, [IP Address Of Client] = CLIENT_NET_ADDRESS

FROM SYS.DM_EXEC_CONNECTIONS 

WHERE SESSION_ID = @@SPID

 

Output

SYS

The IP Address of SQL Server is 172.30.0.95

CONNECTIONPROPERTY

Code


SELECT  
	     net_transport       = CONNECTIONPROPERTY('net_transport')
	   , protocol_type       = CONNECTIONPROPERTY('protocol_type')
	   , auth_scheme         = CONNECTIONPROPERTY('auth_scheme')
	   , local_net_address   = CONNECTIONPROPERTY('local_net_address')
	   , local_tcp_port      = CONNECTIONPROPERTY('local_tcp_port') 
	   , client_net_address  = CONNECTIONPROPERTY('client_net_address')

Output

CONNECTIONPROPERTY

The IP Address of SQL Server is again noted as 172.30.0.95.

Attempt DB Server Access using Server’s IP Address

If you try to access the DB Server using the IP Address 172.30.0.95, you will not be able to.

 

Is your DB Server IP Address private?

MxToolbox

Go to http://mxtoolbox.com/ReverseLookup.aspx and determine if our address is private

Enter Address

ReverseLookup

Response

IsAPrivateIPAddress

Is a private IP address…

 

Commentary

It is important to either mark the DB Server as publicly available or to properly configure the VPC to properly route traffic behind the scene to the DB Server’s private IP Address.