Sql-server – MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can’t Bring It Online

amazon-rdspermissionssql serversql-server-2016ssms

I can take databases offline (via GUI) but can't bring them back online. The server details are as follow:

RDBMS: MS SQL 2016 Express
Host: Amazon AWS/RDS Free Tier

Details/History of the Problem
A few months ago, I created a db instance on Amazon AWS and at the time of creation, the 'master/admin' account was setup via the AWS/RDS web page. With this 'admin' account, I have created several databases on that instance without any problems.

Over the past few months, I have used this 'admin' account to change several databases to contained databases. I do this so that I can setup contained users. I have also done this several times on this server instance with the same admin account with no problems.

Last night, I had just created a new database via this admin account. I then tried to set this new database as a contained database and the process failed. The dialog box error message stated among other things "please try again later".

After the 3rd failed attempt, I decided to take the database offline (via the GUI in SSMS). I did this in a bid to force close any possible open processes or connections that might be on this new database. That worked. However, I have not been able to bring it back online. I have tried via the GUI and also via a query and it keeps failing.

I have then checked the server roles assigned to this 'admin' account. It is not part of sysadmin role. As I understand, the 'sysadmin' role can do absolutely anything on the db instance. I reckon my admin account is not of this sysadmin role because it is meant for the in-house DBAs at Amazon AWS. I have tried to add it as sysadmin but it fails.

To ensure that my 'admin' account is the problem, I have taken another database offline (it's empty). It went offline but it is also failing to come back online.

What could be the problem? Please help. Note that my skill level is very very low and I'm learning as I go along.

The server logs don't show anything useful. I have attached screenshots.

Error bringing database online in SSMS

Error looking at SQL Server error log from SSMS

Permissions for login

Error adding login to sysadmin role

Best Answer

You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping security boundaries.

Setting a database online

As part of your admin account, you have access to set databases offline, but bringing a database back online requires sysadmin (which you don't have). To work around this permission restriction, AWS provides a stored procedure to bring databases online. You'll need to use the rds_set_database_online procedure in the rdsadmin database

EXEC rdsadmin.dbo.rds_set_database_online 'MyOfflineDatabase';

This procedure is fully documented by AWS here.

Reading the error log

Similarly, you cannot use xp_readerrorlog on RDS SQL Server because it requires sysadmin. Instead, AWS provides THE stored procedure rds_read_error_log

EXEC rdsadmin.dbo.rds_read_error_log;

This procedure is fully documented by AWS here.

Related Solutions

Sql-server – Cannot restore Amazon RDS SQL Server database to given point-in-time using SQL Server Management Studio

I would try scripting out the operation instead of running whatever Management Studio is doing "for" you. You shouldn't need to use xp_fileexist (assuming you can validate that the files actually exist in the place the script says). Using 2012 SSMS, I am restoring to a point in time that does not coincide directly with a log backup:

enter image description here

The resulting script in my case was:

BACKUP LOG ... WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY
RESTORE DATABASE ... WITH FILE = 1, NORECOVERY, NOUNLOAD
RESTORE LOG ... WITH FILE = 1, NORECOVERY, NOUNLOAD
...
RESTORE LOG ... FROM DISK = [...path from line 1...] WITH NOUNLOAD, STOPAT = <time>

Sql-server – Newly discovered SQL Server Express has all databases inaccessible/offline

From the error log, there are failed logins, are you trying to connect using any of the below ones ?

Login failed for user 'drily'. Reason: Could not find a login matching the name provided. [CLIENT: 172.17.0.210]

Login failed for user 'WCC\baadmin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

Also, from your screenshot, the report server databases are cleanly shutdown. Do you have auto close option turned ON ?

If you have rights on the server, then below TSQL will help you :

'RESTORE DATABASE <dbname> WITH RECOVERY' or  'ALTER DATABASE <dbname> ONLINE'

What is the output for below SQL:

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc, is_auto_close_on, is_auto_shrink_on
FROM sys.databases;

UPDATE:

I see the problem now ... that auto_close option is turned ON for report server databases. According to BOL:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

Refer to here and here for more detailed explanation.