How to debug Kerberos and LDAP for SQL Server

11 February,2021 by Jack Vamvas

Question: I've deployed Container based SQL Servers and set up integrated authentication with Active Directory. There are some issues and I need to explore error messages for some AD failures.  How can I generate a log to gather trace statements and responses?

Answer: It is possible to gather this information by enabling the SQL Server - SQLPAL - Logging . Part of enabling SQLPAL includes configuring the location for the output log file. By default SQLPAL is turned off. 

Add following to /var/opt/mssql/logger.ini. If the logger.ini is not present , than create the file in the /var/opt/mssql/ path. If it is a Container than the /var/opt/mssql/  should be relative to the Container. 

To trace Kerberos activity

[Output:sql]
type=File
filename=/var/opt/mssql/log/pallog.log

[Logger:security.ldap]
level=debug
outputs=sql

[Logger:security.kerberos]
level=debug
outputs=sql

 

Major Error Code (Value << type_offset)

Value << 24 are caller issues (incorrect password for example)
Value << 16 are internal routine based failures (Can’t contact KDC for example)

 

To trace libos activity 

[Output:sql]
type=File
filename=/var/opt/mssql/log/palstart.log

[Logger:security.ldap]
level=debug
outputs=sql

[Logger:libos.trace]
level=debug
outputs=sql

 

 

KRB5_TRACE=/path/to/file

To make permanent use the EXPORT

 

 

Read more on Containers

List the differences between Kerberos and NTLM

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to debug Kerberos and LDAP for SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer