Share via

SQL Server on Azure VM Troubleshooting: Can't Access The SQL Server Configuration Page From The Portal

  • Article

 

Please check the disclaimer

Introduction

In this quick post, we will see how to solve a problem that was reproduced in an Azure VM with SQL Server. The main point is that the problem began after we tried to change collation on the SQL Server.

Problem

While we change collation in a SQL Server on an Azure VM, probably we will see a problem like the next screenshot shows. Also, except for the previous error maybe we notice a problem in the SSMS while try to log in.

Troubleshooting

The first thing we must do is to check the SQL Server Log Files, we can do that 

SQL Server Version Path
SQL Server 2017  C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log
SQL Server 2016  C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log
SQL Server 2014 C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log
SQL Server 2012 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

Error Log Details

The error log shows like the following:

2019-01-28 14:06:21.00 Logon       Error: 18470, Severity: 14, State: 1.
2019-01-28 14:06:21.00 Logon       Login failed for  user 'NT Service\SQLTELEMETRY'. Reason: The account is  disabled. [CLIENT: <local machine>]
2019-01-28 14:06:26.27 Logon       Error: 18470, Severity: 14, State: 1.
2019-01-28 14:06:26.27 Logon       Login failed for  user 'NT Service\SqlIaaSExtensionQuery'. Reason: The account is  disabled. [CLIENT: <local machine>]

As we earlier said, we cannot connect to the SQL Server instance, neither the windows user or the SQL user.

Solving The Problem

To solve the problem we must first login via the SSMS.

Step 1 - Restart SQL Server in Single User Mode & Create Windows User

To restart the SQL Server service in single user mode, we must open a command line tool as Administrator and follow the next steps :

  1. From the start menu, open RUN, and type CMD

  2. Type the following command to set the SQL Server instance in user mode.  

    C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn>sqlservr -s MSSQLSERVER -mSQLCMD

     

  3. Type the next command to create a Windows Login in the SQL Server  

    CREATE LOGIN [Domain or Workgroup\user] FROM  WINDOWS;  
ALTER SERVER ROLE sysadmin ADD MEMBER [Domain or Workgroup\user];
GO

 

Step 2 - Add The SQLTELEMETRY & SqlIaaSExtensionQuery 

After login via SSMS with the windows user, we create in step 1, we are ready to add the SQL Logins for all the necessary services.

Add NT SERVICE\SQLTELEMETRY

From the Security - Logins - select right-click [New Login...], search for the user NT SERVICE\SQLTELEMETRY and select it.

 

Add Server Roles To The User Login

From the Server Roles menu, select sysadmin and click OK.

 

Add NT SERVICE\SqlIaaSExtensionQuery

From the Security - Logins - select right-click [New Login...], search for the user NT SERVICE\SqlIaaSExtensionQuery and select it.

Add Server Roles To The User Login

From the Server Roles menu, select sysadmin and click OK.

After the previous steps we should have the following logins in the SSMS Logins folder.

 

Correct The Problem

To confirm that the problem is solved, from the Azure Portal main blade select Settings - SQL Server Configuration. As the image below shows the problem is solved.

 

 

Note

All the information in this article is only provided for reference. The article provides options to change critical system parameters, you MUST be very careful to test and deploy them because in some cases the changes can render a system defunct! Use it at your own discretion.

This solution is not supported under any Microsoft standard support program or service. It is provided AS IS without warranty of any kind. Microsoft or the author disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample script remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample script, even if Microsoft has been advised of the possibility of such damages.