dataplat
Connect-DbaInstance cannot connect as gMSA due to bug in code
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
running the following command: Connect-DbaInstance SQLINSTANCE -Debug -SqlCredential $pscredobjofgmsa
produces the following result
DEBUG: 4768 | [12:47:42][Connect-DbaInstance] Starting process block
DEBUG: 4779 | [12:47:42][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 4667 | [12:47:42][Test-Azure] Test for Azure is negative
DEBUG: 4786 | [12:47:42][Connect-DbaInstance] Starting loop for 'SQLINSTANCE': ComputerName = 'SQLINSTANCE', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: 4890 | [12:47:42][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
DEBUG: 4667 | [12:47:42][Test-Azure] Test for Azure is negative
DEBUG: 5008 | [12:47:42][Connect-DbaInstance] authentication method is 'local ad'
DEBUG: 5047 | [12:47:42][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module - dbatools.io'
DEBUG: 5079 | [12:47:42][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: 5092 | [12:47:42][Connect-DbaInstance] EncryptConnection will be set to 'Mandatory'
DEBUG: 5113 | [12:47:42][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: 5132 | [12:47:42][Connect-DbaInstance] Pooled will be set to 'True'
DEBUG: 5158 | [12:47:42][Connect-DbaInstance] TrustServerCertificate will be set to 'True'
DEBUG: 5201 | [12:47:42][Connect-DbaInstance] Building ServerConnection from SqlConnectionInfo
DEBUG: 5203 | [12:47:42][Connect-DbaInstance] ServerConnection was built
DEBUG: 5211 | [12:47:42][Connect-DbaInstance] ConnectAsUser will be set to 'True'
DEBUG: 5214 | [12:47:42][Connect-DbaInstance] ConnectAsUserName will be set to 'gmsawithoutthedollarsign@domain'
DEBUG: 5217 | [12:47:42][Connect-DbaInstance] ConnectAsUserPassword will be set
DEBUG: 5221 | [12:47:42][Connect-DbaInstance] Building Server from ServerConnection
DEBUG: 5223 | [12:47:42][Connect-DbaInstance] Server was built
DEBUG: 5242 | [12:47:42][Connect-DbaInstance] Setting ConnectionContext.StatementTimeout to '0'
DEBUG: 5249 | [12:47:42][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=SQLINSTANCE;Integrated Security=True;Multiple Active Result Sets=False;Encrypt=True;Trust Server Certificate=True;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 5262 | [12:47:42][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: [12:47:42][Connect-DbaInstance] Failure | Error connecting to [SQLINSTANCE]: The user name or password is incorrect.
ConnectionError:
Line |
151381 | throw $records[0]
| ~~~~~~~~~~~~~~~~~
| Error connecting to [SQLINSTANCE]: The user name or password is incorrect.
Steps to Reproduce
Connect-DbaInstance SQLINSTANCE -Debug -SqlCredential $pscredobjofgmsa
Get-DbaAgReplica SQLINSTANCE -Debug -SqlCredential $pscredobjofgmsa
Please confirm that you are running the most recent version of dbatools
2.0.4
Other details or mentions
looking at the code it appears there is an if statement which drops the $ from the account name and rewrites it as user@domain
alternatively, when logging into a powershell session as the gMSA account and running the same cmdlet without explicit credentials, the correct format is used and the following output is produced:
DEBUG: [13:22:03][Connect-DbaInstance] Starting process block
DEBUG: [13:22:03][Connect-DbaInstance] Immediately checking for Azure
DEBUG: [13:22:03][Test-Azure] Test for Azure is negative
DEBUG: [13:22:03][Connect-DbaInstance] Starting loop for 'SQLINSTANCE': ComputerName = 'SQLINSTANCE', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: [13:22:03][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
DEBUG: [13:22:03][Test-Azure] Test for Azure is negative
DEBUG: [13:22:03][Connect-DbaInstance] authentication method is 'local integrated'
DEBUG: [13:22:03][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module - dbatools.io'
DEBUG: [13:22:03][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: [13:22:03][Connect-DbaInstance] EncryptConnection will be set to 'Mandatory'
DEBUG: [13:22:03][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: [13:22:03][Connect-DbaInstance] Pooled will be set to 'True'
DEBUG: [13:22:03][Connect-DbaInstance] TrustServerCertificate will be set to 'True'
DEBUG: [13:22:03][Connect-DbaInstance] Building ServerConnection from SqlConnectionInfo
DEBUG: [13:22:03][Connect-DbaInstance] ServerConnection was built
DEBUG: [13:22:03][Connect-DbaInstance] Building Server from ServerConnection
DEBUG: [13:22:03][Connect-DbaInstance] Server was built
DEBUG: [13:22:03][Connect-DbaInstance] Setting ConnectionContext.StatementTimeout to '0'
DEBUG: [13:22:03][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=SQLINSTANCE;Integrated Security=True;Multiple Active Result Sets=False;Encrypt=True;Trust Server Certificate=True;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: [13:22:03][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: [13:22:05][Connect-DbaInstance] We have a connected server object
DEBUG: [13:22:05][Connect-DbaInstance] We will set server.ComputerName to server.NetName
DEBUG: [13:22:05][Connect-DbaInstance] ComputerName will be set to SQLINSTANCE
DEBUG: [13:22:05][Test-Azure] Test for Azure is negative
DEBUG: [13:22:05][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'DOMAIN\gmsawith$'
DEBUG: [13:22:05][Connect-DbaInstance] We return the server object
DEBUG: [13:22:06][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: [13:22:06][Add-ConnectionHashValue] Adding to connection hash
DEBUG: [13:22:06][Connect-DbaInstance] We are finished with this instance
What PowerShell host was used when producing this error
PowerShell Core (pwsh.exe)
PowerShell Host Version
Name Value
PSVersion 7.2.13 PSEdition Core GitCommitId 7.2.13 OS Microsoft Windows 10.0.22621 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0
SQL Server Edition and Build number
Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
.NET Framework Version
.NET 6.0.20
Are there any workarounds for this?
I am getting "Logon failure: the user has not been granted the requested logon type at this computer", as gMSAs cannot logon interactively.
Why is an interactive logon required when using -SqlCredential?
the closest to a "workaround" i have found is to create a separate PSSession as the gMSA and then run Invoke-Command -Session $gmsasession -ScriptBlock {}. It works but is tedious and particularly annoying since other types of accounts work fine with -SqlCredential.
Can confirm this still works on the latest dbatools and powershell versions.
Thanks @danikuci1 - very annoying because using Invoke-Command doesn't work for my use case
AFAIK the only "known" ways to use gMSA is for the "env" to be supported and prepared for gMSA and then leverage integrated security. The only places it's supported in Windows are:
- IIS
- Task Scheduler
- Windows Service
- Run as
Connect-DbaInstance "can" do what basically SSMS can do, and I don't think you can start SSMS as your normal user and enter a gMSA passwordless account in it.
AFAIK the only "known" ways to use gMSA is for the "env" to be supported and prepared for gMSA and then leverage integrated security. The only places it's supported in Windows are:
- IIS
- Task Scheduler
- Windows Service
- Run as
Connect-DbaInstance "can" do what basically SSMS can do, and I don't think you can start SSMS as your normal user and enter a gMSA passwordless account in it.
They are not passwordless, but the passwords are 256 bytes of random data, so it wouldn't be possible to type it with a keyboard.
Is there anywhere in the code we can change the logon type? I did have a look through Connect-DbaInstance but nothing stuck out.
For all matter and purposes, they are, and indeed, "passwords" are rotated according to GPOs and being able to "know" the password would kinda go against the whole purpose of gMSAs .
So, instead of asking what Connect-DbaInstance could do, both @danikuci1 and @GrantSavill can figure out a connectionstring that works with gMSAs ? If you have one that is working, Connect-DbaInstance could be modified to build it . If you haven't, I think it's time to close this request as it's not implementable.