CODEWITHSUNDEEP
sql-serverpowershellntlmgmsa
Lord Helmet
Lord Helmet

Reputation: 170

Connect SQL Without Using NTLM

Trying to connect to SQL Server without using NTLM. The instance services are using a gMSA account.

$instance = "SERVER.DOMAIN.LOCAL\INSTANCE"
$database = "DATABASE"
$connectionString = "Server = $instance; Database = $database; Integrated Security = $TRUE;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

I don't know how to use any other method other than integrated security. User ID and password doesn't seem to work, I think that's for SQL Server logins, which are disabled.

Here is the full function, for reference, which is my method versus Invoke-SQLCMD

function Execute-SQL($query, [switch]$read, [switch]$write){
$instance                    = "SERVER.DOMAIN.LOCAL\INSTANCE"
$database                    = "DATABASE"
$connectionString            = "Server = $instance; Database = $database; Integrated Security = $TRUE;"
$connection                  = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$cmd                         = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection              = $connection
$cmd.CommandText             = $query
$cmd.CommandTimeout          = 0
if($write){
    try{
        $cmd.ExecuteNonQuery() | Out-Null
    }catch{
        Write-Host $Error[0] -ForegroundColor DarkRed
        Write-Host $cmd.CommandText -ForegroundColor DarkGray
    }
}elseif($read){
    try{
        $output                = ""
        $adapter               = New-Object System.Data.SqlClient.SqlDataAdapter
        $adapter.SelectCommand = $cmd
        $DataSet               = New-Object System.Data.DataSet
        $adapter.Fill($DataSet) | Out-Null
        $output                = $DataSet.Tables[0] | ?{$_}
        $connection.Close()
        return $output
    }catch{
        Write-Host $Error[0] -ForegroundColor DarkRed
        Write-Host $query -ForegroundColor DarkCyan
    }
}else{
    Write-Host "READ/WRITE NOT SPECIFIED" -ForegroundColor DarkCyan
}
$connection.Close()

}

Upvotes: 0

Views: 208

Answers (1)

Lord Helmet
Lord Helmet

Reputation: 170

We figured it out. We had to specify a port for each instance. The port could not be something that was being used by Windows Server. Once we did that, we had to add the instance name and the port to the SPN list. After that, all of my connections worked with NTLM disabled. Had to restart SQL services for each instance for the ports to replicate. Had to use static ports, not dynamic - which seems to default to 1433.

The Kerberos Configuration Manager (download) was a fantastic tool during troubleshooting. It would show the instances and what SPNs were required for each, and whether or not that SPN was entered.

Upvotes: 0

Related Questions