December 15, 2022 at 12:15 pm
Hi everyone. I would like to kerberize SQL connections in the following situation and have not been able to:
I have a single server with two SQL instances and only one IP (the server's). I can't configure more IPs due to infrastructure limitation:
SERVER1.domain.com --> 10.10.10.1
I start each instance on a different port using the default Virtual Account:
INSTANCE1 --> Port 1401 --> NT SERVICE\MSSQL$INSTANCE1
INSTANCE2 --> Port 1402 --> NT SERVICE\MSSQL$INSTANCE2
I create two DNS Aliases (C-Name) to the server name:
ALIAS1.domain.com --> SERVER1.domain.com
ALIAS2.domain.com --> SERVER1.domain.com
I have SQLBrowser started and would not like to use domain user accounts.
I create the SPNs for INSTANCE1 (:1401 port) and INSTANCE2 (:1402 port)
SETSPN -s "MSSQLSvc/ALIAS1.domain.com:INSTANCE1" "DOMAIN\SERVER1$"
SETSPN -s "MSSQLSvc/ALIAS1.domain.com:1401" "DOMAIN\SERVER1$"
SETSPN -s "MSSQLSvc/ALIAS2.domain.com:INSTANCE2" "DOMAIN\SERVER1$"
SETSPN -s "MSSQLSvc/ALIAS2.domain.com:1402" "DOMAIN\SERVER1$"
I also have the server SPNs registered:
SETSPN -s "MSSQLSvc/SERVER1.domain.com:INSTANCE1" "DOMAIN\SERVER1$"
SETSPN -s "MSSQLSvc/SERVER1.domain.com:1401" "DOMAIN\SERVER1$"
SETSPN -s "MSSQLSvc/SERVER1.domain.com:INSTANCE2" "DOMAIN\SERVER1$"
SETSPN -s "MSSQLSvc/SERVER1.domain.com:1402" "DOMAIN\SERVER1$"
If I test the remote connection against the instances, KERBEROS only works if I specify the port or the instance name in the connection string.
I would like the connection string to be just the C-Name and SQL Browser to take care of passing the port of the corresponding instance.
Could anyone tell me how to fix this?
Thank you very much.
December 16, 2022 at 1:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 21, 2022 at 8:44 am
I have not tested your exact situation, but I think you have done the best you can with your current setup.
The crucial point is that both your aliases resolve to the same IP address. This means your SPNs also resolve to the same IP address, and so the port number is needed to distinguish between them.
A potential way round this is to configure one of the instances to listen on a different IP address. You say this is difficult to organise in your environment, but all rules are there for a reason and you have a reason to get a rule changed. Kerberos is the most secure way to connect to SQL Server, and it seems the current rules are preventing you from using this security. Discuss this with your manager to find the best way to get the rule changed.
In your situation I would also want to move away from using the local system account for running the services and use a Group Managed Service (gMSA) account. These will allow you to tailor permissions for each account, which also improves security.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 21, 2022 at 9:02 am
Thanks for the reply. I am calmer because these are the conclusions I have reached after carrying out several tests.
There is a technical impediment to having more than one IP per network card. An alternative is to use several network cards, with a different subnet for each of them and link each instance to an IP of one of the cards.
gMSAs are a pending task that we would also like to test. Our security team forces us to change service account passwords every year and with gMSAs we could prevent that.
Best regards and thank you so much again.
December 21, 2022 at 10:51 am
You are right in saying you will need one network adaptor for each IP address. However, they can all be on the same subnet, they just need unique IP address.
For gMSA accounts the following link may be useful. If you are not using FineBuild then ignore the details about FineBuild parameters, the rest is useful for everyone: https://github.com/SQL-FineBuild/Common/wiki/SQL-Server-Accounts
You should also very seriously consider setting up your servers as hyper-v guests. This gives a layer of indirection between your hardware and the SQL server Windows image.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply