Kerberos Authentication flow
Video version: Kerberos Authentication flow Whenever you login with Windows Authentication to the sever like SQL Server, in many cases, Kerberos is used as a main authentication type. In short, Kerberos is a secure method for verifying the identity of users and services in a network to authenticate users. So, suppose, Kerberos authentication started to fail and your application cannot login? You started to panic and want to troubleshoot as soon as possible! But you do not know how to do that because you do not understand how this authentication works internally. In this case, if you know how Kerberos functions internally, it becomes much easier for you to troubleshoot this issue. The below is the Kerberos authentication flow at high level: 1. Whenever client tries to login to the server, it should first do Name resolution. If there is no DNS cache, it will connect domain controller and bring IP address for the server DNS name. 2. Afterwards, there will be three-way TCP handshake with the server. 3. Following TCP handshake, servers negotiate on protocol types of authentications: NTLM or KERBEROS. Often Kerberos is chosen as this is new protocol with more performance efficiency. 4. After Kerberos is chosen, Client will do TCP handshake on TCP port 88. 5. Then, client will send AS-REQ packet to KDC center in domain controller. AS-REQ (Authentication Service Request) is the initial message sent by a client to the Key Distribution Center (KDC) in Domain controller to get TGT. This message includes the client's principal name (username) and may include pre-authentication data such as password. If the username is correct and password is valid, domain controller sends AS RESPONSE. This response includes TGT key and session key. Here is the important point: TGT key is encrypted with password of KRBTGT account and session key is encrypted with client's password. If you go to your domain controller, you will see this KRBGT account. TGT is encrypted with this account’s password. When the client receives the TGT, it decrypts the session key with password and this session key is placed in memory along with the TGT. Going forward the account’s password is no longer required. When the client makes subsequent ticket requests it will present the TGT and creates a new authentication using the session key and the system timestamp. 6. After getting TGT and session key, client makes TGS request presenting TGT and service principal name (SPNs) of the service the client wants to access. 7. After getting TGS-REQ, domain controller decrypts TGT ticket, validates the user and SPNs. If the SPNs are correctly registered and user credentials, TGT ticket are valid, domain controller responds with TGS-REP which includes the service ticket and a session key. The service ticket is encrypted with the service's secret key, while the session key is encrypted with the client's session key. Here, important point is service ticket is encrypted with the password of service account attached to the server and session key is encrypted by the client’s session key. Therefore, client cannot decrypt service ticket. 8. Finally, by using this service ticket, client makes AP request to the server. And AP-REP is received. This AP-REP is often not mandatory and does not happen depending on the service type. With this, KERBEROS authentication finishes. Authentication flow in network traces 1. TCP-handshake 2. First, as we learn AS REQUEST is sent. One important point here: first AS-REQ fails with PRE-AUTH REQUIRED error because client did not send pre-authorization, or did not send the appropriate type of pre-authorization, to receive a ticket. The client will retry with the appropriate kind of pre-authorization (the KDC returns the pre-authentication type in the error). Many Kerberos implementations will start off without preauthenticated data and only add it in a subsequent request when it sees this error. This is expected and you should ignore this. 3. Afterwards, client again sends AS-REQUEST with appropriate pre-authorization. 4. Domain controller is responding with AS-REP and providing encrypted TGT ticket and session key. 5. By using this session key and TGT, client is making TGS request. It also includes SPN names. 6. Finally, domain controller responds TGS response and includes service key. By using this service key, client makes authentication request.Adding AD Security group to a SQL linked server
Hello, I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone. Here is the topo: I have private software that I'll call X installed on a Hyper-V virtual server. Users of this software connect through virtual machines using the Remote desktop. The application has its own SQL Server database instance. The security for this database is Windows Authenticated. Application X users all belong to a user group in the Windows Server 2019 Active Directory (AD). On the other hand, I have a Y application (Microsoft Dynamic 365) that is installed locally on each workstation and which has its own SQL instance. The application uses an SQL database which I would also call Y. As I would like to share information between the 2 SQL instances, I created a linked server on the X instance to the Y instance. I succeeded with some SQL query to insert, update, and delete some information in a database of instance Y from my instance X. To do this, I granted myself DB_owner rights on the database of X. I have reached the stage of allowing user group X to be able to run from application X using the same queries that interact on SQL server Y. However, I realize that I need to create each user of X in the SQL instance of application Y from the Security, Connection menu. My requests work when I grant DB_Owner roles. I would have thought that I could have used the same group used in AD for my X application. I tried well but it doesn't work. I don't know if this is due to the configuration of my linked server. Here is the configuration I am using: Could someone point me to a solution to avoid having to recreate every user of my AD from X in my instance Y and just link my group X.
