sp_helplogins in AWS/RDS

June 21, 2018June 21, 2018 Daniel Adeniji Amazon Web Services ( AWS ), Microsoft, Relational Data Services ( RDS ), Security Catalog Views, sp_helplogins, SQL Server, System Stored Procedures, Technical, Transact SQL

Background

Against AWS/RDS, reviewing SQL Server permissions, but unable to do via sp_helplogins.

sp_helplogins

Sample


declare @principal sysname

set @principal = 'dadeniji';

exec [dbo].[sp_helplogins]
     @LoginNamePattern = @principal

Error

Msg 15247, Level 16, State 1, Procedure sp_helplogins, Line 72 [Batch Start Line 8]
User does not have permission to perform this action.

Troubleshooting

Reviewed code and determined that it performs an explicit check to see if the running user has access to the securityadmin server role.


----------------  Only SA can run this  -------------------

if (not (is_srvrolemember('securityadmin') = 1))
begin

   raiserror(15247,-1,-1)

   select @RetCode = 1

   goto label_86

   return

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>end

Remediation

Outline

Schema/Name Change
Comment out sysadmin check
Comment out “set nocount on“
Place elsewhere as we can not use master

Code

Code – Schema/Name Change

The original module name is sys.sp_helplogins.

We are unable to use the sys schema and and also to avoid name collision with sp_helplogins, used dbo.sp_helplogins_Customized.

Code – Comment out sysadmin check


/*

--------------  Only SA can run this  -------------------

if (not (is_srvrolemember('securityadmin') = 1))
begin

    raiserror(15247,-1,-1)

    select @RetCode = 1
    goto label_86return

end

*/

Code – Comment out ‘set nocount on’

There are a couple of places where “set nocount on” is issued.

Commented both out, as well.

Code – Master

As we are unable to modify objects in the master db, please place else where.

Source Code Control

GitHub

DanielAdeniji/SQLServer.sp_helplogins
Link

References

Microsoft
- SQL Server
  - System Stored Procedures
    - sp_helplogins
      Link

AWS – Access Key & Security Key

June 7, 2018June 7, 2018 Daniel Adeniji Amazon, Amazon Web Services ( AWS ), BitTitan, Identity and Access Management ( IAM ) - AWS, Security Credentials ( AWS - IAM )

Background

As the AWS Ecosystem continues to grow it’s security authentication mechanisms continues to swell, as well.

Our Instance

Unfortunately various services need various authentication pathways.

For instance, own own implementation requires:

User credentials
- Multi-factor Authentication
IAM Roles

Need

Here I am trying to use a tool and it is asking me for the “AWS access key” and “AWS secret key”

Process

To review or get a new Access Key and Security key, please do the following:

Outline

Launch browser
Connect to IAM
- Generic
  - IAM Home Page
    Link
- Region
  - Region :- US West
    Link
Click on the Users hyperlink
- In case, you do not see your users
  - Please make sure that you are in right Region
  - Also make sure that you are not switched into Role
Access the “Security Credentials” tab
Review the listed “Access Keys”
- In my case did not have Access Keys listed
Created Accessed Key
- Clicked on the “Create Access key” button
- Access Keys was generated
  - Noted the Access Key ID & Secret Access Key
  - Downloaded the csv file which contains the generate Key

Screen Shot

IAM Management Console

Welcome

Users ( Switched to role )

users_role_20180606_1100PM [brushedup].png

Users ( As Self )

Users – [Username] – Security Credentials

Create(d) access key

CreateAccessKey_20180606 (brushedup).png

Crediting

Crediting BitTitan …

BitTitan :- How do I get an access key for Amazon S3?
Link

Amazon – RDS – Parameters – Trace Flags

May 10, 2016March 21, 2017 Daniel Adeniji 1204, 1222, 3226, 4199, 7806, 8017, Amazon, Amazon Web Services ( AWS ), Microsoft, Parameter Groups, Parameters, Relational Data Services ( RDS ), SQL Server, Trace Flags Trace Flag 8017

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

Output – Text


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

Explanation

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

Explanation

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

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

Trace Flags – Generic

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

Trace Flags – 4199

SQL Server query optimizer hotfix trace flag 4199 servicing model
https://support.microsoft.com/en-us/kb/974006
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/
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
Joe P – Developer Gems – SQL Server Trace Flag 4199
http://developergems.blogspot.com/2012/06/you-know-sometimes-as-software.html
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/
Paul White – Optimization Phases and Missed Opportunities
http://sqlperformance.com/2013/06/sql-indexes/recognizing-missed-optimizations
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

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

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

Amazon – RDS – Command Line Interface ( CLI ) – Returning empty resultset

April 26, 2016April 26, 2016 Daniel Adeniji Amazon, Amazon Web Services ( AWS ), CLI, describe-db-instances, describe-db-parameter-groups, Relational Data Services ( RDS ), Technical

Background

Just blew a couple of hours trying to determine why a couple of RDS/CLI Commands are coming back empty.

Commands

Here are the commands I tried out

describe-db-instances

Command


aws rds describe-db-instances

Output


{
"DBInstances": []
}

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups

Output


{
 "DBParameterGroups": []
}

Remediation

Traced the error back to the fact that I had set a wrong region.

Workarounds are to override the default per each command utterance, or go back and reset the default.

Specify Region at Command Level

describe-db-instance

Command


aws rds describe-db-instances  --region us-west-2

Output

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups --region us-west-2

Output

Reset default region

Review Configuration

Command


aws configure

Output

Wrong default – us-west-1

Reset Configuration

Connect to Console and get default

Connect to Console and get default

Reset default via “aws configure”

Here we change the default region from us-west-1 to us-west-2


aws configure

   default region name [us-west-1] : us-west-2

Confirm

Issue query without specifying default

Code


aws rds describe-db-instances