SPN for sql 2016 cluster
-
Hi,
I'm new in sql and spn so excuse my dump question. And my english is bad so ........i try to setup spn for the cluster with 2 nodes.
The 2 nodes must be have the same account to the spn or no
The virtual ip of cluster must be have a spn record and if yes it's the same account of nodei try to validate this requete
select session_id,net_transport,client_net_address,auth_scheme
from sys.dm_exec_connections
where session_id=@@SPID
But i have always this response
iThe account have delegate approuved for all kerberos services.I have no idea for the solution
Maybe someone have a trick for m,e - +xHi,
I'm new in sql and spn so excuse my dump question. And my english is bad so ........i try to setup spn for the cluster with 2 nodes.The 2 nodes must be have the same account to the spn or no
The virtual ip of cluster must be have a spn record and if yes it's the same account of nodei try to validate this requete
select session_id,net_transport,client_net_address,auth_scheme
from sys.dm_exec_connections
where session_id=@@SPIDBut i have always this response
iThe account have delegate approuved for all kerberos services.I have no idea for the solution
Maybe someone have a trick for m,eNo, the 2 nodes don't need to have the same Service account.
Kerberos authentication only works with domain accounts, it doesn't work with SQL Server logins
Try using the Microsoft tool for registering SPNs.
If i'm not mistaken you must be domain administrator.Install it in the servers where the SQL Instances are, run it from there, connect to the instance and configure your SPNs per service.
Microsoft Kerberos Configuration Manager for SQL Server
Greetings.
-
I try Microsoft Kerberos Configuration Manager for SQL Server but it's not working , I try on différent server but i get always error on access wmi
I have read the use the virtual ip of cluster for registring the spn, it's true or not
-
If you are in fact using a domain account, try using the following command in command prompt:
setspn -L <account_name>
example: setspn -L srv_sqlaccountWhat this will do is list all of the SPNs registered to srv_sqlaccount. Do you see both of your servers & your listener listed here?
-
Yes i do this
the setspn -L accountName return the service name/server good
the setspn -L serverName don't return the service returned with the precedent commandbut bizarre the setspn -L FQDNServerName don't give me a result
-
try this
begin
DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
--SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS FQDN
declare @port nvarchar(100)
declare @auth nvarchar(100)
select @port =local_tcp_port ,@auth=auth_scheme
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID-- Check SPN
select 'setSPN -L "' + service_account +'"' as cmd , @auth auth_schme
FROM sys.dm_server_services
where servicename like 'SQL Server%'
and servicename not like '%Agent%'-- Create SPN
select 'setSPN -S "MSSQLSvc/' + Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain +
case when serverproperty('InstanceName') is null then '"' else ':' + Cast(SERVERPROPERTY('InstanceName') as nvarchar) + '"' end
+ ' "' + service_account +'"' as cmd_to_set_spn , @auth auth_schme
FROM sys.dm_server_services
where servicename like 'SQL Server%'
and servicename not like '%Agent%'
and @auth='NTLM'
union
select 'setSPN -S "MSSQLSvc/' + Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain +
case when serverproperty('InstanceName') is null then ':' +cast(@port as nvarchar) + '"' else '/' + Cast(SERVERPROPERTY('InstanceName') as nvarchar) + ':' +cast(@port as nvarchar) + '"' end
+ ' "' + service_account +'"' as cmd , @auth auth_schme
FROM sys.dm_server_services
where servicename like 'SQL Server%'
and servicename not like '%Agent%'
and @auth='NTLM'
end - Alejandro Santana - Tuesday, October 2, 2018 12:32 PM
The service on both nodes must use the same account as the SPN is bound to the user account the service runs under
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
I will resume
The two or many node under cluster must be have the same account for the service ans the spn bound as like
setspn MSSQLsvc/node1.domain.com domain.com\accountname1
setspn MSSQLsvc/node2.domain.com domain.com\accountname1
setspn MSSQLsvc/node3.domain.com domain.com\accountname1and the VirtualIpNameOfCluster must not to have a setspn like this
setspn MSSQLsvc/VirtualIPNameOfCluster.domain.com domain.com\accountname1
Excuse My english
- goher2000 - Wednesday, October 3, 2018 1:37 PM
Hi Goher2000,
I will try your script on my cluster of Preprod
Thank you
- steve.vibert - Thursday, October 4, 2018 5:41 AM
Are you using an Availability Group or Failover Cluster Instance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
- steve.vibert - Wednesday, October 3, 2018 5:31 AM
Can you tell us what the error is telling?
To query if you have an spn in your server open cmd and run
setspn -Q MSSQLSvc/ <SPN>For example, if the server name is MySqlServer.MyDomain.com and SQL Server is listening on port 1433 you could use the following commands to verify if a SPN already exists:
setspn -Q MSSQLSvc/MySQLServer
setspn -Q MSSQLSvc/MySQLServer.MyDomain.com
setspn -Q MSSQLSvc/MySQLServer.MyDomain.com:1433steve.vibert - Thursday, October 4, 2018 5:41 AMWhat account you are using to register the SPN?
•An SPN must belong to one and only one account in Microsoft Active Directory Services. If it belongs to more than one account, Kerberos can fail.
•If the SQL Server service is running under the Local System or Network Service account, the SPN is automatically registered, and Kerberos interacts successfully with the computer that is running SQL Server.
•However,if the SQL Server service is running under a domain account or under a localaccount, the attempt to create the SPN fails in most cases, because the domain account and the local account do not have the right to create their own SPNs.
•When the SPN is not successfully created, no SPN exists for the computer that is running SQL Server. If a domain administrator account is used as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials required to create the SPN are present.
•If you grant “Read servicePrinicipalNameâ€â€–and “Write servicePrincipalNameâ€â€–privileges to SQL Server service account, the account can automatically register an SPN.To register an SPN manually for a clustered instance of SQL Server, run the following command:
setspn -S MSSQLSvc/<FQDN>:<InstanceName> <SQL_Service_Account>
Note:<InstanceName> is required only for named instances.For example, if the FQDN for the default clustered instance of SQL Server is MySQLServer.MyDomain.com, and it is running under the service account SQLSVC, run the following command:
setspn -SMSSQLSvc/MySQLServer.MyDomain.com SQLSVCGreetings.
- Perry Whittle - Thursday, October 4, 2018 3:23 AM
I think you can use different accounts, of course registering each one as SPN for each server for kerberos auth.
Correct me if i'm wrong, im not 100% sure, thanks!
Greetings
-
For the error for Kerberos configuration manger, it's a error acces wmi information. But i try with winrm with the same account (domain admin) and i get infomation .
Yes for the moment i grant read and write to the self entity for the account of SqlService.
What's about security when i set the read end write properetis on account ?
- Alejandro Santana - Thursday, October 4, 2018 9:55 AM
No, they must use the same account, this is detailed in the pre reqs and restrictions for AGs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
- Perry Whittle - Thursday, October 4, 2018 7:23 AM
:Whistling:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
iThe account have delegate approuved for all kerberos services.Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply