Friday, September 20, 2013

0 Setup Kerberos connection to SQL Server

Kerberos is popular security best practice.  In this post, I am going to work through how to setup the Kerberos connection for SQL Server.

Prerequisite:

Windows domain controller setup and have SQL Server on the same domain but in different host.

Domain Controller KDC

Make sure the KDC is running in the DC.

image

Make sure the FQDN is pintable from client

In my lab case, the FQDN is sgc1.stargate.com , We basically need to use the pingable name for the SPN.

If the pingable name is SGC1 only, than the SPN must to use the SGC1.

If the pinable name is SGC1.stargate.com. the SPN must be use SGC1.stargate.com

If both are pingable, than we have to use the FQDN which is SGC1.stargate.com

Setup TCPIP Port

SQL Server support Kerberos through Share memory, TCPIP and Name Pipe. Since I am going to connect from remotely, I like to set up the static Port Number.

image

Register SPN

   1: # SetSPN –A MSSQLSvc/SGC1.stargate.com:1433 stargate\sqlprodid
   2: # setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname
   3: setspn -A MSSQLSvc/SGC1.stargate.com:8001 stargate\sqlprodid
image

 

Test kerberos connection

We can use below query to check whether the authentication is kerberos or not.

   1: select session_id,auth_scheme,net_transport,client_net_address,local_tcp_port 
   2: from sys.dm_exec_connections

image

After make the initial connection, we can check the local ticket for the SPN.

image

List SPN

   1: # setspn -L DOMAIN\SQLSERVER_ACCOUNT
   2: setspn -L stargate\sqlprodid

image

 

Remove SPN

   1: setspn -d MSSQLSvc/SGC1.stargate.com:SGC1SQL2K8R2 stargate\sqlprodid

 

image

List and purge kerberos ticket

   1: klist purge
   2: klist 

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates