ServerConnection and Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication

Today I had to work with the ServerConnection class.

This class provides a method to specify the connection to the Server class.

So usage would be like this:

# http://sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo')  

$sqlServer = "server"
$username = "username"
$password= "password"

$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer, $username, $password)

$server = new-object Microsoft.SqlServer.Management.Smo.Server($serverConnection)

Write-Host ("SQL Version: {0}" -f $server.Information.Version)

$server.databases | % { Write-Host $_.Name }

Now this works for SQL accounts, but not for domain accounts.

My username was in the form of DOMAIN\username, but that failed.

Checking the SQL Server log, it yielded this:

Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication
Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication

So to use the domain account with this object you need to create the $serverConnection like this AND you need to specify your username in the form of: [email protected] (FQN). Entering DOMAIN\username doesn’t seem to work.

$sqlServer = "server"
$username = "[email protected]"
$password= "password"

$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer)

$serverConnection.ConnectAsUser = $true
$serverConnection.ConnectAsUsername = $username
$serverConnection.ConnectAsUserPassword = $password

You need to use the ConnectAsUsername and ConnectAsUserPassword to use domain accounts and set the ConnectAsUser property to true.

When I connect with those options I get the access I need.

You can verify it by executing the following query:

$conn.ExecuteScalar("SELECT SUSER_NAME() as [foo]")

Which nicely yields:

[email protected]

Have a good one,

-Kristof