Sql-server – Could I control which protocol will ODBC uses to connect MSSQL on “integrated windows authentication”

active-directorykerberossql server

I have a Windows machine that should connect to an MSSQL instance with an ODBC driver using "windows integrated authentication".

Now according to the specifications of the login message the server is using SSPI for that. And if I understood the sspi documentation correctly, then SSPI could be use by Kerberos, NTLM or some other authentication protocol.

Now let's say that I want to enforce ODBC to user Kerberos, is it possible to do that by configuring either one of the following components?

SQL Server
Active Directory
ODBC driver

(Hope that my question makes sense? I am really new to the world of Windows authentication and protocols and really confused by all the docs..)

Best Answer

SQL Server will always prefer Kerberos over NTLM, however, NTLM is used as a fallback by SQL Server to ensure connectivity. You cannot disable NTLM authentication at the SQL level, this can only be done at the domain level, however, if you have Kebreros authentication properly configured then SQL Server will use that over NTLM.

See this article for information on properly configuring Kerberos in SQL Server:

https://www.sqlservercentral.com/articles/configuring-kerberos-authentication

Related Solutions

Sql-server – How to get the linked server working using Windows authentication

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation.

The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem.

Your Windows Account must have access to both ServerA and ServerB.
You must not have the setting "Account is sensitive and cannot be delegated."
Both ServerA and ServerB must have their own SPN registered.
The servers must be TCP/IP or named pipes connected.

The SQL Server Books Online article that offers some more detail is "Configuring Linked Servers for Delegation": http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

Sql-server – Cannot register SPN, error 0x80090350, state 4

The error message you're seeing, 0x80090350, is defined as:

c:\util\Err>err 0x80090350

# for hex 0x80090350 / decimal -2146892976 :
  SEC_E_DOWNGRADE_DETECTED                                        winerror.h
# The system detected a possible attempt to compromise
# security.  Please ensure that you can contact the server
# that authenticated you.
# 1 matches found for "0x80090350"

I'm looking this up with the Exchange Error Lookup Tool.

According to the information in this post, this error is often caused by the MaxTokenSize issue caused by an account (indirectly or directly) being a member of a large number of groups.

Another possibility I'd consider is that a duplicate SPN exists. You can determine which SPNs Windows thinks are duplicates by running setspn -X -F (info here).

Best Answer

Related Solutions

Sql-server – How to get the linked server working using Windows authentication

Sql-server – Cannot register SPN, error 0x80090350, state 4

Related Question