Unable to access SQL after straight install from Azure

So you get your new VM and you see SQL installed. You try to connect to SQL and dont know the sa password. So you use the Windows domain account but cannot get in either.

Reason is because the domain may not have been setup yet so you need to connect with the local admin account.

• Thus you sign in to the machine as the local admin account. 
• Sign in to SQL. 
• Change the Properties to Mixed Mode. 
• Then enable the sa user in security. 
• Then set the password.

SQL Database in Recovery Pending Mode

So you have done a restore of your virtual machine.
Sign in to SQL and boom! Your database is in recovery pending mode!
What the hell is that?

To fix:
- Go to the folder where the ldf and mdf is stored, grant access to 'everyone'
-Sign back in to SQL, restart the service
-Run the following script and you are good to go!
ALTER DATABASE dbnamehere SET ONLINE

And voila! Problem resolved!

Capture Login Issues

Ok so you run the capture login script and then get the below message.

supplied parameter sid is in use

you then try to remove the sid from the syslogins table but you get some message related to 'adhoc' something is not allowed.

then you try to create the record manually and get 'the server principal already exists'


How to fix run the below and replace name with the name of the record you have the issue with. You should be fine now.

EXEC sp_droplogin 'namehere'

Management Reporter Database in Suspect Mode

So you check SQL and notice your backups stop running.

Only to notice it is because your MR database is in suspect mode. To resolve the issue i would first suggest, removing MR from the database backup plan and complete your GP backups.

Then run the below script to resolve the MR suspect mode issue. Then create your separate plan for MR backups.

Script:

EXEC sp_resetstatus ManagementReporter;

ALTER DATABASE ManagementReporter SET EMERGENCY

DBCC checkdb(ManagementReporter)

ALTER DATABASE ManagementReporter SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (ManagementReporter, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ManagementReporter SET MULTI_USER