Problem with a local sql account that contains '@' in the name

[nublaii]

I have a problem connecting with a local sql account that contains '@' on the name, something like this: 'deploy@server'.

Even thou the auth type should be 'local sql', the '@' on the username changes the auth type to 'local ad'.

Looking at the code Connect-DbaInstance.ps1 states that the backslash is used to detect auth type:

.PARAMETER SqlCredential
Credential object used to connect to the SQL Server Instance as a different user. 
This can be a Windows or SQL Server account. 
Windows users are determined by the existence of a backslash, so if you are intending to use an alternative Windows connection instead of a SQL login, ensure it contains a backslash.

the actual code does a little more (line 655 onwards), using not only backslash, but also the @:

# support both ad\username and username@ad
...
                    if ($username -like '*@*') {
                        $authType += 'ad'

¿Any way to fix this forcing auth type for a particular connection or should I report this as a bug/documentation problem?

[potatoqualitee]

This is rough as it's such an edge case. Can you use server\username instead?

[nublaii]

Hi, tnx for the quick response.

Your solution would still hit the "...Windows users are determined by the existence of a backslash..."

My problem is the user deploy@random_word_here is a SQL Server account, not a Windows account.

[potatoqualitee]

ahh true, i was just testing it and could not get around it.

ohhh wait! you can use connection strings instead, let me whip one up.

[potatoqualitee]

$server = Connect-DbaInstance -ConnectionString "Data Source=mssql1;User ID=deploy@server;Password=mypassword;"

I just tested it and it worked for me

PS> $server = Connect-DbaInstance -ConnectionString "Data Source=mssql1;User ID=deploy@whatever;Password=test;"
PS> Get-DbaDatabase -SqlInstance $server


ComputerName       : mssql1
InstanceName       : MSSQLSERVER
SqlInstance        : mssql1
Name               : master
Status             : Normal
IsAccessible       : True
RecoveryModel      : Simple
LogReuseWaitStatus : Nothing
SizeMB             : 6.6875
Compatibility      : Version150
Collation          : SQL_Latin1_General_CP1_CI_AS
Owner              : sa
Encrypted          : False
LastFullBackup     : 5/18/2023 3:15:00 AM
LastDiffBackup     : 1/1/0001 12:00:00 AM
LastLogBackup      : 1/1/0001 12:00:00 AM

you may need some of these too ;Multiple Active Result Sets=False;Connect Timeout=15;Encrypt=False;Trust Server Certificate=True

[potatoqualitee]

If you'd like to hide your pw, you can set it as an env var or build it from BetterCredentials https://github.com/Jaykul/BetterCredentials

[nublaii]

Thank you, it's ugly, but works.

I guess the documentation could be updated to give you a heads up about the '@' in the name and the fact that it autmatically changes the auth type to AD.