Sspi handshake failed with error code 0x8009030c state 14

Ошибка SQL Server: 17806, серьезность: 20, состояние: 14. SSPI рукопожатие не удалось с кодом ошибки 0x8009030c, состояние 14

Описание ошибки:


Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.

Эти ошибки появляются в журналах ошибок SQL.

причины ошибок:

Может быть вызвано несколькими причинами, такими как

1. Эта проблема может быть связана с Kerberos и большинством сообщений об ошибках рукопожатия SSPI из-за сбоя Kerberos. Сбой Kerberos, скорее всего, может быть связан с несуществующим SPN (имя участника службы) или плохим SPN для SQL Server. Обычно локальные соединения работают должным образом, поскольку они используют NTLM, и SPN не требуется для NTLM.

Чтобы подтвердить, что мы являемся аутентификацией Kerberos, мы можем выполнить следующий запрос

SELECT distinct auth_scheme FROM sys.dm_exec_connections

2. Ошибки рукопожатия SSPI могут быть разницей во времени / часах между клиентскими часами и часами сервера

Решение:

1. Перезагрузите неисправный контроллер домена

2. Правильное время между клиентом и сервером

3. Установите SPN вручную правильно для SQL Server, используя ниже

Если мы используем Kerberos, то и клиент, и SQL Server должны находиться в одном домене или доверенных доменах с двунаправленным доверием, установленным между доменами, и имя участника службы (SPN) должно быть зарегистрировано в Active Directory для учетной записи службы SQL Server.

Мы можем проверить, есть ли у учетной записи службы уже SPN, запустив ее на рассматриваемом SQL-сервере.

setspn -l <DomainNameSqlServiceAccount>

Если мы не видим никаких записей, начинающихся с MSSQLSvc, то учетная запись не настроена с SPN.

Мы можем вручную зарегистрировать SPN в качестве администратора домена, используя эти команды.

Чтобы подтвердить, что мы являемся аутентификацией Kerberos, мы можем выполнить следующий запрос

SELECT distinct auth_scheme FROM sys.dm_exec_connections

2. Ошибки рукопожатия SSPI могут быть разницей во времени / часах между клиентскими часами и часами сервера

Для создания SPN для имени NetBIOS SQL Server используйте следующую команду:
setspn-A MSSQLSvc/: 1433 DomainAccount
для создания SPN для полного доменного имени SQL Server используйте следующую команду:
setspn-A MSSQLSvc/: 1433 DomainAccount

Wondering how to fix SQL error 17806? We can help you.

As part of our Server Management Services, we assist our customers with several SQL queries on a daily basis.

Today, let us look into the details of this error and its possible solutions.

SQL error 17806

If we are using Windows authentication to connect to a SQL Server in Windows 2003 and above, we may encounter the following error:

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed.

Error: 17806, Severity: 20, State: 14.

This error can happen due to the Active Directory configuration issue or Network related issues.

In Windows 2003 and above, the default method of establishing a connection is Kerberos. However, if it fails, we can establish a connection using NTLM. This may register on the SQL Server error log due to Kerberos failure.

Cause of SQL error 17806

1. SQL Server engine account running with a Domain service account, and that account locked at the time a Kerberos connection attempt. The connection would work, but it would kick down to NTLM.
2. Failed Kerberos Authentication to connect to SQL Server.
   To confirm if we have any database connection using Kerberos authentication, run:

   SELECT distinct auth_scheme FROM sys.dm_exec_connections

   If we only see NTLM and SQL then, there is no Kerberos connection.
   To use Kerberos, the client and SQL Server must be in the same domain and a Service Principal Name (SPN) must be registered with Active Directory for the SQL Server service account.
   We can check if the service account already has SPN, by running:

   setspn -l 

   If we don’t see anything starting with MSSQLSvc then the account is not set up with SPN.

3. SSPI handshake errors can be Time/Clock difference between the client clock differs server clock.

[Looking for more updates? Contact us now!]

Solutions for SQL error 17806

Let us now focus on some methods our Support Engineers suggest in order to fix this error.

Method 1:

• Go to register start –> run … Regedit
• Go to: HKLMSystemCurrentControlSetControlLSA
• Then add DWORD value “DisableLoopbackCheck”
• Set this value to 1
• Reboot after making this change.

Usually, this will affect registry change.

Method 2:

Restart the SQL browser services.

Method 3:

• Reboot the misbehaving Domain Controller
• Correct Client to Server time
• Create an SPN for SQL server.

We can manually register SPN as a Domain Administrator.

