Can’t Access The SQL Server Configuration Page From The Portal

by | Feb 1, 2019 | Azure, Portal, SQL Server

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 isdisabled. [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.

Related Links

468
Share This