To enable mixed mode Authentication in Microsoft SQL Server please follow the steps below.
To change security authentication mode to mixed mode
In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
On the Security page, under Server authentication, select SQL Server and Windows Authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
To enable the sa login by using Management Studio
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, create and confirm a new highly secure password for the sa login.
On the Status page, in the Login section, click Enabled, and then click OK.
Re-setting the SQL Express Instance
This applies to new VM's with the Windows / Microsoft SQL template, where Microsoft SQL is already installed.
It can however be applied at any time (as required), if you are having issue accessing the SQL install.
When you avail of a new Windows template, for a Cloud VM, with Microsoft SQL, you will need to reset the SQL instance installed before it can be used, as it is set to a computer name based on the original template.
Follow the steps below - while logged in via Remote Desktop (RDP), to the server:
Open CMD prompt - as the Administrator.
Run the commands:
cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn
NET stop MSSQL$MSSQLSERVER
sqlservr.exe -ssqlexpress -m
[Do not close the CMD prompt at this point]
Login to Management Studio with localhost\administrator.
Connect to $computername\sqlexpress and add sysadmin role to administrator and then set mixed mode as well if required.
[At this point, within CMD press ctrl+c and enter y]
Now start MSSQL using, in the CMD prompt:
NET start MSSQL$MSSQLSERVER
You should be able to login as administrator with full access.
If you have any issues with this, please send an email to firstname.lastname@example.org, ensuring the email comes from an admin contact's email address, explaining the issue and errors your are receiving, while supplying the following information also:
- The Account ID, for the account which has the Cloud VM subscription.
- The IP address of the Cloud VM in question.
Note also the following articles: