Error 18456, Severity 14, State 1, Windows Authentication

Question

I created a VM with its own username and password. When I load the VM and open the SQL Management Server, Windows Authentication is automatically selected and my VM credentials are auto filled. But when I hit connect, Error 18456, Severity 14, State 1 is thrown. This is my first time attempting to connect to the SQL Management server, so I have no SQL server account set up and therefore can't log in using SQL Server Authentication. What steps should I take to resolve this issue? Thanks in advance!

(P.S. My computer is runs on Windows 11 while the VM runs on Windows 10, not sure if that affects anything?)

Answer 1

Hi,

SSMS came with the VM,

Yes, I explicitly said so in my answer :-)

In fact, I mentioned that I hate that it does come pre-installed since in production in most cases I will recommend not to install extra applications like client app

the VM I downloaded is called the Data Science Virtual Machine 2019.

OK. I found the VM which you speak about :-)

I had to install it from scratch in order to check the default configuration, so I could help better in this specific template.

You are right :-( and this VM has no such options to configure the sql server. In fact, It is coming with a very specific pre-configured options to fit a very specific task. Even the network security group (NSG) rules are pre-configured.

There is some information about this template here:

• https://azure.microsoft.com/en-us/services/virtual-machines/data-science-virtual-machines/#whats-included
• https://learn.microsoft.com/en-us/azure/machine-learning/data-science-virtual-machine/overview
• https://learn.microsoft.com/en-us/azure/machine-learning/data-science-virtual-machine/dsvm-tools-data-platforms#windows

Note! This template is for learning only and not for production. It is not well configure for production and it uses developer license for the SQL Server.

----------

OK... machine is ready and I connect to it. Let's start check the configuration related to the SQL Server

First, check the running services by executing: run -> services.msc

SQL Server looks good, all the expected services are running and I can see that the server is using the default instance name (as I explained above).

Note! The server is not configured for remote access by default. We will need to use the installed SSMS from within the VM

I am getting the same error :-)

This template is not well configured!

let's fix it in three simple steps

---------------------------------------------------------------------------

Step 1: Start SQL Server in single user mode

(1) Open SQL Server configuration manager
(2) Right click on the service of SQL Server instance -> properties
(3) Move to the tab: Startup Parameter, and add the parameter -f
(4) Restart the server

---------------------------------------------------------------------------

Step 2: CREATE LOGIN to your windows user

(1) go to the folder:
C:\Program Files (x86)\Microsoft SQL Server\150\Tools\Binn

(2) Execute: SQLPS.exe
This will open the SQL PowerShell
https://learn.microsoft.com/en-us/sql/tools/sqlps-utility

(3) Connect the server using by running: SQLCMD
Note: Since you are using default instance and connect using current user which is the administrator, no parameter is needed

(4) Execute:

CREATE LOGIN [<your machine name>\<your windows user name>] FROM WINDOWS  
GO  
ALTER SERVER ROLE sysadmin ADD MEMBER [<your machine name>\<your windows user name>]  
GO  
  

for example:

CREATE LOGIN [MachinName\UserName] FROM WINDOWS  
GO  
ALTER SERVER ROLE sysadmin ADD MEMBER [MachinName\UserName]  
GO  
exit  

---------------------------------------------------------------------------

Step 3: Remove the startup parameter from step 1 and restart the SQL Server service.

---------------------------------------------------------------------------

That's it :-)

You can login using your windows user using windows authentication

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

Answer 2

But what really should be fixed is the template.

Absolutely true

Totally Agree 100% @Erland Sommarskog

I meant first to fix the document about error 18456, since everything under the title "User Action" has no value and it is almost totally useless right now in my opinion.

For example, the document suggests to Change server authentication mode and it send the reader to a document, which explain how you can do it (using SSMS object explorer) AFTER you LOGIN to the server, but the issue is that you get error 18456 since you cannot login tot he server! What if no one can connect the server? How this solution have any value?!? Moreover, there is for example a recommendation to contact your SQL Server administrator. Really?!? Who exactly come to read this document if not the administrator?!? What is the solution for the SQL Server administrator? This is the most useless recommendation I saw! Obviously if you have issue then you will first go to your administrator if this is not you, but what should the administrator (which is most of the cases) do when he don't know what to do?!? This is why he came to read the documentation. no?

