Database deploy to AWS RDS failing

MFitchett

Hi guys,

I've got a SQL Server database deploy setup in Deployment Manager and when I deploy it to any other database than an AWS RDS SQL Server DB it works fine.

It appears to create all the database objects and reports completion but then throws an error that it can't log back in to the database like so..


2014-06-28 18:43:46 +00:00 INFO Creating extended properties
2014-06-28 18:43:46 +00:00 INFO The database update succeeded
2014-06-28 18:43:46 +00:00 INFO Dropping users
2014-06-28 18:43:46 +00:00 INFO Skipping post-deploy validation; "Use postdeploy validation" is set to "No" in the database package step options
2014-06-28 18:43:46 +00:00 INFO Deployment complete:
2014-06-28 18:43:46 +00:00 INFO - Writing deployment details to the database for deployed version [Tradesquare_Store_Database\22.0-master]
2014-06-28 18:43:46 +00:00 ERROR Cannot open database "Saas_Store" requested by the login. The login failed.
2014-06-28 18:43:46 +00:00 ERROR Login failed for user 'admin'.
2014-06-28 18:43:46 +00:00 ERROR Caused by:
2014-06-28 18:43:46 +00:00 ERROR -Cannot open database "Saas_Store" requested by the login. The login failed.


Also, after the deploy, the database becomes unusable from SSMS and/or VS Sql Explorer. You can't even delete the DB, it just reports that 'The database <name> is inaccesible'

I have to restore a backup or spin up a new RDS instance.

I've also tried manually creating the database before the deploy, checking that I can create objects ok then doing the deploy and the same thing happens.

Any help would be great,

Cheers,

Matt

MFitchett

Further to this I've got access to some logs and it appears to be this :

014-06-29 17:39:48.11 Logon Error: 18456, Severity: 14, State: 38.

2014-06-29 17:39:48.11 Logon Login failed for user 'admin'. Reason: Failed to open the explicitly specified database 'Saas_Core'. [CLIENT: 46.137...]

'admin' is the name of the administrator on that database, password is correct.

Further info on State 38 reveals : http://sqlblog.com/blogs/aaron_bertrand ... 18456.aspx

The database specified in the connection string, or selected in the Options > Connection Properties tab of the SSMS connection dialog, is no longer valid or online (it might be set to AutoClose or the user may simply not have permission). I came across this once when I typed <default> here instead of picking that option from the list. This is reported as state 27 or state 16 prior to SQL Server 2008.

Note that this could also be a symptom of an orphaned login


I'm not using any database mirroring, just the basic RDS setup..

MFitchett

So it turns out it was me being a dufus.

When creating an RDS instance you specify a username / password.

Turns out that user/pass combo wasn't in my SQL deploy/compare scripts so the deploy was succeeding then last step was removing the admin user with the privileges to login!

All seems so obvious now!