SQL 2008,R2 SSPI handshake failed with error code 0x8009030c

I was faced today with the issue when trying to create an ODBC connection from an Application server to the SQL server.

When using Windows Authentication to access the SQL I got an error of “sql Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.”

Looking at the Application log I got the below

“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”

Now snooping around google I can see that this is caused by various reasons

1- Incorrect SPNs registered to the SQL Server

So performing SETPSPN – L Servername

Which looked fine to me, but if it doesn’t in your case delete the MSSQLSVC SPNs and restart SQL services and it should get re-registered automatically.

I returned to the Application Log on the SQL and now I could see the following

“Login failed. The login is from an untrusted domain and cannot be used with Windows authentication”

I verified that the FQDN of the Machines is infact the expected as others reported that this could be an issue

Now continuing to look for issues, I found this article http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452

As it suggests at the end

“

Check your local security policies to see if any essential rights have been denied.

Try to connect to a share on the server. If connecting to a share fails, the account may not have "access this computer from the network" rights or may be missing other domain or network level permissions.”

 

I tried to make a network share access to verify the connectivity between machines and I was UNABLE to do that.

Running an RSOP (Resultant Set of Policies) report showed that the “Access From network” Setting was locked out to a single user.

So I removed it and refreshed the GP and now I can access the share.

So going again to the ODBC, and it Worked perfectly fine.

Hope this helps out.

Installing SQL 2008 R2 Cluster Step-By-Step

Article Moved to my new blog

http://hanygeorge.com/blog/installing-sql-2008-r2-cluster-step-by-step/

SQL 2008 R2 Installation Error

I was faced by this issue and wanted to share

While installing SQL 2008 R2 midway through the install I got this

Updating permission setting for folder ‘C:\Program Files\Microsoft SQL Server\100\DTS\’ failed. The folder permission setting were supposed to be set to ‘D:(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;0x1200a9;;;BU)’.

Looking at this looks like Permission Issue

So first things first I made sure the account I was installing with had the proper rights to do so and that Install was run as an administrator, but none of that was the issue

I re-installed the OS and SQL but still no GO.

Now this has failed at SSIS packages, looking at the below from Microsoft, which explains the necessary permissions for the service accounts

You can see it requires the permission to write to application log

That made me think that since this is a new installation it had to be some policy on the Group Policy, looking at this

which is Under Computer Configuration / Windows Settings / User Rights Assignment / Manage Auditing and Security Log

You can see that only Exchange Servers have the write right to the Event Log

So I added the account I am using to install SQL and Guess what the error was GONE and SQL installed successfully