SQL Login Issue

[Cameron2024]

Hello,

I created a new VM with the "SQL Server 2022 Standard on Windows Server 2022 Datacenter" image and logged into the machine. I then proceeded to open the Microsoft SQL Management Studio application and login. When logging in via Windows Authentication, I get the following message:

"Login failed for user '<SERVER>\<GoogleCloudUser>'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: <local machine>]"

I've manually installed SQL on various Windows servers with no problems but never tried the image GCP provides until now. I've never had this error elsewhere and haven't been able to resolve after searching for a solution online.

What's the trick? I'm probably missing something obvious.

Thanks in advance.

[ms4446]

I am sorry to hear that you're encountering difficulties with your SQL Server access. I am assuming that SQL Server was installed using Windows Authentication mode exclusively (so you wont be able to login using the 'sa account'.

It seems that the issue may stem from the fact that your account was not included in the administrators group during the installation process, which would consequently deny access to the SQL Server.

Could you please verify if your account is indeed part of the administrators group?

[Cameron2024]

I cannot speak to how SQL was installed as it was part of the boot image I selected when creating the machine.  I did not install SQL myself.

I just logged into the VM and verified that my user is part of the local Administrator user group.

[ms4446]

Thank you for providing more context. Since SQL Server was part of the boot image and your user is part of the local Administrator user group, it's a bit perplexing that you're unable to log in.

Given the situation, I'll suggest some additional troubleshooting steps:

1. Attempt Connection via Command Line

Try connecting to the SQL Server instance using the sqlcmd utility from the command line. Open a command prompt as the local administrator and run:

sqlcmd -E

If you can connect successfully, you can add your user to the SQL Server sysadmin role with:

CREATE LOGIN [DOMAIN\YourUsername] FROM WINDOWS;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\YourUsername];
GO

Replace DOMAIN\YourUsername with your actual domain and username.

2. Review Windows Event Logs and SQL Server Logs

There may be additional information in the Windows Event Logs or SQL Server Logs that can help diagnose the issue. Look for any error or warning messages related to SQL Server or authentication.

3. Restart SQL Server Service

Sometimes, a simple restart of the SQL Server service can resolve connection issues. You can do this from the Services application in Windows or by running the following command in a Command Prompt with administrative privileges:

net stop MSSQLSERVER
net start MSSQLSERVER

[Cameron2024]

I found a solution.  After watching this video (https://www.youtube.com/watch?v=7nCpg-soYI8), I found that what I had to do was open SQL Management Studio as an admin (right-click).  I'm not sure why I must do this as I am logged into the VM as a member of the local admin group.

[ms4446]

Windows has a feature called User Account Control (UAC) that can restrict the permissions of applications, even when they're run by an administrator. When you explicitly run an application as an administrator (using "Run as administrator"), it bypasses some of these restrictions and grants the application elevated privileges.

[NSAlberto78]

Hello everyone,
I too have the same problem.
And I also confirm that starting SMSS with administrator privileges does not lead to the error.
However, this solution does not satisfy since, on the old SQL servers on which the 2017 version runs, I don't have to do this and it works without any problems.
As additional information I can add that I only get this problem if I connect from the server itself, whereas I do not run into it if I open SMSS on another machine in the same network.
To explain further, if I open SMSS directly from the SQL 2022 server called "x" and connect using my Windows credentials, I get the error; if I open SMSS from my PC on the same network and connect to server "x" using my Windows credentials, I do not encounter any problems.

[ms4446]

When you connect locally to SQL Server, it sees your Windows account without the administrator token (because of UAC). But when you connect remotely, the remote filtering kicks in, and your admin privileges aren't filtered out. This might be why you're able to connect from another machine without issues.

You can disable UAC remote filtering by modifying the local security policy or using Group Policy. However, this is not always recommended due to security concerns. Always weigh the benefits against potential security risks.