Now I had to fight with a previously unknown “login not possible” problem: Error: 17832, Severity: 20, State: 8 on a SQL Server 2008 R2. Also, the error message in the SQL server log I had not seen so far, let alone heard of it.
The login packet used to open the connection is structurally invalid; the connection has been closed. statement The SQL Server computer could not process the client logon packet. This may be because the package was not created properly or the package was damaged during the transfer. It may also be caused by the configuration of the SQL Server computer. The listed IP address is the address of the client computer.
Login not possible with many causes
However, let’s start at the beginning… Customer reports that he can not log in to his SQL Server, but on the test environment, but not on the production environment. A look into the SQL Server Log showed that the user used does not fit.
I briefly checked, which possibilities exist for this user or how the user is set up on the SQL server, to understand why a login is not possible. Called the customer and let me explain directly what he does and how exactly his problem occurs.
Since the customer had several users to choose from, and there was only a login problem for his user… I took a closer look at the SQL Server. The first thing that struck me seriously was the SQL Server version 10.50.2500 so only Service Pack 1 (the edition – but some customers want it that way): “This is certainly due to the age of release,” so the server brought a much more recent state (10.50.6529 – MS15-058 -Security Update for SQL Server 2008 R2), the customer informed, he may please test it again.
After some test, I received the feedback: “No, it still does not work. However, now the customer has received another error message…” So I looked into the log again and found that still, the login is not possible, but a new error message has appended.
Error: 17832, Severity: 20, State: 8 – The login packet used to open the connection is structurally invalid
Well, quickly asked Google and found the following… the mistake is longly known and well documented. The following text can be found on the Microsoft pages:
When using Windows Authentication in a Kerberos environment, a client receives a Kerberos ticket that contains a Privilege Attribute Certificate (PAC). The PAC contains various types of authorization data including groups that the user is a member of, rights the user has, and what policies apply to the user. When the client receives the Kerberos ticket, the information contained in the PAC is used to generate the user’s access token. The client presents the token to the SQL Server computer as part of the login packet.
If the token was improperly created or damaged during transmission, SQL Server cannot offer additional information about the problem.
When the user is a member of many groups or has many policies, the token may grow larger than normal to list them all. If the token grows larger than the MaxTokenSize value of the server computer, the client fails to connect with a General Network Error (GNE) and error 17832 can occur. This problem may affect only some users: users with many groups or policies. When the problem is the MaxTokenSize value of the server computer, error 17832 in the SQL Server error log will be accompanied by an error with state 9. For additional details about the Kerberos and MaxTokenSize, see KB327825.
So there I found not only an explanation but of course a solution! 😉
To change the MaxTokenSize on the server computer
- On the Start menu, click Run.
- Type regedit, and then click OK. (If the User Account Control dialog box appears, click Continue.)
- Navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters.
- If the MaxTokenSize parameter is not present, right-click Parameters, point to New, and then click DWORD (32-bit) Value. Name the registry entry MaxTokenSize.
- Right-click MaxTokenSize, and then click Modify.
- In the Value data box type the desired MaxTokenSize value.
- Click OK.
- Close Registry Editor.
- Restart the computer.
Now my customer could test again … Unfortunately, he was still unable to submit a success message, but not because the login failed on the SQL server, but because his SQL statement had a syntax error within the application. Chackaa… so found bug, SQL Server successfully patched, SQL Server login missing fixed (new situation/message met), making customers happy. 😉
So the customer or his user seems to be nested in many domain groups and group policies, so the Kerberos token would have to take too much information, but this can not because the value for MaxTokenSize was undefined or too small. Here only the restrictions on the target server help to expand or to define completely. I immediately pushed it to the max and set the value to “ffff”.
Björn works in Hamburg as Senior Consultant – Microsoft Data Platform and Cloud at Kramer & Crew. He regularly participates in the PASS regional group meeting in Hamburg, the events of the PASS such as SQLSaturday and DataGrillen and organises the Azure Meetup in Hamburg. He is interested in topics such as SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.