We need to add the solution for someone that do not have access to the server and want to use windows authentication - This the exact solution which I gave in this thread, and we need to add the solution for someone that want to login with sql server authentication (which is almost the same steps, except some more steps like changing the authentication mode to mixed mode and instead of adding the windows user to the sysadmin role we enable the sa login and if we want then we can add other sql server login to the sysadmin role)

at the same time, I need to edit the template documentation temporarily until it is fixed and I will contact the team regarding the fix of the template with a link to the fixed documentation or simply send the same information directly (with link here)

Answer 3

Do you run SSMS in the VM on your on own laptop? I think you need to run it on the VM for Windows auth to work; your laptop is not in the same domain.

I would expect the admin account you gave when you created the VM to work, but it was a while since I created a VM in Azure.

Else you can try the measures in this article: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15.

And note that you need to run this on the VM.

Answer 4

Hi,

when I hit connect, Error 18456, Severity 14, State 1 is thrown.

You can read more about error 18456 in the official documentation here:
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

In short, this means authentication failure and State: 1 means you do not have permission to receive the error details. In most cases, You can get more information from the SQL Server error log file.

With that bein said, according to your description and the , if I understand correctly then this is a new VM that you created using on of the default template which comes with SQL Server installed (meaning you did not installed the server yourself after the VM was ready). In addition this means that SQL Server Management Studio was installed automatically in the machine (which I hate doing in production).

IMPORTANT! Please clarify if this is NOT the case.

This is my first time attempting to connect to the SQL Management server, so I have no SQL server account set up and therefore can't log in using SQL Server Authentication.

SQL Server has two type of authentication model which you can choose using the installation of the of the server or change after it was installed. The first is Windows Authentication (which actually means windows or AD user) and the second one is mixed mode.

During the creation of the Virtual Machine you had a step where you needed to configure the SQL Server properties as you can see in the following image.

What you selected during this step defined how you connect the server at this point.

----------

(0) Notice that on the top you have option to select the SQL connectivity

If you selected private (which is the default) or private, then you cannot connect the server from your home for example or any network outside the Azure (at least not until you do more configuration).

In this case, you will need to connect the server from application which is in the virtual machine. For example you can install SSMS or use Azure SQL Studio (which does not require installation). In this case SSMS was already installed for you, so we will use SSMS.

in this case the original administrator of the virtual machine is the sysadmin of the SQL Server (the equivalent of the sa login in on-premises server). In order to connect the server you need to connect the VM using this windows user and not any other user which you may created in the windows.

Once you connected to the VM using RDP -> the VM administrator will be used when you select "windows authentication" in the SSMS connection windows.

By default the server is configured as a default instance which mean you can connect to the SQL Server using the name of the virtual machine, like this:

Is this fit your case? If so, is this solve your issue?

----------

In the sql server configuration step of the Virtual Machine, you have two more options which control the authentication to your SQL Server.

(1) If you enabled SQL Authentication then you had to enter a name for the SQL Server sysadmin and to choose a password. If you selected this option then you need to use that user in order to connect the server

Is this fit the way you configure the VM?

----------

(2) If you enable the second option Azure Key Vault integration then you had to configure a Key Vault. This is much more complex so I will assume that you did not enable this option. If you did enabled this option then please inform us and we'll guide to on how to use this Key Vault for connecting the server.

----------

Please inform us if this did not solve your case, and in that case, please try to provide more information on the steps of creating the virtual machine and configuration of the sql server (as much as you remember)

Answer 5

Thank you, I appreciate the responses. To address the post by @Erland Sommarskog , SSMS came with the VM, and so I ran SSMS on the VM. And to clarify my situation for @Ronen Ariely , the VM I downloaded is called the Data Science Virtual Machine 2019. During the setup, there was no tab titled, "SQL Server Settings". This is what it looked like.

I unfortunately have not been able to resolve the issue. Case (0) sadly did not help. This is what I tried initially and it did not work. I'm not sure if there is a way to check my settings to see if case (1) and/or case (2) apply? Or a way to check/change the settings of my VM to ensure that my "SQL connectivity" is private?

I appreciate the help!