To create an SPN for the NetBIOS name of the SQL Server we use the following command:

setspn –A MSSQLSvc/:1433 

To create an SPN for the FQDN of the SQL Server we use the following command:

setspn -A MSSQLSvc/:1433 

If SQL Server service account grants “Validated write to service principal name” privilege in Active Directory, SQL Server database engine will register itself when it starts and unregisters at shutdown.

If the Domain Administrators do not permit this setting then we should manually register the SPN and the SSPI error will go away.

Also, the SQL Server service account must grant “Account is trusted for delegation” privilege in Active Directory. If the SQL Server connects to another SQL Server using Linked Server then the server must also grant “Trust this computer for delegation to any service” privilege.

For a named instance, we can use the FQDN of the named instance instead of SQL Server FQDN and use the port number.

For a clustered server, we should use the FQDN of the SQL Server virtual name. In this case, we will need two entries, one with port name and one without such as:

setspn -A MSSQLSvc/ 
setspn -A MSSQLSvc/:1433 

Kerberos authentication works when SQL Server, Service Account and Windows account accessing the SQL Server are in the same windows domain.

[Stuck with the error? We’d be happy to assist!]

Conclusion

In short, SQL error 17806 happen due to Active Directory configuration issue or Network issues. Today, we saw some methods our Support Techs found the best fit in order to solve this error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Заметки системного администратора Windows Server

Поиск по этому блогу

Ошибка SQL Server: 17806, серьезность: 20, состояние: 14. SSPI рукопожатие не удалось с кодом ошибки 0x8009030c, состояние 14

• Получить ссылку
• Facebook
• Twitter
• Pinterest
• Электронная почта
• Другие приложения

Описание ошибки:

Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.

Эти ошибки появляются в журналах ошибок SQL.

причины ошибок:

Может быть вызвано несколькими причинами, такими как

1. Эта проблема может быть связана с Kerberos и большинством сообщений об ошибках рукопожатия SSPI из-за сбоя Kerberos. Сбой Kerberos, скорее всего, может быть связан с несуществующим SPN (имя участника службы) или плохим SPN для SQL Server. Обычно локальные соединения работают должным образом, поскольку они используют NTLM, и SPN не требуется для NTLM.

Чтобы подтвердить, что мы являемся аутентификацией Kerberos, мы можем выполнить следующий запрос

SELECT distinct auth_scheme FROM sys.dm_exec_connections

2. Ошибки рукопожатия SSPI могут быть разницей во времени / часах между клиентскими часами и часами сервера

Решение:

1. Перезагрузите неисправный контроллер домена

2. Правильное время между клиентом и сервером

3. Установите SPN вручную правильно для SQL Server, используя ниже

Если мы используем Kerberos, то и клиент, и SQL Server должны находиться в одном домене или доверенных доменах с двунаправленным доверием, установленным между доменами, и имя участника службы (SPN) должно быть зарегистрировано в Active Directory для учетной записи службы SQL Server.

Мы можем проверить, есть ли у учетной записи службы уже SPN, запустив ее на рассматриваемом SQL-сервере.

Если мы не видим никаких записей, начинающихся с MSSQLSvc, то учетная запись не настроена с SPN.

Мы можем вручную зарегистрировать SPN в качестве администратора домена, используя эти команды.

Чтобы подтвердить, что мы являемся аутентификацией Kerberos, мы можем выполнить следующий запрос

SELECT distinct auth_scheme FROM sys.dm_exec_connections

2. Ошибки рукопожатия SSPI могут быть разницей во времени / часах между клиентскими часами и часами сервера

Для создания SPN для имени NetBIOS SQL Server используйте следующую команду:
setspn-A MSSQLSvc/: 1433 DomainAccount
для создания SPN для полного доменного имени SQL Server используйте следующую команду:
setspn-A MSSQLSvc/: 1433 DomainAccount

Источник

Sspi handshake failed with error code 0x8009030c

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Hello, I have a sql 2005 server, and I am a developer, with the database on my own machine. It alwayws works for me but after some minutes the other developer cant work in the application

He got this error

Login failed for user ». The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.140]

and When I see the log event after that error, it comes with another error.

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.140]

He has IIS5 and me too.

I created a user on the domain called ASPSYS with password, then in the IIS on anonymous authentication I put that user with that password, and it works, on both machines.

and in the connection string I have.

add key = » sqlconn » value = » Data Source=ESTACION15;Initial Catalog=GescomDefinitiva;Integrated Security=SSPI; Trusted_Connection=true »/>

