0

On Friday I had to change my domain password and my employer upgraded my machine to Win11 over the weekend. There also seems to have been an upgrade to our security software. Not sure which parts of this might be relevant.

I've been running SQL Server Management Studio (SSMS) using runas.exe (in a shortcut on my taskbar) with no problem under Win10. Since the upgrade, however, while SSMS will run under the alternate windows credentials, it has stopped passing the credentials through to the SQL Servers I'm trying to connect to. When I try to connect, I almost always get

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

Sometimes I'm able to log in to our servers in different domains, but it's not consistent. I don't have the same problem trying to do the exact same thing in Azure Data Studio. Just SSMS. Odder still, if, instead, I shift click the app and choose 'Run as different user', I don't experience any problems at all.

I'm not really sure where to start with this. I've rebooted. I don't really have other domain-linked SQL logins I can test with. Can anyone offer any suggestions as to where the problem lies?

Improve this question
4
  • 1
    First suggestion is to ask the IT people what the heck they did over the weekend.
    – harrymc
    Commented Dec 4, 2023 at 19:48
  • 2
    You must ask your employer. Corporate questions are off topic here.
    – anon
    Commented Dec 4, 2023 at 21:00
  • Truly? Where should I look for the details on this policy?
    – volfied
    Commented Dec 5, 2023 at 20:31
  • @John ah ha! Found it. superuser.com/help/on-topic And right you are. It's not exactly well-advertised. Is there a site in the Stack universe where this question would be more appropriate?
    – volfied
    Commented Dec 5, 2023 at 20:48

2 Answers 2

Reset to default
1

  1. Since you can Run as from the context menu, try recreating the shortcut to SSMS, right-clicking, and set Run as administrator.

  2. To run as a user other than Administrator, make a shortcut or batch file using the runas command. e.g.,

    runas /user:"A E Neumann" "C:\Windows\notepad.exe"

    To avoid entering the user's password each time, use the /savecred switch to store it in Windows Credential Manager, e.g.,

    runas /user:"A E Neumann" /savecred "C:\Windows\notepad.exe"

You might also check if the security update affected GPO. Open gpedit.msc, navigate to User Configuration\Administrative Templates\Start Menu and Taskbar, and check that Show “Run as different user” command on Start is enabled.

Improve this answer
1
  • Thank you for answering, but you didn’t read my question very carefully.
    – volfied
    Commented Dec 5, 2023 at 20:32
1

The workaround that a coworker and I came up with was to create a PowerShell script to achieve the same result. First, because Get-StoredCredential was not seeing the entry for this particular login, I created a custom entry containing the network credentials. Name it whatever you like. We'll go with foo in this example. Then create a script as follows:

$cred=Get-StoredCredential -Target "foo"
Start-Process "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe" -Credential $cred

Obviously, replace the executable path with the appropriate one for your application. I had to do the same thing for the old version of Visual Studio I use. I also had to use ps2exe to convert the script to an executable. You may or may not need to do the same.

None of this addresses why the problem exists, but it does work reliably.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .