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

Also, Run SSMS as Administrator could to the trick when the user login roles are configured correctly.