There are several advanced options that you can modify in SQL Server. If you want to turn on optimize for ad hoc workloads in SQL Server, you can run the following script

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
RECONFIGURE WITH OVERRIDE
GO

If you want to use OPENROWSET, you can run the following

EXECUTE sys.sp_configure'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO

When running SQL Server on Amazon’s AWS RDS, you can’t do it like that. If you try running it, you will get the following error.

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105

User does not have permission to perform this action.

Msg 5812, Level 14, State 1, Line 1

You do not have permission to run the RECONFIGURE statement.

Here is how you have to do it. From the dashboard click on DB Parameter Groups. Click on Create DB Parameter Group, pick the version of SQL Server and fill in the text boxes. Here is what it looks like

Check the DB Parameter Group you just created and click on Edit Parameters

You will see a screen like this pop up

Modify the ones that you are interested in and click Save Changes

Click on DB Instances, Click on the Instance Actions dropdown

Select Modify from this dropdown

You will see a screen like this

In the Parameter Group dropdown, pick the Parameter Group you just created, hit Continue and then Modify DB Instance.

In order for this to take effect, you need to reboot your instance

Click on DB Instances, click on the Instance Actions dropdown

Select reboot from this dropdown

Once the instance is available again, connect from SSMS, right click on the Server, select Properties and click on the Advanced page. It should now match what you have edited in the DB Parameters Group

That is it for this post, as you can see it is pretty simple to do

Also check out my other AWS RDS SQL Server posts: Trying out Amazon Relational Database Service and How to read the error log on an Amazon RDS SQL Server instance