I go to the profiler, and I see that when he browses a page, the database is accesed with user ASPSYS, but when I browse a page, the database is accesed with user SElevalencia.

The only way that the other developer can work again on the project is to restart the whole machine. He has windows xp profession, I have windows 2000.

Источник

Sspi handshake failed with error code 0x8009030c

you should be able to crack the issue.

Please click the Mark as answer button and vote as helpful if this reply solves your problem

I know this is an old thread, under T-SQL, but I recently had the same message so I am offering this. I feel it is appropriate because this thread was one of the first that showed up in my search engine.

Every minute, exactly one minute apart, I was getting «SSPI handshake failed with error code 0x8009030c, state 14. «. I knew why it was failing, but not what was causing it to fail. Here were the circumstances:

• We set up a server
• Installed SQL 2008R2, including Reporting Services.
• After testing we: renamed the server, used T-SQL to change %%SERVERNAME.

Renaming the server was obviously the problem, but we thought it was failing to update the underlying data. The solution was so simple I missed it, as other forum advice was more focused on going «in the weeds» of SQL.

I had to use the reporting services configuration manager to change the server name there, too. After reconfiguring, the «SSPI handshake» error stopped completely.

Источник

Sspi handshake failed with error code 0x8009030c

you should be able to crack the issue.

Please click the Mark as answer button and vote as helpful if this reply solves your problem

I know this is an old thread, under T-SQL, but I recently had the same message so I am offering this. I feel it is appropriate because this thread was one of the first that showed up in my search engine.

Every minute, exactly one minute apart, I was getting «SSPI handshake failed with error code 0x8009030c, state 14. «. I knew why it was failing, but not what was causing it to fail. Here were the circumstances:

• We set up a server
• Installed SQL 2008R2, including Reporting Services.
• After testing we: renamed the server, used T-SQL to change %%SERVERNAME.

Renaming the server was obviously the problem, but we thought it was failing to update the underlying data. The solution was so simple I missed it, as other forum advice was more focused on going «in the weeds» of SQL.

I had to use the reporting services configuration manager to change the server name there, too. After reconfiguring, the «SSPI handshake» error stopped completely.

Источник

Sspi handshake failed with error code 0x8009030c

you should be able to crack the issue.

Please click the Mark as answer button and vote as helpful if this reply solves your problem

I know this is an old thread, under T-SQL, but I recently had the same message so I am offering this. I feel it is appropriate because this thread was one of the first that showed up in my search engine.

Every minute, exactly one minute apart, I was getting «SSPI handshake failed with error code 0x8009030c, state 14. «. I knew why it was failing, but not what was causing it to fail. Here were the circumstances:

• We set up a server
• Installed SQL 2008R2, including Reporting Services.
• After testing we: renamed the server, used T-SQL to change %%SERVERNAME.

Renaming the server was obviously the problem, but we thought it was failing to update the underlying data. The solution was so simple I missed it, as other forum advice was more focused on going «in the weeds» of SQL.

I had to use the reporting services configuration manager to change the server name there, too. After reconfiguring, the «SSPI handshake» error stopped completely.

Источник

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed

I came across a scenario where a more unusual “SSPI Handshake errors” regularly appeared in the error log. In this blog, I am covering the cause of this issue and the solution we followed to fix it:

In the Sql Server error log the below entries was logged:

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: XX.XX.XXX.XX]

Error: 17806, Severity: 20, State: 14.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: XX.XX.XXX.XX]

After checking the web.configs on the webserver with the above IP adress I found the C# application that was the cause of the error. First I tought this would be a easy fix with a simple security policy error. But after verifying the account’s «domainsqlaccount» had «Access this computer from the network” in «Local security policy (secpol.msc)». I was out of ideas.

Online I got the tips to change the faulty connection string on data source to use SQL authentication instead of windows authentication. But that is not good enough solution.

After falling back to my old wisdom. RTFEM — Read the Fucking Error Message. I got a hint from untrusted domain.

It was an issue when using DNS aliases to connect to a machine using a different domain name.

For example, if you have a SQL server on called: SqlServerAlfa on yourdomain.com — which is an Active Directory domain and another one: yourdomain.net. Maybe this is a left over from an earlier naming convention standard that you used or a merger.  To keep your old applications working when migrating to the new naming convention you have set up a DNS alias (CNAME) record for

database.yourdomain.net —> sqlserveralfa.yourdomain.com

You’ll be able to connect to sqlserveralfa.yourdomain.com using Windows integrated security but won’t be able to connect to database.yourdomain.net even though it’s the same server because the domain name doesn’t match your AD domain! This will cause the error in the error log.