SPN issues. SQL 2019 Server 2022, domain service account.
-
Hello, I'll just get straight to it.
I am trying to set up a SQL Server 2019 AlwaysOn AG on Windows Server 2022 but having issues getting my SPN records to register with the Kerberos tool, manually and dynamically.
I have x2 nodes ia-i-c3-sql-01, ia-i-c3-sql-02 which are running through a squid proxy (but anything on our domain bypasses the proxy), domain connected, my specific account is a domain and database sysadmin.
My problem is the typical SPN issue: The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
I thought it would be easy to resolve, and its not, this is everything I have tried so far:
https://clintboessen.blogspot.com/2010/02/dynamically-set-spns-for-sql-service.html - gave permissions to itself so it can dynamically update, restarted services and nothing.
Listed SPNs with setspn, shows only one, so I go to register them and then tells me duplicates were found, so I then try to delete, restart SQL services and still get the same error/issue (see attached image)
And the Kerberos config tool, I don't have a clue what that ones problem is,
I followed the instructions on MS on document and nothing, tried to run it as a local administrator under my domain admin account, tried to fill out the server details with the service account I am trying to use, then my own domain admin account and nothing. Went to the log directory and nothing was recorded.
Permissions: I gave it the permissions in AD to update its SPN following this doc: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver16
Honestly, this is really frustrating me now and I feel like I am over looking something really basic here and I just can't fathom what. I have followed as much of the advice that is out there and still nothing, my brain is just refusing to brain with this problem now and I don't know what else I can do or check.
Any assistance/guidance would be appreciated.
Kind Regards,
James
- This topic was modified 10 months, 3 weeks ago by Jammyuk. Reason: Just adding a bit more details, as forgot some
-
This is what I use:
param ([Parameter(Mandatory = $true, Position = 0)][string]$TargetSQLInstance)
<#
[string]$TargetSQLInstance = $args[0]
Write-Host $('Host - Args count [{0}]' -f $args.count )
Write-output $('Output - Args count [{0}]' -f $args.count )
Write-verbose $('Verbose - Args count [{0}]' -f $args.count )
#>
write-host "--> $TargetSQLInstance <--"
trap {
$theEx = $_
write-ERROR '****Trapped error***' -BackgroundColor Red -ForegroundColor Black
$theEx
Remove-RunStuff ;
break ;
}
#Save current location
Push-Location (Get-Location).path -StackName RegSPN
if ( !( get-module activedirectory ) ) {
Import-Module activedirectory
}
# Check module SQLPS
if ( !(get-module -name SQLServer ) ) {
# save original location
Push-Location
import-module -name SQLServer
#reset current location to original location
Pop-Location
}
#Extract computer name only from potentially fully qualified sql server instance name
$TargetComputerName = [string]($TargetSQLInstance.Split('\')[0]).split('.')[0]
$ADDomain = Get-ADDomain -Current LocalComputer ;
$ADDomain.DNSRoot ;
#example calling setspn
#C:\Program Files\Support Tools\setspn -A MSSQLSvc/myserver.mydomain.com:myportno mydomainserviceaccount
function Get-SQLPortNumber {
param( [string]$SQLInstance
)
# forcing connection using TCP !
$conn = New-Object ("Microsoft.SqlServer.Management.Common.ServerConnection")
#Oh please put some application info in your connection metadata !
$conn.ApplicationName = 'DBA_Powershell_SETSPN'
$conn.WorkstationId = $env:COMPUTERNAME
$Conn.ServerInstance = $SQLInstance
$conn.NetworkProtocol = [Microsoft.SqlServer.Management.Common.NetworkProtocol]::TcpIp
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $conn
$masterdb = $serverInstance.Databases | Where-Object { $_.name -eq "master" }
<#
$SQLErrorlog = $masterdb.ExecuteWithResults("exec sys.sp_readerrorlog").tables[0] | Where-Object { $_.text -like "Server is listening on * 'any' *" }
if ( $SQLErrorlog ) {
$TCPAny = $($SQLErrorlog | select Text -First 1 )
$PortInUse = $TCPAny.Text.Substring($TCPAny.Text.LastIndexOf('>') + 2,( $TCPAny.Text.LastIndexOf(']') - ($TCPAny.Text.LastIndexOf('>') + 2 )))
$serverInstance | Select @{n='ServerName';e={$_.NetName}}, @{n='ComputerNamePhysicalNetBIOS';e={$_.ComputerNamePhysicalNetBIOS}}, @{n='SQLInstance';e={$_.Name}}, @{n='ServiceAccount';e={$_.ServiceAccount}}, @{n='PortNumber';e={$PortInUse}}
#| Out-GridView -Title 'Port In Use'
}
else {
Write-Host $("No TCPIP info found in ERRORLOG file of instance {0}. (rolled over ? if yes Stop/Start needed.)" -f $SQLInstance ) -ForegroundColor black -BackgroundColor Red
}
#>
#Extract portnumber from connection dmv
$PortInUse = $masterdb.ExecuteWithResults('SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID').tables[0].rows[0].local_tcp_port.tostring()
if ( $PortInUse ) {
$serverInstance | Select @{n='ServerName';e={$_.NetName}}, @{n='ComputerNamePhysicalNetBIOS';e={$_.ComputerNamePhysicalNetBIOS}}, @{n='SQLInstance';e={$_.Name}}, @{n='ServiceAccount';e={$_.ServiceAccount}}, @{n='PortNumber';e={$PortInUse}}
}
else {
Write-Host $("No TCPIP info found on instance {0}. (clearly something must be wrong)" -f $SQLInstance ) -ForegroundColor black -BackgroundColor Red
Write-Error $("No TCPIP info found on instance {0}. (clearly something must be wrong)" -f $SQLInstance )
}
$serverInstance.ConnectionContext.Disconnect()
$serverInstance = $null
$conn = $null
}
Function New-SPN ( $FQServerName, $PortNumber, $ServiceAccount ){
#—- Using SetSpn.exe to check Duplicate SPN ———-
#$StrRptFolder="C:\temp"
#setspn is not always couvered in the path system variable
#Push-Location "$env:ProgramFiles\Support Tools\" -StackName "NewSPN"
#$SPNResult = Join-Path $strRptFolder "DuplicatesSPN.txt"
#$spncmd = $("setspn -A MSSQLSvc/{0}:{1} {2} >{3}" -f $FQServerName, $PortNumber, $ServiceAccount, $SPNResult)
$spncmd = $("setspn -A MSSQLSvc/{0}:{1} {2}" -f $FQServerName, $PortNumber, $ServiceAccount)
Invoke-Expression $spncmd
#Pop-Location -StackName "NewSPN"
#& $SPNResult
}
Function Get-SPN {
param ( [string]$ServiceAccount )
$spncmd = $("setspn -L {0}" -f $ServiceAccount )
Invoke-Expression $spncmd
}
Function CheckDuplicateSPN {
$StrRptFolder="$env:temp"
#—- Using SetSpn.exe to check Duplicate SPN ———-
#Push-Location "C:\Program Files\Support Tools\" -StackName "CheckDuplicateSPN"
$SPNResult = Join-Path $strRptFolder "DuplicatesSPN.txt"
$spncmd = "setspn -X -p >$SPNResult"
Invoke-Expression $spncmd
#Pop-Location -StackName "CheckDuplicateSPN"
#Show result
#& $SPNResult
get-content (Join-Path $env:temp "DuplicatesSPN.txt")
}
Function Delete-SPN {
param ( [string] $ServicePrincipalName,
[string] $ServiceAccount
)
$spncmd = $("setspn -D {0} {1}" -f $ServicePrincipalName, $ServiceAccount )
Invoke-Expression $spncmd
}
#—– Call the function to execute the code —-
#clear-host
$SQLInfo = Get-SQLPortNumber -SQLInstance $TargetSQLInstance
$SQLInfo
$DuplicateSPN = CheckDuplicateSPN
if ( $DuplicateSPN ) {
if ( $DuplicateSPN -contains 'found 0 group of duplicate SPNs.' ) {
write-verbose 'OK - no duplicate spns.'
}
else {
Write-Warning 'Duplicate SPNs found ! Double check and remove the invalid one !!!'
$DuplicateSPN | % { write-warning $_.tostring() }
<#
Delete-SPN -ServicePrincipalName MSSQLSvc/XXXXXXXXXX.mydomain.com -ServiceAccount mydaserviceaccount
Delete-SPN -ServicePrincipalName MSSQLSvc/XXXXXXXXXX.mydomain.com:00000 -ServiceAccount mydaserviceaccount
#>
}
}
New-SPN -FQServerName $('{0}.{1}' -f $TargetComputerName, $ADDomain.DNSRoot ) -PortNumber $SQLInfo.PortNumber.ToString() -ServiceAccount $SQLInfo.ServiceAccount.ToString()
Pop-Location -StackName RegSPNTEST IT - test it -TeSt It
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
PS C:\Users\james.shaw\Desktop> .\sql-spn.ps1
cmdlet sql-spn.ps1 at command pipeline positcomn 1
Supply values for the following parameters:
TargetSQLInstance: ia-i-c3-sql-02
--> ia-i-c3-sql-02 <--
ad.vpc7.infra.domain.com
ServerName : IA-I-C3-SQL-02
ComputerNamePhysicalNetBcomS : IA-I-C3-SQL-02
SQLInstance : ia-i-c3-sql-02
ServiceAccount : ad-vpc7\sqlserver$
PortNumber : 1433
Checking domain DC=ad,DC=vpc7,DC=infra,DC=domain,DC=com
CN=IA-I-C3-SQL-02,OU=sql,OU=ia-i-cluster3,OU=Computers,OU=ad-vpc7,DC=ad,DC=vpc7,DC=infra,DC=domain,DC=com
MSSQLSvc/ia-i-c3-sql-02.ad.vpc7.infra.domain.com:1433
MSSQLSvc/ia-i-c3-sql-02.ad.vpc7.infra.domain.com
MSServerClusterMgmtAPI/IA-I-C3-SQL-02
MSServerClusterMgmtAPI/ia-i-c3-sql-02.ad.vpc7.infra.domain.com
WSMAN/ia-i-c3-sql-02
WSMAN/ia-i-c3-sql-02.ad.vpc7.infra.domain.com
TERMSRV/IA-I-C3-SQL-02
TERMSRV/ia-i-c3-sql-02.ad.vpc7.infra.domain.com
RestrictedKrbHost/IA-I-C3-SQL-02
HOST/IA-I-C3-SQL-02
RestrictedKrbHost/ia-i-c3-sql-02.ad.vpc7.infra.domain.com
HOST/ia-i-c3-sql-02.ad.vpc7.infra.domain.com
Duplicate SPN found, aborting operatcomn!
PS C:\Users\james.shaw\Desktop> setspn -L ad-vpc7\sqlserver
Registered ServicePrincipalNames for CN=sqlserver,CN=Managed Service Accounts,DC=ad,DC=vpc7,DC=infra,DC=domain,DC=com:
PS C:\Users\james.shaw\Desktop>Hi Johan,
I appreciate the script, but it still fails with duplicate SPN records, but again when I run setspn -L to see what SPN records are there, none show.
I never had this issue with SQL Server 2019 on Server 2019, but its a problem on server 2022. I have rechecked everything from permissions, DNS, etc. and everything is in place.
I even tried a new service account, with said permissions, etc. and this is also doing the same thing.
Kind Regards,
James
-
the search for duplicate SPN is setspn -X If there are a lot of them, redirect to a notepad so you can search it
if you add -F it will search the entire forest but there are additional permissions considerations to search the forest
-
Sorted it, seems like the SPNs registered directly against my servers hosts names, and not the service account I set up during the custom installation path.
setspn -D MSSQLSvc/IA-I-C3-SQL-01.ad.vpc7.infra.domain.com IA-I-C3-SQL-01
setspn -D MSSQLSvc/IA-I-C3-SQL-01.ad.vpc7.infra.domain.com:1433 IA-I-C3-SQL-01
setspn -D MSSQLSvc/IA-I-C3-SQL-02.ad.vpc7.infra.domain.com IA-I-C3-SQL-02
setspn -D MSSQLSvc/IA-I-C3-SQL-02.ad.vpc7.infra.domain.com:1433 IA-I-C3-SQL-02
This was mostly just a random, wing it scenario as I didn't think it would work. I just replaced "domain/account" with the short name of my FQDN for my server, restarted the services and it worked.
Thank you for the advice everyone.
Have a lovely day 🙂
James
-
Happy it worked out !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply