Configuring Kerberos Constrained Delegation for MSSQL(TDS) Server

Note

Please note that this is a general guide, and specific details may vary depending on your Windows Server setup. Refer to the Windows Server documentation for precise configuration steps.

Kerberos Authentication Configuration on Windows Server

Configure DNS Entry:

1. Open the Server Manager application.
2. Click Tools button on the upper right corner of the window to expand the menu list and select DNS.

3. Go to Forward Lookup Zones.
4. Add an A record: right click on the selected domain name and click New Host (A or AAAAA).
5. Provide the Name and IP address of the Fudo Enterprise Admin Panel (e.g., mgmt.qa.fudo, 10.0.32.241).
6. Click Add Host.

Create Service User Account:

In this section we add the service user mssqluser, which will represent the MSSQL service in the domain.

1. Log in to the server on which you want to setup the Kerberos using the administrator account.
2. Open the Server Manager application.
3. Click Tools button on the upper right corner of the window to expand the menu list and select Active Directory Users and Computers.

4. In the Manager window, navigate to the domain name, or a specific user group, and right-click on the Users catalog.
5. Select New > User.
6. Create a user (e.g., User logon name: mssqluser).

7. Click Next.
8. Provide the password for created user (e.g., PaSSwOrD) and select Password never expires option.
9. Click Next and Finish.

10. Add an SPN (Service Principal Name) by running the following command in the PowerShell or CMD console:

    setspn -S <service class>/<server name>.<domain>:<port>@<domain> <service account>

    Example:

    setspn -S MSSQLSvc/WIN2019-DC1.qa.fudo:1433@QA.FUDO mssqluser

SQL Server Configuration Manager:

1. Launch SQL Server Configuration Manager.
2. Expand SQL Server Services, select the server, and open the Properties.

3. In the Log On tab, enter the authentication credentials for the mssqluser account.

4. Expand SQL Server Network Configuration, select the server, and ensure that the TCP/IP connection is enabled.

Create Service User representing MSSQL on Fudo:

In this section add the service user account mssqlfudo, which will represent the MSSQL service in Fudo Enterprise.

1. Open the Server Manager application.
2. Click Tools button on the upper right corner of the window to expand the menu list and select Active Directory Users and Computers.
3. In the Manager window, navigate to the domain name, or a specific user group, and right-click on the Users catalog.
4. Select New > User.
5. Create a user who will use the Kerberos delegation to log in to MSSQL(TDS) server (e.g., User logon name: mssqlfudo).

6. Click Next.

7. Provide the password for created user (e.g., PaSSwOrD) and select Password never expires option.

8. Click Next and Finish.

9. Add an SPN (Service Principal Name) by running the following command in the PowerShell or CMD console:

   setspn -S <service class>/<fudo admin panel name>:<port>@<domain> <service account> setspn -S MSSQLSvc/mgmt.qa.fudo:1433@QA.FUDO mssqlfudo

Kerberos Delegation

Note

• When the SPN is set for user mssqlfudo, a new tab called Delegation appears in the user modification window.
• If the Delegation tab is not visible, you may need to refresh the Active Directory Users and Computers management console.

1. Right-click on the mssqlfudo account (created in the previous steps) in the Active Directory Users and Computers window, and select Properties.

2. Go to the Delegation tab.

3. Select Trust this user for delegation to specified services only.

4. Choose Use Kerberos only.

5. Add the Services:

   • Click the Add button.
   • In the Select Users or Computers window, type mssqluser and click Check Names to ensure accuracy.
   • Click OK to proceed.

6. Select Services for Delegation:

   • In the Add Services window, you will see a list of SPNs associated with the mssqluser account.
   • Select all available services, or choose specific SPNs if needed.
   • Click OK to confirm the selection.

7. Click Apply and then OK to save the settings.

8. If changes are not immediately visible, refresh the Active Directory Users and Computers console by pressing F5 or right-clicking on the Users container and selecting Refresh.

Generate Keytab:

1. Run the following command in the Powershell or CMD console:

   ktpass -princ HTTP/hostname.yourdomain.local@yourdomain.local -mapuser netbios_domain_name\username -pass password -ptype KRB5_NT_PRINCIPAL -out hostname.yourdomain.local.keytab

   • Example for this use case:

   ktpass -princ MSSQLSvc/mgmt.qa.fudo:1433@QA.FUDO -mapuser QA\mssqlfudo -pass PaSSwOrD -pType KRB5_NT_PRINCIPAL -out mssqlfudo.keytab

2. Copy the generated keytab file to the workstation where you will be configuring Fudo.

Note

It is an alternative authentication method to using a password. The password must match the one specified when creating the user. If the user’s password is changed, their keytab will be invalidated.

Setup Fudo Enterprise

Configure DNS:

1. Go to Settings > Network configuration.
2. Switch to the Name & DNS tab.

3. Configure DNS server to point to a DNS server in the yourdomain.local domain (in this example we will use a domain controller IP address):

   • Click Add DNS server to define new DNS server.
   • Enter DNS server IP address (e.g., 10.0.242.100).
   • Click Save.

Create User in Fudo:

1. Select Management > Users and then click Add user.

2. Enter the user name that matches created in previous steps user account in Active Directory (e.g., Administrator).
3. Select the user role.
4. In the Settings tab, under the Safes section, select main to grant access rights to the Admin Panel.

5. Click Save.

6. Go to the User Data tab, and fill in the Fudo domain (e.g., QA.FUDO).

Note

The Fudo domain should match the domain name specified in the Kerberos ticket.

7. If necessary, please fill in the remaining parameters as needed for your specific configuration. For more details, please refer to the Creating a user section.

8. Click Save and close.

Configure MSSQL(TDS) Server:

1. Select Management > Servers and then click + Add server.

2. Enter server’s unique name (e.g., tds).
3. In the Settings section on the list of available protocols select MSSQL(TDS).
4. In the Destination section select Host and enter server’s host name (in our example, win2019-dc1.qa.fudo).
5. Enter port: 1433.

6. Click Save and close.

Configure Account:

1. Select Management > Accounts and then click Add account.

2. Define object’s name (e.g., tds:forward).

3. In the Type section select FORWARD.
4. In the Target section click Server, and from the drop down list select server created in previous step (e.g., tds) to assign created account to this server.
5. In the Replace secret with section select None.
6. Click Save.

Note

The forward account type is recommended in this scenario because S4U2Proxy delegation requires using the existing user ticket to authenticate against the TDS server. Therefore, the username of the user who logged in to Fudo Enterprise is always used, and the credentials entered for the account will not be utilized.

Configure Listener

1. Select Management > Listeners and then click Add listener.

2. Enter listener’s unique name (e.g., tds-listener).

3. Go to SETTINGS tab and press the MSSQL(TDS) button in the Protocol field.
4. Select Enable constrained delegation option.
5. In the Service principal name field, enter the following: MSSQLSvc/mgmt.qa.fudo:1433@QA.FUDO.
6. Upload Keytab file.
7. In the Connection mode section, select proxy.

Note

It is possible to use bastion mode, but it will not be possible to use bastion login strings.

8. Set the local address (e.g., Any), and port 1433.

9. Click Save and close.

Configure Safe:

1. Select Management > Safes and then click Add safe.

2. Enter safe’s unique name (e.g., tds-safe).

3. Click Save to save the object and proceed with further configuration.

4. Go to the Users tab to assign users allowed to access accounts assigned to this safe.

   • Click Manage users.
   • Mark the checkbox in front of the users’ names to enable their server access through the monitored safe (e.g., user1).
   • Click Save to close the modal window.

5. Select Accounts tab to add accounts accessible through this safe.

   • Click Manage accounts.
   • Mark the checkbox in front of the accounts’ names to add it  (e.g., tds:forward).
   • Click Save to close the modal window.
   • Select added account and click Manage listeners to assign listener (e.g., tds-listener).
   • Click Save to close the modal window.

6. Click Save and close to save the safe configuration.

Establish a Connection

To establish a connection:

1. Run the SQL client application.
2. Select File > Connect object explorer….
3. In the Server name field provide the Fudo Enterprise Admin Panel name set in the DNS entry: mgmt.qa.fudo.
4. From the Authentication list select Windows Authentication.
5. Click Connect.

Note

Logging in using typical SQL Server Authentication is also possible when using a listener with constrained delegation enabled.

Related topics:

• Creating a MS SQL server
• Setting up the MS SQL listener
• Authentication