Cant connect to my SQL VM

Question

hi guys,

i cant connect to my SQL SERVER instance on my VM. Dont know whats wrong because everything seems okay. However the Iaas Agency sql extension isnt working either..

some errors:

The SQL virtual machine resource is not in a valid state for management. The cause could be the SQL virtual machine is in bad states, the underlying virtual machine state is invalid (Example: stopped, deallocated or failed) or the virtual machine was not found. Please make sure the SQL virtual machine and the underlying virtual machine are healthy.

Status is available.

[
{
"code": "ComponentStatus/SQL Server IaaS Agent/failed/56061",
"level": "Error",
"displayStatus": "Provisioning failed",
"message": "PreReq failure: ErrorCode: NotRetryableUnexpectedError, Message: Service with name 'MSSQLSERVER' does not exist or is not running. Please make sure service 'MSSQLSERVER' is running and retry this operation."
},
{
"code": "ComponentStatus/SQL Instance Report/succeeded",
"level": "Info",
"displayStatus": "Provisioning succeeded",
"message": "{\"TargetedSqlInstanceName\":null,\"IsTargetedSqlInstanceInstalled\":false,\"SuggestedSqlInstanceName\":\"MSSQLSERVER\"}"
}
]

I cant even do: Support + troubleshooting>repair (left pane).

Im helpless because i want to create a sql server instance on a VM... (Its image = SQL SERVER 2022 FREE edition)

Could somebody help me please?

Answer 1

Hi,

If you do not need your current VM, then your best option is probably to create a new one following these steps

(1) Go to list of VM: https://portal.azure.com/#view/HubsExtension/BrowseResource/resourceType/Microsoft.Compute%2FVirtualMachines/

(2) Click on Create and select the first option

(3) fill the form

In the "image" field, click on the current selection and write: SQL Server

Select the option: Free SQL Server License: SQL 2019 Developer on Windows Server 2019 - Gen1

Continue to fill all the form

(4) When you scroll down do not forget to check the: Select inbound ports

Make sure that RDP (remote connection in order to manage the server) is marked

Note: If you own Windows licenses with active Software Assurance (SA) or have an active Windows Server subscription, use Azure Hybrid Benefit to save compute cost.

(5) Click next until you get to the SQL Server settings tab. Fill all the information in previous tab (probably you can use the default)

(6) In the SQL Server settings tab make sure that SQL Server settings is set to Public (Internet) or you will not be able to connect the server from outside the Azure!

This was the most important step if you need to connect the SQL Server in the VM from remote!

This is probably what you missed to configure last time.

Select port: 1433

(7) Make sure to enable SQL Authentication

(8) Continue to fill the form (you can configure the SQL instance settings if you want or use the default)

(9) Click next until you get to the Review + create

It should look something like this:

You need to open the firewall for connections to use both RDP (remote connection in order to manage the server) and SQL Server (port 1433)

Answer 2

234606-deployment.txthi Pituach,

I followed every step just as you mentioned however i get an error when my VM is being deployed in my resource group:

Deployment overview page:

Resource: UdemyCourseonP
Type: Microsoft.SqlVirtualMachine/SqlVirtualMachines
Status: Conflict

Operation details:
{
"status": "Failed",
"error": {
"code": "Ext_SqlIaasExtensionError",
"message": "Error: 'Service with name 'MSSQLSERVER' does not exist or is not running. Please make sure service 'MSSQLSERVER' is running and retry this operation.\r\n\r\nMore information on troubleshooting is available at https://aka.ms/vmextensionwindowstroubleshoot '"
}
}